mysql快速插入百萬條數(shù)據(jù)
作者：在赤道吃冰棍兒
www.jianshu.com/p/36b87cb3a05a
前言
假設(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文件
圖1
圖2
2.進(jìn)行sql插入
這里我寫了個(gè)簡(jiǎn)單的sh，進(jìn)行mysql的導(dǎo)入,請(qǐng)看圖3，我里面的密碼用的環(huán)境變量，大家寫的時(shí)候?qū)懽约旱拿艽a就行，經(jīng)過了漫長(zhǎ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即可。
擴(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í)???。
好文章，我在看