MySQL OPTIMIZE TABLE 在 InnoDB 上到底做了什么？真的能整理碎片吗？

InnoDB 表用久了之后，很多 DBA 会发现一个现象：明明删掉了大量数据，磁盘上的 .ibd 文件却几乎没变小，有时候反而更大了。这时候不少人会想到用 OPTIMIZE TABLE 来整理碎片，但执行完一看，文件大小可能没变化，或者干脆提示 Table does not support optimize 做了 rebuild。要理解这个命令的真实行为，得先从 InnoDB 的存储结构说起。

一、InnoDB 的碎片到底是怎么来的

InnoDB 采用聚簇索引组织数据，表本身就是一颗 B+ 树，数据行存放在叶子节点中。每一个页（page）默认 16KB，当删除一行数据时，InnoDB 并不会立刻把这行占用的字节从页里抹掉，而是把这行标记为已删除，空间进入页内的空闲链表。这些被标记删除但尚未复用的空间，就是最常被讨论的“空洞”。

空洞产生的方式不止删除一种。更新操作同样会造成碎片：如果更新后的行变长，导致原页放不下，InnoDB 会进行页分裂，把部分数据搬到新页，原页留下空隙。大量随机的插入再删除，比如临时表、日志表按时间清理旧数据的场景，也会让 B+ 树的页填充率逐渐下降，极端情况下一个页里只有几行有效数据，剩下的全是空闲空间。这些页依然占据着表空间文件，所以文件不会因为数据删除而自动缩小。

需要特别澄清一点：InnoDB 的表空间文件只增不减是其设计行为，不是 bug。ibdata 文件（共享表空间模式）永远不会自动收缩，而独立表空间模式下的 .ibd 文件，只有发生 rebuild 时才可能重建出更小的新文件。理解了这一点，才能明白 OPTIMIZE TABLE 为什么是现在这个样子。

二、OPTIMIZE TABLE 在 InnoDB 上的真实行为

在 MyISAM 时代，OPTIMIZE TABLE 会执行类似 defragmentation 的操作，直接整理数据文件。但 InnoDB 不支持原地整理碎片，所以 MySQL 的处理方式是：如果表支持在线重建（InnoDB 满足条件时），执行 ALTER TABLE ... FORCE，本质上是重建整张表。可以看一下实际执行时的输出：

mysql> OPTIMIZE TABLE t_order;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.t_order  | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t_order  | optimize | status   | OK       |
+---------------+----------+----------+----------+

注意第一行提示：Table does not support optimize, doing recreate + analyze instead。翻译过来就是“该表不支持 optimize，改为重建 + 分析统计信息”。也就是说，在 InnoDB 上，OPTIMIZE TABLE 等价于以下语句：

-- 等价的写法一
ALTER TABLE t_order ENGINE=InnoDB;

-- 等价的写法二（更推荐的现代写法）
ALTER TABLE t_order FORCE;

-- 重建的同时顺便更新统计信息
ANALYZE TABLE t_order;

重建过程是这样的：MySQL 创建一张影子表，把原表数据按主键顺序重新插入进去，插入过程中新页会被紧凑地填充，删除空洞和页分裂残留自然消失。完成后删除原表，把新表重命名顶上去。如果 innodb_file_per_table 开启（这是默认值），新的 .ibd 文件往往比原来小很多；如果关闭，数据写入共享表空间 ibdata1，虽然文件不会缩小，但至少消除了碎片，提升了缓冲池利用率和扫描效率。

还有一点容易被忽略：在 MySQL 5.6.17 之前，OPTIMIZE TABLE 只能重建全文索引以外的部分且锁写；从 5.6.17 开始支持 online DDL，重建期间允许并发 DML，只在收尾阶段短暂持有元数据锁。但“允许并发”不等于“没有代价”，重建会占用大量 IO 和磁盘空间（需要原表两倍左右的空间），buffer pool 也会被大量脏页冲刷影响，业务高峰期执行务必谨慎。

三、如何判断是否需要执行以及生产环境的注意事项

不是碎片出现了就要马上 optimize。一个常用的判断依据是 information_schema 中 DATA_FREE 字段，它表示表空间中已分配但未使用的空间量：

SELECT table_schema, table_name,
       data_free / 1024 / 1024 AS free_mb,
       data_length / 1024 / 1024 AS data_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
  AND table_schema NOT IN ('mysql', 'information_schema',
                           'performance_schema', 'sys')
  AND data_free > 100 * 1024 * 1024
ORDER BY data_free DESC;

这里要提醒一个坑：对于独立表空间的表，统计信息里的 data_free 默认只反映共享表空间中的空隙，值可能一直是几 MB 的固定数，并不代表真实碎片。更可靠的做法是对比“逻辑数据量”和“物理文件大小”：如果一张表估算的数据量只有几个 GB，而 .ibd 文件膨胀到了几十 GB，删除操作又频繁，那才值得做一次 rebuild。也可以观察 Show engine innodb status 中的页分裂情况，或者用 SELECT ... FROM information_schema.INNODB_BUFFER_PAGE 分析缓冲池中页的填充率。

生产环境执行前，建议做好这几件事：确认磁盘剩余空间至少是目标表大小的 1.2 倍以上；确认主从延迟可以承受（rebuild 产生大量 binlog）；选择业务低峰期，并设置会话级的 lock_wait_timeout 较小的值，避免收尾阶段的元数据锁排队阻塞后续查询；大表执行时间可能长达数小时，提前评估窗口。执行完成后对比文件大小确认效果：

-- 执行前查看物理文件大小
SELECT table_name,
       (data_length + index_length) / 1024 / 1024 AS total_mb
FROM information_schema.tables
WHERE table_name = 't_order';

最后补充一句，如果目标只是让优化器拿到更准确的统计信息，用 ANALYZE TABLE 就够了，代价小得多；OPTIMIZE TABLE 的价值在于物理层面的空间回收与页紧凑化，两者不要混用。对持续写入的业务表，与其定期 rebuild，不如在设计阶段就考虑分区表按时间归档，从源头减少大删除带来的碎片问题。

MySQLOPTIMIZE TABLEInnoDB碎片整理修改时间：2026-09-11 03:32:32