探究MySQL中索引和提交頻率對InnoDB表寫入速度的影響_Mysql

本次，我們來看看索引、提交頻率對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倍。
重要：這個建議並不是絕對成立的，要看具體的情境。如果是一個高並發的線上業務，就需要儘快提交事務，避免鎖範圍被擴大。但如果是在非高並發的業務情境，尤其是做資料大量匯入的情境下，就建議採用批量提交的方式。

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

DROP TABLE IF EXISTS `mytab`;CREATE TABLE `mytab` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`c1` int(11) NOT NULL DEFAULT ‘0',`c2` int(11) NOT NULL DEFAULT ‘0',`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`c4` varchar(200) NOT NULL DEFAULT ”,PRIMARY KEY (`id`)) ENGINE=InnoDB;DELIMITER $$$DROP PROCEDURE IF EXISTS `insert_mytab`;CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int)BEGINDECLARE i INT DEFAULT 0;SET AUTOCOMMIT = 0;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) THENCOMMIT;SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I);END IF;END WHILE;

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

COMMIT;SELECT ‘ALL COMMIT;';END; $$$#測試調用call insert_mytab(300000, 1); — 每次一提交call insert_mytab(300000, 10); — 每10次一提交call insert_mytab(300000, 100); — 每100次一提交call insert_mytab(300000, 1000); — 每1千次一提交call insert_mytab(300000, 10000); — 每1萬次提交call insert_mytab(300000, 100000); — 每10萬次一提交call insert_mytab(300000, 0); — 一次性提交

測試耗時結果對比：