前言

假設(shè)現(xiàn)在我們要向mysql插入500萬條數(shù)據(jù)，如何實(shí)現(xiàn)高效快速的插入進(jìn)去叶骨？暫時不考慮數(shù)據(jù)的獲取茫多、網(wǎng)絡(luò)I/O、以及是否跨機(jī)操作忽刽，本文將在本地進(jìn)行數(shù)據(jù)的插入天揖，單純從mysql入手，把優(yōu)化到底跪帝。

1.生成sql文件

首先我們來生成500萬條inster的sql文件今膊，圖1為生成sql文件的程序，運(yùn)行大概需要25s左右伞剑，采用Python3實(shí)現(xiàn)斑唬，生成的文件如圖2。

圖1

圖2

2.進(jìn)行sql插入

這里我寫了個簡單的sh，進(jìn)行mysql的導(dǎo)入,請看圖3恕刘，我里面的密碼用的環(huán)境變量缤谎，大家寫的時候?qū)懽约旱拿艽a就行，經(jīng)過了漫長的運(yùn)行褐着，才插入完成弓千，請看圖4，讓人吐血的運(yùn)行時間献起。

圖3

圖4

看到圖4洋访，運(yùn)行了兩個多小時，才插入了500萬條數(shù)據(jù)??????谴餐，顯然是不能接受的姻政，如果我要插入三五千萬條數(shù)據(jù)豈非要睡上好幾覺了。優(yōu)化必須走起來.....

500萬條數(shù)據(jù)岂嗓，頻繁的磁盤I/O操作汁展，插入效率緩如蝸牛。我們來試試批量插入厌殉，先來減少磁盤I/O操作食绿。

3.生成批量插入sql文件（Python3）

在這里，我的批量插入式一次性插入1000條數(shù)據(jù)公罕，inster進(jìn)行5000次就行器紧，相當(dāng)于5000次I/O操作，比第一次的操作數(shù) 楼眷，大大降低铲汪，來看圖5-6，為生成的文件罐柳。

圖5

圖6

4.批量sql插入

為保證盡可能的準(zhǔn)確性掌腰，兩次插入的表結(jié)構(gòu)，類型及內(nèi)容都一致张吉。

圖7

看到圖7的運(yùn)行時間齿梁，才花了41秒，就插入了500萬條數(shù)據(jù)肮蛹，性能提升了近200倍左右勺择，性能達(dá)到了量級提升。優(yōu)化繼續(xù)在路上.....

之前看到mysql的引擎對比蔗崎，說在頻繁批量插入時酵幕，MyIASM引擎比InnoDB引擎性能更好。我們來試試看??????.....

5.更換引擎

圖8

看到圖8缓苛，我這邊默認(rèn)的引擎還是InnoDB芳撒。

如圖9邓深，我們執(zhí)行命令：alter table batch_jq engine=MYISAM;進(jìn)行更改引擎。（小知識點(diǎn)：mysql終端想清屏笔刹，可以使用system clear命令）

圖9

圖10

如圖10芥备，更改引擎后，只用了25秒就插入了500萬條數(shù)據(jù)舌菜，性能又有了一個新的提升萌壳。我們在數(shù)據(jù)插入完成后，再將引擎更改回InnoDB即可日月。

優(yōu)化在路上....

之后會出一篇針對海量數(shù)據(jù)的sql優(yōu)化袱瓮，準(zhǔn)備階段，敬請期待０А３呓琛！

擴(kuò)展：

（1）如若插入海量數(shù)據(jù)精拟，建議可以先不考慮建立索引燎斩，因為索引也是需要維護(hù)的，會降低插入性能蜂绎，可以等插入完成后栅表，再去建立索引。如若是MyISAM师枣，可以忽略怪瓶，因其延遲更新索引的特性，可以使插入性能大大提升（上述例子兩個表坛吁，均未建立索引）劳殖。

（2）MySQL為了保證ACID中的一致性和持久性，使用了WAL拨脉。

Redo log就是一種WAL的應(yīng)用。當(dāng)數(shù)據(jù)庫忽然掉電宣增，再重新啟動時玫膀，MySQL可以通過Redo log還原數(shù)據(jù)。也就是說爹脾，每次事務(wù)提交時帖旨，不用同步刷新磁盤數(shù)據(jù)文件，只需要同步刷新Redo log就足夠了灵妨。相比寫數(shù)據(jù)文件時的隨機(jī)IO解阅，寫Redo log時的順序IO能夠提高事務(wù)提交速度。

在沒有開啟binlog時泌霍，Redo log的刷盤操作將會是最終影響MySQL TPS的瓶頸所在货抄。為了緩解這一問題，MySQL使用了組提交，將多個刷盤操作合并成一個蟹地，如果說10個事務(wù)依次排隊刷盤的時間成本是10积暖，那么將這10個事務(wù)一次性一起刷盤的時間成本則近似于1。

有什么問題請留言怪与，大家一起探討學(xué)習(xí)??????夺刑。