MySQL的EXPLAIN执行计划详解

概述

EXPLAIN 提供有关 MySQL 如何执行 SQL 语句的信息，也称执行计划。

EXPLAIN 可以查看 SELECT，DELETE，INSERT，REPLACE 和 UPDATE 语句的执行计划。

EXPLAIN 输出的每一行都是对应于 SELECT 语句中的一个表的信息。行信息对应的表的顺序也是 MySQL 在 SQL 语句执行过程中的顺序。

MySQL 在处理 JOIN 时，都是使用的嵌套循环的方法，即 MySQL 从 JOIN 操作的第一张表读取一行记录，然后去第二查找匹配的行记录，
然后是第三张表，以此类推。 处理完所有的表后，MySQL 输出所有被 select 的列字段并回溯 table list, 直到有张表，其中存在更多匹配的记录，
从该表中继续读取下一条记录，然后继续下一个表。

伪码：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

EXPLAIN输出列详解

mysql> desc select * from t1;
+---+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+------------------------------+
|id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra                        |
+---+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+------------------------------+
| 1 | PRIMARY     | NULL  |   NULL     | NULL | NULL          | NULL| NULL    | NULL| NULL |  NULL    |no matching row in const table|
+---+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+------------------------------+

<id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra>

id

SQL 语句中 select 的序列号。如果该行代表的是一个 union 的结果集，那么 id 有可能为NULL，例如当 table 列显示的是 <union M, N> 表明这是 id 为 M 和 N 的 select 的一个union。

mysql> create table t1(a int primary key, b int,  key b(b));
mysql> create table t2(a int primary key, b int,  key b(b));
mysql> explain select * from t1 where a = 1 union select * from t2 where a = 5;
mysql> insert into t1 values(1,2),(2,2);
mysql> insert into t2 values(1,2),(2,2),(5,2);
mysql> explain select * from t1 where a = 1 union select * from t2 where a = 5;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   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)

示例中第三行表示是一个 union 的结果集，table 列 <union 1,2> 表示是 id=1 和 id=2 的 union 结果集，所以 id=NULL，表明并未是一个 SELECT。

select_type

select_type 可能的值为：

select_type	Meaning	中文含义
SIMPLE	Simple SELECT (not using UNION or subqueries)	简单SELECT，不适用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，依赖于外层SQL
UNION RESULT	Result of a UNION.	UNION的结果集
SUBQUERY	First SELECT in subquery	子查询中的第一个SELECT
DEPENDENT SUBQUERY	First SELECT in subquery，dependent on outer query	子查询中的第一个，依赖于外层SQL
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 (see UNCACHEABLE SUBQUERY)

示例：

• SIMPLE
  最简单的查询，无 union 无子查询；

  mysql> explain select * from t1 where a = 2;
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  1 row in set, 1 warning (0.00 sec)
  

• PRIMARY
• UNION
• UNION RESULT
  union 类型的 SQL，最外层为 PRIMARY，UNION 中的第二个或之后的为 UNION，UNION RESULT 为 UNION 结果集。

  mysql> explain select * from t2 where a = 5 union select * from t1 where a in ( select a from t2 where a = 5);
  +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------------+
  | id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                          |
  +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------------+
  |  1 | PRIMARY      | t2         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL                           |
  |  2 | UNION        | NULL       | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | no matching row in const table |
  | NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary                |
  +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------------+
  3 rows in set, 1 warning (0.00 sec)
  

• SUBQUERY
  子查询中的第一个SELECT

  mysql> desc select * from t1 where a = ( select a from t2 where a = 1);
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  |  1 | PRIMARY     | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
  |  2 | SUBQUERY    | t2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  2 rows in set, 1 warning (0.01 sec)
  

• DEPENDENT UNION 和 DEPENDENT SUBQUERY
DEPENDENT UNION，UNION中第二个或之后的SELECT，依赖于外层SQL;
  DEPENDENT SUBQUERY，子查询中的第一个，依赖于外层SQL

  mysql> desc select * from t1 where a in ( select a from t2 where a = 1 union select a from t1 where a = 2);
  +----+--------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
  | id | select_type        | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
  +----+--------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
  |  1 | PRIMARY            | t1         | NULL       | index | NULL          | b       | 5       | NULL  |    2 |   100.00 | Using where; Using index |
  |  2 | DEPENDENT SUBQUERY | t2         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index              |
  |  3 | DEPENDENT UNION    | t1         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    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)
  

• DERIVED
  派生表，当 FROM 后面是个子查询结果集时，从而会生成一个派生表

  mysql> set session optimizer_switch='derived_merge=off';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> explain select * from ( select * from t1) t  where b = 2;
  +----+-------------+------------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
  | id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+------------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
  |  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 5       | const |    1 |   100.00 | NULL        |
  |  2 | DERIVED     | t1         | NULL       | index | NULL          | b           | 5       | NULL  |    2 |   100.00 | Using index |
  +----+-------------+------------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
  2 rows in set, 1 warning (0.00 sec)
  

  Note: MySQL 5.7 引入了 derived_merge，所以需要关闭才可以看到DERIVED执行计划，不然会被优化掉，实际运行时应打开；

未完待续