最近MySQL的筆記有點多了，主要是公司Oracle比較穩(wěn)定維護較少，上周被安排做了一個MySQL億級數(shù)據(jù)的遷移，趁此記錄下學習筆記；

數(shù)據(jù)遷移，工作原理和技術支持數(shù)據(jù)導出、BI報表之類的相似，差異較大的地方是導入和導出數(shù)據(jù)量區(qū)別，一般報表數(shù)據(jù)量不會超過幾百萬，而做數(shù)據(jù)遷移，如果是互聯(lián)網企業(yè)經常會涉及到千萬級、億級以上的數(shù)據(jù)量。

導入和導出是兩個過程，即使做數(shù)據(jù)遷移我們也要分開來看，同時，導入/導出方式又分為：

1、MySQL自帶導入/導出方式

2、各類客戶端導入/導出方式

先總結下導出：

1、導出對于字段較少/字段內容較少的數(shù)據(jù)，通過客戶端方式可以采用navicat等工具導出，我這里本次導出三個字段，都是11位數(shù)字以內的值，用navicat導出每分鐘大約250萬數(shù)據(jù)，

2、MySQL自帶的導出語句：select into outfile語句；

SELECT ... FROM TABLE_A --可以加where條件
INTO OUTFILE "/path/to/file" --導出文件位置
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- 字段分割符和包含符
LINES TERMINATED BY '\n';--換行符

這里fields之前很簡單都可看懂，不做說明，講下fields之后的：

FIELDS TERMINATED BY ',' 代表我字段和字段之間用 逗號 分開 ，如：字段 A 字段 B，導出時候顯示格式為：A,B

OPTIONALLY ENCLOSED BY '"' 代表字段內容用雙引號包含，導出格式如： "A","B"

LINES TERMINATED BY '\n';每條數(shù)據(jù)換行區(qū)分，導出格式如：

"A","B"

"A1","B1"

當然，字段區(qū)分和包含符號可以自行定義，定義為：'    #  都可以

用MySQL自帶導出/導入優(yōu)點是速度極快，缺點是：只能導出文件是在服務器主機所在的本機地址，對于bi之類拿到不數(shù)據(jù)庫主機權限的同事這個方式可能奢望了。不過好在對于字段/內容較少的報表第三方客戶端工具導出速度也不算特別慢；

導入：

重點記錄導入，導入主要是dba做數(shù)據(jù)遷移了，方式也分客戶端和MySQL自帶方式：

這里極度推薦用MySQL導入方式，原因是我之前要遷移1.3億數(shù)據(jù)，用navicat客戶端導入數(shù)據(jù)要22小時，耗時太長且不確定太多，本身navicat等工具就會有假死風險的存在，所不建議超過1萬以上的數(shù)據(jù)通過navicat導入；

MySQL自帶導入方式：

--官方文檔定義如下，注釋是我自己理解添加的：

LOAD DATA 、
[LOW_PRIORITY | CONCURRENT]--無人使用數(shù)據(jù)庫再執(zhí)行/立即執(zhí)行
[LOCAL]--帶這個參數(shù)指服務端即不是服務器主機上讀取文件，不帶這個參數(shù)是默認在服務器主機上讀取文件
INFILE 'file_name' --讀取文件地址、文件名
    [REPLACE | IGNORE]--遇到重復數(shù)據(jù)是：替換/重復寫入，建議使用ignore重復寫入
    INTO TABLE tbl_name --導入到那個表
    [PARTITION (partition_name [, partition_name] ...)]--這行參數(shù)可以不要，建議用后面的fields
    [CHARACTER SET charset_name]--設定導入內容字符格式，utf-8還是GBK等都可以指定
 
 
    [{FIELDS | COLUMNS}  --fields標識符
        [TERMINATED BY 'string'] --系統(tǒng)字段通過什么符號區(qū)分
        [[OPTIONALLY] ENCLOSED BY 'char']--系統(tǒng)字段本身的起始和結束用什么符號區(qū)分
        [ESCAPED BY 'char']--轉義符，如果是文本文件，在文本字段中有特殊字符如雙引號，可通過定義轉義符忽略文本文件特殊字符
    ]
    [LINES  --lines標識符
        [STARTING BY 'string'] --定義行開頭的字符串，如果行開頭沒有字符標識，一般可以不寫
        [TERMINATED BY 'string']--行結束字符串標識，通過定義字符來區(qū)分行與行的數(shù)據(jù)
    ]
    [IGNORE number {LINES | ROWS}]--忽略文件中前面多少行，一般都不寫
--后面都是指定插入到哪些字段
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

原文上說的用load data能速度極快的導入數(shù)據(jù)到數(shù)據(jù)庫中，但是如果要使用fields和lines參數(shù)，則必須要帶一個參數(shù)值且fields必須在lines參數(shù)之前；

本次我使用的語句是：

load data infile '/data/files/T_CUST_INFO.txt' --默認指定服務器文件夾
ignore into table t_dq_user --允許重復記錄插入
fields terminated by ','  --判斷字段通過逗號標識來分隔開
lines terminated by '\n'(CustID,DeviceNo,logintype);--通過換行標識來解析成為每一條數(shù)據(jù)和插入到我指定的字段

插入是很簡單的語句，這里我不做具體舉例操作，我要分享的是如何提高插入效率；

因為在我第一次用語句插入的時候，從晚上12點開始執(zhí)行，到第二天11點還沒有執(zhí)行完。所以不是說用了load不配置其他的就一定很快；

本次我插入的數(shù)據(jù)格式如圖：

文本格式如下：

一共有1億4千萬條數(shù)據(jù)，以文本文檔的形式導出的，有4.3G大??；通過ftp軟件上傳到服務器/data/files文件夾中；

吐槽點1：

由于項目要求三個字段都要有索引，所以我在建表的時候就加了索引，導致耗時遙遙無期；

原因：

索引是要占空間的，如果導入三個字段都要加索引，代表了我要每個字段都寫入索引一次，耗時比不加索引多了幾倍；

優(yōu)化方法：

導入前把表的索引去掉留自增id一個，等導入完之后再添加

吐槽點2：

engine的選擇：

MySQL的engine對如load寫入是不一樣的，特別是有master-slave主從備份的機制：

      對MyISAM引擎： 
          (1)對master服務器進行  ‘load' 操作， 
          (2)在master上所操作的load.txt文件，會同步傳輸?shù)絪lave上，并在tmp_dir 目錄下生成 load.txt文件 
               master服務器插入了多少，就傳給slave多少 
          (3)當master上的load操作完成后，傳給slave的文件也結束時， 
               即：在slave上生成完整的 load.txt文件 
               此時，slave才開始從 load.txt 讀取數(shù)據(jù)，并將數(shù)據(jù)插入到本地的表中 

      對innodb引擎： 
          (1)主數(shù)據(jù)庫進行  ‘Load' 操作 
          (2)主數(shù)據(jù)庫操作完成后，才開始向slave傳輸 load.txt文件， 
               slave接受文件，并在 tmp_dir 目錄下生成 load.txt 文件 
               接受并生成完整的load.txt 后，才開始讀取該文件，并將數(shù)據(jù)插入到本地表中 

所以追求極致速度，幾十億數(shù)據(jù)的，可以考慮選擇myisam引擎，MySQL默認應該是innodb；不過本次我并沒有更改引擎，本人不推薦更改默認的innodb引擎，畢竟Oracle官方主推引擎，綜合性最強，除非有特殊性，不推薦使用myisam。如果用了myisam，注意一下兩點：

用了myisam，可以調整幾個session值擴大讀取內存，提高讀取數(shù)據(jù)，語句如下：

SET SESSION BULK_INSERT_BUFFER_SIZE = 256217728 ;
SET SESSION MYISAM_SORT_BUFFER_SIZE = 256217728 ; 

對于MyISAM引擎，導入前的唯一校驗可以先關閉，之后再打開：

SET UNIQUE_CHECKS=0 --關閉

SET UNIQUE_CHECKS=1 --打開

吐槽點3：

雖然MySQL支持本地客戶端讀取文件，但是由于各種網絡原因，在幾十幾百條數(shù)據(jù)的情況下沒有什么影響，但是到了億級數(shù)據(jù)量，即使1毫秒的影響也會放的特別大，所以建議用ftp傳到服務器上進行讀取

吐槽點4：

經驗分享，導入之后看看服務器狀態(tài)：top 命令看看主機cpu MySQL占用情況，理論上會占用較多cpu，我的第一次耗時賊長的那次，cpu占用10%，這是極度不正常的導入，第二次正常導入cpu占用到了110%，這才是再急速寫入的狀態(tài)；最后1.4億數(shù)據(jù)只耗時：7分多中，所以一定要在執(zhí)行語句后監(jiān)控下服務器，否則語句不一定在正常執(zhí)行。

cpu占用：

注意：load和insert最大的區(qū)別是：load只操作語法一次，之后就是一直是數(shù)據(jù)批量插入，而insert 是每一個數(shù)據(jù)操作一次，也遍歷一次字段索引，所以insert本身對于大數(shù)據(jù)來說是極慢的。

總結：

本次優(yōu)化我感覺最大最明顯的變化是，去除索引后，導入速度極快，索引，重要的事情再說一遍：

導入時候可以先去掉索引，導入完之后再添加。

2020.7.3更新

MySQL導入大數(shù)據(jù)時一定要注意max最大事物限制，前幾個月在做數(shù)據(jù)遷移時，在MySQL8.0 MGR集群上發(fā)生了大事物限制導致實例出問題重啟了MySQL，默認配置應該是一億五千萬的事物限制，當時導入的數(shù)據(jù)比較大也沒做參數(shù)擴展同時也沒做數(shù)據(jù)切分導入或者限流導入，導致數(shù)據(jù)庫堵塞重啟，按照公司要求7*24*365機制，這算是事故了，如果高要求的公司，建議導入的時候注意MySQL本身配置或者導入進行事物提交限制；

到此這篇關于MySQL 億級數(shù)據(jù)導入導出及遷移筆記的文章就介紹到這了,更多相關MySQL 億級數(shù)據(jù)導入導出及遷移內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！