你会看 MySQL 的执行计划（EXPLAIN）吗？
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 | | 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)
可以看出，analyze 的输出结果是基于 format = tree 的
上面执行计划中（format = json/tree）的执行成本（cost）都是估值，而 analyze 中的执行成本是真实值。actual time 代表对应 SQL 执行的真实时间，单位为毫秒。
最后
执行计划的结果中，我们最关心的是 type，它能够最直接的反映出 SQL 执行效率处在什么级别。然后再结合其他字段（例如 Extra）来做更细致的分析。还可以通过各种参数，来分解每个环节的执行情况。
今天的内容就到这里，有哪些想要了解的可以留言告诉我。
微信收款码
支付宝收款码