MySQL优化器代价模型与索引选择问题
MySQL数据库的查询优化器通过代价模型估算不同执行计划的成本，选择代价最低的方案。代价估算的核心输入是统计信息：表的行数、索引的基数（Cardinality）、数据分布等。当统计信息不准确时，优化器可能选择错误的索引或执行计划，导致查询性能急剧下降。MySQL 8.0引入的数据直方图（Histogram）统计信息，为优化器提供了更精确的数据分布信息，有效改善索引选择和Join顺序决策。
传统ANALYZE TABLE收集的统计信息仅包含索引的基数（不同值的数量），不包含数据分布特征。当索引列数据严重倾斜时（如status列90%为active、5%为pending、5%为closed），优化器无法区分高频值和低频值，对WHERE status=”closed”可能错误选择全表扫描而非索引扫描。
MySQL 8.0直方图类型与创建方法
MySQL 8.0支持两种直方图类型：
1. Singleton Histogram（单例直方图）：每个不同值单独记录频率，适合基数较低的列（如枚举类型的status列）。
2. Equi-Height Histogram（等高直方图）：将数据划分为若干桶（Bucket），每个桶包含相近数量的行，记录桶的边界值和频率。适合基数较高的列（如日期列、金额列）。
创建直方图的语法：
-- 为orders表的status列创建单例直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 1024 BUCKETS;
-- 为orders表的create_time列创建等高直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON create_time WITH 256 BUCKETS;
-- 同时为多列创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, create_time, amount WITH 128 BUCKETS;
-- 查看直方图信息
SELECT column_name, histogram
FROM information_schema.column_statistics
WHERE table_name = "orders";
-- 删除直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;
桶数量选择：单例直方图的桶数等于不同值数量（自动设置）；等高直方图推荐64-1024个桶，一般业务场景128-256桶即可满足需求。
直方图如何影响索引选择与执行计划
通过EXPLAIN对比直方图创建前后的执行计划变化：
-- 测试表：orders 1000万行
-- status列值分布：active=90%, pending=5%, closed=3%, cancelled=2%
CREATE INDEX idx_status ON orders(status);
-- 查询低频值（closed=3%）
EXPLAIN SELECT * FROM orders WHERE status = "closed";
-- 无直方图：优化器不知道closed只有3%的数据
-- 可能选择全表扫描 type: ALL, rows: 5000000
-- 创建直方图后
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
EXPLAIN SELECT * FROM orders WHERE status = "closed";
-- 优化器精确知道closed只有3%
-- 选择索引扫描 type: ref, rows: 300000
直方图对范围查询的效果更显著：
-- 金额范围查询
EXPLAIN SELECT * FROM orders WHERE amount BETWEEN 10000 AND 50000;
-- 无直方图：偏差可能超过50%
-- 有直方图：精确统计落在范围内的行数
ANALYZE TABLE orders UPDATE HISTOGRAM ON amount WITH 256 BUCKETS;
EXPLAIN SELECT * FROM orders WHERE amount BETWEEN 10000 AND 50000;
-- 行数估算精度从偏差50%改善到偏差5%以内
Index Dive机制与eq_range_index_dive_limit
Index Dive是MySQL优化器在处理IN列表和范围查询时，对每个值或范围区间分别进行索引探测（Dive），获取精确的行数估算。当IN列表中的值数量小于eq_range_index_dive_limit阈值时，优化器执行Index Dive；超过阈值则回退到索引统计信息估算。
默认配置：
-- 查看当前阈值
SHOW VARIABLES LIKE "eq_range_index_dive_limit";
-- 默认值：0（始终执行Index Dive）
-- 设置合理阈值
SET GLOBAL eq_range_index_dive_limit = 200;
-- 当IN列表超过200个值时，跳过Index Dive
配合直方图使用：
-- 开启直方图后，即使跳过Index Dive，优化器也有精确的分布信息
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
-- 设置较低的dive limit，减少优化器分析时间
SET GLOBAL eq_range_index_dive_limit = 100;
-- 对于IN查询
SELECT * FROM orders WHERE status IN (
"closed", "cancelled", "refunded", "expired", "rejected"
);
-- 5个值 < 100，执行Index Dive获取精确行数
-- 对于大量值的IN查询
SELECT * FROM orders WHERE user_id IN (
/* 500个用户ID */
);
-- 500个值 > 100，跳过Index Dive，直方图提供分布信息
直方图维护与自动化策略
直方图不会自动更新，数据变化后需要手动或定时重新分析：
-- 1. 定时任务重建直方图
CREATE EVENT rebuild_histograms
ON SCHEDULE EVERY 1 DAY STARTS "2026-08-12 02:00:00"
DO
BEGIN
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
ANALYZE TABLE orders UPDATE HISTOGRAM ON create_time;
ANALYZE TABLE users UPDATE HISTOGRAM ON region;
END;
-- 2. 监控直方图过期
SELECT table_name, column_name,
CAST(histogram->"$.last-updated" AS DATETIME) AS last_updated
FROM information_schema.column_statistics;
-- 3. 大批量数据导入后手动刷新
LOAD DATA INFILE "/data/orders.csv" INTO TABLE orders;
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, create_time;
直方图与持久化统计信息的配合：MySQL 8.0的innodb_stats_persistent=ON默认启用持久化统计信息，直方图也存储在数据字典中，服务器重启后不丢失。将直方图统计信息与Index Dive阈值配合调优，可以在优化器精度和规划速度之间取得平衡，这是MySQL数据库运维中常被忽视但效果显著的优化手段。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-zhi-fang-tu-tong-ji-xin-xi-yu-indexdive-you-hua-qi/
