探究MySQL中索引和提交频率对InnoDB表写入速度的影响
时间：2021-05-02 08:21:01|栏目：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) BEGIN DECLARE 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) THEN COMMIT; 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); ― 一次性提交
测试耗时结果对比：
