MySQL 优化器直方图数据分布变化后怎样更新列统计信息怎样完成一个可复用的最小示例

实际操作时你可以先构造干净的最小测试表，灌入符合目标倾斜分布的测试数据，再通过ANALYZE TABLE指令触发统计信息更新，最后查询系统表校验直方图分布是否符合预期，整个流程可以封装成通用脚本复用在不同实例上。

当列上的数据分布发生明显偏移后，直接执行带直方图参数的ANALYZE TABLE语句，就能重新生成对应列的统计信息，覆盖旧的直方图采样结果，完全可以用十行以内的SQL写出可复用的最小复现代码。

我在排查一条按状态筛选的 MySQL 查询时，遇到过一种容易误判的情况：表里的数据已经从“少数几种状态”变成了明显倾斜的分布，但优化器还在沿用旧的列分布信息。此时先别急着加索引，可以先判断这列是否使用了直方图，再用 ANALYZE TABLE ... UPDATE HISTOGRAM 重建统计信息。

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

直方图主要帮助优化器估算列值选择性，数据分布变化后需要重新生成；重建后还要通过 COLUMN_STATISTICS 和 EXPLAIN 观察结果，再决定保持手动更新还是启用自动更新。

• 先分清：索引统计信息和非索引列直方图不是同一类对象。
• 再重建：用指定列和桶数更新直方图，不要无目的地改整张表。
• 后复盘：检查更新时间、采样率和桶数，再对比 EXPLAIN 的估算。

数据分布变化后，先判断是不是直方图过期

直方图适合描述列值分布，尤其适合没有索引、但查询经常拿列值与常量比较的场景。它不是实时维护的索引结构，数据持续写入后，统计信息可能逐渐落后于真实分布。MySQL 8.4 的手册也明确区分了索引带来的维护成本与直方图按需更新的特点。

我通常先用一个不带业务敏感数据的最小表来复现。下面的例子只准备三种状态，重点是观察更新动作和元数据，不把输出写成已经在某台实例上执行过的证据。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL
);

-- 先插入一组可重复的状态分布，再用查询观察估算对象
INSERT INTO orders (id, status, created_at) VALUES
  (1, 'paid', '2026-01-01 09:00:00'),
  (2, 'paid', '2026-01-01 09:01:00'),
  (3, 'pending', '2026-01-01 09:02:00'),
  (4, 'cancelled', '2026-01-01 09:03:00');

-- 这类等值过滤可作为后续 EXPLAIN 的观察对象
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

当业务批量导入或状态比例发生明显变化时，可以把“旧直方图的更新时间较早”和“EXPLAIN 的估算与实际分布偏差扩大”作为触发信号。不要只因为某次查询变慢就断定直方图过期，索引、锁等待和数据页冷热同样可能影响结果。

用 ANALYZE TABLE 重建指定列直方图

确认问题边界后，先对指定列执行一次手动更新。桶数可以从较小值开始，只有在分布复杂且观察结果仍不稳定时再增加；MySQL 8.4 要求桶数为 1 到 1024 的整数，省略时使用默认值。

-- 只重建 status 列，100 是省略 WITH 时的常见默认桶数
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 32 BUCKETS;

-- 如果希望后续 ANALYZE TABLE 和 InnoDB 持久统计重算时一并更新
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 32 BUCKETS
  AUTO UPDATE;

MANUAL UPDATE 是默认策略，适合数据批次集中变化、希望由发布脚本或维护窗口统一更新的场景。AUTO UPDATE 适合经常运行表统计分析、且能接受统计信息随分析动作更新的表。它不是“每次 INSERT 都立即重建”，不要把它当作实时监控开关。

重建后怎样检查统计信息和执行计划

直方图信息通过 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看。真正有用的不是只看“有没有一行记录”，而是关注 JSON 中的 last-updated、sampling-rate、number-of-buckets-specified 和 auto-update。这些字段可以帮助判断更新是否发生、采样是否足够以及后续由谁维护。

-- 从 JSON 里取出复盘时最关心的几个字段
SELECT
  TABLE_NAME,
  COLUMN_NAME,
  HISTOGRAM->>'$."last-updated"' AS last_updated,
  HISTOGRAM->>'$."sampling-rate"' AS sampling_rate,
  HISTOGRAM->>'$."number-of-buckets-specified"' AS bucket_count,
  HISTOGRAM->>'$."auto-update"' AS auto_update
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME = 'status';

-- 重新查看优化器对同一过滤条件的估算
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

这里的 EXPLAIN 结果只用于比较更新前后估算是否更贴近当前分布，不意味着直方图一定会被每种执行计划采用。优化器在适用时才使用直方图，并且对某些范围估算会优先采用其他估算路径。若计划没有变化，应该继续检查索引、条件形式和数据访问成本。

手动更新、自动更新与回退怎样取舍

我更倾向于把更新策略和数据写入节奏绑定，而不是一律打开自动更新。每日批处理结束后数据分布才稳定的表，可以在批处理尾部手动更新，并把更新前后的 EXPLAIN 结果记录下来；写入持续且表统计分析本来就有固定节奏的表，再考虑 AUTO UPDATE。

如果发现直方图不但没有改善估算，反而让排查变复杂，可以先保留复盘记录，再删除指定列的直方图，让优化器回到没有该列直方图的状态：

-- 只移除 status 列的直方图，不影响其他列的统计对象
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- 删除后再次确认该列不再出现在 COLUMN_STATISTICS 中
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME = 'status';

回退并不等于问题已经解决，它只是把变量收回到更容易比较的状态。之后可以重新检查是否应该建立合适索引、调整查询条件或改变统计信息更新窗口。

常见问题

直方图更新后执行计划没有变化，是命令没生效吗？

不一定。先检查 COLUMN_STATISTICS 的更新时间与桶数，再确认该谓词是否属于优化器会使用直方图的形式；如果优化器选择了更优先的估算路径，计划不变也可能是正常结果。

直方图能替代索引吗？

不能。直方图主要改善选择性估算，通常不提供索引那样的访问路径。是否建索引仍要结合过滤频率、写入成本和实际执行计划判断。

AUTO UPDATE 会在每次数据写入后立即更新吗？

不会把它理解成逐行实时更新。它与对表执行 ANALYZE TABLE 以及 InnoDB 持久统计信息重算相关，适合有明确统计分析节奏的环境。