MySQL 的查询优化器如何选择执行计划？

回答重点

选择执行计划一般需要经过以下几个步骤：

1）将 SQL 语句解析为解析树。

2）预处理，包括语法检查、权限验证、查询重写（例如常量表达式计算、子查询展开等）。

3）生成多个执行计划，并选择成本最低的执行计划。

扩展知识

优化器成本的计算方式

实际上优化器的内部实现方式比较复杂，我们仅需着重关心它的成本计算，这样能更好地理解 SQL 的执行计划是如何选择的。

MySQL 会根据成本来选择最终应用的索引，这里成本主要包括 I/O 成本和 CPU 成本。

I/O 成本

所谓的 I/O 成本其实指的是把数据从磁盘加载到内存的成本。

MySQL 是以页的形式来读取数据的，即使你只要一条数据，但是实际读取的还是整页的数据，因为根据空间局部性原理，这条数据被读取，那么距离它空间近的数据，也有很大概率会被读取，因此相邻的数据也应该被加载到内存中，所以 MySQL 默认读取一页。

在优化器内，读取一页的成本记为 1。

CPU 成本

数据从磁盘读取到内存后，需要比较、排序等，这些操作需要占用 CPU 资源，因此优化器以扫描的行记为成本，一行的成本为 0.2。

因此最终的成本计算就是看 扫描行数 * 0.2 + 数据长度/16kb = 成本

对比所有索引的成本，最终选择最低成本的索引！

扩展查询优化器的优化

1）查询重写

优化器会对查询进行重写，来简化查询动作或消除冗余查询。

例如：

• 子查询优化：将子查询转换为连接查询，或者将子查询展开（unroll）。
• 常量表达式计算：在预处理阶段计算常量表达式，以简化查询。

2）表连接优化

优化器会评估不同的表连接顺序，并选择成本最低的连接顺序。简单来说优化器会选择行数更少的表优先进行连接，以减少中间结果集的大小。

例如：

• 嵌套循环连接（Nested Loop Join）：对于小表或带索引的连接使用。
• 哈希连接（Hash Join）：对于大表的连接使用。
• 排序合并连接（Sort-Merge Join）：对于大表的排序后合并连接使用。