MySQL資料插入insert效能最佳化詳解

對於一些資料量較大的系統，面臨的問題除了是查詢效率低下，還有一個很重要的問題就是插入時間長。我們就有一個業務系統，每天的資料匯入需要4-5個鐘。這種費時的操作其實是很有風險的，假設程式出了問題，想重跑操作那是一件痛苦的事情。因此，提高大資料量系統的MySQL insert效率是很有必要的。

經過對MySQL的測試，發現一些可以提高insert效率的方法，供大家參考參考。
1. 一條SQL語句插入多條資料。

常用的插入語句如：

代碼如下	複製代碼
INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); 修改成： INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0), ('1','userid_1','content_1',1);

修改後的插入操作能夠提高程式的插入效率。這裡第二種SQL執行效率高的主要原因有兩個，一是減少SQL語句解析的操作， 只需要解析一次就能進行資料的插入操作，二是SQL語句較短，可以減少網路傳輸的IO。

這裡提供一些測試對比資料，分別是進行單條資料的匯入與轉化成一條SQL語句進行匯入，分別測試1百、1千、1萬條資料記錄。
 

記錄數 單條資料插入 多條資料插入

1百 0.149s 0.011s
1千 1.231s 0.047s
1萬 11.678s 0.218s

2. 在事物中進行插入處理。
把插入修改成：

代碼如下	複製代碼
STARTTRANSACTION; INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); ... COMMIT;

使用事物可以提高資料的插入效率，這是因為進行一個INSERT操作時，MySQL內部會建立一個事物，在事物內進行真正插入處理。通過使用事物可以減少建立事物的消耗，所有插入都在執行後才進行提交操作。

這裡也提供了測試對比，分別是不使用事物與使用事物在記錄數為1百、1千、1萬的情況。
 

記錄數 不使用事物 使用事物

1百 0.149s 0.033s
1千 1.231s 0.115s
1萬 11.678s 1.050s

效能測試：

這裡提供了同時使用上面兩種方法進行INSERT效率最佳化的測試。即多條資料合併為同一個SQL，並且在事物中進行插入。

記錄數 單條資料插入 合并資料+事物插入
1萬 0m15.977s 0m0.309s
10萬 1m52.204s 0m2.271s
100萬 18m31.317s 0m23.332s
 

從測試結果可以看到，insert的效率大概有50倍的提高，這個一個很客觀的數字。

如果要在同一個用戶端在同一時間插入入很多記錄，可以使用INSERT語句附帶有多個values值。這種做法比使用單一值的INSERT語句快多了（在一些情況下比較快）。如果是往一個非空資料表增加記錄，可以調整變數bulk_insert_buffer_size的值使其更快。
 
如果要從不用的用戶端插入大量記錄，使用INSERT DELAYED語句也可以提高速度。
 
對應MyISAM，可以在SELECT語句正在運行時插入記錄，只要這時候沒有正在刪除記錄。
 
想要將一個文字檔載入到資料表中，可以使用LOAD DATA INFILE。這通常是使用大量INSERT語句的20倍。
 
通過一些額外工作，就可以讓LOAD DATA INFILE在資料表有大量索引的情況下運行更快。步驟如下：
 
用create table隨表建一個表
 
執行FLUSH TABLES語句或admin flush-tables命令
 
執行myisamchk –keys-used=0 -rq /path/to/db/tbl_name命令，刪除資料表所有索引。
 
執行LOAD DATA INFILE，資料插入到表中，由於無需更新表索引，因此這將非常快。
 
如果將來只是讀取該表，運行myisampack讓資料表更小。
 
運行myisamchk -r -q /path/to/db/tbl_name重建索引。建立的索引樹在寫入磁碟前先儲存在記憶體中，這省去了磁碟磁碟搜尋，因此速度快很多。重建後的索引樹分布非常均衡。
 
執行FLUSH TABLES語句或mysqladmin flush-tables命令
 
注意，在Mysql 4.0起，可以運行ALTER TABLE tbl_name DISABLE KEYS來代替myisamchk –keys-used=0 -rq /path/to/db/tbl_name.運行ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name.這麼做就可以省去FLUSH TABLES步驟。
 
 
 
可以在鎖表後，一起執行幾個語句來加速INSERT操作：
 
LOCK TABLES a WRITE;
 
INSERT INTO a VALUES(1,23),(2,23);
 
INSERT INTO a VALUES(8,7);
 
UNLOCK TABLES;
 
這對效能提高的好處在於：直到所有的INSERT語句都完成之後，索引緩衝一次性重新整理到磁碟中。通常情況下，有多少次INSERT語句就會有多少次索引緩衝重新整理到磁碟中的開銷。如果能在一個語句中一次性插入多個值的話，顯然鎖表操作也沒有必要了。對於事務表而言，用BEGIN/COMMIT代替LOCK TABLES來提高速度。鎖表也會降低多次串連測試的總時間，儘管每個獨立串連為了等待鎖的最大等待時間也會增加。
 
Connection 1 does 1000 inserts
 
Connection 2,3 and 4 do 1 insert
 
Connection 5 does 1000 inserts
 
如果沒有鎖表，則串連2，3，4會在1，5之前完成。如果鎖表了，則串連2，3，4可能在1，5之後才能完成，但總時間可能只需要40%。Mysql的INSERT、UPDATE、DELETE操作都非常快，不過在一個語句中如果超過5個插入或者更新時最好加鎖以得到更好的效能。如果要一次性做很多次插入，最好在每個迴圈的前後加上LOCK TABLES和UNLOCK TABLES，從而讓其他進程也能訪問資料表；這麼做效能依然不錯。INSERT總比LOAD DATA INFILE插入資料慢，因為二者實現策略有分明的不同。
 
想要MyISAM表更快，在LOAD DATA INFILE和INSERT時都可以增加系統變數key_buffer_size的值。

注意事項：

1. SQL語句是有長度限制，在進行資料合併在同一SQL中務必不能超過SQL長度限制，通過max_allowed_packe配置可以修改，預設是1M。

2. 事物需要控制大小，事物太大可能會影響執行的效率。MySQL有innodb_log_buffer_size配置項，超過這個值會日誌會使用磁碟資料，這時，效率會有所下降。所以比較好的做法是，在事物大小達到配置項資料級前進行事物提交。