MySQL 直方图改善低基数条件估算的配置

我第一次给 MySQL 查询补直方图，是因为一个状态列只有几个值，数据却明显偏向其中一个状态；优化器按近似均匀分布估算后，过滤行数和实际业务感觉对不上。直方图解决的是“列值分布怎么被估算”这个问题，不是凭空创建索引。对没有合适索引、但过滤值分布不均匀的低基数列，它可以补充优化器的选择性信息。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/analyze-table.html

为什么低基数列会需要直方图

“低基数”只说明一列的不同值较少，例如订单状态、地区编码或是否启用，并不等于它适合直方图。真正值得关注的是分布是否倾斜，以及查询是否经常用这些值过滤。如果绝大多数行都是 active，只有很少行是 archived，那么 WHERE status = 'archived' 和 WHERE status = 'active' 的选择性并不相同。

这类信息尤其有用在没有合适单列索引、或优化器需要估算连接与过滤规模的查询上。直方图被持久化到数据字典，优化器可以在构造执行计划时读取列分布；它仍然只是统计信息，不能保证某个计划一定更快。若列已经有稳定合适的索引，先评估索引访问路径和统计信息是否新鲜，不要因为“基数低”就盲目追加直方图。

用 ANALYZE TABLE 建立适量的直方图

最小配置是指定表、列和 bucket 数量。MySQL 8.4 的 WITH N BUCKETS 接受 1 到 1024 的整数；不写时默认使用 100。对值种类很少的列，我通常先用较小数量观察统计效果，再根据分布复杂度调整，不把 bucket 数量当作越大越好。

-- 为状态列生成直方图，示例只绑定一个需要估算的过滤列
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 32 BUCKETS;

-- 需要撤销该列的直方图时，只影响指定列的统计
ANALYZE TABLE orders
  DROP HISTOGRAM ON status;

生成直方图时，MySQL 可能需要把采样数据放入内存。若预计读入的数据超过 histogram_generation_max_mem_size，服务器会改用采样；采样是均匀分布在整张表上的页级采样。表很大时不要只看语句成功，还要查看实际 sampling-rate，否则可能误以为统计覆盖了全部值。

直方图不是所有列都能生成：官方文档明确排除了空间类型和 JSON，单列唯一索引覆盖的列也不适用。加密表和临时表同样有额外限制，遇到这些边界时应回到索引、查询改写或更合适的统计方案。

从 COLUMN_STATISTICS 读取采样率和统计内容

INFORMATION_SCHEMA.COLUMN_STATISTICS 提供列级直方图信息。重点不是把 JSON 原样保存，而是确认统计对象对的是哪张表、哪一列，采样比例是多少，直方图类型和 bucket 配置是否符合预期。

-- 查询指定列的直方图摘要，避免把其他表的统计混在一起
SELECT
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    HISTOGRAM->>'$."sampling-rate"' AS sampling_rate,
    HISTOGRAM->>'$."histogram-type"' AS histogram_type,
    HISTOGRAM->>'$."number-of-buckets-specified"' AS bucket_count,
    HISTOGRAM->>'$."auto-update"' AS auto_update
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'shop'
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME = 'status';

sampling-rate 等于 1，表示生成统计时读取了全部数据；小于 1 则表示只读了一部分数据。采样率低不必然代表统计不可用，但如果业务最关心的稀有值没有被充分采到，估算仍可能偏差较大。对这种情况，可以降低一次生成的内存压力、重新生成，或在掌握完整分布时使用官方支持的 JSON 数据方式维护单列直方图。

手动更新还是 AUTO UPDATE

MySQL 8.4 为直方图增加了 AUTO UPDATE。不写更新选项时默认是 MANUAL UPDATE，只有明确的 ANALYZE TABLE 或相关统计重算触发更新；如果数据持续变化且分布会漂移，可以把维护交给自动更新，但要把额外统计成本和复制行为纳入评估。

-- 对变化频繁的过滤列开启自动更新，并固定本次的 bucket 数量
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 32 BUCKETS AUTO UPDATE;

-- 如果希望由发布或维护窗口显式控制更新，恢复为手动更新
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 32 BUCKETS MANUAL UPDATE;

自动更新并不意味着每次写入都立即重建直方图；它与表的 ANALYZE TABLE 和 InnoDB 持久化统计重算有关。对批量导入、分区切换或状态大规模迁移后的数据，手动安排一次更新通常更容易观察成本和变更影响。

用 EXPLAIN 判断估算是否真的改善

直方图的目标是改善估算，不是直接承诺更短的执行时间。创建或更新后，应在相同参数、相同数据快照和相近会话条件下比较执行计划，重点观察估算行数、过滤比例以及是否因为估算改变而选择了更合适的连接顺序。

-- 只读取计划，比较直方图更新前后的估算变化
EXPLAIN FORMAT=TRADITIONAL
SELECT order_id, created_at
FROM orders
WHERE status = 'archived';

-- 需要机器可读的计划时使用 JSON 输出，便于保存对比记录
EXPLAIN FORMAT=JSON
SELECT order_id, created_at
FROM orders
WHERE status = 'archived';

如果更新直方图后估算仍然不合理，先确认查询确实引用了该列、统计没有过期、采样没有漏掉关键稀有值，再检查 optimizer_switch 中的选择性过滤优化是否被关闭。若直方图没有带来改善，可以用 DROP HISTOGRAM 回退，不要把越来越多的统计对象长期堆在表上。

常见问题

bucket 数量是不是越大越好？

不是。bucket 越多并不自动带来更准确或更快的计划；低基数列可以从较小数量开始，结合采样率、数据分布和 EXPLAIN 结果调整。维护成本和统计稳定性同样要考虑。

直方图能代替索引吗？

不能。直方图提供列值分布统计，帮助优化器估算过滤选择性；它不会创建数据访问路径。查询需要快速定位大量数据时，仍要单独评估索引设计。