MySQL 直方图统计怎样帮助没有索引的过滤条件

没有索引的列并不等于优化器只能“猜”。MySQL 可以为列值分布建立直方图，让优化器知道某个过滤条件大约会留下多少行，从而改进没有单列索引时的选择性估算。不过，直方图是统计信息，不是索引：它不会直接定位数据，也不会保证执行计划一定更快。

直方图最适合帮助 MySQL 认识非索引列的分布。先生成统计，再从 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看状态，最后用 EXPLAIN 对照估算；如果数据分布已经变化，就重建或删除这份统计。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

先区分索引路径和分布统计

假设订单表中的 status 没有单列索引，查询却经常按状态过滤：

-- 这个查询关注 status 的过滤选择性，而不是展示真实运行结果
SELECT id, user_id, created_at
FROM orders
WHERE status = '待处理';

没有索引时，优化器仍然需要估算“待处理”大约占全表多少比例。估算太小，可能低估扫描或后续连接成本；估算太大，也可能放弃一个本来合适的连接顺序。直方图把列值的分布压缩成若干桶，提供比均匀分布假设更具体的统计依据。

它和索引的职责不同：

为非索引列生成直方图

MySQL 使用 ANALYZE TABLE 管理直方图。最小写法是指定表和列；如果不指定桶数量，服务器使用默认值。桶数量可以在 1 到 1024 之间选择，数量越多不代表估算一定越准，仍要结合列的分布和统计维护成本。

-- 为 status 列生成直方图，帮助优化器认识状态值分布
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status;

-- 分布更复杂时可以显式指定桶数量
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

直方图生成会读取用户数据；如果可用内存不足以完整读取，MySQL 可能采用采样。生成后可以从直方图 JSON 中查看 sampling-rate，它反映生成统计时读取数据的比例。采样率较低时，不要把一次估算当成永远准确的结论。

对于 MySQL 8.4，还可以在生成时指定 AUTO UPDATE。不指定时默认是 MANUAL UPDATE，后续需要显式维护；自动更新则会在相关的 ANALYZE TABLE 或 InnoDB 持久统计重新计算时更新直方图。

-- 让后续表统计刷新时一并更新该列直方图
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 32 BUCKETS AUTO UPDATE;

-- 明确回到手动维护，避免把更新责任交给自动统计过程
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 32 BUCKETS MANUAL UPDATE;

从 COLUMN_STATISTICS 读取状态

直方图不需要直接查询数据字典内部表，使用 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看公开视图即可。重点不是只看有没有记录，而是看统计对应的列、桶数量、采样率和更新模式。

-- 查看 orders.status 当前保存的直方图 JSON
SELECT
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    HISTOGRAM
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME = 'status';

JSON 中常见的判断点包括 histogram-type、number-of-buckets-specified、sampling-rate、last-updated 和 auto-update。例如，更新时间很旧而业务数据近期发生了大规模状态迁移，就应该把“估算不准”列入排查，而不是先修改 SQL。

直方图主要用于非索引列。如果列已经有合适的索引，优化器可能通过范围优化或索引探测得到更直接的估算，此时不一定采用直方图。直方图存在，并不表示每条查询都会使用它。

用 EXPLAIN 对照过滤估算

可以用 EXPLAIN 查看查询计划中的 rows 与 filtered。下面的语句只展示应该观察的字段，不把示例数字当成真实执行输出：

-- 观察优化器对扫描行数和过滤比例的估算
EXPLAIN
SELECT id, user_id, created_at
FROM orders
WHERE status = '待处理';

filtered 可以理解为当前访问路径下预计通过条件的百分比。比如直方图反映“待处理”约占较小比例时，估算就不应继续使用均匀分布的直觉数字。真正对比时，应在相近统计状态下记录计划，再生成或重建直方图后重新查看。

但要注意两个边界。第一，EXPLAIN 里的估算改善不等于执行时间一定下降；如果计划本来就受其他条件、连接顺序或排序成本主导，直方图的帮助可能很小。第二，优化器更偏好适用的范围优化估算，适用时不会优先依赖直方图。判断重点是估算是否更贴近数据，而不是强行让计划发生变化。

处理过期与不适用边界

直方图只在生成或更新时读取数据，普通写入不会让它即时同步。因此，下面几种场景需要重新考虑：

• 值分布改变：例如大量订单从“新建”迁移到“完成”，旧桶已经不能代表当前比例，应重新执行 UPDATE HISTOGRAM。
• 统计成本过高：如果某列查询收益不明显，或者维护产生的分析成本超过收益，可以删除直方图。
• 列本身不合适：MySQL 不为 JSON 和空间类型生成直方图；被单列唯一索引覆盖的列也不是直方图的典型使用对象。
• 统计干扰排查：如果确认直方图导致估算异常，可以先重新生成；仍不合适时再删除，而不是长期保留一份过期统计。

-- 数据分布明显变化后重建统计
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

-- 不再需要时删除指定列的直方图
ANALYZE TABLE orders
DROP HISTOGRAM ON status;

一张速查表

MySQL 直方图的价值在于补充列分布信息：它让优化器在没有索引的过滤条件上少一些盲猜。实践时按“生成 → 查看 → EXPLAIN 对照 → 随数据变化维护”的顺序处理，才能把统计信息与执行计划联系起来；如果真正的问题是数据访问路径，仍应回到索引、SQL 形状和连接顺序本身。

相关问题

直方图能不能替代索引？

不能。直方图主要改善估算，索引才负责提供更直接的数据访问路径。是否建索引仍要看查询频率、选择性、写入成本和整体访问模式。

为什么生成直方图后 EXPLAIN 没变化？

可能是优化器已经使用了更优先的范围估算，也可能是当前查询的代价模型不需要这份统计。先看 COLUMN_STATISTICS 是否更新，再结合 EXPLAIN 的访问类型、rows 和 filtered 判断。

数据经常变化时应该选 AUTO UPDATE 吗？

不应只看变化频率。先评估分析成本和估算收益；需要自动维护时可使用 AUTO UPDATE，但仍要关注更新触发条件和统计是否真正适合该列。

直方图看起来不准怎么办？

先检查 last-updated 与 sampling-rate，然后重建直方图并重新查看计划。若列分布或查询模式不适合，删除统计比持续依赖旧数据更清楚。