Mysql 中令人稀里糊涂的Explain

---

本文想和大家来聊聊Mysql中的执行计划，一条SQL语句经过了查询优化器模块分析后，会得到一个执行计划，通过这个执行计划，我们可以知道该条SQL语句具体采用的多表连接顺序是什么，对于每个表具体采用的访问方法是什么 . . .

我们可以使用Mysql提供的Explain命令来获取一条SQL语句的具体执行计划，本文的重点在理解Mysql执行计划中各个选项含义。

查询优化模块属于数据库实现过程中比较困难的一块内容，笔者目前对这块也没有太多研究，但是Explain查询计划又是面试常问的内容，所以本文就不深入查询优化的具体实现，简单和大家唠唠mysql提供的查询计划。

本文基于Mysql 5.7版本进行讲解

---

环境准备

这里我们先准备好测试环境，具体SQL如下所示:

CREATE TABLE s1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

CREATE TABLE s2 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

# =======================为s1生成两万条随机记录================================

DROP PROCEDURE IF EXISTS GenerateRandomData;

DELIMITER //

CREATE PROCEDURE GenerateRandomData()
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE customIncrement INT DEFAULT 10000; -- 自定义递增值
    
    -- 指定要生成的记录数
    SET @num_rows := 20000;
    
    WHILE counter < @num_rows DO
        -- 生成随机数据
        SET @key1 := CONCAT('Key1_', FLOOR(RAND() * 1000));
        SET @key2 := counter + customIncrement; -- 自定义递增值
        SET @key3 := CONCAT('Key3_', FLOOR(RAND() * 1000));
        SET @key_part1 := CONCAT('Key_Part1_', FLOOR(RAND() * 1000));
        SET @key_part2 := CONCAT('Key_Part2_', FLOOR(RAND() * 1000));
        SET @key_part3 := CONCAT('Key_Part3_', FLOOR(RAND() * 1000));
        SET @common_field := CONCAT('Common_', FLOOR(RAND() * 1000));

        -- 插入数据
        INSERT INTO s1 (key1, key2, key3, key_part1, key_part2, key_part3, common_field)
        VALUES (@key1, @key2, @key3, @key_part1, @key_part2, @key_part3, @common_field);
        
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;


CALL GenerateRandomData();


# =======================为s2生成两万条随机记录================================

DROP PROCEDURE IF EXISTS GenerateRandomData;

DELIMITER //

CREATE PROCEDURE GenerateRandomData()
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE customIncrement INT DEFAULT 10000; -- 自定义递增值
    
    -- 指定要生成的记录数
    SET @num_rows := 20000;
    
    WHILE counter < @num_rows DO
        -- 生成随机数据
        SET @key1 := CONCAT('Key1_', FLOOR(RAND() * 1000));
        SET @key2 := counter + customIncrement; -- 自定义递增值
        SET @key3 := CONCAT('Key3_', FLOOR(RAND() * 1000));
        SET @key_part1 := CONCAT('Key_Part1_', FLOOR(RAND() * 1000));
        SET @key_part2 := CONCAT('Key_Part2_', FLOOR(RAND() * 1000));
        SET @key_part3 := CONCAT('Key_Part3_', FLOOR(RAND() * 1000));
        SET @common_field := CONCAT('Common_', FLOOR(RAND() * 1000));

        -- 插入数据
        INSERT INTO s2 (key1, key2, key3, key_part1, key_part2, key_part3, common_field)
        VALUES (@key1, @key2, @key3, @key_part1, @key_part2, @key_part3, @common_field);
        
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;


CALL GenerateRandomData();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

当环境准备好后，当我们对下面这条sql语句执行explain分析，会得到如下查询计划:

explain select * from s1 inner join s2 on s1.key1 == s2.key2 ;
1

确保环境没问题后，下面进入正式唠嗑环节。

---

属性概览

Mysql支持对SELECT，DELETE，INSERT，REPLCAE以及UPDATE语句前面都加上EXPLAIN进行执行分析，本文只针对SELECT展开分析；上面一小节最后也给出了explain查询计划返回结果内容，下面简单看看各个列的作用:

• id : 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
• select_type : SELECT关键字对应的那个查询的类型
• table : 表名
• partitions : 匹配的分区信息
• type : 针对单表的访问方法
• possible_keys : 可能用到的索引
• key : 实际上使用的索引
• key_len : 实际使用到的索引长度
• ref : 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
• rows : 预估的需要读取的记录条数
• filtered : 某个表经过搜索条件过滤后剩余记录条数的百分比
• extra : 一些额外的信息

核心需要关注的属性已用黑色加粗标出。

本文只会针对其中几个重点属性进行讲解。

---

table

不论我们的查询语句有多复杂，包含多少个表，到最后也是需要对每个表进行单表访问的，所以EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

下面举例说明:

• 单表访问

explain select * from s1;
1

• 连接查询

explain select * from s1 inner join s2 on s1.key1 == s2.key2 ;
1

连接查询的执行计划中，出现在前边的表表示驱动表，出现在后边的表表示被驱动表

---

id

查询语句中每出现一个SELECT关键字，设计MySQL的大叔就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列。

对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值：

explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';
1

对于不包含子查询的sql语句，输出结果可参考上一小节。

但是这里需要注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，如果输出的两条记录id相同，说明重写了，否则说明没有重写。

对于union查询，由于mysql需要去重，所以内部需要使用临时表，因此会多出一行id为null的结果:

explain select * from s1 union select * from s2;
1

UNION子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为的临时表（就是执行计划第三条记录的table列的名称），id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。

临时表如何对记录进行去重？

• 临时表也是表，只要为表中记录的所有列建立主键或者唯一索引就好了

跟UNION对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录，如下所示：

explain select * from s1 union all select * from s2;
1

---

select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

Mysql为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

select_type中常用的类型有以下几种:

• SIMPLE : 查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型，注意: 连接查询也算SIMPLE类型。
• PRIMARY : 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY。

• UNION : 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果，这就不多举例子了。
• UNION RESULT : MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，例子上边有，就不赘述了。
• SUBQUERY : 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。

如果子查询不依赖外层查询的值，那么该子查询为不相关子查询，否则为相关子查询。

• DEPENDENT SUBQUERY ： 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。

select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

• DERIVED ： 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED。

id为2的记录就代表子查询的执行方式，它的select_type是DERIVED，说明该子查询是以物化的方式执行的。id为1的记录代表外层查询，大家注意看它的table列显示的是，表示该查询是针对将派生表物化之后的表进行查询的。

• MATERIALIZED : 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED 。

执行计划的第三条记录的id值为2，说明该条记录对应的是一个单表查询，从它的select_type值为MATERIALIZED可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的id值都为1，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的table列的值是，说明该表其实就是id为2对应的子查询执行之后产生的物化表，然后将s1和该物化表进行连接查询。

关于子查询半连接，物化等优化手段，感兴趣的小伙伴可以自行去研究一下，本文就不展开说明了。

---

type

执行计划的一条记录就代表着Mysql对某个表的执行查询时的访问方法，其中的type列就表明具体采用了何种访问方法，Mysql总共存在下面这些访问方法:

• system
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• index_merge
• unique_subquery
• index_subquery
• range
• index
• ALL

下面我们依次来看看每个访问方法具体代表什么含义:

• system : 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。

如果采用Innodb存储引用，则输出的type类型依旧为ALL :

• const : 当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。

• eq_ref : 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref。

从执行计划的结果中可以看出，MySQL打算将s1作为驱动表，s2作为被驱动表，重点关注s2的访问方法是eq_ref，表明在访问s2表的时候可以通过主键的等值匹配来进行访问。

• ref ： 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref 。

• ref_or_null : 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null。

• index_merge : 一般情况下对于某个表的查询只能使用到一个索引，但单表访问在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询。

• unique_subquery ： 类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery

• index_subquery ： index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引

这里针对的是子查询返回的列是唯一索引还是普通索引

• range ： 如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法

• index ： 当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

如果所查询的列被查询优化器决定查询的索引树的索引列所包含，此时的访问方法为index。

• all : 全部扫描

---

possible_keys 和 key

possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些。

这两个属性比较好理解，但是注意一点:

• 在使用index访问方法来查询某个表时，possible_keys列是空的，而key列展示的是实际使用到的索引

注意: possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引

---

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的是一个常数还是某个列。

有的时候与索引列进行等值匹配的对象是一个函数：

---

filtered

连接查询中有一个 condition filtering 的概念，就是MySQL在计算驱动表扇出时采用的一个策略：

• 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。

• 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows列可以看出满足key1 > 'b’的记录有20020条。执行计划的filtered列就代表查询优化器预测在这20020条记录中，有多少条记录满足其余的搜索条件，也就是common_field = 'a’这个条件的百分比。此处filtered列的值是5.00，说明查询优化器预测在20020条记录中有5.00%的记录满足common_field = 'a’这个条件。

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，比方说下边这个查询：

从执行计划中可以看出来，查询优化器打算把s1当作驱动表，s2当作被驱动表。我们可以看到驱动表s1表的执行计划的rows列为20020， filtered列为10.00，这意味着驱动表s1的扇出值就是20020 × 10.00% = 2002，这说明还要对被驱动表执行大约2002次查询。

---

extra

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

Mysql提供的额外信息有好几十个，这里只对重点的几个进行讲解。

---

Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作：

---

Using index condition

有些搜索条件中虽然出现了索引列，但却并不能用来形成范围区间，也就是不能被用来减少需要扫描的记录数量，比如下边这个查询：

其中的key1 > 'z’可以用来形成范围区间，但是key1 LIKE '%a’却不能。我们知道MySQL服务器程序其实分为server层和存储引擎层，在没有索引条件下推特性之前，server层生成执行计划后，是按照下边步骤来执行这个查询的：

• server层首先调用存储引擎的接口定位到满足key1 > 'z’的第一条记录。
• 存储引擎根据B+树索引快速定位到该条二级索引记录后，根据该二级索引记录的主键值进行回表操作，将完整的用户记录返回给server层。
• server层再判断其他的搜索条件是否成立，如果成立则将其发送给客户端，否则的话跳过该记录，然后向存储引擎层要下一条记录。
• 由于每条记录都有一个next_record的属性，根据该属性可以快速定位到符合key1 > 'z’条件的下一条二级索引记录，再执行回表操作，将完整的用户记录返回给server层，然后重复上一步骤，直到将索引idx_key1的范围区间(‘z’, +∞)的所有记录都扫描过为止。

这里头有个问题，虽然key1 LIKE '%a’不能被用于组成范围区间来减少需要被扫描的二级索引记录数量，但这个搜索条件毕竟只涉及到了key1列，而key1列是包含在索引idx_key1中的。所以Mysql在这里进行了如下优化:

• server层首先调用存储引擎的接口定位到满足key1 > 'z’的第一条记录。
• 存储引擎根据B+树索引快速定位到该条二级索引记录后，不着急回表，先判断一下所有关于idx_key1索引的条件是否成立，也就是key1 > ‘z’ AND key1 LIKE '%a’是否成立，如果这些条件不成立，则直接跳过该二级索引记录，然后去找下一条二级索引记录；如果这些条件成立，则执行回表操作，将完整的用户记录返回给server层。
• server层再判断其他的搜索条件是否成立（本例中没有其他的搜索条件了），如果成立则将其发送给客户端，否则的话跳过该记录，然后向存储引擎层要下一条记录。
• 由于每条记录都有一个next_record的属性，根据该属性可以快速定位到符合key1 > 'z’条件的下一条二级索引记录，还是不着急进行回表操作，先判断一下所有关于idx_key1索引的条件是否成立，如果这些条件不成立，则直接跳过该二级索引记录，然后去找下一条二级索引记录；如果这些条件成立，则执行回表操作，将完整的用户记录返回给server层。然后重复上一步骤，直到将索引idx_key1的范围区间(‘z’, +∞)的所有记录都扫描过为止。

每次执行回表操作，都需要将一个聚簇索引页面加载到内存里，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。Mysql中把这个改进称之为索引条件下推（英文名：Index Condition Pushdown）。

这里需要注意一点:

• 在存储引擎层获取到一条二级索引记录后，需要在存储引擎层继续判断key1 > ‘z’ AND key1 LIKE '%a’是否成立。
• 可是key1 > 'z’这个条件不是用来生成范围区间的么，怎么这里还要在存储引擎层作为索引条件下推的条件再判断一遍呢 ? 猜测可能是一种冗余编码设置。
• 即使我们的查询条件中只保留key1 > 'z’这个条件，也是会将其作为索引条件下推中的条件来在存储引擎中判断一遍的，我们来看执行计划（注意看Extra列提示了Using index condition）：

最后还需要注意一点:

• 索引条件下推这个特性只是为了在扫描某个范围区间的二级索引记录时，能尽可能减少回表次数，从而减少IO操作。
• 而对于聚簇索引而言不需要回表，它本身就包含着全部的列，也起不到减少IO操作的作用，所以设计InnoDB的大叔们规定这个索引条件下推特性只适用于二级索引。

---

Use where

当某个搜索条件需要在server层进行判断时，在Extra列中会提示Using where。比如下边这个查询：

对于聚簇索引来说，是用不到索引条件下推特性的，所以所有的搜索条件都得在server层进行处理，也就是说本例中的common_field = 'a’条件是在server层进行判断的，所以该语句的执行计划的Extra列才提示Using where。

有的时候MySQL会扫描某个二级索引的一个范围区间的记录，比方说：

从执行计划中可以看出来，这个语句执行时将会使用到idx_key1二级索引，但是由于该索引并不包含common_field这个列，也就是说该条件不能作为索引条件下推的条件在存储引擎层进行判断，存储引擎需要根据二级索引记录执行回表操作后，将完整的用户记录返回给server层之后，在server层再判断这个条件是否成立，所以本例中的Extra列也显示了Using where的提示。

---

Using filesort

有一些情况下对结果集中的记录进行排序是可以使用到索引的，比如下边这个查询：

这个查询语句可以利用idx_key1索引直接取出key1列的10条记录，然后再进行回表操作就好了。但是很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，设计MySQL的大叔把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示，比如这样：

需要注意的是，如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为使用索引进行排序。

---

Using temporary

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示，比方说这样：

不知道大家注意到没有，上述执行计划的Extra列不仅仅包含Using temporary提示，还包含Using filesort提示，可是我们的查询语句中明明没有写ORDER BY子句呀？这是因为MySQL会在包含GROUP BY子句的查询中默认添加上ORDER BY子句，也就是说上述查询其实和下边这个查询等价：

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY common_field;
1

如果我们并不想为包含GROUP BY子句的查询进行排序，需要我们显式的写上ORDER BY NULL，就像这样：

这回执行计划中就没有Using filesort的提示了，也就意味着执行查询时可以省去对记录进行文件排序的成本了。

另外，执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表，比方说下边这个包含GROUP BY子句的查询就不需要使用临时表：

从Extra的Using index的提示里我们可以看出，上述查询只需要扫描idx_key1索引就可以搞定了，不再需要临时表了。

---

Using join buffer (Block Nested Loop)

基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。

如果表的数据量很大，内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以设计MySQL的大叔提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。设计MySQL的大叔把这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样可以在join buffer中放置更多的记录。

---

Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法，比如下边这个查询语句：

可以在对s2表的执行计划的Extra列显示了两个提示：

• Using join buffer (Block Nested Loop)：这是因为对表s2的访问不能有效利用索引，只好退而求其次，使用join buffer来减少对s2表的访问次数，从而提高性能。
• Using where：可以看到查询语句中有一个s1.common_field = s2.common_field条件，因为s1是驱动表，s2是被驱动表，所以在访问s2表时，s1.common_field的值已经确定下来了，所以实际上查询s2表的条件就是s2.common_field = 一个常数，所以提示了Using where额外信息。

---

Using intersect(…)、Using union(…) 和 Using sort_union(…)

如果执行计划的Extra列出现了Using intersect(…)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称；如果出现了Using union(…)提示，说明准备使用Union索引合并的方式执行查询；出现了Using sort_union(…)提示，说明准备使用Sort-Union索引合并的方式执行查询。比如这个查询的执行计划：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                           |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key3,idx_key1 | 303,303 | NULL |    1 |   100.00 | Using intersect(idx_key3,idx_key1); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+-------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7

其中Extra列就显示了Using intersect(idx_key3,idx_key1)，表明MySQL即将使用idx_key3和idx_key1这两个索引进行Intersect索引合并的方式执行查询。

---

小结

本文简单介绍了一下Mysql中Explain执行计划各个属性的含义，通常面试中比较喜欢问着方面问题，如果是个人学习，更推荐先系统学习一下数据库查询优化这块内容相关知识。

本文主要参考并摘阅至:

• 从根上理解Mysql

---