如何提高InnoDB表BLOB列的存储效率，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

强烈建议不要在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列中CREATETABLE`t_longcol_0`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol`blobNOTNULLCOMMENT'storealldatainablobcolumn',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8ROW_FORMAT=COMPACT;#相应的数据写入存储过程：mysp_longcol_0_ins()CREATEPROCEDURE`mysp_longcol_0_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_0(longcol)selectrepeat('a',65535);set@i=@i+1;endwhile;end;#表1：将64KB字节平均存储在9个列中CREATETABLE`t_longcol_1`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol1`blobNOTNULLCOMMENT'storealldatain9blobcolumns',`longcol2`blobNOTNULL,`longcol3`blobNOTNULL,`longcol4`blobNOTNULL,`longcol5`blobNOTNULL,`longcol6`blobNOTNULL,`longcol7`blobNOTNULL,`longcol8`blobNOTNULL,`longcol9`blobNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#相应的数据写入存储过程：mysp_longcol_1_ins()CREATEPROCEDURE`mysp_longcol_1_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9)selectrepeat('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;endwhile;end;#表2：将64KB数据离散存储在多个BLOB列中CREATETABLE`t_longcol_2`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol1`blobNOTNULLCOMMENT'store100bytesdata',`longcol2`blobNOTNULLCOMMENT'store100bytesdata',`longcol3`blobNOTNULLCOMMENT'store100bytesdata',`longcol4`blobNOTNULLCOMMENT'store100bytesdata',`longcol5`blobNOTNULLCOMMENT'store100bytesdata',`longcol6`blobNOTNULLCOMMENT'store255bytesdata',`longcol7`blobNOTNULLCOMMENT'store368bytesdata',`longcol8`blobNOTNULLCOMMENT'store496bytesdata',`longcol9`blobNOTNULLCOMMENT'store512bytesdata',`longcol10`blobNOTNULLCOMMENT'store640bytesdata',`longcol11`blobNOTNULLCOMMENT'store768bytesdata',`longcol12`blobNOTNULLCOMMENT'store912bytesdata',`longcol13`blobNOTNULLCOMMENT'store1024bytesdata',`longcol14`blobNOTNULLCOMMENT'store2048bytesdata',`longcol15`blobNOTNULLCOMMENT'store3082bytesdata',`longcol16`blobNOTNULLCOMMENT'store4096bytesdata',`longcol17`blobNOTNULLCOMMENT'store8192bytesdata',`longcol18`blobNOTNULLCOMMENT'store16284bytesdata',`longcol19`blobNOTNULLCOMMENT'store20380bytesdata',`longcol20`blobNOTNULLCOMMENT'store5977bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#相应的数据写入存储过程：mysp_longcol_1_ins()CREATEPROCEDURE`mysp_longcol_1_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20)selectrepeat('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;endwhile;end;#表3：将64KB数据离散存储在多个CHAR、VARCHAR、BLOB列中CREATETABLE`t_longcol_3`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol1`char(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol2`char(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol3`char(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol4`char(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol5`char(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol6`varchar(256)NOTNULLDEFAULT''COMMENT'store255bytesdata',`longcol7`varchar(368)NOTNULLDEFAULT''COMMENT'store368bytesdata',`longcol8`varchar(496)NOTNULLDEFAULT''COMMENT'store496bytesdata',`longcol9`varchar(512)NOTNULLDEFAULT''COMMENT'store512bytesdata',`longcol10`varchar(640)NOTNULLDEFAULT''COMMENT'store640bytesdata',`longcol11`varchar(768)NOTNULLDEFAULT''COMMENT'store768bytesdata',`longcol12`varchar(912)NOTNULLDEFAULT''COMMENT'store912bytesdata',`longcol13`varchar(1024)NOTNULLDEFAULT''COMMENT'store1024bytesdata',`longcol14`varchar(2048)NOTNULLDEFAULT''COMMENT'store2048bytesdata',`longcol15`varchar(3082)NOTNULLDEFAULT''COMMENT'store3082bytesdata',`longcol16`varchar(4096)NOTNULLDEFAULT''COMMENT'store4096bytesdata',`longcol17`blobNOTNULLCOMMENT'store8192bytesdata',`longcol18`blobNOTNULLCOMMENT'store16284bytesdata',`longcol19`blobNOTNULLCOMMENT'store20380bytesdata',`longcol20`varchar(5977)NOTNULLDEFAULT''COMMENT'store5977bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#相应的数据写入存储过程：mysp_longcol_3_ins()CREATEPROCEDURE`mysp_longcol_1_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20)selectrepeat('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;endwhile;end;#表4：将64KB数据离散存储在多个VARCHAR、BLOB列中，对比t_longcol_3中几个列是CHAR的情况CREATETABLE`t_longcol_4`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol1`varchar(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol2`varchar(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol3`varchar(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol4`varchar(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol5`varchar(100)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol6`varchar(256)NOTNULLDEFAULT''COMMENT'store255bytesdata',`longcol7`varchar(368)NOTNULLDEFAULT''COMMENT'store368bytesdata',`longcol8`varchar(496)NOTNULLDEFAULT''COMMENT'store496bytesdata',`longcol9`varchar(512)NOTNULLDEFAULT''COMMENT'store512bytesdata',`longcol10`varchar(640)NOTNULLDEFAULT''COMMENT'store640bytesdata',`longcol11`varchar(768)NOTNULLDEFAULT''COMMENT'store768bytesdata',`longcol12`varchar(912)NOTNULLDEFAULT''COMMENT'store912bytesdata',`longcol13`varchar(1024)NOTNULLDEFAULT''COMMENT'store1024bytesdata',`longcol14`varchar(2048)NOTNULLDEFAULT''COMMENT'store2048bytesdata',`longcol15`varchar(3082)NOTNULLDEFAULT''COMMENT'store3082bytesdata',`longcol16`varchar(4096)NOTNULLDEFAULT''COMMENT'store4096bytesdata',`longcol17`blobNOTNULLCOMMENT'store8192bytesdata',`longcol18`blobNOTNULLCOMMENT'store16284bytesdata',`longcol19`blobNOTNULLCOMMENT'store20380bytesdata',`longcol20`varchar(5977)NOTNULLDEFAULT''COMMENT'store5977bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#相应的数据写入存储过程：mysp_longcol_4_ins()CREATEPROCEDURE`mysp_longcol_1_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20)selectrepeat('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;endwhile;end;#表5：将64KB数据离散存储在多个VARCHAR、BLOB列中，和t_longcol_4相比，变化在于前面的几个列长度改成了255，但实际存储长度还是100字节CREATETABLE`t_longcol_5`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol1`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol2`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol3`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol4`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol5`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol6`varchar(256)NOTNULLDEFAULT''COMMENT'store255bytesdata',`longcol7`varchar(368)NOTNULLDEFAULT''COMMENT'store368bytesdata',`longcol8`varchar(496)NOTNULLDEFAULT''COMMENT'store496bytesdata',`longcol9`varchar(512)NOTNULLDEFAULT''COMMENT'store512bytesdata',`longcol10`varchar(640)NOTNULLDEFAULT''COMMENT'store640bytesdata',`longcol11`varchar(768)NOTNULLDEFAULT''COMMENT'store768bytesdata',`longcol12`varchar(912)NOTNULLDEFAULT''COMMENT'store912bytesdata',`longcol13`varchar(1024)NOTNULLDEFAULT''COMMENT'store1024bytesdata',`longcol14`varchar(2048)NOTNULLDEFAULT''COMMENT'store2048bytesdata',`longcol15`varchar(3082)NOTNULLDEFAULT''COMMENT'store3082bytesdata',`longcol16`varchar(4096)NOTNULLDEFAULT''COMMENT'store4096bytesdata',`longcol17`blobNOTNULLCOMMENT'store8192bytesdata',`longcol18`blobNOTNULLCOMMENT'store16284bytesdata',`longcol19`blobNOTNULLCOMMENT'store20380bytesdata',`longcol20`varchar(5977)NOTNULLDEFAULT''COMMENT'store5977bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#相应的数据写入存储过程：mysp_longcol_5_ins()CREATEPROCEDURE`mysp_longcol_1_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20)selectrepeat('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;endwhile;end;#从下面开始，参考第3条建议进行分表，每个表所有列长度总和#分表1，行最大长度100+100+100+100+100+255+368+496+512+640+768+912+3082=7533字节CREATETABLE`t_longcol_51`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol1`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol2`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol3`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol4`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol5`varchar(255)NOTNULLDEFAULT''COMMENT'store100bytesdata',`longcol6`varchar(256)NOTNULLDEFAULT''COMMENT'store255bytesdata',`longcol7`varchar(368)NOTNULLDEFAULT''COMMENT'store368bytesdata',`longcol8`varchar(496)NOTNULLDEFAULT''COMMENT'store496bytesdata',`longcol9`varchar(512)NOTNULLDEFAULT''COMMENT'store512bytesdata',`longcol10`varchar(640)NOTNULLDEFAULT''COMMENT'store640bytesdata',`longcol11`varchar(768)NOTNULLDEFAULT''COMMENT'store768bytesdata',`longcol12`varchar(912)NOTNULLDEFAULT''COMMENT'store912bytesdata',`longcol15`varchar(3082)NOTNULLDEFAULT''COMMENT'store3082bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#分表2，行最大长度1024+2048+4096=7168字节CREATETABLE`t_longcol_52`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol13`varchar(1024)NOTNULLDEFAULT''COMMENT'store1024bytesdata',`longcol14`varchar(2048)NOTNULLDEFAULT''COMMENT'store2048bytesdata',`longcol16`varchar(4096)NOTNULLDEFAULT''COMMENT'store4096bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#分表3，行最大长度8192字节CREATETABLE`t_longcol_53`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol17`blobNOTNULLCOMMENT'store8192bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#分表4，行最大长度16284+20380=36664字节CREATETABLE`t_longcol_54`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol18`blobNOTNULLCOMMENT'store16284bytesdata',`longcol19`blobNOTNULLCOMMENT'store20380bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#分表5，行最大长度5977+4=5981字节CREATETABLE`t_longcol_55`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`longcol20`varchar(5977)NOTNULLDEFAULT''COMMENT'store5977bytesdata',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;#相应的数据写入存储过程：mysp_longcol_51_ins()CREATEPROCEDURE`mysp_longcol_51_ins`(incntint)beginset@i=1;while@i<cntdoinsertintot_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,longcol11,longcol12,longcol15)selectrepeat('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);insertintot_longcol_52(longcol13,longcol14,longcol16)selectrepeat('a',1024),repeat('a',2048),repeat('a',4096);insertintot_longcol_53(longcol17)selectrepeat('a',8192);insertintot_longcol_54(longcol18,longcol19)selectrepeat('a',16284),repeat('a',20380);insertintot_longcol_55(longcol20)selectrepeat('a',5977);set@i=@i+1;endwhile;end;

上述各个测试表都写入5000行记录后，再来对比下其表空间文件大小，以及重整表空间后的大小，观察碎片率。详细对比见下：

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

看完上述内容，你们掌握如何提高InnoDB表BLOB列的存储效率的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！