optimize table 本质是重建整张表：myisam 重整文件，innodb 等价于 alter table ... engine=innodb + analyze table，会锁表、需双倍磁盘空间，且可能因统计信息未更新导致 count(*) 变慢。
OPTIMIZE TABLE 实际干了什么
它不是单纯“整理碎片”，而是重建整张表：先创建新表（带优化后的结构），把旧数据逐行拷贝过去，再原子替换。对 MyISAM 是直接重整索引+数据文件；对 InnoDB，本质等价于 ALTER TABLE ... ENGINE=InnoDB + 统计信息更新 + ANALYZE TABLE。所以它会锁表（或至少加 MDL 锁），期间写操作阻塞，读可能被降级为一致性读但延迟上升。
常见错误现象：OPTIMIZE TABLE 执行完发现磁盘空间反而涨了——因为新表写入时旧表还没删，临时需要 2 倍空间；线上执行后 SELECT COUNT(*) 变慢——新表统计信息没及时生效，需等后台采样或手动 ANALYZE TABLE。
使用场景：表长期高频 DELETE/UPDATE 后 Data_free 显著增长（查 information_schema.TABLES），且业务能接受短时锁表。
ALTER TABLE ENGINE=InnoDB 的真实行为
这个命令对已经是 InnoDB 的表，就是一次“无变更重建”：不改引擎、不改列、不改约束，只重建聚簇索引和二级索引。它跳过统计信息更新和 ANALYZE 步骤，也不重置 auto_increment 值（OPTIMIZE 会重置）。性能上略快一点，因为少两步；但锁表现和空间占用和 OPTIMIZE 几乎一致。
参数差异：ALTER TABLE t ENGINE=InnoDB 可加 ALGORITHM=INPLACE（MySQL 5.6+），但仅当表无全文索引、无虚拟列等限制时才真走原地算法；否则仍退化为 copy-alter，效果等同 OPTIMIZE。
容易踩的坑：ALTER TABLE t ENGINE=InnoDB 在 MySQL 8.0+ 对已为 InnoDB 的表默认走 ALGORITHM=COPY，除非显式指定 ALGORITHM=INPLACE 并满足条件；否则你以为在“轻量重建”，实际还是全表拷贝。
碎片是否真的影响查询性能
绝大多数 OLTP 场景下，Data_free 高 ≠ 查询变慢。InnoDB 的页分裂是局部的，B+ 树结构本身容忍碎片；真正拖慢的是随机 I/O 比例上升（比如大量 WHERE id IN (…) 跨页读）、或缓冲池命中率因无效页增多而下降。如果 SHOW ENGINE INNODB STATUS 里 Buffer pool hit rate 稳定 >99%，碎片大概率不是瓶颈。
判断依据优先看：
- SELECT table_name, data_length, data_free FROM information_schema.TABLES WHERE engine='InnoDB' 中 data_free / data_length > 0.3
- innodb_buffer_pool_read_requests 与 innodb_buffer_pool_reads 比值持续低于 100（说明物理读激增）
- 慢查日志中大量单行主键查询耗时 >10ms
别只盯着 Data_free 数字大就跑 OPTIMIZE——它可能让正在运行的长事务卡住，或触发从库复制延迟突增。
线上环境更安全的替代方案
MySQL 5.7+ 支持在线 DDL，但 OPTIMIZE 和 ENGINE=InnoDB 都不支持完全无锁。更稳妥的做法是分批清理 + 主动收缩：
- 对大表，用 pt-online-schema-change --alter "ENGINE=InnoDB" --execute（Percona Toolkit），它通过触发器增量同步，避免长锁
- 对可归档历史数据的表，先 DELETE 再 ALTER TABLE ... DROP PARTITION（如果分区）或 OPTIMIZE 小范围子集
- 开启 innodb_file_per_table=ON（必须），否则 OPTIMIZE 不释放空间回操作系统，只内部复用
最常被忽略的一点：OPTIMIZE TABLE 后如果没重启 mysqld 或刷新 buffer pool，旧的脏页、预读页、LRU 列表里的废弃页可能还在内存里占位，实际性能提升要等自然淘汰或手动 SET GLOBAL innodb_buffer_pool_dump_now=ON 清理。
