核心思想：索引不是建完就一劳永逸的，它就像我们家里的书架，用久了会乱、会空出很多缝隙。定期整理，才能让查询又快又稳。

一、🤔 为什么会产生索引碎片？

1.1 先打个比方：书架与书

想象你有一个巨大的书架（B+Tree 索引），每层隔板就是一个数据页。

• 你习惯按书名的拼音顺序放书（主键有序），每次新书来了，就插到对应位置。

• 但书架每层一开始都是满的。有一天你买了一本“M”开头的书，发现“M”那层已经满了，怎么办？
  你只能把这一层的书分成两半，新书放中间，左右各留一些空隙。
  → 这就是页分裂，产生了内部碎片（每一层都有空闲位置）。

• 后来你清理掉一大批旧书（大量删除），某些层只剩零星几本书，但书架层数没变，空间还在。
  → 这就是外部碎片（整体浪费了空间）。

• 如果你买书完全不按拼音顺序（随机主键，如UUID），书可能被塞到各个角落，物理位置和逻辑顺序严重不一致，找书时就得来回跑。
  → 这是顺序碎片，导致预读失效。

1.2 碎片带来的“三宗罪”

• 查询变慢：本来读10页数据就能查完，现在要读15页，I/O多了50%。

• 内存浪费：MySQL 的缓冲池（Buffer Pool）里塞满了半空的数据页，有效数据少了。

• 空间膨胀：.ibd 文件远大于实际数据量，备份和迁移都更耗时。

二、🔍 如何检测索引碎片？（动手！）

方法1：看一眼 information_schema

sql

-- 查看指定表的碎片大小（单位MB）
SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS idx_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb
FROM 
    information_schema.tables
WHERE 
    TABLE_SCHEMA = 'your_database' 
    AND TABLE_NAME = 'your_table';

输出示例：

TABLE_NAME	data_mb	idx_mb	free_mb
orders	256.00	128.00	80.00

free_mb 就是表空间中未使用的空间。经验值：如果 free_mb 超过 data_mb + idx_mb 的 10%～15%，就该考虑清理了。

方法2：用 sys 库看更详细的填充率

sql

SELECT * FROM sys.schema_table_statistics WHERE table_name = 'orders';

这里可以看到索引页的利用率，低于 70% 时碎片通常较严重。

三、🧹 索引碎片清理的三种核心方法

方法1：OPTIMIZE TABLE —— 简单粗暴

sql

OPTIMIZE TABLE your_table;

原理：

• 创建一张新表 → 按主键顺序复制数据（相当于重新整理书架） → 删除旧表 → 新表改名。

• 优点：一条命令，效果彻底。

• 缺点：全程锁表，大表可能造成业务停写数十分钟到数小时。

💡 就像你直接把书全部搬出来，重新按顺序摆好，期间别人不能借书。

方法2：ALTER TABLE ... ENGINE=InnoDB —— 换壳法

sql

ALTER TABLE your_table ENGINE=InnoDB;

在 InnoDB 中，这条命令和 OPTIMIZE TABLE 效果完全一样，也是重建表。
选择哪种看习惯，我一般用 ALTER，因为更通用（比如更换引擎时也用）。

方法3：pt-online-schema-change —— 不锁表的神器

对于生产环境的大表，强烈推荐使用 Percona Toolkit 中的 pt-online-schema-change。

bash

pt-online-schema-change --alter "ENGINE=InnoDB" D=your_database,t=your_table \
  --execute --host=127.0.0.1 --user=root --password=yourpass

工作流程（你可以理解为“在线换书架”）：

1. 创建一张与原表结构相同的新表（影子表）。

2. 在原表上创建触发器，把后续的增删改同步到新表。

3. 分批将原表的历史数据复制到新表（可控制速度，不影响业务）。

4. 在极短时间内通过 RENAME TABLE 完成新旧切换。

• 优点：全程不锁表，对业务几乎无感。

• 缺点：需要额外安装工具，操作过程要关注主从延迟。

我第一次在生产环境用这个工具时，紧张得手心冒汗，但执行完发现业务一点没受影响，从此就爱上了它。

四、🚀 实战案例：订单表从“老年痴呆”到“身轻如燕”

场景

某电商 orders 表，存储了 5 亿订单，每天凌晨删除 3 年前的数据。几个月后，开发同事反馈：“按日期查订单越来越慢，SELECT COUNT(*) 要 8 秒多。”

诊断

sql

SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE
FROM information_schema.tables
WHERE TABLE_NAME = 'orders';

结果：

• 数据 + 索引总大小约 30 GB

• DATA_FREE = 12 GB（碎片占比 40%！）

操作

因为是核心表，不能锁表，选择 pt-online-schema-change：

bash

pt-online-schema-change --alter "ENGINE=InnoDB" D=shop,t=orders \
  --execute --chunk-size=10000 --max-lag=5 --critical-load="Threads_running=100"

大约 40 分钟后，命令执行完毕，期间业务无感知。

效果对比

操作	优化前	优化后	提升
SELECT COUNT(*)	8.2 秒	0.9 秒	9倍
按日期范围查询（10万条）	3.5 秒	0.6 秒	5.8倍
索引大小	18 GB	10 GB	节省44%空间

开发同事后来问我：“你们做了什么优化？订单查询突然变快了。” 我笑了笑：“只是给书架整理了整理。”

五、📊 碎片清理方法对比

方法	锁表	风险	适用场景	效果
OPTIMIZE TABLE	全程	低	小表、低峰期	★★★★★
ALTER TABLE ENGINE	全程	低	小表、低峰期	★★★★★
pt-online-schema-change	不锁	中（需监控）	大表、生产环境	★★★★★
删除并重建单个索引	部分	中	仅个别索引碎片	★★★

六、🎓 写在最后（一些碎碎念）

6.1 给新手的建议

如果你刚开始接触数据库维护，可能会觉得“碎片清理”是件很高深的事情。其实它就像你定期整理房间一样，不需要天天做，但也不能一年不做。

• 第一次操作：建议先拿一个测试表练手，用 OPTIMIZE TABLE 体验一下重建的过程，观察一下前后的查询速度。

• 线上操作：一定要先在从库试，或者用 pt-online-schema-change 这种安全工具，别直接在主库 OPTIMIZE，除非你已经做好了心理准备。

6.2 我踩过的坑

• 坑1：有一次以为凌晨业务量小，在主库直接 OPTIMIZE 一张 200GB 的表，结果锁了 2 小时，客服电话被打爆。从此以后，大表只用 pt-osc。

• 坑2：用 ALTER TABLE ENGINE 时，忘了检查磁盘空间，结果重建过程中磁盘写满，数据库直接挂了。所以，重建前一定要确保剩余磁盘空间大于当前表大小的 1.5 倍。

6.3 预防比清理更重要

• 主键设计：尽量用自增整型，避免 UUID 这种随机值，从源头减少页分裂。

• 分区表：对历史数据按月分区，删除数据时直接 DROP PARTITION，不会产生碎片。

• 定期监控：把 DATA_FREE 纳入监控项，设置告警阈值，而不是等用户反馈慢了才去查。

6.4 最后

数据库优化是个持续的过程，没有什么“一招鲜”。索引碎片只是其中一环，但它往往是那个被忽略却又影响巨大的“隐形杀手”。

希望这篇教程能帮你少走一些弯路。如果有什么问题，欢迎在评论区交流，我们一起成长。🚀