MySQL索引碎片与统计信息过期导致EXPLAIN选错索引的排查方法(Data_free、ANALYZE TABLE与innodb_stats_persistent_sample_pages定位)

执行计划毫无征兆地变了。一张跑了半年的orders表,按create_time查最近三天的订单,原本稳稳走idx_create_time,某个周一早上开始变成全表扫描,SQL没改,索引也没人删。


这种事多半不用先怀疑SQL写法。InnoDB给优化器喂的是采样估算出来的统计信息,采样不准或者表被删得千疮百孔,优化器算出来的cost就跟着偏,rows一虚高,它宁可扫全表。


先把两件事分开

统计信息过期和索引碎片是两码事,症状像,处置完全不同。前者是数字算错了,一条ANALYZE TABLE就能回来;后者是物理页真的散了,得重建表才收得回来。

分诊只看EXPLAIN的rows和真实行数差多少,再看Data_free。

EXPLAIN SELECT * FROM orders WHERE create_time >= '2026-11-01';

rows显示900万、实际满足条件只有3万行,基本可以断定是统计信息的问题。

SELECT table_name, data_length, index_length, data_free
FROM information_schema.TABLES
WHERE table_schema='shop' AND table_name='orders';

data_free几十GB,才轮到考虑碎片。


统计信息是谁算的

MySQL 5.6.6起默认开innodb_stats_persistent,统计信息落到mysql.innodb_table_stats与mysql.innodb_index_stats两张系统表,实例重启不丢。innodb_stats_auto_recalc默认ON,表里约10%的行被改动后才异步重算,批量灌数据或者大范围DELETE之后,这个10%的门槛经常还没到,统计信息就已经老得离谱。

直接查这两张表能看到last_update和sample_size:

SELECT * FROM mysql.innodb_table_stats
WHERE database_name='shop' AND table_name='orders'\G
SELECT index_name, stat_name, stat_value, sample_size, stat_description
FROM mysql.innodb_index_stats
WHERE database_name='shop' AND table_name='orders';

stat_value就是Cardinality的来源,SHOW INDEX FROM orders里那一列读的也是它。

手动重算最直接:

ANALYZE TABLE orders;

遇到过一个实例,跑完这条语句rows从900万掉回3万,执行计划当次查询就回到idx_create_time,前后没动任何配置。

默认innodb_stats_persistent_sample_pages是20,对几千万行且数据分布不均的表,20个页采出来的基数偏差能到几倍。实测在MySQL 8.0.36、8.0.40、8.4.0,把采样页数调到200之后,几个倾斜字段的估算明显稳了:

SET SESSION innodb_stats_persistent_sample_pages=200;
ANALYZE TABLE orders;

要固定下来就在my.cnf里写全局值,代价是每次ANALYZE多读一些页,别一上来就设成几千。

非唯一列上的等值查询还要看直方图,MySQL 8.0.3起支持:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME='orders'\G

直方图解决的是status取值不均匀这类采样撑不住的场景,和Cardinality不是一回事。不用了就DROP HISTOGRAM ON status。


Data_free涨上来怎么处理

频繁DELETE加上变长列UPDATE,页会分裂、留下大量半空的页,data_free就是这些没被回收的空间。它只说明有空洞,不影响优化器选索引,但范围扫描要多读不少页,IO会实打实地涨。

有一个前提要先确认:innodb_file_per_table得是ON(5.6.6起默认),每表才有独立的orders.ibd,data_free才等于这一张表的空闲量。之前接手过一套还用共享ibdata1的老库,SHOW TABLE STATUS里data_free显示40GB,那是整个表空间的空闲,跟单表没关系,照着单表去优化纯属白折腾。

重建表能收回空洞:

OPTIMIZE TABLE orders;

InnoDB下这条语句被翻译成ALTER TABLE orders ENGINE=InnoDB,等于整表重建,期间要锁表还要额外一倍的磁盘放临时ibd。现场见过有人把OPTIMIZE TABLE放在主库白天跑,一张200GB的表锁了二十多分钟,上游请求全堵着。生产上要么放低峰,要么用pt-online-schema-change、gh-ost这类在线改表工具,别硬来。

ALTER TABLE orders ENGINE=InnoDB;

重建完记得再跑一次ANALYZE TABLE,新表的统计信息是重新采样的,不补这一步偶尔又会踩回选错索引。


几个容易走偏的地方

EXPLAIN的rows是估算值,不是实际扫描行数。MySQL 8.0.18起可以用EXPLAIN ANALYZE看真实行数和各步骤耗时,两者差得远就别急着改SQL。

EXPLAIN ANALYZE SELECT * FROM orders WHERE create_time>='2026-11-01';

想看优化器为什么弃用某个索引,打开optimizer_trace比猜快:

SET optimizer_trace='enabled=on';
SELECT * FROM orders WHERE create_time>='2026-11-01';
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace='enabled=off';

FORCE INDEX能让线上先止血,但它把优化器的手绑住了,数据分布一变就会变成新的坑,别当长期方案挂着。

版本上,持久统计这套行为在MySQL 5.7.44、8.0.36、8.0.40、8.4.0、8.4.4、9.7.0,几个版本里基本一致,参数名没换过。8.0已在2026年4月停止支持,8.4.0 LTS是2024年4月发的,还留在8.0的实例建议先定升级计划。文中参数默认值与版本信息数据截至2026年11月。