MySQL OPTIMIZE TABLE 怎样判断表空间是否真的回收

我以前遇到过一种很容易误判的维护结果：OPTIMIZE TABLE 返回了 OK，但磁盘监控里的可用空间几乎没有变化。后来我把“回收”拆成了三个问题：InnoDB 内部是否重新组织了页面，表对应的表空间文件是否变小，以及空间是否真的回到了操作系统。三个问题不能用同一个字段回答。

判断 OPTIMIZE TABLE 是否真的回收空间，先确认存储引擎和表空间归属，再保存执行前快照，读取返回的 Msg_type、Msg_text，最后用同一口径比较表状态和独立 .ibd 文件。共享表空间里的空闲页可能只是留在 InnoDB 内部复用，并不意味着数据文件会缩小。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html

先把“回收”拆成三层

MySQL 官方文档对 OPTIMIZE TABLE 的描述是“重组表数据和相关索引的物理存储”，具体变化取决于存储引擎。对 InnoDB 来说，它通常映射为重建表的操作；对 MyISAM 来说，还可能包含修复、排序索引和更新统计信息。

所以我现在把结果分为三层看：

• 逻辑层：表和索引是否被重新组织，空闲页是否减少。
• 文件层：表是否使用独立的 file-per-table 表空间，目标 .ibd 文件是否有可比的变化。
• 系统层：操作系统可用空间是否增加。只有前两层的归属成立时，第三层才有解释价值。

先确认引擎和表空间归属

我不会先执行维护命令再猜结果，而是先确认目标表的引擎、表空间和当前大小。下面的查询只用于建立执行前快照，示例中的库表名请替换成自己的对象。

-- 中文注释：先记录引擎、逻辑大小和估算的空闲空间
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,
       TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders';

-- 中文注释：确认建表选项，重点看是否使用独立表空间
SHOW CREATE TABLE app.orders;

如果是 InnoDB 且启用了 file-per-table，表通常有自己的 .ibd 文件，重建后释放的空间才有机会返回操作系统。若表位于共享表空间，删除或重组产生的空闲空间更可能留在表空间内部，供 InnoDB 后续使用。这里的 DATA_FREE 是表状态中的观察值，不应单独当成操作系统已经释放的字节数。

执行后怎样判读证据

执行时我会把返回结果完整保存，而不是只看最后一行。OPTIMIZE TABLE 的结果集包含 Table、Op、Msg_type 和 Msg_text 等列；其中 note 能说明 InnoDB 实际采用了怎样的重建方式，status 才表示这次操作的状态。

-- 中文注释：执行单表维护，并保留返回的 Table、Msg_type、Msg_text
OPTIMIZE TABLE app.orders;

-- 中文注释：用相同口径读取执行后的表状态，便于和前置快照对照
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,
       TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders';

看到 status / OK，只能说明服务器接受并完成了这次操作；它不自动承诺 .ibd 一定缩小。看到 note 时，要结合表的引擎和表空间归属解释。若结果是错误或警告，应保留完整消息，先处理权限、空间和维护窗口问题，不要把“执行成功”与“空间回收成功”混为一谈。

用同一口径做前后复查

复查时至少保持三个条件一致：同一个 schema 和表名、同一个统计口径、同一个文件归属。对 file-per-table 表，应该把目标表的 .ibd 文件作为物理观察对象；不要把整个数据目录的变化直接归因于这一张表，因为重做日志、临时文件和其他并发任务也会改变目录大小。

如果 DATA_LENGTH 与 INDEX_LENGTH 下降，同时独立 .ibd 文件也变小，才可以较有把握地说这张表的物理空间发生了回收。如果逻辑指标改善但共享表空间文件没有变小，更合理的结论是空间在 InnoDB 内部得到整理或复用。若指标和文件都没有明显变化，也不代表命令一定无效：数据本来就很紧凑、删除量不足或表空间归属不支持文件级缩小，都可能出现这种结果。

我的维护判断清单

1. 先看 ENGINE，确认不是把 MyISAM 的维护语义套到 InnoDB 上。
2. 再看 file-per-table 或共享表空间，决定是否期待独立文件缩小。
3. 保存 OPTIMIZE TABLE 的完整结果集，特别是 Msg_type 和 Msg_text。
4. 用执行前后的 DATA_LENGTH、INDEX_LENGTH、DATA_FREE 做逻辑对照。
5. 只有在文件归属明确时，才把 .ibd 文件与操作系统可用空间纳入最终结论。

对我来说，OPTIMIZE TABLE 的价值不只是“让文件变小”，还包括重建索引、改善页面布局和整理可复用空间。是否值得执行，要结合表的写入删除模式和维护窗口判断，不能只盯着一次磁盘数字。

相关问题

OPTIMIZE TABLE 返回 OK 就代表磁盘空间回收了吗？

不代表。OK 是这次语句的状态结果，空间是否回到操作系统还要看存储引擎、表空间类型以及目标文件的前后变化。

为什么 DATA_FREE 变化了但 .ibd 文件没有变小？

DATA_FREE 反映的是表状态中的空间观察值；共享表空间或文件内部的空闲页可以被 InnoDB 继续复用，文件本身不一定缩小。