MySQL数据行溢出的深入理解-木庄网络博客

本文摘自PHP中文网，作者不言，侵删。

本篇文章给大家带来的内容是关于MySQL数据行溢出的深入理解，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

一、从常见的报错说起

故事的开头我们先来看一个常见的sql报错信息：

相信对于这类报错大家一定遇到过很多次了，特别对于OMG这种已内容生产为主要工作核心的BG，在内容线的存储中，数据大一定是个绕不开的话题。这里的数据“大”，远不止存储空间占用多，其中也包括了单个（表）字段存储多、大，数据留存时间长，数据冗余多，冷热数据不明显导致的体量大，访问峰值随着热点变化明显，逻辑处理复杂导致数据存储压力放大等等。回到这个报错的问题上来，我们先来看一下这个表的结构：

微信截图_20181230091755.png

看到这里，我相信大家会有不同的处理方式了，这里就不对各种处理方式的优劣做比较了，仅仅叙述使用频率较高的两种处理方式。

根据报错的指引，把两个大的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也应征了这个推论，那么现在要怎么解决这个问题？