MySQL 億級數(shù)據(jù)導(dǎo)入導(dǎo)出及遷移筆記

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

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

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

1、MySQL自帶導(dǎo)入/導(dǎo)出方式

2、各類客戶端導(dǎo)入/導(dǎo)出方式

先總結(jié)下導(dǎo)出：

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

2、MySQL自帶的導(dǎo)出語句：select into outfile語句；

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

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

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

OPTIONALLY ENCLOSED BY '"' 代表字段內(nèi)容用雙引號包含，導(dǎo)出格式如： "A","B"

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

"A","B"

"A1","B1"

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

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

導(dǎo)入：

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

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

MySQL自帶導(dǎo)入方式：

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

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

本次我使用的語句是：

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

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

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

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

文本格式如下：

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

吐槽點1：

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

原因：

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

優(yōu)化方法：

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

吐槽點2：

engine的選擇：

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

      對MyISAM引擎： 
          (1)對master服務(wù)器進行  ‘load' 操作， 
          (2)在master上所操作的load.txt文件，會同步傳輸?shù)絪lave上，并在tmp_dir 目錄下生成 load.txt文件 
               master服務(wù)器插入了多少，就傳給slave多少 
          (3)當master上的load操作完成后，傳給slave的文件也結(jié)束時， 
               即：在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默認應(yīng)該是innodb；不過本次我并沒有更改引擎，本人不推薦更改默認的innodb引擎，畢竟Oracle官方主推引擎，綜合性最強，除非有特殊性，不推薦使用myisam。如果用了myisam，注意一下兩點：

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

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

對于MyISAM引擎，導(dǎo)入前的唯一校驗可以先關(guān)閉，之后再打開：

SET UNIQUE_CHECKS=0 --關(guān)閉

SET UNIQUE_CHECKS=1 --打開

吐槽點3：

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

吐槽點4：

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

cpu占用：

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

總結(jié)：

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

導(dǎo)入時候可以先去掉索引，導(dǎo)入完之后再添加。

2020.7.3更新

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

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