Mysql中的执行计划怎么分析？

一、背景

在我们日常工作中，我们可能会遇到一些慢SQL语句或者要对一些SQL进行性能优化，那么就需要使用explain对SQL进行执行计划分析了。Mysql中的执行计划可以通过EXPLAIN或DESCRIBE关键字获取，当我们拿到执行计划后可以帮助我们分析这条sql执行的性能瓶颈在哪里。

例如，我有一张user表，我想分析一下查询的执行计划。

SELECT * FROM user WHERE age > 28;

可以使用EXPLAIN来获取这条sql语句的执行计划。

EXPLAIN SELECT * FROM user WHERE age > 28;

通过执行上面的EXPLAIN语句后，我们就能拿到这条sql的执行计划了。

+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table| partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user | NULL       | range| age           | age  | 5       | NULL |    50|    33.33 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+

接下来我们就可以对执行计划进行分析了。

二、执行计划分析

从上面的执行计划可以看到，一共有12个字段，我们先对这些字段依次介绍一下。

1. id 

执行计划中每个操作的唯一标识，对于执行计划中的每条sql，可能会有多个操作，每个操作都有一个唯一的id。

2. select_type

操作类型，一共包含一项几种类型：

• SIMPLE：表示这个查询是最简单的形式，不包含任何的子查询或联合查询。
• PRIMARY：表示查询中的最外层或最顶层SELECT，它内部可能会包含嵌套的子查询或其它复杂构造。
• SUBQUERY：表示这种类型的SELECT出现在了另外一个查询的from字句或where字句查询中，作为独立的查询被执行。
• DEPENDENT SUBQUERY：类似于SUBQUERY，这个子查询的执行依赖于外部查询的某一行，它会为外部查询的每一行执行一次。
• DERIVED：表示MySQL需要创建一张临时表来存储子查询的结果。
• UNCACHEABLE SUBQUERY：对于不能缓存结果的子查询，MySQL不能将它的值计算出来重复使用，而是在外部查询的时候每次都需要重新计算。
• UNION ：出现在UNION查询中的第二个或后续的查询语句。
• UNION RESULT：用于合并UNION查询的结果集，不是实际的查询操作。
• MATERIALIZED：Mysql8.0引入的新类型，表示子查询结果被物化为临时表，以便重复使用。

3. table

当前操作锁涉及的表。

4. partitions 

当前操作所涉及的分区。

5. type

表示MySQL在执行查询时所采用的检索方式，他是衡量查询性能的重要指标之一。以下时常用的类型和含义：

• system：系统表，数据量很少，往往不需要进行磁盘IO。
• const：表中仅有一行数据匹配，使用主键查询或唯一索引查询。
• eq_ref：使用主键或者唯一约束列，进行关联查询时使用。
• ref：非唯一索引扫描，只会扫描索引树中的一部分来查找匹配的行，使用非唯一索引进行查询时使用。
• range：范围扫描，使用索引进行范围查询，只会扫描索引树中的一个范围来查找匹配的行。
• index：全索引扫描，会遍历索引树来查找匹配的行。通常时不符合最左匹配的查询。
• all：全表扫描，当使用非索引字段查询时，将会遍历全表来找到匹配的行。

故以上类型执行效率由高到低：system > const > eq_ref > ref > range > index > all

6. possible_keys 

表示查询中可以使用的索引，不一定实际使用了这些索引。这个字段列出了可能用于这个查询的所有索引，也包括联合索引。

7. key

表示实际查询使用的索引。

8. key_len

表示索引的长度，索引的长度越短，查询时的效率越高。

9. ref

用来表示哪些列或常量被用来与key列中命名的索引进行比较。

10. rows

表示操作需要扫描的行数，也就是说需要扫描表中多少行才能得到结果。

11. filtered

表示本次操作过滤掉的行数占扫描行数的百分比。值越大，则查询结果越准确。

12. extra

这个字段经常会被忽略，其实也很重要。这个字段表示MySQL在执行查询时所作的一些附加操作。下面是一些常见的extra类型及其含义：

• Using where：表示查询的列未被索引覆盖，或where筛选条件非索引列，或者where筛选条件非索引的前导列。
• Using index：本次查询使用了索引覆盖，只需要扫描索引，无需回表。
• Using index condition：表示本次查询在索引上执行了部分条件过滤。
• Using where;Using index：查询列被索引覆盖，并且where条件中使用了索引列，但不是索引的前导列。或者where条件是索引前导列的一个范围。通常是未遵循最左匹配原则。
• Using filesort：表示MySQL将使用文件排序，而不是索引排序，通常发生在无法使用索引来进行排序。我们应当尽量避免这种情况。

三、总结

通过对执行计划的各个字段进行了分析和说明，我们在进行sql优化的时候，尽可能使用最优的方式来提高性能。