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

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

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

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

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

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

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

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

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

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

這里fields之前很簡(jiǎn)單都可看懂，不做說(shuō)明，講下fields之后的：

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

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

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

"A","B"

"A1","B1"

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

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

導(dǎo)入：

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

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

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

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

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

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

本次我使用的語(yǔ)句是：

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

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

因?yàn)樵谖业谝淮斡谜Z(yǔ)句插入的時(shí)候，從晚上12點(diǎn)開(kāi)始執(zhí)行，到第二天11點(diǎn)還沒(méi)有執(zhí)行完。所以不是說(shuō)用了load不配置其他的就一定很快；

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

文本格式如下：

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

吐槽點(diǎn)1：

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

原因：

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

優(yōu)化方法：

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

吐槽點(diǎn)2：

engine的選擇：

MySQL的engine對(duì)如load寫(xiě)入是不一樣的，特別是有master-slave主從備份的機(jī)制：

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

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

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

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

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

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

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

SET UNIQUE_CHECKS=1 --打開(kāi)

吐槽點(diǎn)3：

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

吐槽點(diǎn)4：

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

cpu占用：

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

總結(jié)：

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

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

2020.7.3更新

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

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