共计 15156 个字符，预计需要花费 38 分钟才能阅读完成。
如何提高 InnoDB 表 BLOB 列的存储效率，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
0、导读
强烈建议不要在 InnoDB 中存储 TEXT/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;
#表 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;
#表 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;
#表 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;
#表 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;
#表 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;
#从下面开始，参考第 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 字节。
可以看到，这种方式的总大小和原始表大小差距最小，其他几种存储方式都比这个来的大。
看完上述内容，你们掌握如何提高 InnoDB 表 BLOB 列的存储效率的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
正文完
