0036. MySQL 8.0 新特性 - 统计直方图

• 1. 📝 概述
• 2. 💻 统计直方图的基本使用
• 3. 📒 索引扫描 vs. 全表扫描

1. 📝 概述

• MySQL 8.0 的统计直方图功能允许你为没有索引的字段提供数据分布信息，帮助查询优化器做出更准确的执行计划判断，从而提升查询性能，是 OLAP 和复杂查询场景下的重要增强功能。
• 统计直方图（Histogram Statistics）
  • 统计直方图 是一种数据库统计信息类型
    • 它记录了一列数据的分布情况
    • 包括值的频率、分布区间等
  • 举个例子：如果某列中大部分值都集中在某个区间，优化器可以根据直方图决定是否使用索引扫描还是全表扫描。
  • MySQL 8.0 引入了统计直方图，用于帮助查询优化器更准确地评估查询代价。
    • 特别适用于那些 没有索引的字段，通过分析数据分布情况，可以显著提升查询执行计划的准确性。
    • 查询优化器在生成执行计划时会参考这些信息，从而做出更优的选择。
• 作用
  • 帮助优化器估算行数
    • 对于没有索引的列，也能更准确地预估匹配行数
  • 优化 JOIN 和 WHERE 条件查询
    • 更好地判断是否进行索引下推或选择合适的连接顺序
  • 提升查询性能
    • 避免因错误估计而导致的低效执行计划

场景	无直方图	有直方图
查询优化器准确性	依赖默认分布模型	根据真实分布做出决策
WHERE 条件命中率估算	❌ 不准确，可能导致慢查询	✅ 更精确，提升执行效率
对无索引字段的支持	❌ 无法有效评估	✅ 可基于分布做成本估算
管理方式	❌ 不可控制	✅ 可手动创建/更新/删除

2. 💻 统计直方图的基本使用

-- 创建直方图
-- 创建单列的统计直方图
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary;

-- 你也可以指定桶的数量（bucket 数量）：
-- 指定最多 10 个桶
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 10 BUCKETS;

-- 删除直方图
-- 删除某一列的直方图
ANALYZE TABLE employees DROP HISTOGRAM ON salary;

• 假设我们有一张员工表 employees，其中 salary 字段没有索引：

SELECT * FROM employees WHERE salary > 10000;

• 使用场景：
  • 如果工资大多集中在 5000~8000 之间
  • 只有少数人超过 10000
• 创建直方图后，优化器将知道：
  • 这个条件只会命中少量数据 → 更倾向于使用索引（如果存在）
  • 或者避免使用索引扫描（如果命中太多）
  • 这样可以避免“本该走索引却走了全表扫描”、“本该走全表扫描却走了索引”的问题。

3. 📒 索引扫描 vs. 全表扫描

• 🤔 为什么在 WHERE 条件命中较多行时，要避免使用索引扫描？
  • 核心原因：索引扫描并不总是比全表扫描快！
  • 当一个查询命中了表中 大量数据行（例如超过 20%~30% 的总行数） 时：
    • 使用索引反而会变慢
    • 这时候优化器选择 全表扫描（Full Table Scan） 更高效
  • 索引原理简介
    • 索引的本质是“跳着读”
    • 索引的作用是帮助你快速定位到某些符合条件的数据页或记录。
    • 如果只命中少量数据，索引可以显著减少 I/O 次数。
    • 但如果命中很多数据：
      • 需要频繁跳跃读取磁盘上的不同数据页（Random I/O）
      • 性能可能比顺序读取整个表（Sequential I/O）还差！
  • 类比：
    • 找一本书中的几个关键词 → 查目录很快 —— 索引扫描
    • 但如果你要读整本书 → 直接从头翻到尾更快 —— 全表扫描
• 成本模型分析（Cost Model）
  • MySQL 查询优化器基于成本估算来决定是否使用索引。
  • 它会考虑以下因素：

因素	说明
数据命中比例	命中越多，越不适合用索引
磁盘访问方式	顺序 vs 随机 I/O
索引深度	B+Tree 层数越多，访问代价越高
表数据大小	小表可能直接走全表更划算

-- 比如，当优化器发现 10000 薪资以上的条件，命中了 80% 的员工。
WHERE salary > 10000

-- 这时候如果使用索引：
-- 需要多次回表查找
-- 每次查找都是一次随机 I/O
-- 效率远低于一次性顺序读取所有数据页

-- 所以优化器会选择：
-- 全表扫描（Type: ALL），一次性读取所有数据页（Sequential I/O），效率更高。

• 假设一张表 employees 有 10,000 行
  • 场景 A：WHERE salary > 10000（命中 1000 行）
    • 只命中 10%，数量少
    • 优化器倾向于使用索引扫描（Index Range Scan）
    • 索引有效，性能好
  • 场景 B：WHERE salary > 5000（命中 9000 行）
    • 命中 90%，数量多
    • 优化器判断使用索引的成本高于全表扫描
    • 所以放弃索引，改用全表扫描（Using where; Using index condition pushdown 不成立）
    • 此时使用索引反而拖慢查询

情况	命中行数	是否使用索引	查询方式	性能表现
条件过滤性强	少量数据（如 <10%）	✅ 是	索引扫描	快
条件过滤性弱	大量数据（如 >20%）	❌ 否	全表扫描	更快
小表查询	全部数据	❌ 否	全表扫描	更快
大表 + 少量匹配	少量数据	✅ 是	索引扫描	快

• 当查询条件命中太多行时，使用索引会导致大量的随机 I/O 和回表操作，反而不如一次性顺序读取全部数据块来得快。因此，优化器会根据直方图等统计信息判断并选择最优的执行计划。