MySQL 8.0 InnoDB ibd文件非预期大幅扩容问题咨询

异常扩容的核心原因 #

• CHAR列实际存储开销远超预期
  你开启了PAD_CHAR_TO_FULL_LENGTH SQL模式，插入的"1"会被自动填充空格到150个字符的长度；再加上字符集是utf8mb4（每个字符最多占4字节），每个非NULL的CHAR(150)列实际要占150*4=600字节，绝非你预想的1字节。
• 行长度剧增引发页分裂与批量空间分配
  原表中CHAR(150)列全为NULL时，InnoDB只用NULL位图标记这些列，每行约10KB；写入非NULL值后，单行列大小直接加600字节，要是批量给多个CHAR列赋值，每行总大小会远超InnoDB默认的16KB页容量。
  当行长度超过页剩余空间时，InnoDB会触发页分裂，把部分行移到新页，原页留下的空洞没法立即回收；而且InnoDB是以**区（Extent）**为单位分配空间的（每个区含64个16KB页，也就是1MB），哪怕只需要少量新页，也可能一次性分配多个区，导致文件大小跳涨。
• 溢出页的额外开销
  当单行总大小超过页容量时，InnoDB会把部分列数据存到溢出页，每行可能对应好几个溢出页，进一步加剧空间占用。
• ibd文件无法自动收缩
  InnoDB的ibd文件一旦扩容，就算后续把数据改回NULL或者删除，已分配的空间也不会自动释放，只会标记为可复用，文件大小会一直维持高位。

控制文件大小的优化方案 #

• 调整CHAR列定义与SQL模式
  • 关闭PAD_CHAR_TO_FULL_LENGTH：执行SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';（全局生效要修改my.cnf后重启），这样CHAR列只存实际字符长度，不用填充空格，能大幅减少非NULL值的存储开销。
  • 把CHAR(150)改成VARCHAR(150)：VARCHAR只存实际字符长度（加1-2字节的长度标记），NULL状态下同样只占NULL位图空间，比CHAR更省空间，尤其适合非固定长度的场景。
• 优化行格式与页大小
  • 启用COMPRESSED行格式：建表时指定ROW_FORMAT=COMPRESSED，InnoDB会自动压缩数据，有效降低大字段的存储空间。
  • 调整页大小（谨慎操作）：要是单行长度远超16KB，可在初始化数据库时设置innodb_page_size=32K或64K（仅支持新库，已有库没法改），减少页分裂和溢出页的产生。
• 回收空闲空间
  • 执行OPTIMIZE TABLE your_table_name;：这个命令会重建表，回收空洞空间，把ibd文件缩小到实际数据占用的大小。注意：执行期间表会被锁，得在业务低峰期操作；MySQL 8.0之后也可以用ALTER TABLE your_table_name ENGINE=InnoDB;，效果一样还支持在线DDL。
• 批量写入的优化技巧
  • 尽量批量更新，别单行更新：减少页分裂的频率，降低碎片生成。
  • 先删后插（业务允许的话）：如果要把大量NULL改成非NULL，可先导出数据，清空表后重新插入，避免页分裂带来的空间浪费。
• 日常监控与维护
  • 定期查看表碎片：执行SHOW TABLE STATUS LIKE 'your_table_name'\G，关注Data_free字段，要是这个值太大，及时做碎片整理。

内容的提问来源于stack exchange，提问作者Gorazd Petrovič