优化InnoDB表BLOB,TEXT列的存储效率

首先，介绍下对于MySQL 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，都不会导致现实表空间增大。

总结

以上就是这篇文章的全部内容了，但愿本文的内容对大家的学习或者工作拥有一定的参考学习价值，感谢大家对脚本之家的支撑。要是你想理解更多相干内容请查看下面相干链接