模块三：查询执行与优化原理

一、SQL 执行整体流程回顾

一条 SQL 从提交到返回结果的路径：

  1. 解析 SQL
  2. 预处理
  3. 优化器生成执行计划
  4. 执行器按计划调用存储引擎
  5. 返回结果

核心理解：

  • 优化器决定"怎么查"
  • 执行器负责"按计划执行"
  • 存储引擎负责"真正读数据"

二、单表访问方法

MySQL 在访问单表时，会选择不同的访问方式。

使用 EXPLAIN 查看：

1

EXPLAIN SELECT * FROM user WHERE id = 1;

1️⃣ const

  • 主键或唯一索引等值查询
  • 最快方式
  • 结果最多一行

2️⃣ ref

  • 普通索引等值查询
  • 返回多行

3️⃣ range

  • 范围查询
  • BETWEEN、>、<、IN 等

4️⃣ index

  • 全索引扫描
  • 比 ALL 好，但仍然扫描全部索引

5️⃣ ALL

  • 全表扫描
  • 最慢方式

三、索引合并（Index Merge）

当多个单列索引可以同时使用时：

类型：

  • Intersection（交集）
  • Union（并集）
  • Sort-Union

示例：

1

EXPLAIN SELECT * FROM user WHERE age = 20 OR city = 'beijing';

四、多表连接原理

MySQL 连接的本质：

嵌套循环（Nested Loop）

1️⃣ 嵌套循环连接（Nested-Loop Join）

执行流程：

  1. 选择驱动表
  2. 从驱动表取一行
  3. 到被驱动表查找匹配记录
  4. 循环执行

驱动表选择原则：

  • 小表优先
  • 过滤性强的表优先

2️⃣ 基于块的嵌套循环（Block Nested-Loop Join）

当被驱动表没有索引时：

  • 使用 join buffer
  • 批量匹配

相关参数：

1

SHOW VARIABLES LIKE 'join_buffer_size';

五、子查询优化

子查询在优化阶段会被重写。

1️⃣ 物化（Materialization）

  • 先执行子查询
  • 结果存入临时表
  • 主查询再使用

2️⃣ 半连接（Semi-Join）

用于 IN 子查询优化。

优化方式：

  • Duplicate Weedout
  • FirstMatch
  • LooseScan

3️⃣ 子查询转连接

示例：

1

SELECT * FROM user WHERE id IN (SELECT user_id FROM order);

优化器可能改写为：

JOIN 查询

六、执行计划分析

使用 EXPLAIN 查看：

1

EXPLAIN SELECT * FROM user WHERE age > 20;

重要字段：

  • id → 执行顺序
  • select_type → 查询类型
  • type → 访问方法
  • key → 使用索引
  • rows → 预估扫描行数
  • Extra → 额外信息

七、执行流程总结

单表查询：

  • 优化器选择访问方式
  • 可能使用索引
  • 可能全表扫描

多表查询：

  • 选择驱动表
  • 执行嵌套循环
  • 可能使用 join buffer

子查询：

  • 可能物化
  • 可能转连接

本章核心理解

✔ 访问类型决定性能
✔ ALL 是性能警告信号
✔ 小表驱动大表
✔ 子查询本质是重写与优化
✔ 执行计划必须会看

下一篇：查询优化器与成本模型