[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

首先,介绍下关于InnoDB引擎存储格式的几个要点:
1、InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理、维护。启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效;
2、InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size 可以修改,在5.6以前的版本,只能修改源码重新编译,但并不推荐修改这个配置,除非你非常清楚它有什么优缺点;
3、InnoDB的data page在有新数据写入时,会预留1/16的空间,预留出来的空间可用于后续的新纪录写入,减少频繁的新增data page的开销;
4、每个data page,至少需要存储2行记录。因此理论上行记录最大长度为8KB,但事实上应该更小,因为还有一些InnoDB内部数据结构要存储;
5、受限于InnoDB存储方式,如果数据是顺序写入的话,最理想的情况下,data page的填充率是15/16,但一般没办法保证完全的顺序写入,因此,data page的填充率一般是1/2到15/16。因此每个InnoDB表都最好要有一个自增列作为主键,使得新纪录写入尽可能是顺序的;
6、当data page填充率不足1/2时,InnoDB会进行收缩,释放空闲空间;
7、MySQL 5.6版本的InnoDB引擎当前支持COMPACT、REDUNDANT、DYNAMIC、COMPRESSED四种格式,默认是COMPACT格式,COMPRESSED用的很少且不推荐(见下一条),如果需要用到压缩特性的话,可以直接考虑TokuDB引擎;
8、COMPACT行格式相比REDUNDANT,大概能节省20%的存储空间,COMPRESSED相比COMPACT大概能节省50%的存储空间,但会导致TPS下降了90%。因此强烈不推荐使用COMPRESSED行格式
9、当行格式为DYNAMIC或COMPRESSED时,TEXT/BLOB之类的长列(long column,也有可能是其他较长的列,不一定只有TEXT/BLOB类型,看具体情况)会完全存储在一个独立的data page里,聚集索引页中只使用20字节的指针指向新的page,这就是所谓的off-page,类似ORACLE的行迁移,磁盘空间浪费较严重,且I/O性能也较差。因此,强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型
10、当InnoDB的文件格式(innodb_file_format)设置为Antelope,并且行格式为COMPACT 或 REDUNDANT 时,BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索页中(最大768字节的作用是便于创建前缀索引/prefix index),其余更多的内容存储在额外的page里,哪怕只是多了一个字节。因此,所有列长度越短越好
11、在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的,不能共享。因此强烈不建议在一个表中使用多个长列

综上,如果在实际业务中,确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时,有下面几点建议:
1、尽可能将所有数据序列化、压缩之后,存储在同一个列里,避免发生多次off-page;
2、实际最大存储长度低于255的列,转成VARCHAR或者CHAR类型(如果是变长数据二者没区别,如果是定长数据,则使用CHAR类型);
3、如果无法将所有列整合到一个列,可以退而求其次,根据每个列最大长度进行排列组合后拆分成多个子表,尽量是的每个子表的总行长度小于8KB,减少发生off-page的频率;
4、上述建议是在data page为默认的16KB前提下,如果修改成8KB或者其他大小,请自行根据上述理论进行测试,找到最合适的值;
5、字符型列长度小于255时,无论采用CHAR还是VARCHAR来存储,或者把VARCHAR列长度定义为255,都不会导致实际表空间增大;
6、一般在游戏领域会用到比较多的BLOB列类型,游戏界同行可以关注下。

下面是测试验证过程,有耐心的同学可以慢慢看:

#
# 测试案例:InnoDB中长列存储效率
# 测试场景描述:
# 在InnoDB表中存储64KB的数据,对比各种不同存储方式# 每个表写入5000行记录,观察最后表空间文件大小对比
#

#表0:所有数据存储在一个BLOB列中
CREATE TABLE `t_longcol_0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol` blob NOT NULL COMMENT 'store all data in a blob column',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

#相应的数据写入存储过程:mysp_longcol_0_ins()
CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_0(longcol) select repeat('a',65535);
set @i = @i + 1;
end while;
end;

#表1:将64KB字节平均存储在9个列中
CREATE TABLE `t_longcol_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store all data in 9 blob columns',
`longcol2` blob NOT NULL,
`longcol3` blob NOT NULL,
`longcol4` blob NOT NULL,
`longcol5` blob NOT NULL,
`longcol6` blob NOT NULL,
`longcol7` blob NOT NULL,
`longcol8` blob NOT NULL,
`longcol9` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) select
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',5535);
set @i = @i + 1;
end while;
end;

#表2:将64KB数据离散存储在多个BLOB列中
CREATE TABLE `t_longcol_2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol2` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol3` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol4` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol5` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol6` blob NOT NULL COMMENT 'store 255 bytes data',
`longcol7` blob NOT NULL COMMENT 'store 368 bytes data',
`longcol8` blob NOT NULL COMMENT 'store 496 bytes data',
`longcol9` blob NOT NULL COMMENT 'store 512 bytes data',
`longcol10` blob NOT NULL COMMENT 'store 640 bytes data',
`longcol11` blob NOT NULL COMMENT 'store 768 bytes data',
`longcol12` blob NOT NULL COMMENT 'store 912 bytes data',
`longcol13` blob NOT NULL COMMENT 'store 1024 bytes data',
`longcol14` blob NOT NULL COMMENT 'store 2048 bytes data',
`longcol15` blob NOT NULL COMMENT 'store 3082 bytes data',
`longcol16` blob NOT NULL COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` blob NOT NULL COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表3:将64KB数据离散存储在多个CHAR、VARCHAR、BLOB列中
CREATE TABLE `t_longcol_3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_3_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表4:将64KB数据离散存储在多个VARCHAR、BLOB列中,对比t_longcol_3中几个列是CHAR的情况
CREATE TABLE `t_longcol_4` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_4_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表5:将64KB数据离散存储在多个VARCHAR、BLOB列中,和t_longcol_4相比,变化在于前面的几个列长度改成了255,但实际存储长度还是100字节
CREATE TABLE `t_longcol_5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_5_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#从下面开始,参考第3条建议进行分表,每个表所有列长度总和
#分表1,行最大长度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字节
CREATE TABLE `t_longcol_51` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表2,行最大长度 1024 + 2048 + 4096 = 7168 字节
CREATE TABLE `t_longcol_52` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表3,行最大长度 8192 字节
CREATE TABLE `t_longcol_53` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表4,行最大长度 16284 + 20380 = 36664 字节
CREATE TABLE `t_longcol_54` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表5,行最大长度 5977 + 4 = 5981 字节
CREATE TABLE `t_longcol_55` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_51_ins()
CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol15) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',3082);

insert into t_longcol_52(longcol13,longcol14,longcol16) select
repeat('a',1024),
repeat('a',2048),
repeat('a',4096);

insert into t_longcol_53(longcol17) select repeat('a',8192);

insert into t_longcol_54(longcol18,longcol19) select
repeat('a',16284),
repeat('a',20380);

insert into t_longcol_55(longcol20) select repeat('a',5977);

set @i = @i + 1;
end while;
end;

上述各个测试表都写入5000行记录后,再来对比下其表空间文件大小,以及重整表空间后的大小,观察碎片率。详细对比见下:
mysql-optimization-case-blob-stored-in-innodb-optimization

最后一种分表方式中,5个子表的表空间文件大小总和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字节。
可以看到,这种方式的总大小和原始表大小差距最小,其他几种存储方式都比这个来的大。

[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倍。
重要:这个建议并不是绝对成立的,要看具体的场景。如果是一个高并发的在线业务,就需要尽快提交事务,避免锁范围被扩大。但如果是在非高并发的业务场景,尤其是做数据批量导入的场景下,就建议采用批量提交的方式。

下面是详细的测试案例过程,有兴趣的同学可以看看:

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

测试耗时结果对比:
mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert

[MySQL FAQ]系列 — MySQL联合索引是否支持不同排序规则

篇首语:
截止到目前的5.7.4版本为止,MySQL的联合索引仍无法支持联合索引使用不同排序规则,例如:ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。

先来了解下MySQL关于索引的一些基础知识要点:

• a、EXPLAIN结果中的key_len只显示了条件检索子句需要的索引长度,但 ORDER BY、GROUP BY 子句用到的索引则不计入 key_len 统计值;
• b、联合索引(composite index):多个字段组成的索引,称为联合索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3)
• c、覆盖索引(covering index):如果查询需要读取到索引中的一个或多个字段,则可以从索引树中直接取得结果集,称为覆盖索引;
例如:SELECT col1, col2 FROM t;
• d、最左原则(prefix index):如果查询条件检索时,只需要匹配联合索引中的最左顺序一个或多个字段,称为最左索引原则,或者叫最左前缀;
例如:SELECT * FROM t WHERE col1 = ? AND col2 = ?;
• e、在老版本(大概是5.5以前,具体版本号未确认核实)中,查询使用联合索引时,可以不区分条件中的字段顺序,在这以前是需要按照联合索引的创建顺序书写SQL条件子句的;
例如:SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?;
• f、MySQL截止目前还只支持多个字段都是正序索引,不支个别字段持倒序索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC),这里的DESC只是个预留的关键字,目前还不能真正有作用
• g、联合索引中,如果查询条件中最左边某个索引列使用范围查找,则只能使用前缀索引,无法使用到整个索引;
例如:SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 这时候,只能用到 idx 索引的最左2列进行检索,而col3条件则无法利用索引进行检索
• h、InnoDB引擎中,二级索引实际上包含了主键索引值;

关于 key_len 的计算规则:

• 当索引字段为定长数据类型,比如:char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;
• 当索引字段为变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;
• 当字段定义为非空的时候,是否为空的标记将不占用字节;
• 同时还需要考虑表所使用字符集的差异,latin1编码一个字符1个字节,gbk编码一个字符2个字节,utf8编码一个字符3个字节;

因此,key_len长度的计算公式

• varchr(10)变长字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
• varchr(10)变长字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)
• char(10)固定字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)
• char(10)固定字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)

附,关于 filesort 排序算法:
光看 filesort 字面意思,可能以为是要利用磁盘文件进行排序,实则不全然。
当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序)。

对于filesort,MySQL有两种排序算法:
1、两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数 sort_buffer_size 设定)中进行排序,完成排序之后再次通过行指针信息取出所需的列。
注:该算法是4.1之前只有这种算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。不过,这种方法内存开销较小。

2、一次扫描算法(single pass)
该算法一次性将所需的列全部取出,在内存中排序后直接将结果输出。

注:从 MySQL 4.1 版本开始支持该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的列也取出来,就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在SQL中仅仅取出需要的列是非常有必要的。

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

后面是几个几个测试结果,有兴趣不怕累的可以看看,哈哈。

测试MySQL版本:5.5.37-log MySQL Community Server (GPL)

#创建一个测试表,id是主键字段,(a1, a2) 组成联合索引

(yejr@imysql.com)> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL DEFAULT '0',
`a2` int(10) unsigned NOT NULL DEFAULT '0',
`aa` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx` (`a1`,`a2`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

#填充了64条测试数据

(yejr@imysql.com)> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 64
Avg_row_length: 256
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 122
Create_time: 2014-09-15 17:17:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

#对 a1、a2 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 倒序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#由于同时对a1、a2都是倒序排序,因此完全可以用到索引的顺序,只是反向扫描而已
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1 desc, a2 desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2正序排序,只取a1字段,可以直接使用该联合索引取回结果,并且排序完成
#匹配规则c

(yejr@imysql.com)> explain select a1 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 正序排序,只取a2字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a2 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只对 a1 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对 a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#只取a1字段,同时只对 a1 字段正序排序,这时可用联合索引取得结果,同时也可以利用前缀索引的原则进行排序
#符合规则c

(yejr@imysql.com)> explain select a1 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只取a1字段,同时只对 a2 字段正序排序,这时虽然可用联合索引取得结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a2 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 、a2顺序排序,取得主键id字段,可以直接使用该联合索引取回结果并完成排序。
#这里需要注意下,二级索引其实是包括主键索引的,因此用idx索引即可取到全部结果。
#下面这个SQL也是一样的效果:select a1,a2,id from t order by a1, a2;
#符合规则c、h

(yejr@imysql.com)> explain select id from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对a2 倒序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 倒序排序,对a2 正序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1 desc, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#过滤条件a1字段(使用前缀索引扫描,key_len为4),对a2字段进行正序排序,取得主键id字段,可以直接使用联合索引取回结果
#符合规则a、c、d、h

(yejr@imysql.com)> explain select id from t where a1 = 219 order by a2\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx
key: idx
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index

[MySQL FAQ]系列 — SAVEPOINT语法错误一例

前几天帮同事解决一个案例,在主从复制环境下,从库上的MySQL版本号是5.5.5,遇到下面的错误:

#其他非相关信息我都隐藏掉了
 [(yejr@imysql.com)]> show slave status \G;
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Last_Errno: 1064
 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6e86db84_14847168f19__8000' at line 1' on query. Default database: 'act'. Query: 'SAVEPOINT 6e86db84_14847168f19__8000'
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 1064
 Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6e86db84_14847168f19__8000' at line 1' on query. Default database: 'act_log'. Query: 'SAVEPOINT 6e86db84_14847168f19__8000'

第一感觉是遇到保留关键字了,不过看到这么长的字符串,不应该是保留关键字才对。
经过尝试,最后发现是字符串中的 “e” 这个字符如果存在就可能会报错,看起来应该是bug才对了。
在MySQL的bug系统里确实找到了这个bug,不过看bug描述,在5.5版本中应该是已经修复了才对,看来太不靠谱了呀~~
关于这个bug:Savepoint identifier is occasionally considered as floating point numbers

其实除了升级版本外,解决方法也很简单,把savepoint后面的 identifier 字符串用反引号(波浪号的下档键,英文叫做 backticks 键)引用起来就行。
例如:

savepoint `6e86db84_14847168f19__8000`;

这样就可以了。

这个案例也提示我们,在写SQL时,涉及到数据库、表、字段、identifier 等名称时,最好是都能用反引号引用,确保可用。
曾经看到线上数据表有个字段名是 check ,这个名字在MySQL里很早就已经是保留关键字,幸好开发同学比较靠谱,都加上了反引号。

关于savepoint的2个bug:
Savepoint Identifier should be enclosed with backticks
Savepoint identifier is occasionally considered as floating point numbers