MySQL在运行一段时间后，尤其是那些写操作频繁的表，常常会出现索引碎片。索引碎片是指B+树索引结构中由于数据页的删除、更新导致空间没有被有效复用，从而让索引文件变得松散、不连续。这种碎片会增加磁盘IO和页读取量，使查询效率下降。
为什么会产生索引碎片
当执行DELETE语句时，InnoDB并不会立即把索引记录占用的空间返还给操作系统，而是标记为可重用。UPDATE若导致记录长度变化或主键变更，也会产生页分裂。随着时间推移，索引页填充率降低，形成碎片。
- 大量删除后未重建索引
- 使用UUID或随机主键引起页分裂
- 频繁的更新变长字段
如何查看索引碎片情况
可以通过information_schema中的表来观察数据空闲空间。下面的语句能列出潜在碎片较多的表：
SELECT table_name, engine, data_free, table_rows FROM information_schema.tables WHERE table_schema = 'your_db' AND data_free > 0 ORDER BY data_free DESC;
其中data_free表示分配了但未使用的字节数，如果数值较大且表行数相对稳定，就可能存在明显碎片。
MySQL索引碎片整理方法
使用OPTIMIZE TABLE
对InnoDB表，OPTIMIZE TABLE会触发重建表和索引，回收空间。
OPTIMIZE TABLE user_order;
执行时会对表加锁，并在完成后更新统计信息。
使用ALTER TABLE重建
另一种方式是利用InnoDB的在线DDL来重建表：
ALTER TABLE user_order ENGINE=InnoDB;
该语句会创建一个新表并拷贝数据，完成后替换原表，同样能起到整理碎片的作用。
对分区表的处理
如果是分区表，可以只针对某个分区做整理：
ALTER TABLE log_data REBUILD PARTITION p202301;
整理碎片的注意事项
| 事项 | 说明 |
|---|---|
| 业务低峰操作 | 整理过程消耗IO和CPU，应在访问量少时执行 |
| 预留磁盘空间 | 重建表需要额外空间存放临时数据 |
| 备份优先 | 大表操作前建议先备份，防止意外 |
什么时候不需要频繁整理
如果表的写入非常规律且以追加为主，或者data_free占比较小，则不必频繁整理。过度整理反而会增加数据库负担。一般建议结合监控，按季度或半年评估一次。
合理设计主键、控制变长字段更新，能从源头减少索引碎片的产生。
通过上面介绍的MySQL索引碎片整理方法，你可以根据实际表状态和业务节奏，选择合适的维护方式，保持数据库查询性能稳定。