MySQL进阶之路--EXPLAIN执行计划详解

执行计划简介

执行计划是指一条SQL语句在经过MySQL查询优化器的优化会后，具体的执行方式。MySQL为我们提供了EXPLAIN语句，来获取执行计划的相关信息。需要注意的是，EXPLAIN语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
执行计划支持SELECT, DELETE, INSERT, REPLACE以及 UPDATE语句。对于SELECT语句，EXPLAIN会生成扩展信息，这些信息可以通过紧随EXPLAIN语句之后的SHOW WARNINGS语句显示。

执行计划的输出格式

列	json名称	含义
id	select_id	每个`SELECT`语句都会对应一个唯一的标识符id
select_type	None	`SELECT`关键字对应的查询类型
table	table_name	每行输出的表名
partitions	partitions	匹配的分区
type	access_type	表的访问方法
possible_keys	possible_keys	可能用到的索引
key	key	实际用到的索引
key_len	key_length	实际用到的索引长度
ref	ref	当使用索引等值查询时，与索引作比较的列或常量
rows	rows	预计要读取的行数
filtered	filtered	按表条件过滤后，留存的记录数的百分比（过滤后的行数/过滤前行数）
extra	None	附加信息

创建测试用表及并生成数据

创建测试用表
表结构如下所示，实际使用中会创建u1，u2两张表，两张表结构一致，除id列外，其它字段随机插入。

CREATE TABLE u1 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    card_no INT,
    school VARCHAR(100),
    city VARCHAR(100),
    district VARCHAR(100),
    street VARCHAR(100),
    hoppy VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_name (name),
    UNIQUE KEY idx_card_no (card_no),
    KEY idx_school (school),
    KEY idx_address(city, district, street)
) Engine=InnoDB CHARSET=utf8;

生成数据
先定义一个生成随机数的函数rand_string:

DELIMITER $$
DROP FUNCTION IF EXISTS rand_string$$
CREATE  FUNCTION `rand_string`(num INT) RETURNS varchar(255) CHARSET UTF8
BEGIN
    DECLARE origin_str char(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < num DO
        SET return_str = CONCAT(return_str, SUBSTRING(origin_str , FLOOR(1 + RAND()*52 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;

创建存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS gen_user_data$$
CREATE  PROCEDURE `gen_user_data`(num INT) 
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num DO
        INSERT INTO u1(id, name, card_no, school, city, district, street, hoppy) VALUES(i, rand_string(1+FLOOR(RAND()*100)), 10000000+i, rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)));
        INSERT INTO u2(id, name, card_no, school, city, district, street, hoppy) VALUES(i, rand_string(1+FLOOR(RAND()*100)), 10000000+i, rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)));
        SET i = i +1;
    END WHILE;
END $$
DELIMITER ;

调用存储过程，生成数据

CALL gen_user_data(10000);

执行计划输出各列详解

id

查询语句中每个SELECT关键字，都会有一个对应的id。
最简单的查询如下所示，由于只有一个SELECT关键字，所以只有id为1的记录。

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在多表连接的情况下，有可能出现id相同的情况。比如下面这种情况，虽然只有一个SELECT关键字，但是连接查询了两张表，由于每张表都有一行输出记录，所以一共有两行记录；但是由于是同一个SELECT查询出来的，所以id值相同，都是1。这种情况下，出现在上面的表叫驱动表，出现在下面的表叫被驱动表。下面的示例中u2是驱动表，u1是被驱动表。

mysql> EXPLAIN SELECT * FROM u1 inner join u2 WHERE u1.name = u2.name;
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | u2    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9899 |   100.00 | Using where |
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u2.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

如果一条查询语句中，出现多个SELECT关键字，则id会依次递增。
比如下面这条查询语句，由于存在子查询，整个查询一共包含两个SELECT关键字，则id从1增加到2。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (select name from u2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref        | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE       | u1          | NULL       | ALL    | idx_name      | NULL       | NULL    | NULL       | 9818 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 303     | zz.u1.name |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | u2          | NULL       | index  | idx_name      | idx_name   | 303     | NULL       | 9899 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

另外，需要注意的是，子查询有可能被优化为连接查询，如下面这个查询，虽然我们输入的是子查询，但从EXPLAIN的输出结果看，两行输出的id相同，均为1，可以看出，子查询被转化成了连接查询。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (SELECT name FROM u2 WHERE u1.school = u2.school);
+----+-------------+-------+------------+------+---------------------+------------+---------+--------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys       | key        | key_len | ref          | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------------+------------+---------+--------------+------+----------+-----------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | idx_name,idx_school | NULL       | NULL    | NULL         | 9818 |   100.00 | Using where                 |
|  1 | SIMPLE      | u2    | NULL       | ref  | idx_name,idx_school | idx_school | 303     | zz.u1.school |    1 |     4.55 | Using where; FirstMatch(u1) |
+----+-------------+-------+------------+------+---------------------+------------+---------+--------------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)

对于包含UNION子句的查询来说，除了会有多个SELECT关键字外，还会出现一种特殊情况，就像下面这条语句所展示的。

mysql> EXPLAIN SELECT * FROM u1 UNION SELECT * FROM u2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL            |
|  2 | UNION        | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

上面语句的输出中包含了id为NULL的行，且table列显示的是<union1,2>，extra列显示Using temporary。<union1,2>表明，MySQL将id为1和id为2的两个查询结果合并起来；Using temporary表明，MySQL为这个合并结果创建了一个临时表，为什么要创建临时表呢，主要是为了去重用的。我们知道，UNION查询的结果是要去重的。如果不需要去重的话，还要不要创建临时表呢，我们用UNION ALL语句来试一下：

mysql> EXPLAIN SELECT * FROM u1 UNION ALL SELECT * FROM u2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL  |
|  2 | UNION       | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

可见，如果不需要去重的话，就不会建立临时表了。

table

执行计划的输出结果中，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

<unionM,N>: 本行引用了id为M和N的行的UNION结果；
<derivedN>: 本行引用了id 为N的表所产生的的派生表结果。派生表有可能产生自FROM语句中的子查询。
<subqueryN>: 本行引用了id 为N的表所产生的的物化子查询结果。

关于 <unionM,N>和<subqueryN>的例子，我们在讲解id属性的时候已经展示了，下面展示下 <derivedN>的情况：

mysql> explain SELECT * FROM (SELECT * FROM u1 UNION ALL SELECT * FROM u2) as t;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19717 |   100.00 | NULL  |
|  2 | DERIVED     | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9818 |   100.00 | NULL  |
|  3 | UNION       | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9899 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

这个示例中，table列为<derived2>，说明使用到了id为2的派生表。

select_type

根据每个SELECT所起到的作用，对其进行分类，并为其设置了select_type属性，这个属性分为以下几种：

类型	英文原文	描述
SIMPLE	Simple `SELECT`(not using `UNION` or subqueries)	没有用到`UNION`或子查询的简单查询
PRIMARY	Outermost `SELECT`	最外层的`SELECT`
UNION	Second or later `SELECT` statement in a `UNION`	`UNION`中的第二个或更后面的`SELECT`
DEPENDENT UNION	Second or later `SELECT` statement in a `UNION`, dependent on outer query	依赖于外部查询的，`UNION`中的第二个或更后面`SELECT`
UNION RESULT	Result of a `UNION`	`UNION`结果
SUBQUERY	First `SELECT` in subquery	子查询中的第一个`SELECT`
DEPENDENT SUBQUERY	First `SELECT` in subquery, dependent on outer query	子查询中的第一个`SELECT`，依赖于外部查询
DERIVED	Derived table	派生表
MATERIALIZED	Materialized subquery	物化子查询
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query	无法缓存结果的子查询，针对外部查询的每一行必须重新进行求值
UNCACHEABLE UNION	The second or later select in a `UNION` that belongs to an uncacheable subquery	`UNION` 中属于不可缓存子查询的第二个或更靠后的`SELECT`

翻译的不好，大家凑合看哈，下面我们来详细讲解一下。

SIMPLE
如果查询中不包括UNION和子查询，那么这个SELECT的类型就是SIMPLE。

比如下面所示的单表查询：

mysql> EXPLAIN SELECT * FROM u1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

当然，连接查询也属于SIMPLE类型：

mysql> EXPLAIN SELECT * FROM u1 INNER JOIN u2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL                                  |
|  1 | SIMPLE      | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

PRIMARY
一条查询语句中，可能会包含多个SELECT关键字，最外层的SELECT，其类型就是PRIMARY。

mysql> EXPLAIN SELECT * FROM u1 UNION SELECT * FROM u2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL            |
|  2 | UNION        | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

从输出可以看到，整个查询由两个SELECT组成，最外层（最左侧）的SELECT语句为SELECT * FROM u1，它的select_type为PRIMARY。

UNION
UNION语句中的第二个或更后的SELECT关键字，其类型为UNION。
比如上面那个UNION查询，最外层的SELECT其类型为PRIMARY，第二个SELECT查询SELECT * FROM u2其类型就是UNION。
DEPENDENT UNION
与UNION类似，都是指UNION语句中的第二个或更后的SELECT关键字，不过此UNION查询依赖于外部查询。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (SELECT name FROM u1 UNION  SELECT name FROM u2);
+----+--------------------+------------+------------+------+---------------+----------+---------+------+------+----------+-----------------+
| id | select_type        | table      | partitions | type | possible_keys | key      | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+----------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | u1         | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | 9818 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | u1         | NULL       | ref  | idx_name      | idx_name | 303     | func |    1 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | u2         | NULL       | ref  | idx_name      | idx_name | 303     | func |    1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+----------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

UNION RESULT
MySQL选择使用临时表来完成UNION查询的去重工作时，其select_type就是UNION RESULT。上面的示例中就有，就不多说了。
SUBQUERY
如果子查询没有被转换成连接查询，且是不相关子查询，且查询优化器对该子查询做了物化处理，那么该子查询的第一个SELECT语句，其select_type就是SUBQUERY。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (SELECT name FROM u2) or name = "abc";
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | u1    | NULL       | ALL   | idx_name      | NULL     | NULL    | NULL | 9818 |   100.00 | Using where |
|  2 | SUBQUERY    | u2    | NULL       | index | idx_name      | idx_name | 303     | NULL | 9899 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

$\color{red}{Tips}$ ：子查询物化是指创建一张临时表来保存子查询结果，以加快查询速度和效率。MySQL在首次需要子查询结果时创建临时表，以后再次需要时，则直接从临时表读取，不需要再去重复执行子查询，提高了查询效率。物化时，MySQL尽可能先在内存中创建临时表，如果表很大，则会在磁盘上创建临时表。

DEPENDENT SUBQUERY
如果子查询没有被转换成连接查询，且是相关子查询，那么该子查询的第一个SELECT关键字代表的查询语句，其select_type就是DEPENDENT SUBQUERY。
需要注意的是，对于DEPENDENT SUBQUERY，子查询针对其外部上下文中变量的每个不同值集重新求值一次，也就是说DEPENDENT SUBQUERY会被执行多次。

mysql> EXPLAIN SELECT * FROM u1 WHERE name IN (SELECT name FROM u2 where u2.name = u1.name) OR name="abc";
+----+--------------------+-------+------------+------+---------------+----------+---------+------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra                    |
+----+--------------------+-------+------------+------+---------------+----------+---------+------------+------+----------+--------------------------+
|  1 | PRIMARY            | u1    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9818 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | u2    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u1.name |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+------+---------------+----------+---------+------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.01 sec)

DERIVED
派生表一般存在于FROM子句中。
对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED，比方说下边这个查询：

mysql> EXPLAIN SELECT * FROM (SELECT name, school FROM u1 GROUP BY name, school) AS t;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL                            |
|  2 | DERIVED     | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

又或者下面这条语句：

mysql> EXPLAIN SELECT * FROM (SELECT * FROM u1 UNION ALL SELECT * FROM u2) as t;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19717 |   100.00 | NULL  |
|  2 | DERIVED     | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9818 |   100.00 | NULL  |
|  3 | UNION       | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9899 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

MATERIALIZED
当执行子查询语句时，查询优化器先将子查询物化后再与外层查询进行连接查询时，其select_type为MATERIALIZED：

mysql> EXPLAIN SELECT * FROM u1 WHERE name IN (SELECT name from u2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref        | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE       | u1          | NULL       | ALL    | idx_name      | NULL       | NULL    | NULL       | 9818 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 303     | zz.u1.name |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | u2          | NULL       | index  | idx_name      | idx_name   | 303     | NULL       | 9899 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

从执行计划可以看出，有两行id为1的记录，说明优化器将子查询转换成了连接查询，其id为2的行的select_type为MATERIALIZED，说明是先将子查询进行物化，然后再转换成了连接查询。

UNCACHEABLE SUBQUERY
略过。
UNCACHEABLE UNION
略过。

partitions

查询记录中匹配的分区。对于非分区表，该值为NULL。

type

EXPLAIN执行计划中的每一行都代表着对一张表的查询，而type列则表示该表的访问方法。访问方法按从好到坏，依次为system、const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL，下面分别来介绍一下。

system
如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是system。

先看看存储引擎为InnoDB的表访问方法：

mysql> CREATE TABLE single_row (id INT NOT NULL) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO single_row VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> EXPLAIN SELECT * FROM single_row;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | single_row | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到，对于InnoDB 引擎，type类型为ALL，换成MyISAM 引擎看看：

mysql> ALTER TABLE single_row engine=MyISAM;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM single_row;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | single_row | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到，改成MyISAM 引擎后，type类型为system。

const
当使用主键或唯一索引与常量作等值比较时（如果主键或唯一索引是联合索引的话，则联合索引的每一部分都必须是和常量做等值比较），就会使用 const，例如：

mysql> EXPLAIN SELECT * FROM u1 WHERE id=1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref
对于连接查询来说，如果被驱动表是使用主键或者非空唯一索引与常量进行等值比较（如果主键或非空唯一索引是联合索引，则联合索引的每一列都要做等值比较）的方法进行访问的，那么该驱动表访问方法为eq_ref。

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.id=u2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL     | 9818 |   100.00 | NULL  |
|  1 | SIMPLE      | u2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | zz.u1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

需要注意的是，如果唯一索引允许为NULL，那么访问方法就不是eq_ref，请看如下示例：

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.card_no=u2.card_no;
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | idx_card_no   | NULL        | NULL    | NULL          | 9818 |   100.00 | Using where |
|  1 | SIMPLE      | u2    | NULL       | ref  | idx_card_no   | idx_card_no | 5       | zz.u1.card_no |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

可以看到，在唯一索引允许为NULL时，访问方法为ref。

ref
当通过普通二级索引列与常量进行等值匹配来查询时，其访问方法有可能是ref，ref可用于使用=或<=>运算符进行比较的索引列。在以下示例中，MySQL可以使用ref访问方法来处理查询：

mysql> EXPLAIN SELECT * FROM u1 WHERE name="abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM u1 WHERE name <=> "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

fulltext
连接使用了fulltext索引。
ref_or_null
这种连接类型类似于ref，但是MySQL会额外搜索包含NULL值的行。在以下示例中，MySQL可以使用ref_or_null的访问方法来查询：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" OR name IS NULL;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | ref_or_null | idx_name      | idx_name | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge
查询优化器选择使用索引合并来优化查询。索引合并分为Intersection、Union、Sort-Union三种。在使用索引合并的情况下，输出行中的key列包含使用的索引列表，而key_len包含使用的索引的长度（按长度最长的情况计算）列表。

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" OR school = "abc";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |    2 |   100.00 | Using union(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

unique_subquery
类似于两表连接时，被驱动表的eq_ref方法；unique_subquery被用于IN子查询中，如果IN子查询中能够用主键索引做等值匹配进行查询的话，那么该子查询的访问方法为unique_subquery。

mysql> EXPLAIN SELECT * FROM u1 WHERE id IN (SELECT id FROM u2 WHERE u1.card_no = u2.card_no) OR card_no = 1000;
+----+--------------------+-------+------------+-----------------+---------------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys       | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+---------------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | u1    | NULL       | ALL             | idx_card_no         | NULL    | NULL    | NULL | 9818 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | u2    | NULL       | unique_subquery | PRIMARY,idx_card_no | PRIMARY | 4       | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+-----------------+---------------------+---------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

可以看到，对u2表的查询使用的是PRIMARY主键索引，其type列为unique_subquery。

index_subquery
类似于unique_subquery，只不过在子查询中用到的是非唯一索引：

mysql> EXPLAIN SELECT * FROM u1 WHERE school IN (SELECT school FROM u2 WHERE u1.name = u2.name) OR hoppy = "abc";
+----+--------------------+-------+------------+----------------+---------------------+------------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type           | possible_keys       | key        | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+----------------+---------------------+------------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | u1    | NULL       | ALL            | NULL                | NULL       | NULL    | NULL | 9818 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | u2    | NULL       | index_subquery | idx_name,idx_school | idx_school | 303     | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+----------------+---------------------+------------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

range
当使用索引进行范围查询时，其访问类型为range。

mysql> EXPLAIN SELECT * FROM u1 WHERE id > 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 4909 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT * FROM u1 WHERE name LIKE "abc%";
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | range | idx_name      | idx_name | 303     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index
如果查询中用的是覆盖索引，索引中的数据就可满足查询需要，此时的访问方法为index。

mysql> EXPLAIN SELECT street FROM u1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | index | NULL          | idx_address | 909     | NULL | 9818 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ALL
当使用全表扫描的方式进行查询时，访问方法为ALL。通常，可以通过添加索引来避免ALL访问方法。

mysql> EXPLAIN SELECT * FROM u1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys

possible_keys列表示MySQL执行查询时可能用到的索引。如果这一列为NULL，则没有使用到索引；这种情况下，需要检查WHERE语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" AND school = "abc";
+----+-------------+-------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name,idx_school | idx_name | 303     | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到，possible_keys列显示了两种可用的索引idx_name和idx_school，从key列可以看到，实际使用的是idx_name索引。

需要注意的一点是，当type为index时，会出现possible_keys为NULL而key列为实际使用到的索引的情况，如下所示：

mysql> EXPLAIN SELECT city FROM u1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | index | NULL          | idx_address | 909     | NULL | 9818 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

key
key列表示MySQL实际使用到的索引。
当使用覆盖索引时，key有可能会命中possible_keys中不存在的索引。
key_len
key_len列表示MySQL实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。
索引长度由三个部分组成：
1、索引本身字节数
对于固定长度类型的索引列来说，就是它实际占用的存储空间的长度，比如说int类型占用4个字节；对于可变长度类型来说，是它实际占用的存储空间的最大长度，比如说varchar(100)类型，如果编码类型为utf8，那么该长度为100*3=300个字节，如果编码类型为utf8mb4，那么该长度为100*4=400个字节
2、如果该索引列允许为NULL值，则key_len再加1个字节。
3、如果是变长字段，则key_len再加2个字节。

示例如下：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

由于name列为varchar(100)类型，编码格式为utf8并且允许为NULL，则计算公式如下：

100 * 3 + 1 + 2 = 303

再来看一下固定长度索引的示例：

mysql> EXPLAIN SELECT * FROM u1 WHERE id = 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

上例中使用到了主键索引，int类型且不允许为NULL，索引key_len列显示为4。

ref
ref列显示的是，通过索引列做等值比较查询时（也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时），哪些列或常量被用来与key列中的索引做比较。
示例如下：

mysql> EXPLAIN SELECT * FROM u1 WHERE id = 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE  u1.name = u2.name;
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | u2    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9899 |   100.00 | Using where |
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u2.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

如果显示为func，则表示使用到的值是某个函数的结果。

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE  u1.name = SUBSTRING(u2.name, 1, 10);
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | u2    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | 9899 |   100.00 | NULL                  |
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | func |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

rows
rows列表示MySQL认为执行查询时所需要检查的行数。对于InnoDB表，这个值为估计值，并不完全准确。
filtered
filtered列表示被查询条件过滤后的留存百分比。最大值为100，意味着实际并没有过滤掉任何行。此值从100逐渐降低时，表示被过滤掉的行数越来越多。
rows列表示MySQL要检查的行数的估计值，rows × filtered表示过滤后，要和下张表进行join的行数。

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.name = u2.name AND u1.hoppy = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9818 |    10.00 | Using where |
|  1 | SIMPLE      | u2    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u1.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

从执行计划可以看出，u1为驱动表，u2为被驱动表。驱动表预计扫描9818行记录，有10%的记录会被保留下来，也就是说驱动表大概有9818 * 10% = 981.8条记录被用来u2表做连接，也就是说被驱动表将会被执行约982次。

Extra

这列包含了MySQL解析查询的额外信息，通过这些信息，可以更准确的理解MySQL到底是如何执行查询的。
Extra列信息比较多，我们挑一些常用的来讲解一下。

Using index
在使用了覆盖索引时，Extra列会显示Using index信息。
如下所示：

mysql> EXPLAIN SELECT city, street FROM u1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | index | NULL          | idx_address | 909     | NULL | 9818 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using index condition
索引条件下推的详细内容我在单独的一篇文章里介绍过了，不熟悉的小伙伴可以看这里：一文读懂什么是MySQL索引下推（ICP）

如果查询优化器选择使用索引条件下推这个特性，在Extra列中将会显示Using index condition，比如下面的查询：

mysql> EXPLAIN SELECT * FROM u1 WHERE city = "abc" AND district LIKE "%abc";
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_address   | idx_address | 303     | const |    1 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Using where
如果查询没有用到索引（也就是全表扫描），那么当查询语句的WHERE条件中有针对该表的列搜索时，Extra列会显示Using where。请看下面的示例：

mysql> EXPLAIN SELECT * FROM u1 WHERE hoppy = 'abc';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果查询使用到了索引，那么当WHERE条件中包含该索引之外的列时，Extra列会显示Using where。请看下面的示例：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" AND hoppy = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using join buffer (Block Nested Loop)
在连接查询执行过程中，如果被驱动表不能有效的利用索引加快查询速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是基于块的嵌套循环算法，比如下面这个查询：

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.hoppy = u2.hoppy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL                                               |
|  1 | SIMPLE      | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

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

比如这个查询的执行计划，使用到了Intersect索引合并：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "a" AND school = "b";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |    1 |   100.00 | Using intersect(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

再比如下面这个查询的执行计划，使用到了Union索引合并：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "a" OR school = "b";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |    8 |   100.00 | Using union(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

下面这个查询语句，使用到了Sort-Union索引合并:

mysql> EXPLAIN SELECT * FROM u1 WHERE name < "a" OR school < "b";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |  395 |   100.00 | Using sort_union(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Using filesort
有一些情况下对结果集中的记录进行排序时可以使用到索引，但是在很多情况下，无法使用到索引，只能在内存（数据较少）或磁盘（数据较大）中进行排序。这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示，比如下面这条语句：

mysql> EXPLAIN SELECT * FROM u1  ORDER BY hoppy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

$\color{red}{Tips}$ ：通常来讲，出现文件排序会使查询性能变差，应该尽量避免，尽量将文件排序提升为索引排序。

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

mysql> EXPLAIN SELECT  hoppy, count(*) as cnt  FROM u1  GROUP BY hoppy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到，上述执行计划的Extra列不仅仅包含Using temporary提示，还包含Using filesort提示，可是我们的查询语句中明明没有写ORDER BY子句呀？这是因为MySQL会在包含GROUP BY子句的查询中默认添加上ORDER BY子句。如果我们并不想为包含GROUP BY子句的查询进行排序，需要我们显式的写上ORDER BY NULL，就像这样：

mysql> EXPLAIN SELECT  hoppy, count(*) as cnt  FROM u1  GROUP BY hoppy ORDER BY NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

可见，Extra列里没有出现Using filesort提示了，这就节约了排序成本，提升了查询效率。

$\color{red}{Tips}$ ：与Using filesort类似，Extra列出现Using temporary也不是什么好事，应该尽量避免。

参考与感谢：

1、《MySQL 是怎样运行的：从根儿上理解 MySQL》
2、MySQL官方手册

最后编辑于：2021.03.22 21:58:17

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,189评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,577评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,857评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,703评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,705评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,620评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,995评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,656评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,898评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,639评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,720评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,395评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,982评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,953评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,195评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,907评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,472评论 2赞 342