MySQL 性能优化：EXPLAIN 执行计划与 join

MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（MySQL 认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）

当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimizer 时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

一、join 查询语句的顺序

语法格式：

解析顺序：

二、七种 Join 查询

假设有 A 表和 B 表两张表为例，

三、Mysql join 的本质，Nested Loop Join 和 Hash Join

Mysql 在不同版本对 join 的实现有不同的实现方式，mysql5.7 之前，是通过 Nested Loop join 方式实现的，在 mysql 8 以后对这种嵌入循环查询的方式采用 hash join 的算法进行了优化。

MySQL 使用嵌套循环算法或它的变体来执行表之间的连接。

Nested-Loop Join Algorithm

A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined.一个简单的嵌套循环联接(NLJ)算法一次从循环中的第一个表读取一行，将每一行传递到一个嵌套循环，该循环处理联接中的下一个表。只要还有需要连接的表，这个过程就会重复多次。

如果使用简单的 NLJ 算法，连接处理如下:

因为 NLJ 算法一次只将一行从外部循环传递到内部循环，所以它通常读取在内部循环中处理的表多次。

分析 t1 join t2 ：

t1 分别取出每一个记录去从 t2 中匹配 t2 中的每一个列，然后再合并数据，这样，如果数据量过大（尤其是 t1），对数据库的开销会很大，所以最好是小表 join 大表

t1 join t2 的时候，mysql 会内部进行一个优化(内连接，因为外连接已经指定了驱动表)，在读取数据的时候，不一定是先读取 t1，在读取 t2。在这种情况下如果要指定先读哪一个表中的数据，可以使用关键字 STRAIGHT_JOIN，这样就强制了左表 join 右表的顺序。即：select * frm t1 STRAIGHT_JOIN t2 on t1.id = t2.id

Block Nested-Loop Join Algorithm

A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. This reduces by an order of magnitude the number of times the inner table must be read.

块嵌套循环(BNL)连接算法使用缓冲在外部循环中读取的行，以减少必须读取内部循环中的表的次数。例如，如果将 10 行读入一个缓冲区并将该缓冲区传递给下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有 10 行进行比较。这可以将必须读取内部表的次数减少一个数量级。

如果使用简单的 B-NLJ 算法，连接处理如下:

分析 t1 join t2 ：

如果 join 列没有索引，就会采用 BNLJ，可以看到中间有一个 join buffer 缓冲区，是将 t1 表的所有 join 相关的列都缓存到 join buffer 中，然后批量的和 t2 表惊醒匹配，将 NLJ 中匹配多次的方式合并为一次，降低了 t2 表中的访问频率。

默认情况下 join_buffer_size=256K,在查找的时候 Mysql 会将所有的需要的列缓存到 join buffer 中，包括 select 的列。在一个有 N 个 join 关联的 sql 中，在执行的时候会分配 N-1 个 join buffer

Hash join

Beginning with MySQL 8.0.18, MySQL employs a hash join for any query for which each join has an equi-join condition and uses no indexes

从 MySQL 8.0.18 开始，MySQL 对任何查询都具有相等连接条件且不使用索引的查询使用哈希连接

如：SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;

EXPLAIN 查询执行计划后可以到 Extra 中看到 Extra: Using where; Using join buffer (hash join)

散列连接通常比以前 MySQL 版本中使用的块嵌套循环算法更快，也更适合使用散列连接。从 MySQL 8.0.20 开始，删除了对块嵌套循环的支持，服务器在以前使用块嵌套循环的地方使用散列连接。

默认情况下，MySQL 8.0.18 及以后版本尽可能使用散列连接。可以使用一个 BNL 和 NO_BNL 优化器提示来控制是否使用散列连接。

(MySQL 8.0.18 支持 hash_join=on 或 hash_join=off 作为 optimizer_switch 服务器系统变量以及优化器提示 hash_join 或 NO_HASH_JOIN 的设置的一部分。在 MySQL 8.0.19 及以后版本中，这些不再有任何效果。)

哈希连接的内存使用可以使用 join_buffer_size 系统变量来控制;散列连接不能使用超过此数量的内存。当散列连接所需的内存超过可用内存时，MySQL 通过使用磁盘上的文件来处理。如果发生这种情况，您应该知道，如果散列连接无法装入内存，并且创建的文件超过 open_files_limit 设置的文件，则连接可能不会成功。为避免此类问题，请作出以下任何一项更改:

• 增加 join_buffer_size，这样散列连接就不会溢出到磁盘。

• 增加 open_files_limit。从 MySQL 8.0.18 开始，散列连接的连接缓冲区是递增分配的;因此，您可以将 join_buffer_size 设置得更高，而无需使用分配大量 RAM 的小查询，但是外部连接会分配整个缓冲区。在 MySQL 8.0.20 及以后版本中，散列连接也用于外连接，因此这不再是一个问题。

分析 t1 join t2 ：

select * from t1 join t2 on t1.id = t2.id；

hash join 在进行一次查询的时候，会先选择一个表中的 join 字段进行 hash 运算——hash(t1.id)，将该字段全量数据进行 hash 后，会存放到 hash table 中，但是如果表数据太多，内存是放不下全量数据的，所以 mysql 对这种情况做了额外的处理，会将数据写入文件块中。内存中数据匹配完成之后，会将文件块中的数据加载到内存，然后重复以上的过程。

四、EXPLAIN 执行计划

MySQL 提供了一个执行计划的工具（在架构中我们有讲到， 优化器最终生成的就是一个执行计划） ， 其他数据库，例如 Oracle 也有类似的功能。通过 EXPLAIN 我们可以模拟优化器执行 SQL 查询语句的过程， 来知道 MySQL 是怎么处理一条 SQL 语句的。 通过这种方式我们可以分析语句或者表的性能瓶颈。

通过 EXPLAIN 命令我们可以知道：

1、 多张表关联查询时表的读取顺序

2、 数据读取的操作类型

3、 可以使用哪些索引和实际用到了哪些索引

4、 表之间的引用（ref）

……

id

id：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序

id 相同，执行顺序由上至下

id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

id 相同不同，同时存在：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行；衍生=DERIVED

select_type

select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询

• SIMPLE：简单的 select 查询，查询中不包含子查询或者 UNION

• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY

• SUBQUERY：在 SELECT 或者 WHERE 列表中包含了子查询

• DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放在临时表里

• UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED

• UNION RESULT：从 UNION 表获取结果的 SELECT

table

table：显示这一行的数据是关于哪张表的

输出行所引用的表的名称。这也可以是以下值之一：

<unionM,N>: 行是指具有 和 id 值的行 的 M 并集 N。

<derivedN>：该行是指用于与该行的派生表结果 id 的值 N。例如，派生表可能来自 FROM 子句中的子查询 。

<subqueryN>：该行是指与物化子查询该行的结果 id 的值 N。

type

type：访问类型排列，显示查询使用了何种类型

type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

挑重要的来说：system>const>eq_ref>ref>range>index>ALL，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

• system：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，这个也可以忽略不计

• const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量

• 

• eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

• ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

• range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束于另一点，不用扫描全部索引

• index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘数据库文件中读的）

• all：FullTable Scan，将遍历全表以找到匹配的行（全表扫描）

一般来说，得保证查询只是达到 range 级别，最好达到 ref

possible_keys

显示可能应用在这张表中的索引，一个或多个

若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为 null，则没有使用索引

若查询中使用了覆盖索引，则该索引仅出现在 key 列表中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len 显示的值为索引最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的

ref

显示索引哪一列被使用，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值

由 key_len 可知 t1 表的索引 idx_col1_col2 被充分使用，t1 表的 col1 匹配 t2 表的 col1，t1 表的 col2 匹配了一个常量，即’ac’；

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息

Using filesort（文件排序）：

• MySQL 中无法利用索引完成排序操作成为“文件排序”

• 说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

• 出现 Using filesort 不好（九死一生），需要尽快优化 SQL

示例中第一个查询只使用了 col1 和 col3，原有索引派不上用场，所以进行了外部文件排序；第二个查询使用了 col1、col2 和 col3，原有索引派上用场，无需进行文件排序

Using temporary（创建临时表）：

使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by

出现 Using temporary 超级不好（十死无生），需要立即优化 SQL 示例中第一个查询只使用了 col1，原有索引派不上用场，所以创建了临时表进行分组；第二个查询使用了 col1、col2，原有索引派上用场，无需创建临时表

Using index（覆盖索引）：

1. 表示相应的 select 操作中使用了覆盖索引（Coveing Index），避免访问了表的数据行，效率不错！

2. 如果同时出现 using where，表明索引被用来执行索引键值的查找

3. 如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作

4. 覆盖索引（Covering Index），也说为索引覆盖

• 理解方式一：就是 select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

• 理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。注意：如果要使用覆盖索引，一定要注意 select 列表中只取出需要的列，不可 select * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

Using where：表明使用了 where 过滤

Using join buffer：表明使用了连接缓存

impossible where：where 子句的值总是 false，不能用来获取任何元组

select tables optimized away：在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct：优化 distinct，在找到第一匹配的元组后即停止找同样值的工作