前言

假設(shè)現(xiàn)在我們要向MySQL插入500萬條數(shù)據(jù)，如何實(shí)現(xiàn)高效快速的插入進(jìn)去？暫時(shí)不考慮數(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插入

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

圖3

圖4

看到圖4，運(yùn)行了兩個(gè)多小時(shí)，才插入了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)行時(shí)間，才花了41秒，就插入了500萬條數(shù)據(jù)，性能提升了近200倍左右，性能達(dá)到了量級(jí)提升。優(yōu)化繼續(xù)在路上.....

之前看到mysql的引擎對(duì)比，說在頻繁批量插入時(shí)，MyIASM引擎比InnoDB引擎性能更好。我們來試試看.....

5.更換引擎

圖8

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

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

圖9

圖10

如圖10，更改引擎后，只用了25秒就插入了500萬條數(shù)據(jù)，性能又有了一個(gè)新的提升。我們?cè)跀?shù)據(jù)插入完成后，再將引擎更改回InnoDB即可。

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

之后會(huì)出一篇針對(duì)海量數(shù)據(jù)的sql優(yōu)化，準(zhǔn)備階段，敬請(qǐng)期待！！！

擴(kuò)展：

（1）如若插入海量數(shù)據(jù)，建議可以先不考慮建立索引，因?yàn)樗饕彩切枰S護(hù)的，會(huì)降低插入性能，可以等插入完成后，再去建立索引。如若是MyISAM，可以忽略，因其延遲更新索引的特性，可以使插入性能大大提升（上述例子兩個(gè)表，均未建立索引）。

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

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

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

有什么問題請(qǐng)留言，大家一起探討學(xué)習(xí)。