MySQL數據插入insert性能優化詳解

可能用很多朋友會發現如果使用insert插入較小數據量我們會很慢，但如果上萬條記錄同時插入時會發現很慢，下面我來給大家介紹為什麼會這樣及數據插入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配置項，超過這個值會日志會使用磁盤數據，這時，效率會有所下降。所以比較好的做法是，在事物大小達到配置項數據級前進行事物提交。