关于数据库写入慢的问题autocommit,索引等对Innodb写入速度的影响

关于线上数据库写入慢问题总结分析

 # by coco

 # 2014-09-27

之前安排的说主从延迟是否是写入慢的问题，一直没有思路，最近通过看书《MySQL内核---InnodbDB存储引擎》，有点思路，再网上找，果真有这么的测试。下面其实是网上的一个测试示例，拿过来自己测试了一遍，现在就测试结果总结如下：本次，我们来看看索引、提交频率对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倍。

测试脚本：

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 $$ CREATE DEFINER = 'root'@'%' PROCEDURE db1.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    delimiter ;

 

#测试调用

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); — 一次性提交：

 

call insert_mytab(30000,1); — 每次一提交

call insert_mytab(30000,10); — 每10次一提交

call insert_mytab(30000,100); — 每100次一提交

call insert_mytab(30000,1000); — 每1千次一提交

call insert_mytab(30000,10000); — 每1万次提交

call insert_mytab(30000,0); — 一次性提交：

 

试耗时结果对比：（网上测试30万数据给出的结果）

 

 

注：下面是线上2.163环境：（16G内存，8核cpu）测试3W数据结果

单位（s）	主键+普通索引	完全无索引	只有主键
每行提交	30.76 sec	28.30 sec	37.98 sec
每10行提交一次	5.53 sec	5.12 sec	6.48 sec
每100次一提交	4.61 sec	4.25 sec	3.82 sec
每1千次一提交	4.06 sec	5.63 sec	4.37 sec
每1万次提交	5.38 sec	3.34 sec	4.05 sec

 

注：下面是线下测试环境（8.97：4G内存 4核cpu ）所得数据：测试3W数据结果

单位（s）	主键+普通索引	完全无索引	只有主键
每行提交	2 min 47.84	2 min 42.40 sec	2 min 53.82 sec
每10行提交一次	23.27 sec	22.26 sec	18.72 sec
每100次一提交	5.24 sec	4.79 sec	6.19 sec
每1千次一提交	3.46 sec	3.20 sec	3.63 sec
每1万次提交	2.93 sec	2.74 sec	2.81 sec
每1万次提交	2.92 sec	2.75 sec	2.76 sec

 

特别注意：autocommit自动提交。自动提交对super用户是无效的。所以用普通用户链接进去时设置。不要设定 autocommit 这个开关，让它保持 autocommit=1 这个默认状态。

平常有查询\更新都是需要得到最新的数据，根本不需要启动一个事务，除非有特定状况才需要开启事务，再手工用 start transaction ... commit/rollback 。

这种全局设置，在生产环境中没有多大意义。一般都是在应用程序框架（如连接池的库）中设置

autocommit 是否为 ON/OFF, 说白了，就是得到数据库连接以后，显示的调用一次 set autocommit on/off (or =1/0)