MySQL innodb load data.vs.insert 前因後果

       前一段時間接了個業務比較特殊，它的情境是這樣的：

–每天入庫資料量3億左右–寫多讀少，每隔5分鐘寫入10w資料–按天分表，多年同一天的資料在一個表裡–同一天的資料每隔5分鐘更新一次–資料來源，經過Hadoop分析過後的csv檔案--類似於這樣的，會有並發同時跑，但是資料量和間隔時間不一樣資料庫配置：普通sas盤，24G記憶體，16核CPU，單一實例，1主1備最佳化前的做法是:1. 把原來的前幾分鐘的資料根據日期delete掉2. 使用程式讀取分析過的csv檔案，拼裝成insert into tbname (col1,col2,......) values (val1,val2,....,),(val3,val4,.......,),.......;

存在問題：

1. 主庫寫入太慢最長的一個表插入時間要329分鐘

2. 備庫延遲太大（>20w秒），慢查詢檔案很大16G，存在單點故障

效率圖：

最佳化做法：

把insert換成load

最佳化成果:

CPU消耗情況：

io情況有所好轉，但是iowait仍然嚴重，因為本來就是io bound型，雖然減少了大部分記錄慢日誌的io,其他io情況，沒有像寫入效率那麼明顯。

備庫沒有延遲，解決了單點故障

原因：

MySQL手冊裡已經說得很清楚:

INSERT速度： http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html 

如何提高innodb表的load data效率：http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html 

1.  關掉自動認可

2.  禁掉唯一索引

3.  禁掉外鍵

這樣能節省大量物理io

原因：

load data跳過sql解析，直接產生資料檔案;

load data在匯入之前會關掉索引，匯入完成後更新索引；

load data開始執行後佔用的記憶體空間不會被purge掉

另外load data的速度和檔案每行的大小有關，每行所佔的位元組數越少，load的速度越快；同樣的前提，沒有索引的要比有索引的要快；

如何提高插入表的效率，歡迎移步： http://www.informit.com/articles/article.aspx?p=377652&seqNum=4 
http://www.innodb.com/doc/innodb_plugin-1.0/innodb-create-index.html
http://www.mysqlperformanceblog.com/2008/04/23/testing-innodb-barracuda-format-with-compression/

http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/