【MySQL经典案例分析】关于数据行溢出由浅至深的探讨
本文由云+社区发表
一、从常见的报错说起
故事的开头我们先来看一个常见的sql报错信息：
相信对于这类报错大家一定遇到过很多次了，特别对于omg这种已内容生产为主要工作核心的bg，在内容线的存储中，数据大一定是个绕不开的话题。这里的数据“大”，远不止存储空间占用多，其中也包括了单个（表）字段存储多、大，数据留存时间长，数据冗余多，冷热数据不明显导致的体量大，访问峰值随着热点变化明显，逻辑处理复杂导致数据存储压力放大等等。回到这个报错的问题上来，我们先来看一下这个表的结构：
看到这里，我相信大家会有不同的处理方式了，这里就不对各种处理方式的优劣做比较了，仅仅叙述使用频率较高的两种处理方式。
- 根据报错的指引，把两个大的varchar（22288）改成text、blob
- 根据业务特点，缩小varchar的存储长度,或者按照规则拆分成多个小的vachar和char
这两种的处理方式也各有优缺点，把字段改成text或者blob，不仅增大了数据存储的容量，对这个字段的索引页只能采用前缀或者全文索引了，如果业务侧存储的是json格式的数据，5.7支持json数据类型是个不错的选择，可以针对单个子类进行查询和输出。同样如果缩小和拆分的话就比较依赖业务的场景和逻辑需求了，业务使用的逻辑上需要修改，工程量也需要评估。
二、深入探索
接着我们再来深入分析下关于限制大小“65535”的一些容易混淆的概念。
1、“65535”不是单个varchar(n)中n的最大限制，而是整个表非大字段类型的字段的bytes总合。
---------------------------------------------------------------------------------------------
every table (regardless of storage engine) has a maximum row size of 65,535 bytes. storage engines may place additional constraints on this limit, reducing the effective maximum row size.
---------------------------------------------------------------------------------------------
2、不同的字符集对字段可存储的max会有影响，例如，utf8字符需要3个字节存储，对于varchar（255）character set utf8列，会占用255×3 =765的字节。故该表不能包含超过65,535/765=85这样的列。gbk是双字节的以此类推。
3、可变长度列在评估字段大小时还要考虑存储列实际长度的字节数。例如，varchar（255）character set utf8列需要额外的两个字节来存储值长度信息，所以该列需要多达767个字节存储，其实最大可以存储65533字节，剩余两个字节存储长度信息。
4、blob、text、json列不同于varchar、char等字段，列长度信息独立于行长存储，可以达到65535字节真实存储
5、定义null列会降低允许的最大列数。
- innodb表，null和not null列存储大小是一样
- myisam表，null列需要额外的空间记录其值是否为null。每个null需要一个额外的位（四舍五入到最接近的字节）。最大行长度计算如下：
row length = 1 + (sum of column lengths) + (number of null columns + delete_flag + 7)/8 + (number of variable-length columns)
- 静态表，delete_flag = 1，静态表通过在该行记录一个位来标识该行是否已被删除。
- 动态表，delete_flag = 0，该标记存储在动态行首，动态表具体可以根据
6、对于innodb表，null和not null列存储大小是一样
7、innodb允许单表最多1000个列
8、varchar主键只支持不超过767个字节或者768/2=384个双字节 或者767/3=255个三字节的字段 而gbk是双字节的，utf8是三字节的
9、不用的引擎对索引的限制有区别
- innodb每个列的长度不能大于767 bytes；所有组成索引列的长度和不能大于3072 bytes
- myisam 每个列的长度不能大于1000 bytes，所有组成索引列的长度和不能大于1000 bytes
三、真正的故障
下面来说下今天遇到的业务故障，线上业出现了大量的如下报错，导致程序无法写入数据：
按照提示和正常的思路，我们先第一反应认为业务存在如下的问题：
- 设置的表结构中字段超过了限制
- 某个字段插入的数据长度超过了改字段设置的max值
接着查看了业务的库表结构，如下：
很快排除了第一个原因，因为首先业务的报错不是在建立表的时候出现的，如果是表中非大字段之和65535，在建表的时候就会出错，而业务是在写入的时候才报错的，而且通过库表结构也能发现大量的都是mediumblob类型字段，非大字段加起来远小于65535。
接着根据业务提供的具体sql，appversion、datadata、elt_stamp、id这几个非大字段，也并没有超过限制，mediumblob类型字段最大可存储16m，业务的数据远远没有达到这个量级。按照报错的提示把 appversion、datadata、elt_stamp、id这几个非大字段均改成blob类型，还是无法解决。（根据之前的分析，必然不是问题的根源）。
冷静下来后，发现其实还有个细节被忽略掉了，业务的失败率不是100%，说明还是有成功的请求，通过对比成功和失败的sql，发现果然数据量差异的还是mediumblob类型字段。那么现在第一个想到的就是，max_allowed_packet这个参数，是不是调小了，是的单个请求超过大小被拒绝了，查了下配置的值（如下图），配置的大小1g，sql的数据长度远没有这么大，这个原因也排除了。
查到这里基本上排除了常见几个问题，接着再看一下另一个参数的限制：innodb_page_size，这个的默认值是16k，每个page两行数据，所以每行最大8k数据。
查看了下数据表row_format是compact，那么我们可以推断问题的原因应该就是innodb默认的approach存储格式会把每个blob字段的前864个字节存储在page里，所以blob超过一定数量的话，单行大小就会超过8k，所以就报错了。通过对比业务写成功和失败的sql也应征了这个推论，那么现在要怎么解决这个问题？
- 业务拆分表，大字段进行分表存储
- 通过解决row_format的存储方式解决问题
由于业务单表的存储条数并不大，而且业务逻辑不适合拆分，所以我们要在row_format上来解决这个问题。
barracuda文件格式下拥有两种新的行记录格式compressed和dynamic两种，新的两种格式对于存放blob的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在blob page中。compressed行记录格式的另一个功能就是存储在其中的数据会以zlib的算法进行压缩。
相关的变更操作就相对简单了：
1、 修改mysql全局变量：
set global innodb_file_format='barracuda';
2、平滑变更原表的属性：
row_format=compressed
四、继续学习
通过这个案例我们可以从中提炼出两个值得深入研究一下的点：
1、关于innodb_page_size
从mysql5.6开始，innodb_page_size可以设置innodb数据页为8k,4k，默认为16k。这个参数在一开始初始化时就要加入my.cnf里，如果已经创建了表，再修改，启动mysql会报错。
那么在5.6的版本之前要修改这个值，怎么办？那只能是在源码上做点文章了，然后重新rebuild一下mysql。
univ_page_size是数据页大小，默认的是16k，该值是可以设置必须为2的次方。对于该值可以设置成4k、8k、16k、32k、64k。同时更改了univ_page_size后需要更改univ_page_size_shift 该值是2的多少次方为univ_page_size，所以设置数据页分别情况如下：
接着再来说一下innodb_page_size设置成不同值的对于mysql性能上的影响，测试的表含有1亿条记录，文件大小30g。
①读写场景（50%读50%写）
16k，对cpu压力较小，平均在20%
8k，cpu压力为30%~40%，但select吞吐量要高于16k
②读场景（100%读）
16k和8k差别不明显
innodb buffer pool管理页面本身也有代价，page数越多，那么相同大小下，管理链表就越长。因此当我们的数据行本身就比较长（大块插入），更大的页面更有利于提升速度，因为一个页面可以放入更多的行，每个io写的大小更大，可以更少的iops写更多的数据。 当行长超过8k的时候，如果是16k的页面，就会强制转换一些字符串类型为text，把字符串主体转移到扩展页中，会导致读取列需要多一个io，更大的页面也就支持了更大的行长，64k页面可以支持近似32k的行长而不用使用扩展页。 但是如果是短小行长的随机读取和写入，则不适合使用这么大的页面，这会导致io效率下降，大io只能读取到小部分。
2、关于row_format
innodb存储引擎保存记录，是以行的形式存放的。在innodb 1.0.x版本之前，innodb 存储引擎提供了 compact 和 redundant 两种格式来存放行记录数据。mysql 5.1 中的innodb_plugin 引入了新的文件格式：barracuda，该文件格式拥有新的两种行格式：compressed和dynamic。并且把 compact 和 redundant 合称为antelope。可以通过命令show table status like 'table_name'；来查看当前表使用的行格式，其中 row_format 列表示当前所使用的行记录结构类型。
mysql 5.6 版本中，默认 compact ，msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，默认值是dynamic，也可以在 create table 的时候指定row_format=dynamic（通过这个可动态调整表的存储格式）。如果要修改现有表的行模式为compressed或dynamic，必须先将文件格式设置成barracuda（set global innodb_file_format=barracuda;）。再用alter table tablename row_format=compressed;去修改才能生效，否则修改无效却无提示。
①compact
如果blob列值长度 <= 768 bytes，不会发生行溢出(page overflow)，内容都在数据页(b-tree node)；如果列值长度 > 768字节，那么前768字节依然在数据页，而剩余的则放在溢出页(off-page)，如下图：
上面讲的blob或变长大字段类型包括blob、text、varchar，其中varchar列值长度大于某数n时也会存溢出页，在latin1字符集下n值可以这样计算：innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，减去其它列值所占字节数，约等于n。
②compressed或dynamic
对blob采用完全行溢出，即聚集索引记录（数据页）只保留20字节的指针，指向真实存放它的溢出段地址：
dynamic行格式，列存储是否放到off-page页，主要取决于行大小，它会把行中最长的那一列放到off-page，直到数据页能存放下两行。text/blob列 <=40 bytes 时总是存放于数据页。可以避免compact那样把太多的大列值放到 b-tree node，因为dynamic格式认为，只要大列值有部分数据放在off-page，那把整个值放入都放入off-page更有效。
compressed 物理结构上与dynamic类似，但是对表的数据行使用zlib算法进行了压缩存储。在long blob列类型比较多的情况下用，可以降低off-page的使用，减少存储空间（50%左右，可参见之前“【数据库评测报告】第三期：innodb、tokudb压缩性能”报告中的测试结果），但要求更高的cpu，buffer pool里面可能会同时存储数据的压缩版和非压缩版，所以也多占用部分内存。
最后参考了《高性能mysql》，给出一些使用blob这类变长大字段类型的建议：
①大字段在innodb里可能浪费大量空间。例如，若存储字段值只是比行的要求多了一个字节，也会使用整个页面来存储剩下的字节，浪费了页面的大部分空间。同样的，如果有一个值只是稍微超过了32个页的大小，实际上就需要使用96个页面。
②太长的值可能使得在查询中作为where条件不能使用索引，因而执行很慢。在应用where条件之前，mysql需要把所有的列读出来，所以可能导致mysql要求innodb读取很多扩展存储，然后检查where条件，丢弃所有不需要的数据。
③一张表里有很多大字段，最好组合起来单独存到一个列里面。让所有的大字段共享一个扩展存储空间，比每个字段用自己的页要好。
④把大字段用compress()压缩后再存为blob，或者在发送到mysql前在应用程序中进行压缩，可以获得显著的空间优势和性能收益。
⑤扩展存储禁用了自适应哈希，因为需要完整的比较列的整个长度，才能发现是不是正确的数据。
此文已由作者授权腾讯云+社区发布