最近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ì)超過幾百萬(wàn)，而做數(shù)據(jù)遷移，如果是互聯(lián)網(wǎng)企業(yè)經(jīng)常會(huì)涉及到千萬(wàn)級(jí)、億級(jí)以上的數(shù)據(jù)量。

導(dǎo)入和導(dǎo)出是兩個(gè)過程，即使做數(shù)據(jù)遷移我們也要分開來看，同時(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ù)，通過客戶端方式可以采用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)單都可看懂，不做說明，講下fields之后的：

FIELDS TERMINATED BY ’,’ 代表我字段和字段之間用 逗號(hào) 分開 ，如：字段 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è)方式可能奢望了。不過好在對(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)的存在，所不建議超過1萬(wàn)以上的數(shù)據(jù)通過navicat導(dǎo)入；

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

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

LOAD DATA 、[LOW_PRIORITY | CONCURRENT]--無人使用數(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ù)寫入，建議使用ignore重復(fù)寫入 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)字段通過什么符號(hào)區(qū)分[[OPTIONALLY] ENCLOSED BY ’char’]--系統(tǒng)字段本身的起始和結(jié)束用什么符號(hào)區(qū)分[ESCAPED BY ’char’]--轉(zhuǎn)義符，如果是文本文件，在文本字段中有特殊字符如雙引號(hào)，可通過定義轉(zhuǎn)義符忽略文本文件特殊字符 ] [LINES --lines標(biāo)識(shí)符[STARTING BY ’string’] --定義行開頭的字符串，如果行開頭沒有字符標(biāo)識(shí)，一般可以不寫[TERMINATED BY ’string’]--行結(jié)束字符串標(biāo)識(shí)，通過定義字符來區(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ù)庫(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 ’,’ --判斷字段通過逗號(hào)標(biāo)識(shí)來分隔開lines terminated by ’n’(CustID,DeviceNo,logintype);--通過換行標(biāo)識(shí)來解析成為每一條數(shù)據(jù)和插入到我指定的字段

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

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

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

文本格式如下：

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

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

原因：

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

優(yōu)化方法：

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

engine的選擇：

MySQL的engine對(duì)如load寫入是不一樣的，特別是有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才開始從 load.txt 讀取數(shù)據(jù)，并將數(shù)據(jù)插入到本地的表中

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

所以追求極致速度，幾十億數(shù)據(jù)的，可以考慮選擇myisam引擎，MySQL默認(rèn)應(yīng)該是innodb；不過本次我并沒有更改引擎，本人不推薦更改默認(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)閉，之后再打開：

SET UNIQUE_CHECKS=0 --關(guān)閉SET UNIQUE_CHECKS=1 --打開吐槽點(diǎn)3：

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

經(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%，這才是再急速寫入的狀態(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ù)來說是極慢的。

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

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

MySQL導(dǎo)入大數(shù)據(jù)時(shí)一定要注意max最大事物限制，前幾個(gè)月在做數(shù)據(jù)遷移時(shí)，在MySQL8.0 MGR集群上發(fā)生了大事物限制導(dǎo)致實(shí)例出問題重啟了MySQL，默認(rèn)配置應(yīng)該是一億五千萬(wàn)的事物限制，當(dāng)時(shí)導(dǎo)入的數(shù)據(jù)比較大也沒做參數(shù)擴(kuò)展同時(shí)也沒做數(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)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！