可能用很多朋友會發現如果使用insert插入較小數據量我們會很慢,但如果上萬條記錄同時插入時會發現很慢,下面我來給大家介紹為什么會這樣及數據插入insert性能優化方法,有需要的朋友可進入參考.

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

經過對MySQL的測試，發現一些可以提高insert效率的方法，供大家參考參考。

1.一條SQL語句插入多條數據.

常用的插入語句,代碼如下:

1. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); 
2. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); 
3.  
4. //修改成： 
5.  
6. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0), 
7. ('1','userid_1','content_1',1); 

修改后的插入操作能夠提高程序的插入效率,這里第二種SQL執行效率高的主要原因有兩個,一是減少SQL語句解析的操作,只需要解析一次就能進行數據的插入操作,二是SQL語句較短,可以減少網絡傳輸的IO.

這里提供一些測試對比數據,分別是進行單條數據的導入與轉化成一條SQL語句進行導入,分別測試1百、1千、1萬條數據記錄.

1. 記錄數 單條數據插入 多條數據插入 
2. 1百 0.149s 0.011s  
3. 1千 1.231s 0.047s  
4. 1萬 11.678s 0.218s 

2.在事物中進行插入處理,把插入修改成如下代碼:

1. STARTTRANSACTION; 
2. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); 
3. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); 
4. ... 
5. COMMIT;  

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

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

1. 記錄數 不使用事物 使用事物 
2. 1百 0.149s 0.033s  
3. 1千 1.231s 0.115s  
4. 1萬 11.678s 1.050s 

性能測試:這里提供了同時使用上面兩種方法進行INSERT效率優化的測試,即多條數據合并為同一個SQL,并且在事物中進行插入.

1. 記錄數 單條數據插入 合并數據+事物插入  
2. 1萬 0m15.977s 0m0.309s  
3. 10萬 1m52.204s 0m2.271s  
4. 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操作:

1. LOCK TABLES a WRITE; 
2.  
3. INSERT INTO a VALUES(1,23),(2,23); 
4.  //Vevb.com
5. INSERT INTO a VALUES(8,7); 
6.  
7. UNLOCK TABLES; 

這對性能提高的好處在于:直到所有的INSERT語句都完成之后,索引緩存一次性刷新到磁盤中,通常情況下,有多少次INSERT語句就會有多少次索引緩存刷新到磁盤中的開銷,如果能在一個語句中一次性插入多個值的話,顯然鎖表操作也沒有必要了,對于事務表而言,用BEGIN/COMMIT代替LOCK TABLES來提高速度,鎖表也會降低多次連接測試的總時間,盡管每個獨立連接為了等待鎖的最大等待時間也會增加.

1. Connection 1 does 1000 inserts 
2.  
3. Connection 2,3 and 4 do 1 insert 
4.  
5. 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配置項，超過這個值會日志會使用磁盤數據，這時，效率會有所下降。所以比較好的做法是，在事物大小達到配置項數據級前進行事物提交。