MySQL histogram 怎样用直方图解释选择性估算

MySQL histogram 是帮助优化器理解列值分布的统计信息，不是新的索引访问路径。看选择性估算时，我会先问“这个条件预计留下多少行”，再问“用什么路径读这些行”。直方图可能改善前一个判断，但不保证查询改用索引，也不保证耗时下降。

MySQL 官方文档：https://dev.mysql.com/doc/refman/8.4/en/optimizer-statistics.html

我会先把估算和索引分开看

面对一条按状态筛选的报表SQL，我倾向先检查状态分布是否倾斜，而不是立即给每个字段加索引。这是分析方法的取舍，不是一次已经发生的线上调优故事；下面的数字是人为构造的教学样本，本文没有运行数据库或基准。

直方图把列值分布以桶的形式存入统计信息，供优化器估计条件命中比例。对我来说，它最有价值的地方是解释“为什么同一个字段不同取值的过滤能力不同”；不适应之处则是，统计生成了，计划也未必改变，读者需要接受“估算更有依据”和“必然更快”不是一件事。

本文按MySQL 8.4文档说明能力，不把直方图写成刚在8.4首次出现的新功能，也不直接套用其他数据库或MySQL兼容分支的语法。尤其不要把搜索中的“创建直方图”译成不存在的 CREATE HISTOGRAM 语句。

从累积频率推回某个值的占比

假设一列 state_code 没有NULL，教学总体恰好有100行：值1有70行，值2有20行，值3有10行。若用singleton直方图分别表示这些值，按值排序的累积频率可以写成下面的示意表。它不是MySQL实际输出。

这里 state_code=2 的命中比例是20%，不是90%。90%表示“小于等于2”的累积比例。无NULL的这个例子也说明，不能把每个桶的累积频率直接当作单值频率，再把它们相加。

MySQL的singleton桶包含单个值与累积频率；equi-height桶则覆盖值范围，还记录范围边界和不同值数量。直方图中另有null-values、sampling-rate等信息，因此真实数据有NULL、采样或范围桶时，不能生搬上面无NULL、全体已知的简化算式。

结构上，应把 state_code、直方图、桶、累积频率和条件命中比例分开看。桶表达分布，选择性是针对某个谓词的估计，二者不是同一个字段。

用正确语句建立并读取统计

下面是面向隔离测试库的原创表定义。state_code没有二级索引，方便单独讨论非索引过滤条件的估算。先由读者准备符合自己分布的脱敏测试数据；空表并不能提供有意义的选择性对比。

-- 仅在隔离测试库创建独立对象，不覆盖生产表。
CREATE TABLE histogram_probe_events (
    event_id BIGINT NOT NULL PRIMARY KEY,
    state_code INT,
    occurred_at DATETIME NOT NULL
) ENGINE=InnoDB;

-- 先记录版本，本文按MySQL 8.4语法说明。
SELECT VERSION();

-- 测试数据准备好后，生成列直方图；此操作会修改统计元数据。
ANALYZE TABLE histogram_probe_events
UPDATE HISTOGRAM ON state_code WITH 8 BUCKETS
MANUAL UPDATE;

8只是这个教学例子的桶数，不是生产建议。桶数范围为1到1024；更多桶不能修复所有数据相关性或坏查询。统计更新可能消耗资源并改变计划，应按数据库变更流程安排，不把ANALYZE当纯只读查询。

-- 按当前schema、表和列限制范围，读取实际统计，不伪造JSON内容。
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
       JSON_UNQUOTE(JSON_EXTRACT(
           HISTOGRAM, '$."histogram-type"')) AS histogram_type,
       JSON_EXTRACT(HISTOGRAM, '$."sampling-rate"') AS sampling_rate,
       JSON_EXTRACT(HISTOGRAM, '$."null-values"') AS null_fraction,
       JSON_UNQUOTE(JSON_EXTRACT(
           HISTOGRAM, '$."last-updated"')) AS last_updated,
       JSON_EXTRACT(HISTOGRAM, '$."buckets"') AS buckets
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'histogram_probe_events'
  AND COLUMN_NAME = 'state_code';

结果中没有记录时，先确认schema、列名和生成语句返回的诊断消息，不能把空结果当成“分布均匀”。sampling-rate为1表示读取了全部数据，小于1表示采用抽样；采样可能错过重要稀有值，所以统计对象存在不等于估算必然精确。

比较计划时别只盯着 rows

我更愿意保留一份带明确条件的基线计划，然后仅更新这列统计，再用同一SQL比较。不要在更新统计的同时加索引、改过滤值、删数据，否则难以解释变化来自哪里。以下是读者可采用的计划观察语句，本文未执行。

-- 在统计更新前后分别保存同一语句的估算计划。
EXPLAIN
SELECT event_id, occurred_at
FROM histogram_probe_events
WHERE state_code = 2;

传统EXPLAIN中的rows是访问路径预计检查的行数，filtered是条件预计保留的百分比。单表节点可用 rows × filtered / 100 帮助理解预计保留量，而不是只盯着rows是否下降。举一个纯算术例子：若某计划估计检查500行，filtered为12%，则预计保留约60行；这不是该测试表的实测计划。

即使仍然全表扫描，filtered也可能因统计信息而变化；没有新的索引就不会凭空产生相应索引访问路径。最终是否采用直方图，还受谓词形式、现有索引统计和优化器规则影响，不能仅因计划数字没变就断定生成失败。

在这一层我会分开记录：WHERE谓词、直方图统计、访问路径、rows估算、filtered估算和预计保留行。统计为代价判断提供信息，计划展示估算，不提供真实耗时证明。如果后来需要实际执行数据，应在可控环境另行测量；EXPLAIN ANALYZE会实际运行查询，不应当作普通EXPLAIN随手用于重型报表。

更新策略和适用人群要一起决定

MySQL 8.4支持MANUAL UPDATE与AUTO UPDATE。手动模式是未指定时的默认行为；自动模式会在相关ANALYZE及持久化统计重算场景更新直方图，不等于每一次数据写入都同步重新构建。对我来说，选择哪种模式取决于数据变化节奏和可接受的计划波动，而不是一概把“自动”视为更好。

旧数据分布变化很大时，保留旧直方图可能误导估算；但频繁重建也有成本。应把统计时间、采样比例、实际查询条件和计划变化一起记录，再决定是否更新。若只是一次新建统计实验，可在确认不再需要后移除该实验列的直方图；不要删除他人已有统计来制造对照。

-- 仅用于移除本次新建的实验统计，不删除表数据或索引。
ANALYZE TABLE histogram_probe_events
DROP HISTOGRAM ON state_code;

直方图能解释两个强相关字段的联合条件吗？

这里讨论的是列级统计，不能把单列分布直接当作完整联合分布。某状态只出现在特定区域时，分别知道状态与区域的占比，仍不足以准确描述二者组合；不要把所有误差都归因于桶数不够。

有直方图就不用索引了吗？

不是。直方图帮助估计，索引提供可能的访问路径。是否建索引还要结合过滤、排序、回表、写入代价和业务负载判断。两者职责不同。

估算变准就一定更快吗？

不一定。计划可能不变，也可能仍受I/O、缓存或并发限制。本文能给出的结论是如何解释统计与估算，不是未经测量的收益承诺。

我会优先把直方图用于理解分布倾斜、解释条件过滤估算；若需求只是“让某条SQL立刻走指定索引”，它不是对应的控制开关。把频率、估算和真实执行分开，才能判断下一步该改统计还是改访问路径。

参考：优化器列统计；ANALYZE TABLE直方图语法；EXPLAIN估算字段。