从优化器到执行计划

理解 MySQL EXPLAIN 的执行原理，需要深入到 MySQL 查询处理的核心机制。EXPLAIN 命令本身并不执行查询，而是模拟查询优化器的决策过程，生成一个"虚拟"的执行计划。这个过程涉及查询解析、预处理、优化器决策和执行计划生成四个关键阶段。

学习完本篇后还是有个小建议，不要盲目追求"完美"的执行计划，应根据业务需求平衡优化成本。这是我们踩坑后的肺腑之言。

EXPLAIN 背后的科学与艺术
EXPLAIN 命令是 MySQL 优化器的窗口，它展示了查询执行计划的关键信息，但理解这些信息需要结合优化器的工作原理。
理解优化器的成本模型：优化器基于统计信息和成本估算选择执行计划。这个我在我博客的一个章节中已经讲过了，大家可以朝前翻一翻。
关注 type 和 rows 列：这是评估查询效率的核心指标。
定期更新统计信息：通过 ANALYZE TABLE 确保统计信息的准确性。

我们先理解MySQL 查询处理的基本流程（这个章节，我在前面的文章中也深入的剖析了）

当你执行一条 SQL 查询时，MySQL 内部会经历以下步骤：

查询解析（Parser）
将 SQL 文本解析为抽象语法树（AST），检查语法正确性。
查询预处理（Preprocessor）
验证查询对象（表、列）是否存在，检查权限，进行语义分析。
查询优化（Optimizer）
生成多个执行计划，并选择代价最小的方案。这是 EXPLAIN 的核心关注点。
查询执行（Execution Engine）
根据优化器生成的计划，调用存储引擎执行具体操作。

EXPLAIN 命令主要作用于第 3 阶段，展示优化器选择的执行计划。

EXPLAIN 执行原理详解

1. 查询优化器的工作机制

MySQL 优化器的核心目标是找到执行查询的最低成本方案。它通过以下步骤实现：

生成候选执行计划：针对 SQL 查询，生成多种可能的执行路径（如不同的表连接顺序、索引使用方式）。
成本估算：对每个候选计划计算执行成本，成本模型考虑以下因素：
- 磁盘 I/O 成本（读取数据页的代价）
- CPU 成本（比较、排序、计算等操作）
- 内存使用成本（临时表、排序缓冲区等）
选择最优计划：选择成本最低的执行计划作为最终方案。

2. EXPLAIN 如何获取执行计划

当你执行 EXPLAIN SELECT ... 时，MySQL 会：

解析查询：与正常查询一样，先构建抽象语法树。
生成执行计划：优化器生成候选计划并选择最优方案。
返回计划信息：将优化器的决策信息以表格形式返回，而非真正执行查询。

3. EXPLAIN 输出的关键信息来源

EXPLAIN 的输出字段（如 type、key、rows）直接反映了优化器的决策逻辑：

type（连接类型）：
优化器根据索引使用情况和表连接方式，选择不同的访问类型（如 ALL、index、range 等）。例如，当没有合适的索引时，优化器会选择全表扫描（type=ALL）。
key（实际使用的索引）：
优化器评估所有可能的索引（possible_keys），选择能最小化查询成本的索引。
rows（估算扫描行数）：
优化器基于索引统计信息（存储在 information_schema.STATISTICS 表中）估算需要扫描的行数。例如：
```
-- 查看索引统计信息
SELECT * FROM information_schema.STATISTICS 
WHERE table_name = 'orders' AND index_name = 'idx_user_id';
```

MySQL 优化器的两种类型

1. 基于规则的优化器（RBO）

早期 MySQL 使用 RBO，它根据预定义的规则选择执行计划，例如：

优先使用主键或唯一索引
优先处理小表连接大表

缺点：不考虑数据分布和统计信息，可能选择次优计划。

2. 基于成本的优化器（CBO，MySQL 5.1+）

现代 MySQL 使用 CBO，它通过估算不同计划的执行成本来选择最优方案。CBO 依赖以下因素：

索引统计信息：通过 ANALYZE TABLE 命令收集，存储在 information_schema 中。
查询条件：如 WHERE 子句的过滤强度。
表连接方式：如嵌套循环连接（Nested Loop Join）、哈希连接（Hash Join）。

示例：

-- 收集表的统计信息
ANALYZE TABLE orders;

-- 查看优化器成本模型变量
SHOW VARIABLES LIKE 'optimizer_switch';

EXPLAIN 执行计划的局限性

虽然 EXPLAIN 提供了宝贵的优化线索，但它也有以下局限性：

估算而非实际值：rows 列是基于统计信息的估算值，实际执行时可能差异较大。
不显示执行细节：EXPLAIN 不会展示查询执行过程中的动态优化（如查询执行时的内存使用情况）。
无法预测数据倾斜：如果数据分布不均匀（如索引列存在大量重复值），估算结果可能不准确。
存储引擎差异：不同存储引擎（如 InnoDB、MyISAM）的执行计划可能不同。

深入理解 EXPLAIN 的高级技巧

1. 使用 optimizer_trace 查看详细优化过程

MySQL 5.6+ 提供 optimizer_trace 系统变量，可获取更详细的优化器决策过程：

-- 启用优化器跟踪
SET optimizer_trace="enabled=on";

-- 执行查询
SELECT * FROM users WHERE age > 30;

-- 获取优化器跟踪信息
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

2. 分析索引统计信息

通过 SHOW INDEX FROM table_name 查看索引的基数（Cardinality），评估统计信息的准确性：

SHOW INDEX FROM orders;

如果 Cardinality 值与实际行数差异较大，可执行 ANALYZE TABLE 更新统计信息。

3. 理解成本计算模型

MySQL 使用以下公式估算查询成本：

总成本 = 访问成本（I/O 成本） + 操作成本（CPU 成本）

可通过以下命令查看优化器成本模型变量：

SHOW VARIABLES LIKE 'cost_model%';

目录CONTENT

【原理篇】聊聊MySQL EXPLAIN 执行原理