如何通过InnoDB表空间碎片检查与收缩有效解决MySQL磁盘空间虚高问题？

本文共计963个文字，预计阅读时间需要4分钟。

这不是误报，而是+InnoDB+表空间（包括共享表空间ibdata1或独立表空间.ibd文件）的实际现象：

典型表现包括：

• SELECT SUM(data_length) FROM information_schema.tables WHERE engine='InnoDB' 返回值远超 du -sh *.ibd 实际大小（对独立表空间）或 du -sh ibdata1（对系统表空间）
• 执行 OPTIMIZE TABLE 后 .ibd 文件明显变小，但之前一直没变化
• 监控显示磁盘使用率持续上涨，但业务写入量稳定甚至下降

如何准确检查 InnoDB 表碎片率？

不能只看 Data_length，要结合页利用率和空闲页比例。最直接方式是查 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 和 INFORMATION_SCHEMA.INNODB_SYS_INDEXES，但更实用的是用以下 SQL 计算估算碎片率：

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb, ROUND(data_free / 1024 / 1024, 2) AS free_mb, ROUND(100 * data_free / (data_length + index_length + data_free), 2) AS frag_pct FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema = 'your_db_name' AND data_free > 0 ORDER BY frag_pct DESC;

关键字段说明：

• data_free：该表所在表空间中当前标记为“空闲”的字节数（注意：仅对独立表空间 .ibd 准确；ibdata1 中此项恒为 0）
• 碎片率 > 25% 且 free_mb > 50（MB），通常值得收缩
• 若 data_free 为 0，说明该表未启用 innodb_file_per_table，或刚建表尚未发生过 delete/update

收缩 .ibd 文件的可靠方法（MySQL 5.6+）

必须确保 innodb_file_per_table = ON（默认开启），否则所有表都挤在 ibdata1 里，无法单表收缩。

推荐操作顺序（线上环境务必先备份）：

• 确认表使用独立表空间：SHOW CREATE TABLE your_table\G，输出中含 ENGINE=InnoDB 且无特殊提示即可
• 执行 ALTER TABLE your_table ENGINE=InnoDB —— 这是最轻量、最安全的重建方式，会释放碎片并重写整个 .ibd 文件
• 替代方案：OPTIMIZE TABLE your_table 效果相同，但会加锁更久；MySQL 8.0+ 支持 ALGORITHM=INPLACE 的在线 DDL，但 OPTIMIZE 仍会触发全表拷贝
• 执行后检查：ls -lh your_table.ibd 看文件大小是否下降，再查 information_schema.tables 验证 data_free 是否归零

注意：ALTER TABLE ... ENGINE=InnoDB 在 MySQL 5.7+ 默认走 online DDL（不锁表读），但若表有全文索引或外键约束，可能退化为 copy algorithm，需提前验证。

为什么不能直接 shrink ibdata1？

这是最常见的误解。一旦启用 innodb_file_per_table = OFF（或早期 MySQL 默认配置），所有表数据都写入 ibdata1，而该文件不支持在线收缩。即使删光所有表，ibdata1 体积也不会减少。

唯一安全收缩路径是：

• 导出全部数据（mysqldump --all-databases --single-transaction）
• 停库，删除 ibdata1、ib_logfile* 和所有 .ibd 文件
• 修改配置强制使用独立表空间：innodb_file_per_table = ON
• 重启 MySQL（此时会重建 ibdata1，仅保留系统元数据，约 76M）
• 导入数据

这个过程不可逆、耗时长、必须停服，所以生产环境应从一开始就启用 innodb_file_per_table，并定期检查新表是否意外落在 ibdata1 中（通过 SELECT name, file_format FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'db_name/%';，若 file_format 为空或为 Antelope，需警惕）。