MySQL 8.0 直方图统计信息：优化器成本估计的精度提升-编程阁

MySQL 8.0 直方图统计信息：优化器成本估计的精度提升

一、统计信息的精度瓶颈：优化器的"盲区"

MySQL 优化器依赖统计信息选择执行计划——表的行数、索引的基数（cardinality）、列值的分布。传统统计信息只有"列有多少不同值"，不知道值是如何分布的。当数据分布严重倾斜时（如 90% 的订单状态为"已完成"），优化器可能严重低估过滤后的行数，选择全表扫描而非索引扫描。

生产环境中，统计信息精度面临三个核心痛点：第一，均匀分布假设——优化器假设列值均匀分布，但实际数据往往严重倾斜；第二，关联列的独立性假设——优化器假设 WHERE 条件中的列相互独立，但实际列间常有相关性；第三，统计信息更新延迟——ANALYZE TABLE是手动触发的，统计信息可能过时。

这个问题的本质是：直方图（Histogram）通过存储列值的分布信息，让优化器了解数据的真实分布，而非依赖均匀分布假设，从而提升成本估计的精度。

二、直方图统计信息的底层机制

flowchart TB subgraph 无直方图["无直方图: 均匀分布假设"] direction LR U1[status列: 100种值] U2[总行数: 100万] U3[估计: 每种值 10000 行] U4[实际: completed=90万, 其余=10万] U5[误差: 90倍] end subgraph 有直方图["有直方图: 真实分布"] direction LR H1[等宽直方图<br/>每个桶存储范围和频率] H2[等高直方图<br/>每个桶存储近似相同的行数] H3[估计: completed≈90万] H4[误差: <5%] end subgraph MySQL实现["MySQL 8.0 直方图"] direction TB M1[单例直方图<br/>适合低基数列<br/>存储每个值的频率] M2[等高直方图<br/>适合高基数列<br/>最多1024个桶] end

关键机制解析：

单例直方图：为每个不同值存储其频率。适合低基数列（如性别、状态），精确度高但存储开销与基数成正比。
等高直方图：将列值排序后分成 N 个桶，每个桶包含近似相同数量的行。适合高基数列（如金额、日期），存储开销固定（最多 1024 个桶），精度随桶数增加而提升。
选择性估计：优化器使用直方图估计WHERE status = 'completed'的选择性——从直方图中查找 'completed' 的频率，乘以总行数得到估计行数。

三、MySQL 直方图的工程实践

3.1 创建与管理直方图

-- 为orders表的status列创建直方图 ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS; -- 为amount列创建直方图（高基数列，更多桶） ANALYZE TABLE orders UPDATE HISTOGRAM ON amount WITH 256 BUCKETS; -- 为多列创建直方图 ANALYZE TABLE orders UPDATE HISTOGRAM ON status, amount, create_date; -- 查看直方图信息 SELECT * FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME = 'orders'; -- 删除直方图 ANALYZE TABLE orders DROP HISTOGRAM ON status;

3.2 直方图对执行计划的影响

-- 场景: orders表100万行, status列90%为'completed' -- 无直方图时: 优化器假设均匀分布 -- 估计 WHERE status = 'completed' 返回 10000 行 (100万/100种状态) -- 选择: 全表扫描 -- 有直方图后: 优化器知道真实分布 -- 估计 WHERE status = 'completed' 返回 900000 行 -- 选择: 全表扫描 (正确选择，因为大部分行都满足条件) -- 关键场景: WHERE status = 'pending' (仅1%) -- 无直方图: 估计10000行，可能选全表扫描 -- 有直方图: 估计10000行，选索引扫描 (正确) -- 验证执行计划变化 EXPLAIN SELECT * FROM orders WHERE status = 'pending'; EXPLAIN SELECT * FROM orders WHERE status = 'completed';

3.3 直方图维护策略

-- 定期更新直方图（建议每天或每周） -- 创建存储过程自动更新 DELIMITER // CREATE PROCEDURE refresh_histograms() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE tbl VARCHAR(64); DECLARE cur CURSOR FOR SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMN_STATISTICS; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO tbl; IF done THEN LEAVE read_loop; END IF; -- 重新采集直方图 SET @sql = CONCAT( 'ANALYZE TABLE ', tbl, ' UPDATE HISTOGRAM ON ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME = tbl), ' WITH 256 BUCKETS' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur; END // DELIMITER ; -- 调度: 每天凌晨3点执行 -- CREATE EVENT refresh_hist_event -- ON SCHEDULE EVERY 1 DAY STARTS '03:00:00' -- DO CALL refresh_histograms();