MySQL 8.0 直方图统计信息优化器成本估计的精度提升一、统计信息的精度瓶颈优化器的盲区MySQL 优化器依赖统计信息选择执行计划——表的行数、索引的基数cardinality、列值的分布。传统统计信息只有列有多少不同值不知道值是如何分布的。当数据分布严重倾斜时如 90% 的订单状态为已完成优化器可能严重低估过滤后的行数选择全表扫描而非索引扫描。生产环境中统计信息精度面临三个核心痛点第一均匀分布假设——优化器假设列值均匀分布但实际数据往往严重倾斜第二关联列的独立性假设——优化器假设 WHERE 条件中的列相互独立但实际列间常有相关性第三统计信息更新延迟——ANALYZE TABLE是手动触发的统计信息可能过时。这个问题的本质是直方图Histogram通过存储列值的分布信息让优化器了解数据的真实分布而非依赖均匀分布假设从而提升成本估计的精度。二、直方图统计信息的底层机制flowchart TB subgraph 无直方图[无直方图: 均匀分布假设] direction LR U1[status列: 100种值] U2[总行数: 100万] U3[估计: 每种值 10000 行] U4[实际: completed90万, 其余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();四、直方图的边界分析直方图不替代索引统计直方图提供列值分布信息但不替代索引的基数统计。两者互补——直方图用于非索引列的过滤估计索引统计用于索引列的范围估计。更新频率的权衡直方图更新需要全表扫描列值大表的更新耗时较长。更新频率需要在精度和开销之间权衡——数据变化快的表需要更频繁更新。关联列的局限直方图是单列统计无法捕捉列间相关性。WHERE city 北京 AND category 餐饮的选择性估计仍假设两列独立可能低估行数。适用边界直方图适合数据分布倾斜、查询条件涉及非索引列的场景。对于均匀分布的数据直方图的收益有限。五、总结MySQL 8.0 直方图通过存储列值分布信息提升优化器成本估计的精度。落地路线建议起步阶段为高频查询条件涉及的列创建直方图观察执行计划是否改善。优化阶段根据列的基数选择直方图类型——低基数用单例直方图高基数用等高直方图。强化阶段建立直方图自动维护机制定期更新统计信息。精细化阶段监控直方图对执行计划的影响量化查询性能改善效果。