MySQL histogram 怎样辅助优化器估算非均匀数据

MySQL histogram 把单列的取值分布保存成统计桶，让优化器在估算常量过滤条件时，能够区分常见值和稀有值，而不是把不同值视为同样常见。它补充的是选择率信息，不是新的数据访问路径：估算更接近实际，并不等于扫描行数或耗时必然下降。

下面限定 MySQL 8.4，以没有状态列索引的隔离样例说明，不讨论索引创建，也不把这项长期存在的能力包装成新发布趋势。示例未在本文环境执行，所有比例均为构造数据的预期值，不是性能实测。

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

状态值不均匀时，先补充分布信息

订单几乎都是已完成，只有少量待处理时，“状态共有三个值”不足以回答“待处理会留下多少行”。对已有测试表，最小写法如下；请先确认当前数据库和权限，完整建表示例见后文。

-- 为现有测试表的状态列收集直方图；这里选择显式手动维护
ANALYZE TABLE histogram_order_demo
UPDATE HISTOGRAM ON status_code WITH 16 BUCKETS MANUAL UPDATE;

直方图、状态列和常量谓词共同服务于过滤比例估算；该比例再成为候选计划成本比较的输入。它并不保存业务行的位置，更不会单凭统计信息跳过不匹配的磁盘记录。

桶保存的是频率，不是业务行

不同值数量不超过指定桶数时，MySQL 可生成 singleton，一桶对应一个值；否则生成 equi-height，一桶概括一个取值区间。前者存值与累计频率，后者还存区间边界和区间内不同值数量。“等高”不是把值域平均切成同样宽度。

例如无空值样例中，三个状态的比例按值排序是 2%、8%、90%，对应的累计比例是 2%、10%、100%。中间桶写 10% 不表示这个状态本身占 10%，其份额是相邻累计比例的差。本例特意使用非空整数列，避免把字符串编码或空值份额混进解释。

它描述的是单列分布，不能直接当作“渠道与状态的联合分布”。两列强相关时，只凭各自的 histogram 仍不能保证组合条件估算准确。

适合的是过滤估算失准的查询

适合先试的对象，是分布明显偏斜、过滤估算偏差可观察的列，特别是缺少相应索引统计帮助的常量条件。业务开发可以检查稀有状态条件，DBA 则需要评估统计维护对整体负载的影响。

列与常量的等值、范围以及 IN 等条件属于可利用直方图的形式，但“形式支持”不等于本次计划必用。若范围优化器能够提供估算，MySQL 优先采用其估算；不要把所有计划变化都归因于 histogram，也不要期待它直接估算任意列对列关联条件。

单表样例可能始终全表扫描，却出现更合理的过滤估算。这仍说明统计信息有作用，但不构成加速证据。在复杂查询中，估算变化可能影响候选计划的取舍，是否受益需要对真实工作负载另行观察。

采样和陈旧统计也会带来偏差

维护风险有两个独立维度：采集时是否充分代表分布，以及之后业务分布是否已改变。元数据中的 sampling-rate 表示采样比例，last-updated 表示生成时间；二者不是准确率证书。增加桶数也不能修复陈旧数据。

MySQL 8.4 支持 AUTO UPDATE：启用后，表的 ANALYZE 及 InnoDB 持久统计重算可触发相应直方图更新。它不是逐条写入同步更新；本文选择 MANUAL UPDATE，让对照只在明确的采集动作后发生。不要把 8.4 的选项直接套到未核对版本的旧环境。

生产维护应先评估采集资源和锁等待，并约定刷新时机；不能因为不修改业务行，就视为完全无成本。也不建议全局关闭其他优化能力来证明单个直方图是否有用。

用隔离数据完成一次受控对照

在新建的隔离测试库中执行下面片段；表名若已存在会报错，请勿为了继续而删除已有业务对象。十行数字交叉组合产生一万行，状态分别为 200 行、800 行、9000 行。

-- 确认版本与当前测试库；不要在业务库照搬建表片段
SELECT VERSION(), DATABASE();

-- 状态列不建索引，单独观察分布统计的作用
CREATE TABLE histogram_order_demo (
  id INT NOT NULL PRIMARY KEY,
  status_code INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

-- 用四位十进制组合构造一万行，避免递归深度依赖
INSERT INTO histogram_order_demo (id, status_code, amount)
WITH digits AS (
  SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2
  UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
  UNION ALL SELECT 9
), numbers AS (
  SELECT a.d + 10*b.d + 100*c.d + 1000*d.d AS n
  FROM digits AS a CROSS JOIN digits AS b
  CROSS JOIN digits AS c CROSS JOIN digits AS d
)
SELECT n + 1,
       CASE WHEN n 

EXPLAIN ANALYZE 会真正执行查询，不能作为无负载的静态检查。这里只对隔离样例的 SELECT 使用它。预期筛选出 200 行，但不预设生成前的估算百分比、精确成本或运行时间。

读取本列元数据，确认收集对象和维护属性；若语句返回错误，先处理权限、版本或对象问题，不应把没有成功收集当作优化器拒绝使用。

-- 只看当前库、当前表、当前列，避免同名对象混淆
SELECT COLUMN_NAME,
       HISTOGRAM->>'$."histogram-type"' AS histogram_type,
       HISTOGRAM->>'$."sampling-rate"' AS sampling_rate,
       HISTOGRAM->>'$."last-updated"' AS last_updated,
       HISTOGRAM->>'$."auto-update"' AS auto_update,
       JSON_LENGTH(HISTOGRAM->'$.buckets') AS bucket_count
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'histogram_order_demo'
  AND COLUMN_NAME = 'status_code';

-- 仅移除本实验创建的状态列直方图，不删除表或业务数据
ANALYZE TABLE histogram_order_demo DROP HISTOGRAM ON status_code;

移除后可再保存同一查询的估算，形成对照。对于原先已有 histogram 的生产对象，DROP 不是恢复旧版本统计；应先保留原配置及可恢复资料，再安排变更。

看估算误差，再看执行代价

传统 EXPLAIN 的 rows 描述访问方法估计的行数，filtered 是剩余条件的过滤百分比，两者相乘再除以 100，可理解为该表这一阶段预计留下的行数，不能误读成整条查询最终返回量。TREE 中过滤节点的估算行数和实际行数更适合本样例直接对照；多循环节点还必须结合 loops 阅读。

桶数越多越好吗？

不是。可指定 1 到 1024 个桶；本例只有三个状态，16 只是容量选择，不意味着一定生成16个桶。先看分布和估算误差，再讨论更细粒度。

建立成功后为什么计划不变？

估算可能改善但不足以改变成本排序，也可能当前谓词或已有估算方式没有采用它。不要用计划是否换形状作为唯一判断。

普通 ANALYZE TABLE 一定刷新 histogram 吗？

本文显式手动维护模式下，应使用 UPDATE HISTOGRAM；8.4 自动维护模式另有触发规则。先看维护模式，再判断刷新动作，不要把键分布统计与单列直方图一概而论。

采用路径应是“小范围选列、固定数据对照、记录估算与代价、约定维护、无收益则撤销新增统计”。histogram 值得用于修补偏斜分布信息，但它不是承诺加速的开关。

官方参考：统计桶、谓词与优先级 https://dev.mysql.com/doc/refman/8.4/en/optimizer-statistics.html；维护语法与更新模式 https://dev.mysql.com/doc/refman/8.4/en/analyze-table.html；过滤比例 https://dev.mysql.com/doc/refman/8.4/en/condition-filtering.html；计划与实际执行 https://dev.mysql.com/doc/refman/8.4/en/explain.html。