MySQL删除数据后如何通过Optimize Table回收存储空间？

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

InnoDB将数据存储在页面（page）中，执行DELETE操作时，只是将行标记为已删除，页面本身仍保留在+.ibd文件中。后续的INSERT操作会复用这些页面。这种空洞不会自动收缩文件，操作系统也不会回收这部分空间。

常见误判现象：

• 执行 SELECT table_name, data_length, data_free FROM information_schema.tables WHERE table_schema='your_db' AND table_name='your_table';，发现 data_free 值很大（比如 > 100MB），但磁盘上 your_table.ibd 文件体积没变
• SHOW TABLE STATUS LIKE 'your_table'; 中 Data_free 字段显示非零，说明已有明显碎片
• 表中大量使用 VARCHAR、TEXT、BLOB 类型，且经历过高频 UPDATE/DELETE

OPTIMIZE TABLE 实际做了什么（InnoDB 5.6+）

它本质是执行一次隐式的 ALTER TABLE ... ENGINE=InnoDB，走的是 Online DDL 流程（但仍有锁）：

• 新建一个空的 .ibd 文件（临时命名如 #sql-ib123-456789.ibd）
• 按主键顺序逐页读取原表数据，写入新文件（同时重建二级索引）
• 完成写入后，原子性地交换文件名，删掉旧 .ibd
• 最终效果：物理文件大小 ≈ 实际数据 + 索引占用，data_free 归零或极小

注意：OPTIMIZE TABLE 在整个过程中会对表加 MDL 锁（允许读，阻塞写），持续时间取决于表大小和 I/O 性能；如果表超过 10GB，建议在低峰期操作。

比 OPTIMIZE TABLE 更轻量的替代方案

并非所有场景都值得触发完整重建。以下方式更可控、更低风险：

• 确认 innodb_file_per_table=ON（否则 OPTIMIZE TABLE 对共享表空间 ibdata1 无效）
• 对大表优先尝试 ALTER TABLE your_table ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=SHARED; —— MySQL 5.6+ 支持部分 inplace 操作，虽不彻底压缩，但能减少锁时间和 I/O
• 若只是想清空整张表，直接用 TRUNCATE TABLE your_table;，它立即释放 .ibd 文件并重置 AUTO_INCREMENT
• 极端情况（如误删 90% 数据又无法停机）：导出再导入，mysqldump -u user -p db table --no-create-info > data.sql，然后 DROP TABLE + CREATE TABLE + mysql -u user -p db

容易被忽略的关键限制

很多人卡在第一步就失败，原因往往不是语法错，而是配置或权限问题：

• OPTIMIZE TABLE 要求用户有 ALTER 和 INDEX 权限，仅 SELECT 不够
• 临时目录（tmpdir）需有足够空间容纳新表——至少等于当前 .ibd 大小
• MyISAM 表的 OPTIMIZE TABLE 会锁全表（读写都阻），而 InnoDB 是只锁写；别混用引擎判断锁行为
• MySQL 8.0+ 默认关闭 innodb_file_per_table？不，它默认是 ON，但老实例可能仍为 OFF，务必先查 SELECT @@innodb_file_per_table;

真正决定空间能否回收的，从来不是命令本身，而是表是否启用了独立表空间、是否有足够磁盘余量做重建、以及你愿不愿意承担那几十秒到几分钟的写阻塞。