MySQL / 系统课时

用直方图改善基数估算

用直方图改善基数估算

当过滤列的数据高度倾斜时,优化器如果不了解真实分布,就可能把少量值估成大量行,进而误判访问路径或连接顺序。本节在 MySQL 8.4 中构造倾斜数据,创建直方图,并对比创建前后的估算结果。

学习目标

  • 判断哪些倾斜列值得建立直方图。
  • 读懂 singleton、equi-height、桶数与选择性之间的关系。
  • 用 ANALYZE TABLE 创建、检查和维护直方图,并用 EXPLAIN ANALYZE 验证估算是否改善。

倾斜数据为什么会误导估算

优化器比较计划时依赖“预计会有多少行继续向下流动”。对于没有合适索引、且值分布很不均匀的列,普通统计信息难以表达“90% 行为 PAID、1% 行为 CANCELLED”这样的分布,过滤行数就可能估偏。

直方图记录的是列值分布统计,作用是帮助优化器估算谓词选择性;它不会像 B+Tree 索引那样提供新的物理访问路径。对于已经能通过索引范围估算或 index dive 得到较可靠行数的条件,直方图通常不是第一选择。

无直方图与有直方图时的选择性估算对比

构造可重复的倾斜样本

下面创建 1000 行订单状态,其中 900 行是 PAID、90 行是 NEW、10 行是 CANCELLED。表上故意不为 status 建索引,让估算主要依赖统计信息。实验表统一使用 histogram_demo,清理语句也必须删除同名表。

sql
DROP TABLE IF EXISTS histogram_demo;

CREATE TABLE histogram_demo (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(16) NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

INSERT INTO histogram_demo (status, amount)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT
    CASE
        WHEN n <= 900 THEN 'PAID'
        WHEN n <= 990 THEN 'NEW'
        ELSE 'CANCELLED'
    END,
    100.00 + n / 100
FROM seq;

SELECT status, COUNT(*) AS rows_count
FROM histogram_demo
GROUP BY status
ORDER BY rows_count DESC;

预期结果

text
status     rows_count
PAID       900
NEW        90
CANCELLED  10

先刷新普通表统计,再观察没有直方图时的估算。EXPLAIN ANALYZE 会真正执行查询,因此实验应放在测试环境。

sql
ANALYZE TABLE histogram_demo;

EXPLAIN ANALYZE
SELECT id, amount
FROM histogram_demo
WHERE status = 'CANCELLED';

预期结果

text
实际返回行数应为 10。
执行树会同时显示 estimated rows 与 actual rows;未创建直方图时的 estimated rows 取决于当前统计信息,记为 <VALUE>。

创建并读取直方图

WITH 16 BUCKETS 表示请求最多 16 个桶。示例列只有 3 个不同值,因此会形成 singleton 直方图,每个值都可以单独表示;当不同值更多、无法由桶逐个覆盖时,则使用 equi-height 桶近似值域分布。

sql
ANALYZE TABLE histogram_demo
UPDATE HISTOGRAM ON status
WITH 16 BUCKETS
MANUAL UPDATE;

SELECT
    COLUMN_NAME,
    HISTOGRAM->>'$."histogram-type"' AS histogram_type,
    HISTOGRAM->>'$."number-of-buckets-specified"' AS requested_buckets,
    HISTOGRAM->>'$."sampling-rate"' AS sampling_rate
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'histogram_demo'
  AND COLUMN_NAME = 'status';

预期结果

text
COLUMN_NAME  histogram_type  requested_buckets  sampling_rate
status       singleton       16                 <VALUE>

sampling_rate 表示生成统计时读取的数据比例,1 表示未采样。大表可能因为直方图生成时的采样而使用低于 1 的比例,因此生产环境不要把桶内容理解成逐行精确计数。

MySQL 8.4 支持 AUTO UPDATE 和 MANUAL UPDATE。MANUAL UPDATE 适合由变更流程或维护窗口显式控制统计刷新;AUTO UPDATE 则允许直方图随父表统计维护而自动更新。未显式指定时使用 MANUAL UPDATE,因此需要自动维护时应在创建直方图时明确启用 AUTO UPDATE。

sql
ANALYZE TABLE histogram_demo
UPDATE HISTOGRAM ON status
WITH 16 BUCKETS
AUTO UPDATE;

ANALYZE TABLE histogram_demo;

预期结果

text
两条语句均应成功执行。
第二条 ANALYZE TABLE 会重新计算表统计,并按已启用的 AUTO UPDATE 策略刷新 status 直方图。

再次执行同一条查询即可验证估算是否向真实值收敛。

sql
EXPLAIN ANALYZE
SELECT id, amount
FROM histogram_demo
WHERE status = 'CANCELLED';

预期结果

text
actual rows 仍为 10。
创建直方图后,estimated rows 应更接近真实的 10 行;具体 cost 与计时为 <VALUE>。

这里关注的不是执行时间变快了多少,而是估算行数更可信。估算改善后,真正受益的往往是更复杂查询中的连接顺序、访问路径或排序方案选择。

桶数与维护边界

桶越多,通常越能描述细粒度分布,但会增加统计生成与存储成本;桶太少则可能把差异很大的值压进同一范围。实际调优时应通过计划对比验证桶数,而不是机械地把桶数设得很大。

直方图尤其适合分布明显倾斜、经常参与过滤但不值得单独建索引的列;不同值较少时常能形成 singleton,不同值较多时则由 equi-height 桶近似分布。如果过滤列已有高效索引,范围优化器或 index dive 能给出更直接的估算,应先验证它们是否已经足够准确。

在 MySQL 8.4 中,维护策略应同时考虑数据变化和更新方式:需要随表统计自动刷新时,可以创建直方图时启用 AUTO UPDATE;希望由变更流程或维护窗口显式控制时,使用 MANUAL UPDATE,再按实际数据分布执行 UPDATE HISTOGRAM。无论采用哪种模式,都应把“统计是否仍然代表当前数据”纳入验证,而不是只依赖模式本身。

数据分布发生明显变化后,应重新检查 INFORMATION_SCHEMA.COLUMN_STATISTICS,并根据所选维护模式确认直方图是否已经更新。判断是否值得保留它的标准不是“计划看起来更复杂”,而是同一业务谓词的 estimated rows 是否更接近 actual rows,以及最终计划是否因此更合理。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

用直方图改善基数估算

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通