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优化的时候,尽可能使用最优的方式来提高性能。