索引碎片会显著降低mysql查询和写入性能，导致I/O增加、缓冲池命中率下降、范围扫描变慢等；可通过INFORMATION_SCHEMA查询页利用率和Cardinality判断碎片程度；推荐使用ALTER table … ENGINE=InnoDB或DROP/ADD INDEX在线重建，避免OPTIMIZE TABLE和已废弃的innodb_defragment。
索引碎片确实会显著拖慢查询和写入性能
当 MySQL（尤其是 InnoDB）频繁执行 INSERT、UPDATE、delete，特别是对主键或二级索引做非顺序写入时，页内会产生空洞、页间产生物理碎片。这会导致：磁盘 I/O 增加（需要读更多页）、缓冲池命中率下降、范围扫描变慢、甚至 select ... ORDER BY 或 LIMIT 查询出现意外延迟。
如何确认索引是否已碎片化
别靠猜测，用 INFORMATION_SCHEMA.INNODB_INDEX_STATS 和 SHOW INDEX 结合查真实数据分布：
-
SELECT table_name, index_name, stat_value FROM INFORMATION_SCHEMA.INNODB_INDEX_STATS WHERE database_name = 'your_db' AND table_name = 'your_table' AND index_name = 'your_index' AND stat_name = 'n_leaf_pages';—— 查叶子节点页数 -
SELECT data_length, index_length FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'your_db' AND table_name = 'your_table';—— 算出平均索引页利用率（index_length / (n_leaf_pages × 16384)），低于 65% 就值得警惕 - 观察
SHOW INDEX FROM your_table中的Cardinality是否长期远低于实际行数（可能说明统计信息陈旧，也常伴随碎片）
在线重建索引的可靠方法（MySQL 5.6+）
InnoDB 支持 ALgoRITHM=INPLACE 的 DDL，但不是所有操作都真正“在线”。关键看是否锁表、是否阻塞 DML：
- 最安全：
ALTER TABLE your_table ENGINE=InnoDB;—— 触发整表重建，释放碎片，MySQL 5.6+ 默认使用INPLACE（不锁表），但大表仍耗时长、占临时空间 - 精准重建单个索引：
ALTER TABLE your_table DROP INDEX idx_name, ADD INDEX idx_name (col1, col2);—— 实际是先删后建，同样走 inplace（只要没改列定义），比全表重建轻量 - 避免用
OPTIMIZE TABLE：它本质就是ALTER TABLE ... ENGINE=InnoDB，但在某些版本中会隐式升级为copy算法（尤其开启innodb_file_per_table=OFF时），导致锁表 - 注意
innodb_defragment已在 MySQL 8.0.29 废弃，不要用
日常维护中容易被忽略的关键点
碎片不是“修一次就一劳永逸”的问题。高频更新 + 长期不维护的组合才是罪魁祸首：
- 批量写入尽量按主键顺序（比如时间戳 + 自增 ID 组合），避免随机插入引发页分裂
- 定期检查（比如每周）
data_free字段：SELECT table_name, data_free FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='your_db' AND data_free > 1024*1024*100;—— 超过 100MB 空闲空间大概率有严重碎片 - 如果用的是 MySQL 8.0+，可启用
innodb_defragment_frequency（需配合innodb_defragment_n_pages），但它只对特定模式有效，且无法替代主动重建 - 分区表的碎片要单独处理每个分区，
ALTER TABLE ... REORGANIZE PARTITION不等于重建索引，得对每个分区显式ALTER TABLE ... ENGINE=InnoDB
碎片影响往往在业务高峰期才暴露，而那时再重建风险极高。把索引健康检查纳入 dba 日常巡检项，比等慢查询报警再动手更稳妥。