MySQL 8.0性能调优：直方图统计与索引跳跃扫描实战

直方图统计为什么是SQL查询优化的隐藏利器

MySQL 8.0引入的直方图统计（Histogram Statistics）是数据库运维中容易被忽视的性能优化手段。当表缺少合适索引时，优化器只能靠粗略的统计信息估算行数，导致执行计划走偏——全表扫描替代了索引扫描，性能差距可能达到数十倍。直方图让优化器在无索引场景下也能精确估算选择率，选出更优的执行计划。

直方图的两种类型与适用场景

MySQL 8.0支持两种直方图：

– SINGLETON直方图：适用于低基数列（如性别、状态枚举），每个唯一值一个桶，记录精确频次

– EQUI-HEIGHT直方图：适用于高基数列（如用户ID、金额），等高划分桶数，每个桶包含近似相同的行数

创建直方图的语法：

-- 查看当前表的直方图信息
SELECT * FROM information_schema.column_statistics
WHERE schema_name = 'order_db';

-- 为status列创建SINGLETON直方图（低基数）
ANALYZE TABLE orders UPDATE HISTOGRAM ON status
  WITH 8 BUCKETS;

-- 为amount列创建EQUI-HEIGHT直方图（高基数）
ANALYZE TABLE orders UPDATE HISTOGRAM ON amount
  WITH 32 BUCKETS;

-- 为多列同时创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, amount, create_time
  WITH 100 BUCKETS;

直方图对执行计划的影响验证

以订单表为例，验证直方图对SQL查询优化的效果：

-- 场景：按status查询（status没有索引）
-- 无直方图时的执行计划
EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';
-- type=ALL, filtered=10.00%, rows=500000
-- 优化器估算filtered=10%，但实际cancelled可能只有5000行

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

EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';
-- filtered变为1.00%（更精确），优化器选择更优执行计划

直方图在多表关联场景下的价值更大：

-- 多表关联：直方图影响驱动表选择
EXPLAIN SELECT o.*, p.product_name
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.status = 'cancelled'
  AND p.category = 'electronics';

-- 无直方图：优化器不知道status='cancelled'的选择率，可能选错驱动表
-- 有直方图：优化器精确估算两表的匹配行数，选择更优的关联顺序

索引跳跃扫描（Index Skip Scan）实战

MySQL 8.0.13引入的索引跳跃扫描解决了”联合索引前缀列不在WHERE条件中”的经典问题。这是SQL查询优化中一个重要的改进。

传统规则：联合索引INDEX(a, b)只能用于WHERE a=?或WHERE a=? AND b=?的查询，WHERE b=?无法走索引。跳跃扫描打破了这一限制。

-- 创建联合索引
CREATE INDEX idx_category_status ON products(category, status);

-- 传统情况下无法走索引的查询
SELECT * FROM products WHERE status = 'active';

-- MySQL 8.0.13+跳跃扫描执行计划
EXPLAIN SELECT * FROM products WHERE status = 'active';
-- Extra中出现"Skip scan"，表示使用了跳跃扫描
-- type=range，而非ALL全表扫描

跳跃扫描的生效条件：

– 前缀列（category）的基数较低——不同值数量远小于表总行数

– 前缀列值数量 * 每个前缀值下的预估行数 远小于 总行数

– 查询只引用索引中的后续列，不引用前缀列

直方图与跳跃扫描的协同优化

直方图和跳跃扫描可以配合使用，进一步优化执行计划：

-- 复杂查询场景
-- 索引：INDEX idx_cat_brand_status (category, brand, status)

-- 1. 为category创建直方图（帮助跳跃扫描评估成本）
ANALYZE TABLE products UPDATE HISTOGRAM ON category WITH 20 BUCKETS;

-- 2. 为status创建直方图（优化器精确估算过滤行数）
ANALYZE TABLE products UPDATE HISTOGRAM ON status WITH 8 BUCKETS;

-- 3. 验证查询计划
EXPLAIN SELECT brand, COUNT(*)
FROM products
WHERE status = 'active'
GROUP BY brand;

-- 预期：跳跃扫描走idx_cat_brand_status
-- 优化器根据直方图精确估算选择率

直方图的维护与自动化更新策略

直方图不会自动更新，数据变更后需要手动或定时刷新：

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

-- 全量刷新（定期执行，建议在低峰期）
CREATE EVENT refresh_histograms
ON SCHEDULE EVERY 1 DAY
STARTS '03:00:00'
DO BEGIN
  ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 8 BUCKETS;
  ANALYZE TABLE orders UPDATE HISTOGRAM ON amount WITH 32 BUCKETS;
  ANALYZE TABLE products UPDATE HISTOGRAM ON category WITH 20 BUCKETS;
END;

刷新频率建议：

– 数据变更频繁的表：每天刷新一次

– 数据相对稳定的表：每周刷新一次

– 数据量特别大的表：每月刷新一次

数据库高可用架构中的直方图一致性

数据库高可用架构中，主从复制的直方图需要注意一致性问题：

– 直方图的元数据存储在mysql.column_statistics表中，该表不会被复制到从库

– 从库需要单独创建直方图，否则从库的查询优化器无法利用直方图信息

– 读写分离架构下，读请求走从库时，直方图的缺失可能导致执行计划偏差

-- 在从库单独创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 8 BUCKETS;
ANALYZE TABLE products UPDATE HISTOGRAM ON category WITH 20 BUCKETS;

对于数据迁移实战场景，迁移完成后第一步应该是重建统计信息和直方图，否则新环境的查询性能会明显下降：

-- 数据迁移后的统计信息重建脚本
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND table_type = 'BASE TABLE';

-- 重建直方图
ANALYZE TABLE core_table UPDATE HISTOGRAM ON key_column WITH 32 BUCKETS;

直方图统计和索引跳跃扫描是MySQL性能调优工具箱中两个互补的利器。直方图解决的是”优化器算不准”的问题，跳跃扫描解决的是”联合索引前缀缺失”的问题。数据库运维中，两者配合使用可以在不增加索引、不改写SQL的前提下，显著提升查询性能。