MySQL 直方图实验：数据偏斜、过滤比例与统计过期

一张任务表里，完成状态占 99%，待处理状态占 1%。优化器如果把两种状态估得差不多，后续连接顺序和中间结果大小就可能算错。给状态列建索引是一种选择，但如果这里只需要改善过滤比例估计，MySQL 的列直方图可能是更窄的一次调整。

先把两件事分开：索引提供访问数据的路径，直方图提供数据分布的描述。直方图能让优化器更清楚“符合条件的行大概占多少”，不会凭空建立一条定位这些行的捷径。

做一份分布明显不均匀的样本

实验固定 MySQL 8.4、InnoDB。下面创建的状态列没有索引，便于把估算变化和访问路径变化分开观察。

CREATE DATABASE IF NOT EXISTS histogram_lab
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE histogram_lab;

CREATE TABLE work_item (
    id BIGINT NOT NULL,
    task_state TINYINT NOT NULL,
    priority SMALLINT NOT NULL,
    payload VARCHAR(120) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 11000;
INSERT INTO work_item (id, task_state, priority, payload)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n,
       CASE WHEN n <= 9900 THEN 0 ELSE 2 END,
       MOD(n, 5),
       CONCAT('work-', n)
FROM seq;

ANALYZE TABLE work_item;
SELECT task_state, COUNT(*) AS actual_count
FROM work_item
GROUP BY task_state;

数据构造保证状态 0 有 9900 行，状态 2 有 100 行。这是脚本定义的样本分布，不是某个线上系统的统计。

先记录不带直方图时的计划：

EXPLAIN
SELECT id, payload
FROM work_item
WHERE task_state = 2;

EXPLAIN FORMAT=TREE
SELECT id, payload
FROM work_item
WHERE task_state = 2;

普通 EXPLAIN 里的 rows 与 filtered 要一起读。前者反映访问阶段估计读取多少行，后者反映条件过滤后保留的比例。表扫描读很多行、最后只留下少量行，两者并不矛盾。

也不要期待每个实例在建直方图前都显示同一个比例。版本小补丁、其他统计信息和优化器设置，都可能影响估计。实验要保存自己的前后输出，而不是对着别人的截图找完全相同的数字。

显式建立列直方图

ANALYZE TABLE work_item
    UPDATE HISTOGRAM ON task_state WITH 16 BUCKETS
    MANUAL UPDATE;

SELECT COLUMN_NAME,
       HISTOGRAM->>'$."histogram-type"' AS histogram_type,
       HISTOGRAM->>'$."sampling-rate"' AS sampling_rate,
       HISTOGRAM->>'$."last-updated"' AS last_updated,
       JSON_EXTRACT(HISTOGRAM, '$.buckets') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'work_item';

这里指定 MANUAL UPDATE，让后面的分布变化实验不会被自动刷新打断。MySQL 8.4 也支持 AUTO UPDATE，不能照搬旧版本文章，把“直方图永远不会自动更新”说成所有版本都成立的规则。

本例只有两个不同值，桶预算有 16 个，适合观察 singleton 直方图：一个值对应一个桶，桶里记录累计比例。若桶信息近似为值 0 对应 0.99、值 2 对应 1.0，后一个值的频率要由相邻累计比例相减得到，不是说状态 2 占了全部数据。

重跑前面的 EXPLAIN，重点检查过滤比例是否更接近真实分布。可以再执行：

EXPLAIN ANALYZE
SELECT id, payload
FROM work_item
WHERE task_state = 2;

EXPLAIN ANALYZE 会真的执行查询。此处是隔离库中的只读语句，适合把估计与实际返回行数并排核对。拿它去检查高成本线上查询之前，要先接受一次真实执行的负担。

最容易误判的地方就在这里：计划仍然是扫描，并不意味着直方图没起作用。没有状态索引，数据库仍需要访问数据来筛选；改变的是优化阶段的判断依据。单表小查询未必因此明显变快，在多表连接中，中间结果大小估得更准才更可能影响路径选择。

让分布变一次，观察旧统计如何误导

UPDATE work_item
SET task_state = 2
WHERE id <= 4900;

SELECT task_state, COUNT(*) AS actual_count
FROM work_item
GROUP BY task_state;

SELECT HISTOGRAM->>'$."last-updated"' AS last_updated,
       JSON_EXTRACT(HISTOGRAM, '$.buckets') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'work_item'
  AND COLUMN_NAME = 'task_state';

此时两种状态各有 5000 行。手动更新模式下，修改数据不会逐行维护这份直方图。实际分布变了，统计还停留在原来的 99% 与 1%，优化器就可能拿着一张过时地图做判断。

刷新同一列，再保存执行计划：

ANALYZE TABLE work_item
    UPDATE HISTOGRAM ON task_state WITH 16 BUCKETS
    MANUAL UPDATE;

EXPLAIN
SELECT id, payload
FROM work_item
WHERE task_state = 2;

生产环境里，批量回灌、归档删除和状态迁移都可能制造这种变化。是否定期更新，应该由分布变动和查询敏感度决定；不能只因为每天有写入，就机械地给所有列创建几千个桶。

什么情况下还是应该建立索引

给状态列建立索引后继续对照：

CREATE INDEX idx_task_state ON work_item (task_state);
ANALYZE TABLE work_item;

EXPLAIN
SELECT id, payload
FROM work_item
WHERE task_state = 2;

现在查询有了一条索引访问候选，但匹配一半记录时，回表成本可能让优化器仍选择扫描。不要把“建了索引必须使用”当目标。把条件换回稀有值、调整取回列数或改变分布，都可能改变这个决定。

对适用的范围条件，优化器可能采用索引相关的行数估计，并优先于直方图。因此直方图不是叠上去就永远更准的一层补丁。先确认当前谓词是否适用，再确认优化器从哪里得到估计，最后才谈加桶、刷新或移除。

另一个边界是列之间的相关性：假设某种任务状态只出现在某个租户，分别统计两列不等于描述了它们的联合分布。单列分布准确，也可能把组合条件估错。遇到这种情况，应该回到建模、联合索引和实际执行行数，而不是不停扩大桶数。

若调整没有收益，可以删除这一列的直方图，回到清晰的基线：

ANALYZE TABLE work_item DROP HISTOGRAM ON task_state;
DROP DATABASE histogram_lab;

直方图值得用在“优化器需要更好分布信息”的地方。查询缺的是定位路径、组合条件建模或可控返回范围时，它补不了那个缺口。

别把采样结果当成全表逐行清点

读取直方图时，除了桶，还应同时保存采样比例和更新时间。小实验可能采到全表，大表不一定。少数极稀有值在样本里的表示可能不稳定，扩大桶预算也不能补回根本没有采到的信息。桶数量解决的是分布如何压缩表达，采样比例解决的是先看到了多少数据，它们不是同一个旋钮。

还要核对谓词的写法。本文使用列与常量比较，适合直接讨论直方图；把状态藏进复杂表达式，或者拿另一张表的列做比较，就不能沿用同一解释。看到过滤估计偏差时，先还原实际 SQL 和绑定值，再检查是否真的走到了这份统计信息。

计划验证可以分成两层保存：一层是原始数据的精确分组计数，另一层是优化器的估计和实际执行。前者确认样本本身，后者确认估计如何参与查询。只贴一张“优化后耗时”截图，读者无法判断是统计更新起作用，还是恰好第二次读取命中了缓存。

在正式实例执行统计维护，也要核对权限、复制和维护窗口。ANALYZE TABLE 会修改统计信息，并非普通只读查询；默认还涉及二进制日志记录。它看起来比加索引轻，不代表可以绕过变更流程随手对所有大表运行。