MySQL OPTIMIZE TABLE 在 InnoDB 上的实际作用与碎片整理

OPTIMIZE table 在 InnoDB 中重建整张表：先创建结构相同的新表，按主键顺序插入原数据，再原子替换；需额外磁盘空间、持S锁阻塞DML；文件变小仅表示回收空闲页，不提升查询性能。

OPTIMIZE TABLE 在 InnoDB 上到底做了什么

它不是“整理碎片”这么简单，而是重建整张表：先创建一个空的、结构相同的新表，把原表数据逐行 INSERT 进去（按主键顺序），再原子性地替换原表。这意味着 OPTIMIZE TABLE 会触发一次全表重建，期间需要额外磁盘空间（约等于原表大小），并持有 S（共享）锁较长时间，DML 操作会被阻塞或排队。

为什么执行后 .ibd 文件变小了但查询没变快

文件体积下降，通常只说明「未使用的页被回收」，比如之前有大量 delete 导致页内空闲空间多，重建后紧凑排列。但这不等于 B+ 树深度变浅或缓存命中率提升。InnoDB 的查询性能更依赖：buffer_pool_size 是否足够、索引选择性、查询是否走索引、以及数据局部性。如果业务是随机主键写入 + 频繁更新，重建后的“紧凑”状态可能几小时内就又退化了。

替代 OPTIMIZE TABLE 的更轻量方案

多数场景下没必要跑全表重建：

• 用 ALTER TABLE tbl_name ENGINE=INNODB 替代，语义等价但更明确，且可配合 ALgoRITHM=INPLACE（5.6+）减少锁时间（仍需全表拷贝数据）
• 对大表，优先考虑 pt-online-schema-change 工具，在线重建，避免锁表
• 监控 information_schema.INNODB_SYS_TABLESTATS 中的 STAT_INDEX_SIZE 和 STAT_DATA_SIZE，比看文件大小更能反映真实膨胀
• 定期检查 DATA_FREE 字段（SHOW TABLE STATUS 输出），> 0 且持续增长才值得干预

容易被忽略的副作用和限制

OPTIMIZE TABLE 会重置表的 AUTO_INCREMENT 值为当前最大值 +1；如果表有外键引用，必须先禁用 FOREIGN_KEY_CHECKS；在 mysql 5.7.24+ 默认开启 innodb_file_per_table 下才有效果，否则所有表共用 ibdata1，OPTIMIZE 对系统表空间无影响。另外，如果表正在被 X 锁占用（如长事务未提交），命令会直接失败并报错 Error 150 或超时。