MySQL 億級數據導入導出及遷移筆記

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

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

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

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

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

先總結下導出：

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

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’;每條數據換行區分，導出格式如：

“A”,”B”

“A1″,”B1”

當然，字段區分和包含符號可以自行定義，定義為：’    #  都可以

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

導入：

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

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

MySQL自帶導入方式：

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

LOAD DATA 、
[LOW_PRIORITY | CONCURRENT]--無人使用數據庫再執行/立即執行
[LOCAL]--帶這個參數指服務端即不是服務器主機上讀取文件，不帶這個參數是默認在服務器主機上讀取文件
INFILE 'file_name' --讀取文件地址、文件名
    [REPLACE | IGNORE]--遇到重復數據是：替換/重復寫入，建議使用ignore重復寫入
    INTO TABLE tbl_name --導入到那個表
    [PARTITION (partition_name [, partition_name] ...)]--這行參數可以不要，建議用後面的fields
    [CHARACTER SET charset_name]--設定導入內容字符格式，utf-8還是GBK等都可以指定
 
 
    [{FIELDS | COLUMNS}  --fields標識符
        [TERMINATED BY 'string'] --系統字段通過什麼符號區分
        [[OPTIONALLY] ENCLOSED BY 'char']--系統字段本身的起始和結束用什麼符號區分
        [ESCAPED BY 'char']--轉義符，如果是文本文件，在文本字段中有特殊字符如雙引號，可通過定義轉義符忽略文本文件特殊字符
    ]
    [LINES  --lines標識符
        [STARTING BY 'string'] --定義行開頭的字符串，如果行開頭沒有字符標識，一般可以不寫
        [TERMINATED BY 'string']--行結束字符串標識，通過定義字符來區分行與行的數據
    ]
    [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能速度極快的導入數據到數據庫中，但是如果要使用fields和lines參數，則必須要帶一個參數值且fields必須在lines參數之前；

本次我使用的語句是：

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);–通過換行標識來解析成為每一條數據和插入到我指定的字段

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

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

本次我插入的數據格式如圖：

文本格式如下：

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

吐槽點1：

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

原因：

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

優化方法：

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

吐槽點2：

engine的選擇：

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

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

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

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

用瞭myisam，可以調整幾個session值擴大讀取內存，提高讀取數據，語句如下：

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支持本地客戶端讀取文件，但是由於各種網絡原因，在幾十幾百條數據的情況下沒有什麼影響，但是到瞭億級數據量，即使1毫秒的影響也會放的特別大，所以建議用ftp傳到服務器上進行讀取

吐槽點4：

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

cpu占用：

註意：load和insert最大的區別是：load隻操作語法一次，之後就是一直是數據批量插入，而insert 是每一個數據操作一次，也遍歷一次字段索引，所以insert本身對於大數據來說是極慢的。

總結：

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

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

2020.7.3更新

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

到此這篇關於MySQL 億級數據導入導出及遷移筆記的文章就介紹到這瞭,更多相關MySQL 億級數據導入導出及遷移內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• mysql 數據插入優化方法之concurrent_insert
• mysql優化之query_cache_limit參數說明
• Mysql如何導出篩選數據並導出帶表頭的csv文件
• hibernate 配置數據庫方言的實現方法
• 詳解MySQL數據庫千萬級數據查詢和存儲