MYSQL教程[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入

《MYSQL教程[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响》要点：
本文介绍了MYSQL教程[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响，希望对您有用。如果有疑问，可以联系我们。

先直接说几个结论吧：MYSQL数据库

1、关于索引对写入速度的影响：
a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%；
b、如果有自增列做主键,并且二级索引,写入速度约降低 27.37%；

因此,InnoDB表最好总是有一个自增列做主键.MYSQL数据库

?MYSQL数据库

2、关于提交频率对写入速度的影响(以表中只有自增列做主键的场景,一次写入数据30万行数据为例)：MYSQL数据库

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倍；

因此,0);">最好是等待所有事务结束后再批量提交,而不是每执行完一个SQL就提交一次.MYSQL数据库

曾经有一次对比测试mysqldump启用extended-insert和未启用导出的SQL脚本,0);">后者比前者慢了不止5倍.MYSQL数据库

?MYSQL数据库

下面是详细的测试案例过程,有兴趣的同学可以看看：MYSQL数据库

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;MYSQL数据库

DELIMITER $$$
DROP PROCEDURE IF EXISTS `insert_mytab`;MYSQL数据库

CREATE PROCEDURE `insert_mytab`(in rownum int,in commitrate int)
BEGIN
DECLARE i INT DEFAULT 0;MYSQL数据库

SET AUTOCOMMIT = 0;MYSQL数据库

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;MYSQL数据库

END WHILE;MYSQL数据库

/* 最终再提交一次,确保成功 */
COMMIT;
SELECT 'ALL COMMIT;';MYSQL数据库

END; $$$MYSQL数据库

#测试调用
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); -- 一次性提交MYSQL数据库

?MYSQL数据库

测试耗时结果对比：

?MYSQL数据库

?MYSQL数据库

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!