MySQL 优化器直方图数据分布变化后怎样更新列统计信息为什么结果为空或不符合预期

如果一列的数据分布已经明显变化，MySQL 仍然沿用旧的优化器直方图，常见表现是执行计划没有按预期调整、EXPLAIN 中的行数估算偏差变大，或者查询 INFORMATION_SCHEMA.COLUMN_STATISTICS 时拿不到想看的结果。解决这类问题的关键不是反复执行普通的 ANALYZE TABLE，而是明确管理列直方图的语句、查看位置和使用边界。

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

本文以 MySQL 8.4 为背景，围绕一个具体任务展开：数据分布变化后，怎样重新生成指定列的直方图，并判断结果为什么为空或不符合预期。示例中的表名和数据是说明用的原创示例，不代表某个线上库的真实执行结果。

先看清直方图到底解决什么问题

MySQL 的列直方图存储在数据字典中，通过 INFORMATION_SCHEMA.COLUMN_STATISTICS 这个视图读取。它用桶描述一列的值分布，优化器可以借此估算与常量比较的过滤选择性。对于没有合适索引、但数据分布明显不均匀的列，这类信息尤其有帮助。

它和索引统计信息不是同一件事。直方图是按需创建或更新的，表数据持续修改后会逐渐过期；普通的 ANALYZE TABLE orders 主要更新表和索引相关统计，并不会替代对列直方图的显式管理。反过来，即使直方图存在，优化器在适用范围内也可能优先使用范围优化器或索引估算。

数据变化后用 UPDATE HISTOGRAM 重建

重建直方图使用带 UPDATE HISTOGRAM 的 ANALYZE TABLE，并且一次只指定一张表。最小写法如下：

-- 为非索引过滤列重新生成直方图；省略 BUCKETS 时使用默认桶数
ANALYZE TABLE shop_orders
  UPDATE HISTOGRAM ON order_status;

-- 数据倾斜更明显时，显式给出桶数；N 的范围是 1 到 1024
ANALYZE TABLE shop_orders
  UPDATE HISTOGRAM ON order_status, region_code
  WITH 128 BUCKETS;

第二条语句会同时替换 order_status 和 region_code 的已有直方图。没有出现在本次语句中的其他列不会被顺手删除或重建。桶数不是越大越好：桶太少可能抹平尖锐分布，桶太多则增加统计数据和维护成本。可以先使用默认值，再根据列的基数和查询估算偏差调整。

如果批量导入后马上要确认新分布，建议把直方图更新放在导入任务的维护阶段，而不是把它当成每一条写入的同步动作。语句返回 histogram、status 和说明文本时，先记录这次操作针对的列；不要只根据查询计划是否立刻变化来判断生成成功。

用 COLUMN_STATISTICS 判断更新结果

直方图的可读入口是 INFORMATION_SCHEMA.COLUMN_STATISTICS。下面的查询只取目标表和目标列，并抽取几个能帮助排查的字段：

-- 只查看目标列，避免把其他表的直方图混在一起
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    HISTOGRAM->>'$."histogram-type"' AS histogram_type,
    HISTOGRAM->>'$."number-of-buckets-specified"' 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 = 'shop_orders'
  AND COLUMN_NAME IN ('order_status', 'region_code');

读结果时可以按下面的含义判断：

空结果不等于更新失败。它通常说明当前筛选没有匹配到保存的直方图；先去掉列名过滤，只确认表名，再逐步加回条件。如果确实没有记录，重新执行明确的 UPDATE HISTOGRAM。

为什么 EXPLAIN 看起来没有变化

直方图存在，并不代表每条 SQL 都会消费它。MySQL 文档列出的典型适用形式是列与常量的 =、范围比较、IN、IS NULL 等谓词。查询把列包在复杂表达式中、比较对象不是可判断的常量，或者优化器已经有更合适的索引估算时，直方图可能不会成为主要依据。

-- 用 EXPLAIN 观察估算行数和过滤比例；这里只观察计划，不修改数据
EXPLAIN
SELECT order_id, created_at
FROM shop_orders
WHERE region_code = '华东'
  AND order_status = 'pending';

-- 检查优化器是否关闭了条件过滤估算相关开关
SELECT @@optimizer_switch;

排查可以按四个方向进行：

1. 先确认列确实有新记录，并比较 last-updated 与批量导入时间。
2. 再确认谓词是对列与常量做比较，而不是期待直方图解决所有连接、排序或表达式估算。
3. 如果列本身有索引，接受优化器可能优先采用索引相关估算；直方图并不是强制计划提示。
4. 检查 condition_fanout_filter 是否被关闭。关闭它会影响其他优化行为，不能只为一条 SQL 临时照抄。

因此，“更新成功但执行计划没变”与“直方图没有更新”是两类问题。前者要看使用条件和估算来源，后者才需要回到 COLUMN_STATISTICS 和 ANALYZE TABLE 语句本身。

自动更新、手动更新和删除怎么选

默认的直方图更新方式是手动更新。对于批量导入节奏明确、希望把统计维护放在低峰窗口的系统，手动方式更容易控制成本：

-- 手动更新是默认行为，明确写出 MANUAL UPDATE 便于运维脚本阅读
ANALYZE TABLE shop_orders
  UPDATE HISTOGRAM ON region_code
  WITH 64 BUCKETS MANUAL UPDATE;

-- 需要取消一列的直方图时只影响指定列
ANALYZE TABLE shop_orders
  DROP HISTOGRAM ON region_code;

如果希望普通的 ANALYZE TABLE 或 InnoDB 持久统计重算时一起维护某列，可以在创建或更新时指定 AUTO UPDATE。它适合数据变化规律稳定、统计维护已经纳入常规分析任务的场景，但仍要关注分析操作的资源和锁影响：

-- 让后续常规统计重算时也按同样的桶数更新该列直方图
ANALYZE TABLE shop_orders
  UPDATE HISTOGRAM ON order_status
  WITH 128 BUCKETS AUTO UPDATE;

删除直方图不是修复数据问题的第一选择。只有当它持续误导估算、列已有更可靠的索引统计，或业务分布变化让它不再值得维护时，才使用 DROP HISTOGRAM。删除前先保留当前查询计划和统计查询结果，方便回退判断。

一张表记住这次排查

最稳妥的处理顺序是：先确认数据变化，再用 UPDATE HISTOGRAM 重建，随后查询 COLUMN_STATISTICS 读更新时间和桶信息，最后用 EXPLAIN 判断优化器是否有理由采用它。把这四步分开，才能区分“统计没更新”“查询不适用”和“已有更优估算来源”。

常见延伸问题

普通 ANALYZE TABLE 能不能替代 UPDATE HISTOGRAM？

不能简单替代。普通语句主要处理表和索引分布；列直方图需要带 UPDATE HISTOGRAM 子句显式生成或更新。

为什么 COLUMN_STATISTICS 没有数据？

通常是目标列没有保存直方图，或查询筛选的 schema、表名、列名不匹配。先缩小到表名，再检查列名和直方图语句。

桶数应该固定成 1024 吗？

不应该。桶数应结合数据基数、分布倾斜程度和维护成本选择；先用默认值，再根据估算问题调整，避免为了“更精确”盲目扩大。

更新直方图后一定会改变执行计划吗？

不一定。只有当查询谓词适用且直方图提供的估算优于其他来源时，执行计划才可能改变。