MySQL 8.0Notes

第 08 章：优化器

zjc 于 2026-01-08 发布

这是《MySQL 8.0 源码与内核实战》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 优化器决定 SQL 的物理执行方式：使用哪个索引、按什么顺序连接表、是否物化子查询、使用哪种聚合策略。它的目标是基于代价模型估计总成本，而不是保证绝对最优。

8.1 输入输出

Query_block
  -> logical transformations
  -> candidate access methods
  -> join order search
  -> access method selection
  -> physical plan

核心决策：

  1. 单表访问路径；
  2. 连接顺序；
  3. 连接算法；
  4. 是否使用索引条件下推；
  5. 是否使用覆盖索引；
  6. 是否物化派生表；
  7. 是否使用临时表；
  8. 排序是否可由索引完成。

8.2 单表访问路径

路径 说明
table scan 全表扫描
index scan 扫描整个索引
range scan 范围或多个区间
ref / eq_ref 使用索引等值或前缀匹配
full text 全文索引
index merge 合并多个索引
skip scan 联合索引跳过前导列，受版本和条件限制

示例：

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status TINYINT,
  created_at DATETIME,
  KEY idx_user_created(user_id, created_at)
) ENGINE=InnoDB;

EXPLAIN FORMAT=TREE
SELECT * FROM orders
WHERE user_id=100 AND created_at>='2026-01-01';

如果查询只返回少量行，二级索引加回表可能优于全表扫描。

8.3 连接算法

算法 过程
Nested Loop 外层每行到内层查找
Block Nested Loop 外层分块后比较
Hash Join 构建小表哈希表并探测

8.0 逐步增强 hash join，用于无可用索引的等值连接。执行计划中可通过 EXPLAIN FORMAT=TREE 观察 join 节点。

影响连接代价：

  1. 外层行数；
  2. 内层访问方式；
  3. 缓冲区；
  4. 过滤条件；
  5. join buffer；
  6. 回表次数；
  7. 内存排序。

8.4 连接顺序搜索

多表连接的顺序组合会快速增长：

table count N -> permutations N!

优化器会做剪枝，并受 optimizer_search_depth、optimizer_prune_level 等参数影响。

常见策略：

  1. 先估计单表过滤后的行数；
  2. 优先低代价访问路径；
  3. 依赖连接条件传播；
  4. 限制搜索深度；
  5. 保留候选计划；
  6. 读取系统统计和引擎统计。

8.5 统计信息

InnoDB 统计包括：

  1. 表行数估计；
  2. 索引基数；
  3. 不同值数量；
  4. 索引层级；
  5. 聚簇索引大小；
  6. 二级索引大小。

查看：

SHOW INDEX FROM orders;
ANALYZE TABLE orders;
SELECT * FROM information_schema.innodb_tablestats;

控制方式：

SET GLOBAL innodb_stats_persistent_sample_pages=64;

统计过旧或抽样偏差会导致行数估计错误，进而影响连接顺序。

8.6 条件处理

可传递条件：

t1.id = t2.id AND t1.id = 100
=> t2.id = 100

不可直接使用索引的常见形式：

写法 影响
col+1=10 需要表达式重写或无法使用
LOWER(col)='a' 取决于函数和排序规则
col LIKE '%x' 前导通配无法普通 range
隐式类型转换 可能无法使用索引
OR 非索引列 可能退化为扫描

8.7 子查询

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status=1);

策略：

  1. semijoin；
  2. materialization；
  3. first match；
  4. loose scan；
  5. duplicate weedout；
  6. correlated subquery execution。

EXPLAIN FORMAT=TREE 和 optimizer trace 能看到选择。8.0 对子查询策略有多轮增强，版本差异明显。

8.8 Optimizer Trace

SET optimizer_trace='enabled=on,one_line=off';
SELECT * FROM orders WHERE user_id=100;

SELECT trace
FROM information_schema.optimizer_trace\G

重点看：

  1. range 分析；
  2. 每个索引估计行数；
  3. 单表访问代价；
  4. join 顺序候选；
  5. 最终计划原因；
  6. 是否物化；
  7. 是否下推条件。

8.9 Hint

常见 hint：

SELECT /*+ INDEX(orders idx_user_created) */ *
FROM orders WHERE user_id=100;

SELECT /*+ JOIN_ORDER(orders, users) */ *
FROM orders JOIN users ON users.id=orders.user_id;

SELECT /*+ NO_HASH_JOIN(orders, users) */ ...

使用原则：

  1. 先确认默认计划不合理的原因；
  2. 用最小范围加 hint；
  3. 记录版本和数据分布；
  4. 上线后监控；
  5. 数据变化后重新评估；
  6. 不把 hint 当作长期掩盖统计问题的手段。

8.10 源码入口

常见文件：

文件 职责
sql/sql_optimizer.cc 优化主流程
sql/sql_select.cc SELECT 逻辑和计划
sql/range_optimizer/* range 分析
sql/join_optimizer/* 连接优化
sql/sql_executor.cc 计划执行
sql/opt_hints.cc hint 解析

调试断点：

b JOIN::optimize
b mysql_select
b calculate_scan_cost

本章小结

优化器基于统计信息和代价模型，在候选访问路径与连接顺序中做搜索。读源码时要结合 EXPLAIN、optimizer trace 和统计信息，理解行数估计、过滤条件、连接策略和版本差异。

思考题

  1. 为什么优化器不能保证最优计划？
  2. 统计信息如何影响连接顺序？
  3. range、ref、index scan 的代价差异是什么？
  4. 子查询 semijoin 和物化的适用条件有什么不同？
  5. optimizer trace 应重点查看哪些字段？