這篇文章主要介紹了MySQL中索引和提交頻率對InnoDB表寫入速度的影響,作者通過實際測試運行時間的對比來驗證,需要的朋友可以參考下

本次，我們來看看索引、提交頻率對InnoDB表寫入速度的影響，了解有哪些需要注意的。

先直接說幾個結論吧：

1、關于索引對寫入速度的影響：

a、如果有自增列做主鍵，相對完全沒索引的情況，寫入速度約提升 3.11%;

b、如果有自增列做主鍵，并且二級索引，相對完全沒索引的情況，寫入速度約降低 27.37%;

因此，InnoDB表最好總是有一個自增列做主鍵。

2、關于提交頻率對寫入速度的影響(以表中只有自增列做主鍵的場景，一次寫入數據30萬行數據為例)：

a、等待全部數據寫入完成后，最后再執行commit提交的效率最高;

b、每10萬行提交一次，相對一次性提交，約慢了1.17%;

c、每1萬行提交一次，相對一次性提交，約慢了3.01%;

d、每1千行提交一次，相對一次性提交，約慢了23.38%;

e、每100行提交一次，相對一次性提交，約慢了24.44%;

f、每10行提交一次，相對一次性提交，約慢了92.78%;

g、每行提交一次，相對一次性提交，約慢了546.78%，也就是慢了5倍;

因此，最好是等待所有事務結束后再批量提交，而不是每執行完一個SQL就提交一次。

曾經有一次對比測試mysqldump啟用extended-insert和未啟用導出的SQL腳本，后者比前者慢了不止5倍。

重要：這個建議并不是絕對成立的，要看具體的場景。如果是一個高并發的在線業務，就需要盡快提交事務，避免鎖范圍被擴大。但如果是在非高并發的業務場景，尤其是做數據批量導入的場景下，就建議采用批量提交的方式。

下面是詳細的測試案例過程，有興趣的同學可以看看：

1. DROP TABLE IF EXISTS `mytab`; 
2. CREATE TABLE `mytab` ( 
3. `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
4. `c1` int(11) NOT NULL DEFAULT ‘0', 
5. `c2` int(11) NOT NULL DEFAULT ‘0', 
6. `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
7. `c4` varchar(200) NOT NULL DEFAULT ”, 
8. PRIMARY KEY (`id`) 
9. ) ENGINE=InnoDB; 
10.  
11. DELIMITER $$ 
12. DROP PROCEDURE IF EXISTS `insert_mytab`; 
13.  
14. CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int) 
15. BEGIN 
16. DECLARE i INT DEFAULT 0; 
17.  
18. SET AUTOCOMMIT = 0; 
19.  
20. WHILE i < rownum DO INSERT INTO mytab(c1, c2, c3,c4) VALUES( FLOOR(RAND()*rownum),FLOOR(RAND()*rownum),NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)); SET i = i+1; /* 達到每 COMMITRATE 頻率時提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN 
21. COMMIT; 
22. SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I); 
23. END IF; 
24.  
25. END WHILE; 

/* 最終再提交一次,確保成功 */

1. COMMIT; 
2. SELECT ‘ALL COMMIT;'; 
3.  
4. END; $$ 
5.  
6. #測試調用 
7. call insert_mytab(300000, 1); — 每次一提交 
8. call insert_mytab(300000, 10); — 每10次一提交 
9. call insert_mytab(300000, 100); — 每100次一提交 
10. call insert_mytab(300000, 1000); — 每1千次一提交 
11. call insert_mytab(300000, 10000); — 每1萬次提交 
12. call insert_mytab(300000, 100000); — 每10萬次一提交 
13. call insert_mytab(300000, 0); — 一次性提交 

測試耗時結果對比：