索引碎片与统计信息维护：执行计划突然变差的隐形杀手

大家好我是小耶写功课只是为了我踩过的坑你们别再踩了你有没有遇到过这种情况一条SQL昨天还跑得飞快今天突然慢了一个数量级。执行计划没变、索引没删、数据量也没暴涨。你翻遍了慢查询日志、看了EXPLAIN、检查了服务器负载一切看起来都正常——但就是慢了。问题往往藏在两个容易被忽视的地方索引碎片和统计信息。索引碎片让数据库“翻书”翻得更慢统计信息过时让优化器“看地图”看得更偏。两者叠加一条正常的SQL就可能变成慢查询。今天把这两个“隐形杀手”彻底拆开讲一遍。一、索引碎片数据在磁盘上“散架”了索引碎片是什么想象一本精装书原来页码连续、装订整齐。你不断在书里撕掉旧页、插入新页——书脊松动页码错乱翻书要找半天。索引碎片就是数据库里的“书脊松动”。BTree索引是InnoDB的索引结构。在理想状态下索引页是连续存储的扫描时顺序读取I/O效率很高。但随着频繁的增删改操作原本连续的索引页开始出现空洞和不连续。这些空洞和不连续就是索引碎片。碎片的两种类型内部碎片索引页内部有未被使用的空间。DELETE操作会留下“空洞”UPDATE可能导致页内空间分布不均。索引页的利用率降低原来一个页能存100条记录现在只能存70条数据库就要读更多页。外部碎片索引页在物理存储上不再连续。INSERT导致页分裂新的索引页被分配到磁盘的不同位置。范围扫描时磁盘磁头需要在不同位置之间跳跃I/O次数成倍增加。碎片如何影响性能I/O次数增加原本1次磁盘读能加载的索引数据现在可能要读2-3次缓冲池命中率下降碎片导致更多不连续的数据页被加载到内存挤占热点数据范围扫描变慢原本顺序扫描变成了跳跃式读取如何诊断索引碎片MySQL没有直接的“碎片率”指标但可以通过以下方式判断方法一查看表空间碎片SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, ROUND(DATA_FREE / (DATA_LENGTH INDEX_LENGTH) * 100, 2) AS fragment_pct FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND DATA_FREE 0 ORDER BY DATA_FREE DESC;DATA_FREE表示表中未使用的空间碎片。当DATA_FREE超过100MB时就应该关注了。方法二监控缓冲池命中率当Innodb_buffer_pool_read_requests与Innodb_buffer_pool_reads的比值低于1000:1时表明可能存在碎片问题。SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;方法三对比索引大小与实际数据量SHOW INDEX FROM your_table;如果索引的Cardinality不同值数量与表实际行数明显不符可能既存在碎片问题也存在统计信息问题。如何治理索引碎片方法一OPTIMIZE TABLEOPTIMIZE TABLE your_table;OPTIMIZE TABLE对InnoDB表实际上是重建表回收空间、消除碎片、更新统计信息。但会加读锁大表操作时可能阻塞业务。适用场景业务低峰期、中小表、碎片率较高时。方法二ALTER TABLE重建ALTER TABLE your_table ENGINEInnoDB;效果与OPTIMIZE TABLE类似同样会锁表。方法三pt-online-schema-change推荐线上使用生产环境推荐使用Percona Toolkit的pt-online-schema-change工具可以在线重建表不阻塞读写。bashpt-online-schema-change --alter ENGINEInnoDB Dyour_db,tyour_table什么时候该治理碎片碎片率处理建议说明5%暂不处理正常范围5%-20%观察可择机处理微软建议5%-30%重组索引20%尽快安排处理性能影响明显DATA_FREE100MB优先处理空间浪费严重二、统计信息优化器的“眼睛”花了统计信息是什么统计信息是优化器做出执行计划决策的“眼睛”——它告诉优化器表有多大、列有多少个不同值、数据分布如何。优化器根据这些信息计算每种执行方式的“代价”然后选择代价最小的方案。统计信息包含什么表的总行数每列的不同值数量Cardinality列的NULL值比例数据分布直方图统计信息过时的后果优化器“看错”了如果统计信息过旧优化器就会基于错误的信息做决策表实际有1000万行统计信息显示只有100万行 → 优化器可能选择全表扫描认为“反正没多少数据”某列实际有50万个不同值统计信息显示只有5000个 → 优化器低估了索引的选择性放弃使用该索引典型案例一张日志表有500万行user_id列上有索引。业务方反馈一条查询突然变慢SELECT * FROM user_logs WHERE user_id 12345 AND create_time 2026-01-01;正常情况下走(user_id, create_time)复合索引扫描几十行就返回。但实际执行计划显示typeALL全表扫描。检查统计信息后发现这张表的统计信息还是三个月前收集的——当时表只有50万行。优化器根据旧统计信息估算user_id12345可能返回很多行旧数据中该用户有大量记录全表扫描更“划算”。执行ANALYZE TABLE更新统计信息后查询从5秒降到0.05秒。如何检查统计信息是否过时方法一对比EXPLAIN的rows与实际行数执行EXPLAIN后看rows列的估算值再实际执行查询对比实际扫描行数。如果估算值和实际值差了一个数量级以上统计信息很可能过旧。方法二查看统计信息的最后更新时间SELECT TABLE_NAME, UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db;如果统计信息已经几周甚至几个月没有更新而表的数据变化很大就需要执行ANALYZE TABLE。方法三查看索引的CardinalitySHOW INDEX FROM your_table;Cardinality列显示索引列的不同值数量估算。如果Cardinality与实际明显不符说明统计信息需要更新。如何更新统计信息ANALYZE TABLE your_table;ANALYZE TABLE会重新收集表的统计信息行数、Cardinality、数据分布等让优化器获得准确的数据分布。对于MySQL 8.0还可以创建直方图来改善数据分布不均时的估算ANALYZE TABLE your_table UPDATE HISTOGRAM ON column_name WITH 100 BUCKETS;统计信息维护的最佳实践场景建议操作说明批量数据导入后立即执行ANALYZE TABLE数据变化巨大大量数据删除后立即执行ANALYZE TABLE表行数变化显著表结构变更后执行ANALYZE TABLE新索引需要统计信息日常定期维护每周或每月一次根据数据变化频率调整EXPLAIN中rows与实际差距10倍立即执行ANALYZE TABLE优化器选错索引的风险高三、碎片与统计信息的联动维护索引碎片和统计信息常常“结伴作案”——表经过了大量增删改既产生了碎片又让统计信息过时。一条SQL突然变慢往往是两者的叠加效应。联动维护策略1. 定期巡检清单建议每周或每月执行一次以下检查-- 1. 检查碎片情况 SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, ROUND(DATA_FREE / (DATA_LENGTH INDEX_LENGTH) * 100, 2) AS fragment_pct FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND DATA_FREE 0 ORDER BY DATA_FREE DESC; -- 2. 检查统计信息状态 SHOW INDEX FROM your_table;2. 日常维护窗口在业务低峰期如凌晨对高频变动的表依次执行-- 先更新统计信息 ANALYZE TABLE your_table; -- 碎片严重时再重建 OPTIMIZE TABLE your_table;3. 生产环境安全操作规范第一次操作先在测试表上演练线上环境先用pt-online-schema-change或从库测试监控操作期间的锁等待和业务影响准备回滚方案四、总结执行计划突然变差的“隐形杀手”往往不是SQL写错了而是索引碎片和统计信息这两个“看得到但容易被忽略”的问题。索引碎片让数据库“翻书”翻得更慢——增加I/O、降低缓冲池命中率统计信息过时让优化器“看地图”看得更偏——选错索引、走错执行计划把索引碎片治理和统计信息维护纳入日常运维体系你就能在业务方投诉之前提前发现并消除隐患。定期巡检、及时治理、安全操作——这三件事做好了执行计划“突然变差”的情况会越来越少。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~