MySQL需清理索引碎片是因为频繁DML导致页内空闲空间和页间物理不连续，降低B+树利用率、增加I/O、削弱缓冲池命中率；OPTIMIZE TABLE可有效重建表与索引以清理碎片，但可能引发锁阻塞或执行计划突变；轻量替代方案包括ALTER TABLE ... FORCE或REBUILD；是否清理应基于Data_free占比＞20%、页分裂率＞1%/秒及P99延迟升高等性能指标综合判断。

MySQL 为什么需要清理索引碎片？

InnoDB 表在频繁的 INSERT、UPDATE、DELETE 后，页内会产生空闲空间（gap），页之间也可能出现物理存储不连续，导致 B+ 树索引页利用率下降、查询时 I/O 增加、缓冲池命中率降低。这不是“磁盘碎片”那种操作系统级问题，而是 InnoDB 存储引擎内部的逻辑与物理碎片混合现象。

典型表现包括：SHOW TABLE STATUS 中 Data_free 值持续偏高（尤其远大于 0）、innodb_buffer_pool_read_requests 与 innodb_buffer_pool_reads 比值明显下降、慢查询中 EXPLAIN 显示 rows 估算严重偏离实际扫描量。

OPTIMIZE TABLE 是否真能清理索引碎片？

对 InnoDB 表执行 OPTIMIZE TABLE t1 实际等价于 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY（MySQL 5.7 及以前）或 ALGORITHM=INPLACE（8.0+ 默认，但仅当满足条件时）。它会重建表和所有二级索引，释放空闲页、重排数据页、更新统计信息，是清理碎片最直接有效的方式。

但要注意：

• OPTIMIZE TABLE 在 MySQL 8.0 中默认使用 ALGORITHM=INPLACE，但若表含全文索引、虚拟列、外键约束等，可能自动退化为 COPY，触发全表锁（DML 阻塞）
• 执行期间会持有 S 锁（共享锁），阻塞写入；若退化为 COPY，则升级为 X 锁（排他锁），读也会被阻塞
• 对于大表，即使 INPLACE 模式，仍需大量 I/O 和临时空间，且统计信息更新后可能导致执行计划突变

OPTIMIZE TABLE orders;

更轻量的替代方案：ALTER TABLE ... FORCE 或 REBUILD

想绕过 OPTIMIZE TABLE 的语义歧义和隐式行为，可显式用 ALTER TABLE 触发重建：

• ALTER TABLE t1 ENGINE=InnoDB; —— 最通用，强制重建（同 OPTIMIZE 效果）
• ALTER TABLE t1 FORCE; —— MySQL 5.6+ 支持，语义明确为“重建表”，不修改结构，避免误判字段变更
• ALTER TABLE t1 REBUILD; —— MySQL 8.0.23+ 引入，专为在线重建设计，只重排数据页和索引页，不重新计算统计信息（需后续 ANALYZE TABLE）

三者均会重建所有索引，但 REBUILD 是目前最可控、开销最小的选择，前提是版本够新且无需即时更新统计信息。

ALTER TABLE logs REBUILD;

如何判断是否真有必要清理？别盲目执行

碎片不是“越高越要清”，关键看是否影响性能。建议先量化评估：

• 查 Data_free 占比：SELECT (Data_free / Data_length) AS frag_ratio FROM information_schema.TABLES WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='t1'; —— 超过 20% 且持续增长才值得干预
• 看页分裂频率：SHOW ENGINE INNODB STATUS\G 中查找 Number of pages written 和 Number of page splits，分裂率长期 > 1%/秒说明写入模式激进
• 对比 cardinality 与实际唯一值：若 SHOW INDEX FROM t1 中某索引 Cardinality 远低于预期（如时间戳索引 cardinality ≈ 1），说明统计失真，碎片可能已干扰采样

真正容易被忽略的是：碎片影响往往藏在长尾延迟里——单条查询没变慢，但 P99 响应时间升高、缓冲池 pages made young 次数异常增多。这类问题必须结合监控指标交叉验证，不能只盯一个 Data_free。