MySQL 8.0直方图统计信息优化慢查询：从原理到生产环境实践

MySQL优化器为何选择错误索引

MySQL查询优化器基于成本估算选择执行计划，当表数据分布严重倾斜时，优化器的统计信息不够精确会导致索引选择错误。典型场景：status字段有10个值，其中90%是”已完成”，优化器可能认为status=”待处理”的行占10%而选择全表扫描，实际该值只占0.1%。MySQL 8.0引入的直方图统计信息（Histogram Statistics）解决了这一问题，让优化器掌握列值分布的真实信息。

直方图类型与创建语法

MySQL 8.0支持两种直方图：

等高直方图（Equi-height Histogram）：将数据按频率分成等高的桶，每个桶存储范围端点和累计频率，适合数据分布不均匀的列。这是默认类型。

单值直方图（Singleton Histogram）：每个不同值一个桶，适合列值种类较少且分布不均匀的场景。

-- 查看表中哪些列有直方图
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM 
FROM information_schema.COLUMN_STATISTICS 
WHERE TABLE_NAME = "orders";

-- 对status列创建等高直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- 对user_id列创建单值直方图（值种类少）
ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id WITH 2 BUCKETS;

-- 删除直方图
ANALYZE TABLE orders DROP HISTOGRAM ON status;

直方图如何改变执行计划

创建直方图前后，通过EXPLAIN对比执行计划差异：

-- 创建前：优化器估算rows=50000，选择全表扫描
EXPLAIN SELECT * FROM orders WHERE status = "待处理";
-- type: ALL, rows: 50000, filtered: 10.00

-- 创建直方图后
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

EXPLAIN SELECT * FROM orders WHERE status = "待处理";
-- type: ref, rows: 50, filtered: 100.00, key: idx_status

直方图让优化器准确知道”待处理”状态只占0.1%的数据，从而选择索引扫描。注意直方图对等值查询和范围查询都有效，但对JOIN条件的优化有限。

直方图与索引统计信息的区别

索引统计信息（通过ANALYZE TABLE生成）只记录索引列的NDV（不同值数量）和平均每值行数，不记录具体值的频率分布。直方图则精确记录每个值或值范围的频率占比。

两者的适用场景：

– 索引列：优化器已有索引统计信息，通常不需要额外直方图
– 非索引列：直方图让优化器能评估过滤性，决定是否走其他索引
– 多条件查询：直方图帮助评估各条件的选择性，选择最优索引组合

生产环境维护策略

直方图是快照，不会自动随数据变化更新。需要制定刷新策略：

-- 方案1：写入定时任务Event
DELIMITER //
CREATE EVENT refresh_histograms
ON SCHEDULE EVERY 6 HOUR
DO BEGIN
  ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
  ANALYZE TABLE orders UPDATE HISTOGRAM ON region WITH 50 BUCKETS;
END //
DELIMITER ;

-- 方案2：通过pt-online-schema-change在低峰期执行
-- 方案3：应用层在数据大幅变更后触发

刷新频率取决于数据变化速率和查询性能敏感度。对于数据日增量超过5%的热表，建议每6小时刷新一次；冷表可以每天一次。直方图创建本身有成本（需全表扫描采样），在TB级表上执行可能需要几分钟，应在低峰期操作。

直方图使用限制与注意事项

MySQL直方图当前存在几个限制：不支持多列联合直方图、不支持JSON类型列、InnoDB持久化统计信息和直方图可能冲突、分区表需要每个分区单独分析。在以下场景优先考虑直方图：表中存在数据倾斜严重的列、查询条件包含非索引列、优化器频繁选错索引导致慢查询。配合慢查询日志和performance_schema.events_statements_summary分析，可以精确定位需要直方图优化的列。