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

刘水镜

发布于 2022-07-29 13:25:17

4020

发布于 2022-07-29 13:25:17

文章被收录于专栏：做个开发者

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

用法简介

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

 explain select * from t;

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)

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

字段	JSON Name	说明
id	select_id	查询标识符
select_type	/	查询类型
table	table_name	查询记录所在表
partitions	partitions	查询匹配的分区（没有进行表分区，则为 NULL）
type	access_type	连接类型
possible_keys	possible_keys	可以选择的索引
key	key	实际上用到的索引
key_len	key_length	被用到索引的长度，比如联合索引中有几个被用到
ref	ref	与索引相比较的列
rows	rows	要扫描的行数（估算值）
filtered	filtered	按表条件过滤的行百分比
Extra	/	附加信息

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

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

字段详解

id

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

id 相同

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

+----+-------------+-------+------------+--------+---------------+--------+
| 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)

id 不同

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

 +----+-------------+-------+------------+-------+---------------+--------+
 | 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)

id 包含 NULL

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

 +----+--------------+------------+------------+-------+---------------+-----------+
 | 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)

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

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

select_type

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

查询类型	JSON Name	说明
SIMPLE	/	简单 SELECT（没有 UNION 或子查询）
PRIMARY	/	查询包含 UNION 或子查询，则最外层的查询被标识为 PRIMARY
UNION	/	UNION 中的第二个或更后面的 SELECT 语句
DEPENDENT UNION	dependent (true)	UNION 中的第二个或更后面的 SELECT 语句，依赖于外部查询
UNION RESULT	union_result	UNION 的结果
SUBQUERY	/	子查询中的第一个 SELECT 语句
DEPENDENT SUBQUERY	dependent (true)	子查询中的第一个 SELECT 语句，依赖于外部查询
DERIVED	/	派生表 SELECT
DEPENDENT DERIVED	dependent (true)	派生表依赖于另一个表
MATERIALIZED	materialized_from_subquery	将子查询的结果物化（生成临时表）
UNCACHEABLE SUBQUERY	cacheable (false)	结果无法缓存且必须为外部查询的每一行重新计算的子查询
UNCACHEABLE UNION	cacheable (false)	UNION 中的第二个或更后面的 SELECT 语句，属于不可缓存子查询（参考 UNCACHEABLE SUBQUERY）

UNION 或者子查询 MySQL 会自动产生临时表。派生表可以简单理解为具有别名的临时表。生成临时表的这个动作称为物化（水变成蒸汽叫汽化）临时表通常在内存里，当其 size 超过一定范围会被存入磁盘

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

type

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

取值	说明
system	表中只有一条记录，const 类型的特例
const	表中最多有一条匹配数据，用于主键或唯一索引的等值匹配
eq_ref	出现在多表查询中，前表结果中的每一条记录，在后表中有唯一的对应。同样是主键或唯一索引等值匹配
ref	普通索引的等值匹配（= 或 <=>）
fulltext	全文索引
ref_or_null	跟 ref 类似，增加了对 NULL 的判断
index_merge	合并索引（用到了两个及以上的索引）
unique_subquery	子查询用到主键或唯一索引
index_subquery	子查询用到普通索引
range	范围匹配（ = 、 < > 、 > 、 > = 、 < 、 < = 、 IS NULL、 < = > 、 BETWEEN、 LIKE 或 IN）
index	扫描索引树（在覆盖索引的情况下优于 ALL）
ALL	全表扫描

还有一种 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);

输出格式如下：

+----------------------------------------------------------------------------------+
| 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)

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

使用 json 方式：

 desc format = json select * from t1;

输出结构为一个 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)

简介表中的 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);

输出结果：

+-------------------------------------------------------------------------------------------------------+
| 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)