提高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行记录后,再来对比下其表空间文件大小,以及重整表空间后的大小,观察碎片率。详细对比见下:
最后一种分表方式中,5个子表的表空间文件大小总和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字节。
可以看到,这种方式的总大小和原始表大小差距最小,其他几种存储方式都比这个来的大。