MySQL 8 直方图特性详解

一、直方图概述

MySQL 8.0 引入了直方图统计信息，这是一种更精确的统计方法，用于优化查询性能。直方图提供了列数据分布的详细统计信息，帮助优化器生成更准确的执行计划。

二、直方图类型

1. 等宽直方图 (Singleton)

每个桶代表一个单一的列值
适用于基数较低（唯一值较少）的列
存储列值和该值的频率

2. 等高直方图 (Equi-height)

每个桶代表一个值范围
适用于基数较高（唯一值较多）的列
存储桶边界和累积频率

三、创建和管理直方图

1. 创建直方图

-- 为指定列创建直方图ANALYZE TABLE table_name 
UPDATE HISTOGRAM ON column_name [, column_name2] WITH n BUCKETS;-- 示例：为users表的age列创建直方图ANALYZE TABLE users UPDATE HISTOGRAM ON age WITH 100 BUCKETS;

2. 查看直方图信息

-- 查看直方图元数据SELECT * FROM information_schema.COLUMN_STATISTICSWHERE TABLE_NAME = 'users';-- 查看直方图的JSON内容SELECT 
  JSON_PRETTY(HISTOGRAM->>'$.buckets') as buckets,
  HISTOGRAM->>'$.histogram-type' as type,
  HISTOGRAM->>'$.number-of-buckets-specified' as buckets_specifiedFROM information_schema.COLUMN_STATISTICSWHERE TABLE_NAME = 'users' 
  AND COLUMN_NAME = 'age';

3. 删除直方图

ANALYZE TABLE table_name DROP HISTOGRAM ON column_name;

四、直方图内部结构

1. 数据字典存储

直方图信息存储在数据字典中，位于 mysql.column_statistics 表：

-- 实际存储位置（系统表）SELECT * FROM mysql.column_statistics;

2. JSON 结构示例

{
  "histogram-type": "singleton",
  "number-of-buckets-specified": 100,
  "buckets": [
    [18, 0.25],  -- 值18，频率25%    [25, 0.15],  -- 值25，频率15%    [30, 0.35]   -- 值30，频率35%  ]}

五、优化器如何使用直方图

1. 选择性估算

-- 查询示例：优化器使用直方图估算age>30的行数EXPLAIN SELECT * FROM users WHERE age > 30;-- 优化器计算过程：-- 1. 查找直方图中age>30的桶-- 2. 计算总频率-- 3. 估算返回行数 = 总行数 × 总频率

2. 连接顺序优化

当多表连接时，直方图帮助优化器确定最佳连接顺序：

-- 假设有users和orders表连接SELECT * FROM users u 
JOIN orders o ON u.id = o.user_idWHERE u.age BETWEEN 25 AND 35;-- 直方图帮助优化器：-- 1. 估算age条件的选择性-- 2. 决定先过滤users还是先连接

六、最佳实践

1. 选择合适的桶数量

-- 少量唯一值：使用较少的桶ANALYZE TABLE products UPDATE HISTOGRAM ON category_id WITH 10 BUCKETS;-- 大量唯一值：使用较多的桶ANALYZE TABLE logs UPDATE HISTOGRAM ON timestamp WITH 250 BUCKETS;

2. 更新策略

-- 定期更新直方图（数据变化较大时）-- 自动化脚本示例DELIMITER //CREATE EVENT update_histogramsON SCHEDULE EVERY 1 DAYDOBEGIN
  -- 更新关键查询列的直方图
  ANALYZE TABLE users UPDATE HISTOGRAM ON age, registration_date WITH 100 BUCKETS;
  ANALYZE TABLE orders UPDATE HISTOGRAM ON order_date, total_amount WITH 100 BUCKETS;END //DELIMITER ;

3. 监控直方图使用

-- 检查哪些查询受益于直方图SELECT 
  QUERY,
  TRACE->>'$**.histogram_present' as histogram_usedFROM information_schema.OPTIMIZER_TRACEWHERE TRACE->>'$**.histogram_present' IS NOT NULL;

七、性能影响和限制

1. 优势

更准确的选择性估算
改善连接顺序决策
减少优化器错误

2. 限制

直方图不自动更新
占用存储空间（每个直方图约8KB）
不支持所有数据类型（如BLOB、TEXT）

3. 存储空间计算

-- 估算直方图占用空间SELECT 
  TABLE_NAME,
  COLUMN_NAME,
  -- 每个直方图约占用
  LENGTH(HISTOGRAM) / 1024 as size_kbFROM information_schema.COLUMN_STATISTICS;

八、实用示例

场景：电商查询优化

-- 1. 创建关键列直方图ANALYZE TABLE products 
UPDATE HISTOGRAM ON price, category_id, stock_quantity 
WITH 200 BUCKETS;ANALYZE TABLE orders 
UPDATE HISTOGRAM ON order_date, total_amount, status WITH 200 BUCKETS;-- 2. 复杂查询示例SELECT p.name, COUNT(o.id) as order_countFROM products pJOIN order_items oi ON p.id = oi.product_idJOIN orders o ON oi.order_id = o.idWHERE p.price BETWEEN 50 AND 100
  AND p.category_id = 5
  AND o.order_date >= '2023-01-01'GROUP BY p.idHAVING order_count > 10;-- 直方图帮助优化器：-- - 估算price范围的选择性-- - 估算category_id=5的选择性-- - 决定连接顺序

九、注意事项

直方图 vs 索引统计：直方图补充但不替代索引统计信息
维护成本：大数据集上创建直方图可能耗时
兼容性：直方图是MySQL 8.0+特性
测试验证：在应用直方图前后，使用EXPLAIN验证执行计划变化

总结

MySQL 8 直方图是强大的优化工具，通过提供详细的列数据分布信息，显著改善了查询优化器的决策能力。正确使用直方图可以：

减少全表扫描
优化连接顺序
提高复杂查询性能

建议对查询条件中频繁使用且数据分布不均的列创建直方图，并建立定期更新机制。

ITPub博客

MySQL 8 直方图特性详解

MySQL 8 直方图特性详解

一、直方图概述

二、直方图类型

1. 等宽直方图 (Singleton)

2. 等高直方图 (Equi-height)

三、创建和管理直方图

1. 创建直方图

2. 查看直方图信息

3. 删除直方图

四、直方图内部结构

1. 数据字典存储

2. JSON 结构示例

五、优化器如何使用直方图

1. 选择性估算

2. 连接顺序优化

六、最佳实践

1. 选择合适的桶数量

2. 更新策略

3. 监控直方图使用

七、性能影响和限制

1. 优势

2. 限制

3. 存储空间计算

八、实用示例

场景：电商查询优化

九、注意事项

总结

最新文章