你会看 MySQL 的执行计划（EXPLAIN）吗？-mysql执行计划

SQL 执行太慢怎么办?我们通常会使用 EXPLAIN 命令来查看 SQL 的执行计划，然后根据执行计划找出问题所在并进行优化。

用法简介

EXPLAIN 的用法很简单，只需要在你的 SQL 前面加上 EXPLAIN 即可。例如：

 explain select * from t;1.

PS：insert、update、delete 同样可以通过 explain 查看执行计划，不过通常我们更关心 select 的执行情况

你会看到如下输出：

 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 1 row in set, 1 warning (0.00 sec)1.
2.
3.
4.
5.
6.

执行计划结果字段说明如下表：

EXPLAIN 的用法非常简单，看一眼就会。但是要根据输出结果找到问题并解决，就没那么容易了。就好比操作拍 CT 的机器可能相对简单，但要从 CT 成像中看出问题并给出治疗方案就需要丰富的知识和大量的临床经验了。

因此，我们需要知道每个字段代表什么指标;什么样的取值是我们想要的，什么样是需要优化的;最后还要知道如何优化成我们想要的值。

字段详解

id

标识符。查询操作的序列号。通常都是正整数，但当有 UNION 操作时，该值可以为 NULL。

id 相同

explain select * from t1 where t1.id in (select t2.id from t2);1.

 +----+-------------+-------+------------+--------+---------------+--------+
 | id | select_type | table | partitions | type   | possible_keys | ...    |
 +----+-------------+-------+------------+--------+---------------+--------+
 |  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | ....   |
 |  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | ....   |
 +----+-------------+-------+------------+--------+---------------+--------+
 2 rows in set, 1 warning (0.00 sec)1.
2.
3.
4.
5.
6.
7.

2 rows in set, 1 warning (0.00 sec)

id 不同

 explain select * from t1 where t1.id = (select t2.id from t2);1.

 +----+-------------+-------+------------+-------+---------------+--------+
 | id | select_type | table | partitions | type  | possible_keys | ...    |
 +----+-------------+-------+------------+-------+---------------+--------+
 |  1 | PRIMARY     | NULL  | NULL       | NULL  | NULL          | ....   |
 |  2 | SUBQUERY    | t2    | NULL       | index | NULL          | ....   |
 +----+-------------+-------+------------+-------+---------------+--------+
 2 rows in set, 1 warning (0.00 sec)1.
2.
3.
4.
5.
6.
7.

id 包含 NULL

 explain select id from t1 union (select id from t2);1.

+----+--------------+------------+------------+-------+---------------+-----------+
 | id   | select_type  | table      | partitions | type  | possible_keys | ...     |
 +------+--------------+------------+------------+-------+---------------+---------+
 |  1   | PRIMARY      | t1         | NULL       | index | NULL          | ...     |
 |  2   | UNION        | t2         | NULL       | index | NULL          | ...     |
 | NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | ...     |
 +------+--------------+------------+------------+-------+---------------+---------+
 3 rows in set, 1 warning (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.

id 为 NULL 时，table 列值为 < unionM，n > 格式，表示该行为 id 为 m 和 n 联合的结果

id 顺序的规则：如果 id 相同，执行顺序由上到下;如果不同，执行顺序由大到小。

select_type

SELECT 类型，常见的取值如下表：

UNION 或者子查询 MySQL 会自动产生临时表。派生表可以简单理解为具有别名的临时表。生成临时表的这个动作称为物化(水变成蒸汽叫汽化)

临时表通常在内存里，当其 size 超过一定范围会被存入磁盘

 # 临时表
  select * from t1 join t2 on t1.id = t2.id where t1.id > 1;
 
 # 派生表，临时表取个别名
 select * from (select * from t1) t;1.
2.
3.
4.
5.

type

连接字段为主键或者唯一索引，此类型通常出现于多表的join查询，表示对于前表的每一个结果，都对应后表的唯一一条结果。并且查询的比较是=操作，查询效率比较高。

还有一种 NULL 的情况，比如 select min(id) from t1，但 MySQL 官方没有提及这种情况，所以我们不在此讨论

性能从优到劣依次为：

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

优化原则：最好做到 const，至少做到 ref，避免 ALL

ref

查询中用来和索引比较的类型，如：id = 1，值为 const;如果是联合查询或者子查询则为关联的字段;如果使用了函数，则为 func。

Extra

Extra 用来存放一些附加信息，通常用来配合 type 的输出来做 SQL 优化。

扩展

desc

desc 与 explain 作用相同，可以互相代替，后面的例子中均使用 desc 来查看执行计划。

format

explain/desc 还支持一些参数，format 顾名思义，是用来格式化输出结果的。它包括两种格式化方式：tree 和 json。

比如：

desc format = tree select * from t1 where t1.id in (select t2.id from t2 where t2.id > 1);1.

输出格式如下：

 +----------------------------------------------------------------------------------+
 | EXPLAIN                                                                          |
 +----------------------------------------------------------------------------------+
 | -> Nested loop inner join  (cost=0.70 rows=1)
     -> Filter: (t2.id > 1)  (cost=0.35 rows=1)
         -> Index scan on t2 using a2_uidx  (cost=0.35 rows=1)
     -> Single-row index lookup on t1 using PRIMARY (id=t2.id)  (cost=0.35 rows=1)
  |
 +----------------------------------------------------------------------------------+
 1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

执行计划结果以树形结构展示，可以清晰的看出语句之间的嵌套关系，还有基本的执行成本(cost)。

使用 json 方式：

desc format = json select * from t1;1.

输出结构为一个 JSON 结构：

 +---------------------------------------------------+
 | EXPLAIN                                           |
 +---------------------------------------------------+
 | {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "0.35"
     },
     "table": {
       "table_name": "t1",
       "access_type": "ALL",
       "rows_examined_per_scan": 1,
       "rows_produced_per_join": 1,
       "filtered": "100.00",
       "cost_info": {
         "read_cost": "0.25",
         "eval_cost": "0.10",
         "prefix_cost": "0.35",
         "data_read_per_join": "56"
       },
       "used_columns": [
         "id",
         "a1",
         "b1"
       ]
     }
   }
 } |
 +---------------------------------------------------+
 1 row in set, 1 warning (0.00 sec)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.

简介表中的 JSON Name 指的就是这里 JSON 结果的 key

json 格式会展示出更加详细的信息，可以看到执行成本划分的更加细致了，方便定位到慢 SQL 的问题具体出现在哪个环节。

analyze

除了 format 以外，explain/desc 还可以使用 analyze 参数：

 desc analyze select * from t1 where t1.id in (select t2.id from t2 where t2.id > 1);1.

输出结果：

 +-------------------------------------------------------------------------------------------------------+
 | EXPLAIN                                                                                               |
 +-------------------------------------------------------------------------------------------------------+
 | -> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.018..0.018 rows=0 loops=1)
     -> Filter: (t2.id > 1)  (cost=0.35 rows=1) (actual time=0.016..0.016 rows=0 loops=1)
         -> Index scan on t2 using a2_uidx  (cost=0.35 rows=1) (actual time=0.015..0.015 rows=0 loops=1)
     -> Single-row index lookup on t1 using PRIMARY (id=t2.id)  (cost=0.35 rows=1) (never executed)
  |
 +-------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

可以看出，analyze 的输出结果是基于 format = tree 的

上面执行计划中(format = json/tree)的执行成本(cost)都是估值，而 analyze 中的执行成本是真实值。actual time 代表对应 SQL 执行的真实时间，单位为毫秒。

最后

执行计划的结果中，我们最关心的是 type，它能够最直接的反映出 SQL 执行效率处在什么级别。然后再结合其他字段(例如 Extra)来做更细致的分析。还可以通过各种参数，来分解每个环节的执行情况。