MySQL 直方图统计帮助范围查询估算的使用方式

MySQL 直方图最适合解决这类问题：范围条件落在非索引列上，列值分布又明显倾斜，优化器只凭普通统计信息难以估准筛选比例。直方图把列值分布按桶保存，让优化器更接近真实选择率；它改善的是行数估算，不等同于创建索引，也不保证查询一定改用另一套执行计划。

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

范围查询为什么会估错

一次性能排查中，常见现象不是 SQL 突然无法执行，而是 EXPLAIN 里的 rows 与 filtered 和业务数据分布不匹配。比如订单金额大多集中在低区间，少量记录分布在高区间；查询 total_amount BETWEEN 5000 AND 10000 时，如果优化器不知道这种倾斜，就可能高估或低估命中行数，进一步影响连接顺序和计划成本判断。

-- 先观察范围条件的计划估算，不用示例值冒充真实线上结果
EXPLAIN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount BETWEEN 5000 AND 10000;

直方图适用于列与常量之间的等值、大小比较、BETWEEN、IN、IS NULL 等谓词。它主要对非索引列有价值；如果范围优化器可以从索引取得更好的行数估算，MySQL 会优先采用范围优化器的估算。

根因不是“没有索引”这么简单

索引能提供访问路径，也会在写入时维护；直方图只保存分布统计，按需创建或更新，对表数据修改没有持续维护开销。两者解决的问题不同。如果查询需要读取表中很大比例的行，增加索引未必划算，但准确的选择率仍可帮助优化器评估连接顺序。反过来，如果查询必须快速定位极少量记录，直方图不能替代合适索引。

另一个容易忽略的根因是统计过期。数据分布发生大幅变化后，旧直方图不会自动代表新分布；MySQL 8.4 支持 AUTO UPDATE，但默认仍是 MANUAL UPDATE。采用哪一种，应结合数据变化频率和统计维护窗口。

用 ANALYZE TABLE 创建直方图

下面为 orders.total_amount 创建 64 个桶。官方允许的桶数是 1 到 1024；省略时默认 100。桶并非越多越好，应从中等数量开始，再根据基数、采样率和估算效果调整。

-- 为倾斜的非索引金额列创建 64 桶直方图
ANALYZE TABLE orders
UPDATE HISTOGRAM ON total_amount
WITH 64 BUCKETS
MANUAL UPDATE;

该语法一次只处理一张表，可以同时列出多个列。执行需要目标表的 SELECT 和 INSERT 权限；分析期间，InnoDB 和 MyISAM 表会取得读锁，因此生产环境应放在可控窗口执行。直方图不支持视图、临时表、加密表，也不能为 JSON 或空间类型列生成；单列唯一索引覆盖的列同样不生成直方图。

创建后先检查统计对象

直方图保存在数据字典中，用户通过 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看。重点关注类型、实际桶数、采样率和更新时间：

-- 只提取排查需要的字段，避免直接阅读整段 HISTOGRAM JSON
SELECT
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    HISTOGRAM->>'$."histogram-type"' AS histogram_type,
    JSON_LENGTH(HISTOGRAM->'$.buckets') AS bucket_count,
    HISTOGRAM->>'$."sampling-rate"' AS sampling_rate,
    HISTOGRAM->>'$."last-updated"' AS last_updated
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME = 'total_amount';

singleton 表示一个桶对应一个离散值，equi-height 表示一个桶覆盖一段值域。sampling-rate 为 1 表示读取了全部数据，小于 1 表示生成时使用了采样。采样并不等于统计无效，但要把它纳入误差判断。

修复后怎样判断估算变好了

重新运行同一条 EXPLAIN，比较 rows 与 filtered 的变化，再结合可控环境中的实际返回行数判断。官方文档说明，未建立索引的列如果使用了直方图，估算效果会反映在 filtered。不要只看查询耗时：缓存、并发和存储状态都会干扰一次耗时。

-- 保持 SQL 与条件不变，只比较创建直方图前后的估算字段
EXPLAIN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount BETWEEN 5000 AND 10000;

如果估算仍然不合理，先确认谓词是否是“列与常量比较”，再检查直方图是否过期、采样比例是否过低，以及该列是否已经由范围优化器通过索引取得估算。直方图没有改善计划时，可以更新后再测；仍无收益就删除，不要把它当成必须永久保留的配置。

更新、自动维护和删除

-- 数据分布显著变化后手动重建统计
ANALYZE TABLE orders
UPDATE HISTOGRAM ON total_amount WITH 64 BUCKETS
MANUAL UPDATE;

-- 若维护策略允许，可让后续统计维护自动更新该直方图
ANALYZE TABLE orders
UPDATE HISTOGRAM ON total_amount WITH 64 BUCKETS
AUTO UPDATE;

-- 直方图无收益或造成错误估算时删除
ANALYZE TABLE orders
DROP HISTOGRAM ON total_amount;

防止同类估算问题再次出现

• 只为存在倾斜、且估算确实影响计划的列建立直方图，不按表批量铺满。
• 把 EXPLAIN 估算、实际行数和数据分布变化放进同一份排查记录。
• 大批导入、归档或业务分布改变后，安排统计更新，而不是等慢查询出现再处理。
• 需要快速定位少量记录时优先评估索引；需要改善非索引列选择率时再考虑直方图。
• 记录桶数、采样率和更新时间，避免只知道“建过直方图”却不知道它是否仍代表当前数据。

使用边界速查

小结

MySQL 直方图的正确定位是“给优化器补充分布信息”。先用 EXPLAIN 发现范围条件估算偏差，再为具体列创建直方图，检查桶类型和采样率，并用同一查询比较估算变化。它适合非索引列的选择率判断，却不是索引替代品；数据变化后要更新，确认无收益时应删除。