【MySQL经典案例分析】关于数据行溢出由浅至深的探讨
最新回答
清风未央
2026-10-22 06:42:10
从常见的报错说起，数据“大”不仅意味着存储空间占用多，还包括单个字段存储多、大，数据留存时间长，数据冗余多，冷热数据不明显导致的体量大，访问峰值随热点变化明显，逻辑处理复杂导致数据存储压力放大等问题。常见处理方式包括将字段改为text或blob类型，或缩小和拆分字段。使用频率较高的两种处理方式各有利弊，text或blob类型增大了数据存储容量，但对索引页的处理方式受限，而缩小和拆分则依赖于业务场景和逻辑需求，需要评估工程量。深入探索限制大小“65535”的概念，该值并非单个varchar(N)中N的最大限制，而是整个表非大字段类型的字段的bytes总合。不同字符集对字段存储的最大值有影响，例如UTF8字符需要3个字节存储，GBK是双字节的。可变长度列在评估字段大小时还要考虑存储列实际长度的字节数。BLOB、TEXT、JSON列与varchar、char等字段不同，列长度信息独立于行长存储，可以达到65535字节真实存储。定义NULL列会降低允许的最大列数。row长度与多个因素相关，包括所有列的长度、NULL列数量、删除标志和变量长度列的数量。对于InnoDB表，NULL和NOT NULL列的存储大小相同。InnoDB允许单表最多1000个列。varchar主键支持的字段长度限制依据字符集，GBK使用双字节，UTF8使用三字节。不同引擎对索引的限制不同。
下面分享一个业务故障案例。线上业务出现大量报错，导致无法写入数据。首先，我们考虑业务存在如数据量过大导致表结构限制的问题。查看业务库表结构后，排除了表中非大字段之和超过65535的可能，因为业务在写入时才报错。接着，检查业务提供的SQL，发现appversion、datadata、elt_stamp、id这几个非大字段并没有超过限制，且mediumblob类型字段最大可存储16M，业务数据远未达到这个量级。根据报错提示，将这些非大字段均改成blob类型，问题依旧。冷静下来后，注意到业务的失败率不是100%，说明还是有成功的请求。对比成功与失败的SQL，发现差异在于mediumblob类型字段。问题出在max_allowed_packet参数上，单个请求超过大小被拒绝。检查配置的值（1G），发现实际数据长度远没有这么大，排除了该原因。接着，查看innodb_page_size参数，该值默认为16K。在Barracuda文件格式下，InnoDB存储引擎采用了两种新的行记录格式：Compressed和Dynamic，用于存放BLOB数据。Compressed行记录格式支持压缩存储，而Dynamic行记录格式则允许在数据页中只存放20个字节的指针，实际数据存储在BLOB Page中。解决此问题的方法是修改全局变量innodb_file_format为'Barracuda'，然后将表的ROW_FORMAT属性设置为'COMPRESSED'。
通过这个案例，我们可以提炼出两个值得深入研究的点：关于innodb_page_size，从MySQL5.6开始，innodb_page_size可以设置为8K、4K，默认为16K。如果需要修改这个值，可以在初始化时加入my.cnf文件，但如果是已经创建的表，修改后启动MySQL会报错。在MySQL5.6版本之前，修改这个值需要在源码上做修改，然后重新编译MySQL。关于Row_format，InnoDB存储引擎保存记录是以行的形式存放的。在InnoDB 1.0.x版本之前，提供了Compact和Redundant两种格式。MySQL 5.1中的innodb_plugin引入了Barracuda文件格式，该格式拥有新的两种行格式：compressed和dynamic。通过命令SHOW TABLE STATUS LIKE 'table_name'；可以查看当前表使用的行格式。修改现有表的行模式为compressed或dynamic，必须先将文件格式设置为Barracuda，然后使用ALTER TABLE tablename ROW_FORMAT=COMPRESSED；命令才能生效。
关于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。更大的页面更有利于提升速度，因为一个页面可以放入更多的行，每个IO写的大小更大，可以更少的IOPS写更多的数据。但是，对于短小行长的随机读写，则不适合使用这么大的页面，这会导致IO效率下降，大IO只能读取到小部分。
总结，关于innodb_page_size和Row_format的设置，需要根据业务需求和性能考量来选择合适的值。innodb_page_size可以设置为8K、4K或16K，根据读写场景的不同选择不同的值可以优化MySQL性能。Row_format的选择也会影响到数据的存储和查询效率，通过使用compressed或dynamic行格式，可以更好地优化BLOB数据的存储和查询。同时，还需要关注数据的存储效率和查询性能，避免大字段浪费大量空间，影响查询速度。在设计表结构时，合理使用大字段存储策略，如将多个大字段组合存储，或使用压缩存储，可以提高空间利用效率和性能。最后，要定期评估和调整innodb_page_size和Row_format的设置，以满足业务需求和优化性能。本文内容已由作者授权腾讯云+社区发布。
热门标签
