: 索引碎片清理-提升查询效率的关键操作（手把手保姆级教程）)
核心思想索引不是建完就一劳永逸的它就像我们家里的书架用久了会乱、会空出很多缝隙。定期整理才能让查询又快又稳。一、 为什么会产生索引碎片1.1 先打个比方书架与书想象你有一个巨大的书架BTree 索引每层隔板就是一个数据页。你习惯按书名的拼音顺序放书主键有序每次新书来了就插到对应位置。但书架每层一开始都是满的。有一天你买了一本“M”开头的书发现“M”那层已经满了怎么办你只能把这一层的书分成两半新书放中间左右各留一些空隙。→ 这就是页分裂产生了内部碎片每一层都有空闲位置。后来你清理掉一大批旧书大量删除某些层只剩零星几本书但书架层数没变空间还在。→ 这就是外部碎片整体浪费了空间。如果你买书完全不按拼音顺序随机主键如UUID书可能被塞到各个角落物理位置和逻辑顺序严重不一致找书时就得来回跑。→ 这是顺序碎片导致预读失效。1.2 碎片带来的“三宗罪”查询变慢本来读10页数据就能查完现在要读15页I/O多了50%。内存浪费MySQL 的缓冲池Buffer Pool里塞满了半空的数据页有效数据少了。空间膨胀.ibd 文件远大于实际数据量备份和迁移都更耗时。二、 如何检测索引碎片动手方法1看一眼information_schemasql-- 查看指定表的碎片大小单位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_NAMEdata_mbidx_mbfree_mborders256.00128.0080.00free_mb就是表空间中未使用的空间。经验值如果free_mb超过data_mb idx_mb的 10%15%就该考虑清理了。方法2用sys库看更详细的填充率sqlSELECT * FROM sys.schema_table_statistics WHERE table_name orders;这里可以看到索引页的利用率低于 70% 时碎片通常较严重。三、 索引碎片清理的三种核心方法方法1OPTIMIZE TABLE—— 简单粗暴sqlOPTIMIZE TABLE your_table;原理创建一张新表 → 按主键顺序复制数据相当于重新整理书架 → 删除旧表 → 新表改名。优点一条命令效果彻底。缺点全程锁表大表可能造成业务停写数十分钟到数小时。 就像你直接把书全部搬出来重新按顺序摆好期间别人不能借书。方法2ALTER TABLE ... ENGINEInnoDB—— 换壳法sqlALTER TABLE your_table ENGINEInnoDB;在 InnoDB 中这条命令和OPTIMIZE TABLE效果完全一样也是重建表。选择哪种看习惯我一般用ALTER因为更通用比如更换引擎时也用。方法3pt-online-schema-change—— 不锁表的神器对于生产环境的大表强烈推荐使用 Percona Toolkit 中的pt-online-schema-change。bashpt-online-schema-change --alter ENGINEInnoDB Dyour_database,tyour_table \ --execute --host127.0.0.1 --userroot --passwordyourpass工作流程你可以理解为“在线换书架”创建一张与原表结构相同的新表影子表。在原表上创建触发器把后续的增删改同步到新表。分批将原表的历史数据复制到新表可控制速度不影响业务。在极短时间内通过RENAME TABLE完成新旧切换。优点全程不锁表对业务几乎无感。缺点需要额外安装工具操作过程要关注主从延迟。我第一次在生产环境用这个工具时紧张得手心冒汗但执行完发现业务一点没受影响从此就爱上了它。四、 实战案例订单表从“老年痴呆”到“身轻如燕”场景某电商orders表存储了 5 亿订单每天凌晨删除 3 年前的数据。几个月后开发同事反馈“按日期查订单越来越慢SELECT COUNT(*)要 8 秒多。”诊断sqlSELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM information_schema.tables WHERE TABLE_NAME orders;结果数据 索引总大小约 30 GBDATA_FREE 12 GB碎片占比 40%操作因为是核心表不能锁表选择pt-online-schema-changebashpt-online-schema-change --alter ENGINEInnoDB Dshop,torders \ --execute --chunk-size10000 --max-lag5 --critical-loadThreads_running100大约 40 分钟后命令执行完毕期间业务无感知。效果对比操作优化前优化后提升SELECT COUNT(*)8.2 秒0.9 秒9倍按日期范围查询10万条3.5 秒0.6 秒5.8倍索引大小18 GB10 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 最后数据库优化是个持续的过程没有什么“一招鲜”。索引碎片只是其中一环但它往往是那个被忽略却又影响巨大的“隐形杀手”。希望这篇教程能帮你少走一些弯路。如果有什么问题欢迎在评论区交流我们一起成长。
MySQL技巧(四): 索引碎片清理-提升查询效率的关键操作（手把手保姆级教程）
发布时间：2026/7/22 22:37:24
核心思想索引不是建完就一劳永逸的它就像我们家里的书架用久了会乱、会空出很多缝隙。定期整理才能让查询又快又稳。一、 为什么会产生索引碎片1.1 先打个比方书架与书想象你有一个巨大的书架BTree 索引每层隔板就是一个数据页。你习惯按书名的拼音顺序放书主键有序每次新书来了就插到对应位置。但书架每层一开始都是满的。有一天你买了一本“M”开头的书发现“M”那层已经满了怎么办你只能把这一层的书分成两半新书放中间左右各留一些空隙。→ 这就是页分裂产生了内部碎片每一层都有空闲位置。后来你清理掉一大批旧书大量删除某些层只剩零星几本书但书架层数没变空间还在。→ 这就是外部碎片整体浪费了空间。如果你买书完全不按拼音顺序随机主键如UUID书可能被塞到各个角落物理位置和逻辑顺序严重不一致找书时就得来回跑。→ 这是顺序碎片导致预读失效。1.2 碎片带来的“三宗罪”查询变慢本来读10页数据就能查完现在要读15页I/O多了50%。内存浪费MySQL 的缓冲池Buffer Pool里塞满了半空的数据页有效数据少了。空间膨胀.ibd 文件远大于实际数据量备份和迁移都更耗时。二、 如何检测索引碎片动手方法1看一眼information_schemasql-- 查看指定表的碎片大小单位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_NAMEdata_mbidx_mbfree_mborders256.00128.0080.00free_mb就是表空间中未使用的空间。经验值如果free_mb超过data_mb idx_mb的 10%15%就该考虑清理了。方法2用sys库看更详细的填充率sqlSELECT * FROM sys.schema_table_statistics WHERE table_name orders;这里可以看到索引页的利用率低于 70% 时碎片通常较严重。三、 索引碎片清理的三种核心方法方法1OPTIMIZE TABLE—— 简单粗暴sqlOPTIMIZE TABLE your_table;原理创建一张新表 → 按主键顺序复制数据相当于重新整理书架 → 删除旧表 → 新表改名。优点一条命令效果彻底。缺点全程锁表大表可能造成业务停写数十分钟到数小时。 就像你直接把书全部搬出来重新按顺序摆好期间别人不能借书。方法2ALTER TABLE ... ENGINEInnoDB—— 换壳法sqlALTER TABLE your_table ENGINEInnoDB;在 InnoDB 中这条命令和OPTIMIZE TABLE效果完全一样也是重建表。选择哪种看习惯我一般用ALTER因为更通用比如更换引擎时也用。方法3pt-online-schema-change—— 不锁表的神器对于生产环境的大表强烈推荐使用 Percona Toolkit 中的pt-online-schema-change。bashpt-online-schema-change --alter ENGINEInnoDB Dyour_database,tyour_table \ --execute --host127.0.0.1 --userroot --passwordyourpass工作流程你可以理解为“在线换书架”创建一张与原表结构相同的新表影子表。在原表上创建触发器把后续的增删改同步到新表。分批将原表的历史数据复制到新表可控制速度不影响业务。在极短时间内通过RENAME TABLE完成新旧切换。优点全程不锁表对业务几乎无感。缺点需要额外安装工具操作过程要关注主从延迟。我第一次在生产环境用这个工具时紧张得手心冒汗但执行完发现业务一点没受影响从此就爱上了它。四、 实战案例订单表从“老年痴呆”到“身轻如燕”场景某电商orders表存储了 5 亿订单每天凌晨删除 3 年前的数据。几个月后开发同事反馈“按日期查订单越来越慢SELECT COUNT(*)要 8 秒多。”诊断sqlSELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM information_schema.tables WHERE TABLE_NAME orders;结果数据 索引总大小约 30 GBDATA_FREE 12 GB碎片占比 40%操作因为是核心表不能锁表选择pt-online-schema-changebashpt-online-schema-change --alter ENGINEInnoDB Dshop,torders \ --execute --chunk-size10000 --max-lag5 --critical-loadThreads_running100大约 40 分钟后命令执行完毕期间业务无感知。效果对比操作优化前优化后提升SELECT COUNT(*)8.2 秒0.9 秒9倍按日期范围查询10万条3.5 秒0.6 秒5.8倍索引大小18 GB10 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 最后数据库优化是个持续的过程没有什么“一招鲜”。索引碎片只是其中一环但它往往是那个被忽略却又影响巨大的“隐形杀手”。希望这篇教程能帮你少走一些弯路。如果有什么问题欢迎在评论区交流我们一起成长。
