OPTIMIZE TABLE 并非万能解药，因其锁表、耗双倍磁盘空间且仅在 DATA_FREE 显著偏高（>30%）时才适用；更优方案是分批删除、ALTER TABLE ... ALGORITHM=INPLACE、分区 DROP 或 TRUNCATE。
为什么 OPTIMIZE TABLE 在大批量删除后不是万能解药
大批量 DELETE 后表空间不释放，是 InnoDB 的常态行为——它只标记页为“可复用”，不主动归还磁盘给操作系统。OPTIMIZE TABLE 确实会重建表、整理碎片、回收空间，但代价很高：全程锁表（MySQL 5.6+ 对普通表仍是独占 DML 锁），且需要两倍临时磁盘空间。如果你的表有 100GB，执行期间可能瞬时占用 200GB+ 临时空间，而磁盘爆满或主从延迟飙升就是直接后果。
- 仅当
DATA_FREE显著偏高（比如 > 表实际数据量的 30%）且业务低峰期可用时才考虑 -
OPTIMIZE TABLE在 MySQL 8.0+ 对已启用innodb_file_per_table=ON的表更安全，但依然锁表 - 如果启用了
innodb_file_per_table=OFF，OPTIMIZE无法回收系统表空间（ibdata1）中的碎片，此时无效
更稳妥的替代方案：ALTER TABLE ... ENGINE=InnoDB
这和 OPTIMIZE TABLE 底层行为一致（重建表 + 重写聚簇索引），但语义更明确、兼容性更好，且支持在线 DDL（MySQL 5.6+）。关键在于加 ALGORITHM=INPLACE 和 LOCK=NONE 参数，才能真正避免锁表。
- 必须确认表没有全文索引、外键约束或虚拟列（否则可能退化为 COPY 模式）
- 命令示例：
ALTER TABLE t1 ENGINE=InnoDB ALGORITHM=INPLACE LOCK=NONE; - 执行前先查
SHOW CREATE TABLE t1;，确保引擎确实是InnoDB，避免误操作变成 MyISAM - 注意监控
INFORMATION_SCHEMA.INNODB_METRICS中的dml_reads和dml_writes，防止长事务阻塞 DDL
真正治本：从删除方式入手，避免碎片爆炸
问题根源不在“删完要不要优化”，而在“怎么删”。一次性 DELETE FROM t WHERE ... 删除百万行，会生成海量 undo 日志、分裂 B+ 树节点、留下大量空闲页。不如分批控制节奏。
- 用主键范围分片删除：
DELETE FROM t WHERE id BETWEEN 10000 AND 20000;，每次删 1w 行，配合SLEEP(0.1)降低 I/O 压力 - 避免
ORDER BY RAND()或无索引条件的DELETE，全表扫描+逐行判断会拖慢整个过程并加剧锁竞争 - 如果只是清空旧数据，优先考虑
TRUNCATE TABLE（注意：不可回滚、重置自增、需 DROP 权限） - 对日志类大表，按时间分区（
PARTITION BY RANGE (TO_DAYS(created_at))）后，直接DROP PARTITION是零碎片、秒级操作
怎么快速判断是否真需要物理重组
别凭感觉，看 INFORMATION_SCHEMA.TABLES 和 SHOW TABLE STATUS 的真实指标。碎片不是“看起来乱”，而是“空间浪费影响查询性能”。
- 查碎片率：
SELECT DATA_LENGTH, DATA_FREE, ROUND(DATA_FREE/DATA_LENGTH, 2) AS frag_ratio FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; -
DATA_FREE = 0表示当前无明显空闲页，OPTIMIZE没意义 - 即使
DATA_FREE很大，也要结合SELECT COUNT(*)和AVG_ROW_LENGTH判断是否因行长度波动大导致“假碎片”（如大量 TEXT 字段） - 观察
SHOW ENGINE INNODB STATUS\G中的Hash table size和buffer pool hit rate——如果命中率长期低于 95%，才值得怀疑碎片影响缓存效率
真正麻烦的从来不是执行一条 OPTIMIZE，而是没想清楚：删除逻辑是否可优化、表结构是否适配生命周期、监控指标是否真的指向空间问题。盲目优化，可能比不优化更伤。
