MySQL 8.4 ANALYZE TABLE 直方图的桶数量配置方法

MySQL 8.4 可以用 ANALYZE TABLE 为非索引列维护直方图，帮助优化器更准确地估算过滤条件的行数。桶数量写在 UPDATE HISTOGRAM 语句的 WITH N BUCKETS 中，N 的合法范围是 1 到 1024；省略时使用 100。真正要解决的不是“桶越多越好”，而是让桶数量足以描述查询关心的数据分布，同时不把统计维护变成无意义的开销。

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

最稳妥的做法是先从 32、64 或 100 这类可解释的值开始，更新后查看 HISTOGRAM 中的 number-of-buckets-specified、histogram-type 和 sampling-rate，再结合真实查询的 EXPLAIN 结果决定是否调整。低基数列不需要盲目设置 1024；高基数且分布复杂的列，才有理由逐步增加桶数。

先分清桶数配置解决什么问题

直方图保存的是列值分布的统计信息，主要帮助优化器估算没有合适单列索引、或者过滤条件分布不均匀的列。它不是索引替代品，也不会把全表扫描自动变成索引扫描。MySQL 8.4 中，直方图可以按列创建、更新或删除，统计信息持久化在数据字典中，用户通过 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看。

桶数 N 可以理解为“允许统计模型把值域切成多少个观察区间”。如果列的不同值数量小于或等于 N，MySQL 可能生成 singleton 直方图，让一个桶对应一个值；如果不同值更多，则可能生成 equi-height 直方图，让一个桶表示一个值域范围。这里的 N 是配置请求值，不等于所有情况下最终可见桶数组的长度。

用 WITH N BUCKETS 配置桶数量

语句的核心形态如下。表名和列名替换成自己的对象，N 只填整数：

-- 为订单表的金额列生成 64 个桶的直方图
ANALYZE TABLE shop_order
  UPDATE HISTOGRAM ON amount
  WITH 64 BUCKETS;

如果需要同时处理几列，可以放在同一个 ON 子句中，但要确认这些列属于同一张表：

-- 同一张表的两个过滤列使用相同的桶数
ANALYZE TABLE shop_order
  UPDATE HISTOGRAM ON status, region_id
  WITH 64 BUCKETS;

如果省略 WITH 子句，MySQL 使用默认的 100。想修改已有直方图的桶数，直接再次执行 UPDATE HISTOGRAM；它会替换指定列的旧直方图，不会影响没有列在本次语句中的其他直方图。

桶数怎么选才有依据

可以先按列的基数、分布形状和查询谓词选择一个起始值。下面是工程上的起步建议，不是 MySQL 强制规则：

实际选择时，先问三个问题：查询是否经常按该列过滤；列值是否明显偏斜；优化器是否因为估算偏差选错了连接顺序或访问路径。若只是为了“让统计更精细”而建直方图，却没有对应的查询问题，就没有必要把 N 调大。

查看请求桶数和直方图类型

更新完成后，可以读取 INFORMATION_SCHEMA.COLUMN_STATISTICS 的 HISTOGRAM JSON。下面的查询只取当前表和列最关键的三个字段：

-- 查看直方图请求值、类型和采样率
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  HISTOGRAM->>'$."number-of-buckets-specified"' AS requested_buckets,
  HISTOGRAM->>'$."histogram-type"' AS histogram_type,
  HISTOGRAM->>'$."sampling-rate"' AS sampling_rate
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'shop_order'
  AND COLUMN_NAME = 'amount';

重点看三个字段：

• number-of-buckets-specified：创建或更新时传入的 N。
• histogram-type：通常是 singleton 或 equi-height，反映列的不同值数量与桶数之间的关系。
• sampling-rate：生成直方图时读入的数据比例。小于 1 表示发生了采样，不应把统计结果理解成对每一行的精确枚举。

采样率偏低时，不要只靠继续增加 N 解决问题。先确认数据量、histogram_generation_max_mem_size 和列分布是否允许当前统计达到目标；如果数据已经变化很多，也应重新生成直方图。

调整、自动更新和删除

如果 EXPLAIN 仍然显示过滤行数估算不理想，可以按一次只改变一个变量的方式比较 32、64、128 等配置。不要同时更换索引、改写 SQL 和调整桶数，否则很难知道改善来自哪里。

-- 先用更高的桶数重新生成，便于和旧方案对比
ANALYZE TABLE shop_order
  UPDATE HISTOGRAM ON amount
  WITH 128 BUCKETS;

-- MySQL 8.4 可选择在表级 ANALYZE 时自动更新该直方图
ANALYZE TABLE shop_order
  UPDATE HISTOGRAM ON amount
  WITH 128 BUCKETS
  AUTO UPDATE;

MySQL 8.4 支持 AUTO UPDATE；如果不写 AUTO UPDATE 或 MANUAL UPDATE，默认是手动更新。启用自动更新后，后续对该表执行 ANALYZE TABLE 时会沿用该直方图上次指定的桶数。数据变化频繁但查询确实依赖列分布时，它比长期不更新更合适；如果数据更新不规律，手动更新更容易控制维护时机。

如果对比后确认直方图没有帮助，可以删除它：

-- 删除指定列的直方图，让优化器不再使用这份列分布统计
ANALYZE TABLE shop_order
  DROP HISTOGRAM ON amount;

几个容易误用的边界

• 直方图主要用于非索引列的分布估算；已经有合适索引的列不一定从直方图中受益，先看实际计划。
• JSON 和空间类型不支持生成直方图；临时表、加密表也不适合作为直方图对象。
• 单列唯一索引覆盖的列不能生成直方图，因为唯一性本身已经提供了更直接的选择性信息。
• 更新语句需要对应表的权限；批量更新前还要考虑统计生成对内存和读锁的影响。
• 直方图是按需更新的，数据长期变化却不重新生成时，旧统计可能逐渐失真。

最后可以用下面的清单收尾：先确认列确实是查询瓶颈，再从 32、64 或默认 100 开始；执行更新后读取请求桶数、类型和采样率；使用 EXPLAIN 比较估算；只有在分布复杂且问题仍存在时才增加 N；不再受益时删除直方图。

相关问题

WITH 1024 BUCKETS 一定比 64 更准确吗？

不一定。桶越多意味着更细的分段，但数据采样、内存限制和数据本身的分布都会影响结果。应以实际过滤估算和执行计划变化为准。

为什么查询 COLUMN_STATISTICS 看不到某列？

先确认该列已有直方图、库表列名正确，并且当前账号对对象有可见权限。没有生成直方图的列不会凭空出现在该视图中。

修改桶数需要先 DROP HISTOGRAM 吗？

不需要。对同一列再次执行 UPDATE HISTOGRAM 并指定新的 N 即可替换旧统计；只有确认不再需要时才使用 DROP HISTOGRAM。