MySQL 查询走错执行计划：统计信息失真与直方图修复实战，基数估算与 EXPLAIN ANALYZE 对比

  1.  Home
  2. Text  
    3. Share to：

索引明明建了，为什么查询还是全表扫描

这是个人站长做数据库优化时最常见的困惑之一：EXPLAIN 一看，key 列是 NULL，type 是 ALL，几千行的表还好，上百万行的表就直接拖垮服务器。你反复确认索引确实存在、字段类型也没问题、SQL 也没写错，那问题往往不在语法层面，而在优化器的判断依据——统计信息。

MySQL 的查询优化器是基于代价（cost-based）的。它在决定走不走某个索引之前，需要先估算几种执行方案各自要读多少行、要付出多少代价。这个估算的原料，就是优化器手里掌握的统计信息。如果统计信息失真，优化器就会得出一个「全表扫描更便宜」的错误结论，哪怕索引明明是现成的。

本文把这件事拆成三个层次讲清楚：统计信息是怎么来的、什么时候会失真、以及 MySQL 8 引入的直方图（histogram）如何补上这块短板。全部结论都基于可复现的操作，你可以边看边在自己服务器上跑一遍。

先搞清楚：优化器手里的两套统计信息

InnoDB 提供给优化器的统计信息分两类，很多人只知道第一类。

第一类：索引统计（index statistics），大致描述「有多少行」

它记录每张表大约有多少行、每个索引里有多少个不同的值（cardinality，基数）、索引页的分布情况等。这些数据存在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 两张表里，是持久化的，重启不会丢。

查看方法很简单：

SELECT table_name, index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'zz1984' AND table_name = 'typecho_contents'
ORDER BY index_name, stat_name;

其中 n_diff_pfx01 表示索引第一列的不同值数量，这就是基数。基数的准确度直接决定优化器判断一个索引的「区分度」好不好。基数越接近总行数，说明这个索引越能快速定位到少量行，优化器就越愿意用它。

第二类：数据分布统计（data distribution），描述「值是怎么分布的」

这一类是 MySQL 8 才真正补强的。它回答的是另一个问题：在某个列上，某些具体值是不是特别多？

举个经典例子：一张订单表有 status 字段，全表 100 万行，其中 98 万行是 finished（已完成），只有 2000 行是 pending（待处理）。现在你查询 WHERE status = 'pending'。

如果优化器只知道「status 列有 4 个不同值、表有 100 万行」，它会天真地做除法：100 万 ÷ 4 ≈ 25 万行。它认为这个查询要扫 25 万行，那还不如全表扫描快。于是它放弃索引，走了全表扫描——而实际上只需要读 2000 行。

这就是均匀分布的假设失效。第一类统计信息只能告诉你「有几个不同的值」，不能告诉你「每个值各有多少行」。数据一旦倾斜（skew），优化器就会做出灾难性的误判。直方图就是来解决这个问题的。

什么情况下统计信息会失真

理解了原理，就能明白失真是怎么发生的。实践中最常见的四种情形：

1. 大批量写入后没有重新统计

InnoDB 的统计信息是采样得来的，而且有自动更新阈值：当表上被修改的行数超过一定比例（由 innodb_stats_auto_recalc 控制，默认开启，阈值约 10%）时会触发后台重算。如果你的操作是「一次性导入 80 万行到一张 100 万行的表」，改动比例虽然大，但采样是随机的、有可能没覆盖到新数据的关键分布，于是基数估得离谱。

典型症状：数据导入前查询很快，导入后突然全部走全表扫描。

2. 采样页数太小，长尾值采不到

InnoDB 不会扫描整个索引来统计，而是随机抽取若干叶子页。相关参数是 innodb_stats_persistent_sample_pages（默认 20）。对于几百万行、分布又很散的索引，20 个页的样本严重不足，基数会被系统性低估。

不要盲目把这个值调到几千——采样是在线操作，会占用 IO 和 CPU，大表上可能引发卡顿。合理做法是针对特定表单独调整：

ALTER TABLE typecho_contents
  STATS_SAMPLE_PAGES = 200;

3. 数据分布本身就是倾斜的

如上文的 status 例子。倾斜数据靠单纯的采样和基数统计永远估不准，必须用直方图。

4. 前缀索引、函数索引、隐式类型转换

如果索引建在 VARCHAR(255) 的前 10 个字符上（前缀索引），那么不同值的统计也只是基于前 10 个字符的，区分度信息本身就有损。另外 WHERE phone = 13800138000 里 phone 是 varchar 而值是数字，会触发隐式转换，索引直接失效——这已经不是统计问题，而是写法问题，但排查时经常和统计问题混在一起。

动手：用直方图修复倾斜列的估算

MySQL 8.0 开始支持 ANALYZE TABLE ... UPDATE HISTOGRAM。它会在列上建立一份值分布的描述，并把结果存进数据字典（information_schema.COLUMN_STATISTICS）。

第一步：建立直方图

ANALYZE TABLE typecho_contents
  UPDATE HISTOGRAM ON status, type
  WITH 128 BUCKETS;

WITH 128 BUCKETS 表示把值分布切成 128 个桶。桶数越多越精确，但存储和统计开销也越大。经验值：区分度高的列可以用 100～256；只要 2～3 个值的枚举列，用 32 个桶都嫌多。

直方图有两种类型，MySQL 会自动选择：

  • 等频直方图（Singleton）——每个桶装一个具体的值，适合不同值数量小于桶数的列，比如 status。
  • 等宽直方图（Equi-height）——把值域切成等高的区间，适合连续型的列，比如 price、created 时间戳。

第二步：确认直方图确实建立了

SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'zz1984' AND TABLE_NAME = 'typecho_contents';

如果返回空，说明直方图没建立成功。常见原因是列的类型不受支持（比如 TEXT/BLOB 大字段做直方图意义不大），或者语法里的列名写错。

第三步：对比前后 EXPLAIN 的 rows 估算

这一步最关键，也是很多人跳过的。建立直方图后，重新跑 EXPLAIN：

EXPLAIN SELECT * FROM typecho_contents WHERE status = 'pending'\G

看输出里的 rows 列。建立直方图前，这个数字可能是几十万；建立后，它会收敛到接近真实行数。当估算行数足够小，优化器自然就会转向使用索引。

不能只信 EXPLAIN，要交叉验证

排查这类问题有一个容易踩的坑：EXPLAIN 显示的 rows 是估算值，不是真实值。你要确认优化器的估算是否偏离现实，最靠谱的方法是把它和实际执行结果对比。

-- 估算值
EXPLAIN SELECT * FROM typecho_contents WHERE status = 'pending';

-- 真实值（8.0.18+ 支持 EXPLAIN ANALYZE，会真正执行并给出实际行数）
EXPLAIN ANALYZE SELECT * FROM typecho_contents WHERE status = 'pending';

EXPLAIN ANALYZE 的输出里会同时给出 estimated rows 和 actual rows。如果两者差了一个数量级以上，基本可以断定统计信息有问题，或者是直方图建得不够细。

注意 EXPLAIN ANALYZE 会真的执行查询，在写库上要谨慎使用，最好先用 LIMIT 或加一段时间范围把代价压住，或者直接在从库上跑。

什么时候该手工刷统计，什么时候不该

很多人一遇到慢查询就 ANALYZE TABLE，其实要分清场景。

建议手工刷的场景

  • 大批量数据迁移、导入、批量删除之后（改动量远超 10% 阈值）
  • 表结构发生大改，比如新增了索引、改了字段类型
  • 刚做主从切换，从库统计信息还是旧的
  • 发现某些查询的执行计划在数据量没变的情况下突然变差

不建议频繁刷的场景

  • 小表（几千行以内），全表扫描本身就够快，刷不刷无所谓
  • 业务高峰期，ANALYZE TABLE 会占用 IO，大表上可能持续几十秒
  • 已有的统计信息本来就在自动更新阈值内，重复刷只是浪费资源

还有一个重要细节：直方图不会自动更新。它是一次性建立的静态快照，数据分布变了之后必须手工重建。所以对于持续写入的倾斜列，要么定期（比如每天低峰期）用定时任务重建，要么在设计上避免依赖它。

# 低峰期重建直方图（crontab 示例：每天凌晨 4:10）
10 4 * * * mysql -uroot -p'密码' zz1984 -e \
  "ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 64 BUCKETS;" \
  >> /var/log/hist_refresh.log 2>&1

把统计信息纳入日常巡检

统计信息问题最讨厌的地方是它是静默的：数据库不报错、服务不崩溃，只是查询悄悄从毫秒变成几秒，CPU 悄悄被打满。等到你发现网站变慢，可能已经过去了好几天。

建议在巡检脚本里加上这几个动作：

  • 对比 mysql.innodb_table_stats 里的 n_rows 和 SELECT COUNT(*) 的真实行数，偏差超过 20% 就要警惕
  • 记录关键表的基数（cardinality）变化趋势，突然大幅波动说明数据分布变了
  • 对已知倾斜的枚举列，检查直方图是否还在（有没有被 DROP 掉）
  • 把 EXPLAIN ANALYZE 的估算偏差做成告警指标，超过阈值就通知

常见问题解答

Q：建了直方图，优化器还是不走索引，为什么？

A：直方图只修正「行数估算」，不改变「代价模型」。如果走索引本身确实回表代价很高（比如要读的行占全表 30% 以上），优化器仍然会选全表扫描，这是正确判断。此时应该考虑覆盖索引，让查询不用回表。

Q：直方图会影响写入性能吗？

A：建立和重建时会有一次统计开销，建立完成后只影响优化器估算，不影响 DML 写入路径。

Q：主从复制环境下，直方图会同步到从库吗？

A：ANALYZE TABLE ... UPDATE HISTOGRAM 是默认写入 binlog 的，会在从库重放。但如果从库只读且 binlog 格式不合适，需要确认 binlog_format 设置。ANALYZE TABLE 的普通统计信息更新则不一定复制，自动统计在各节点独立进行。

Q：统计信息、直方图、索引，三者的关系是什么？

A：索引是「高速公路」，决定数据怎么被快速找到；统计信息是「路况报告」，告诉优化器哪条路更好走；直方图是统计信息的精度补充，专门修正倾斜数据上的误判。三者缺一不可——只有索引不被理解，就等于修了路却没人走。

最后一句提醒

做数据库调优，不要一上来就加索引。先看执行计划，再看估算值，再对比真实值，最后才决定是加索引、建直方图，还是改写 SQL。优化器永远是基于已有的信息做判断，你给它的信息错了，它的判断就一定是错的。把统计信息维护好，往往比多建三个索引更能解决问题——而且不用改一行代码。

Last modification：September 29th, 2026 at 10:23 pm
© 允许规范转载

相关文章

Leave a Comment