MySQL——EXPLAIN用法详解

「爱情、让人受尽委屈。」 2024-02-21 11:42 50阅读 0赞

EXPLAIN是MySQL官方提供的sql分析的工具之一，可以用于模拟优化器执行sql查询语句，从而知道MySQL是如何处理[sql语句][sql]。EXPLAIN主要用于分析查询语句或表结构的性能瓶颈。

以下是基于MySQL5.7.19版本进行分析的，不同版本之间略有差异。

## 1、EXPLAIN使用 ##

explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。

![d93288abfe2a4854a524aa0d1e97d0a7.png][]

## 2、EXPLAIN包含的信息 ##

<table style="width:690px;"> MySQL EXPLAIN包含字段 
 <thead> 
 <tr> 
 <th style="width:114px;">列名</th> 
 <th style="width:574px;">说明</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="width:114px;">id</td> 
 <td style="width:574px;">执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">select_type</td> 
 <td style="width:574px;">显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">table</td> 
 <td style="width:574px;">访问引用哪个表（引用某个查询，如“user”）</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">partitions</td> 
 <td style="width:574px;">匹配的分区</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">type</td> 
 <td style="width:574px;">数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">possible_keys</td> 
 <td style="width:574px;">揭示哪一些索引可能有利于高效的查找</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">key</td> 
 <td style="width:574px;">显示mysql决定采用哪个索引来优化查询</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">key_len</td> 
 <td style="width:574px;">显示mysql在索引里使用的字节数</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">ref</td> 
 <td style="width:574px;">显示了之前的表在key列记录的索引中查找值所用的列或常量</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">rows</td> 
 <td style="width:574px;">为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">filtered</td> 
 <td style="width:574px;">表示此查询条件所过滤的数据的百分比</td> 
 </tr> 
 <tr> 
 <td style="width:114px;">extra</td> 
 <td style="width:574px;">额外信息，如using index、filesort等</td> 
 </tr> 
 </tbody> 
</table>

### 2.1 id ###

id是用来顺序标识整个查询中SELELCT语句的，在嵌套查询中id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果。大致分为下面几种情况：

**（1）id相同，执行顺序由上至下**

EXPLAIN select * from user,school,class where user.school_id = school.school_id and user.class_id = class.class_id

![2de529ef734d4e4191929f31eb091344.png][]

三个id都为1，表示具有相同的优先级，执行顺序由上而下，具体执行顺序由优化器决定，这里执行顺序为school，user，class。

**（2）id不同，数字越大优先级越高**

EXPLAIN select * from user where school_id = (select school_id from school where school_id = (select school_id from class where class_id = 2))

![d6f249ba1a144799a2223f40196293d3.png][]如果sql中存在子查询，那么id的序号会递增，id越大越先被执行。如上述查询，执行顺序是class、school、user，也就是说，最里面的子查询最先执行，由里往外执行。

> 如果上述查询中的“=”换成“in”，则运行结果会发生变化，原因是每执行一次最外层子查询，里面的子查询都会被重复执行，这也就是sql优化中提到的尽量不要使用in关键字

使用in关键字示例：

EXPLAIN select * from user where school_id in (select school_id from school where school_id in (select school_id from class where class_id = 2))

![40746e87b99a43f89fef9fbf9364360c.png][]

### 2.2 select\_type ###

select\_type表示查询的类型

<table style="width:500pt;"> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:bottom;width:148px;">类型</td> 
 <td style="text-align:center;vertical-align:bottom;width:517px;">说明</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:148px;">simple</td> 
 <td style="vertical-align:bottom;width:517px;">简单子查询，不包含子查询和union</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:148px;">primary</td> 
 <td style="vertical-align:bottom;width:517px;">包含union或者子查询，最外层的部分标记为primary</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:148px;">subquery</td> 
 <td style="vertical-align:bottom;width:517px;">一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:148px;">derived</td> 
 <td style="vertical-align:bottom;width:517px;">派生表——该临时表是从子查询派生出来的，位于form中的子查询</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:148px;">union</td> 
 <td style="vertical-align:bottom;width:517px;">位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:148px;">union result</td> 
 <td style="vertical-align:bottom;width:517px;">用来从匿名临时表里检索结果的select被标记为union result</td> 
 </tr> 
 </tbody> 
</table>

EXPLAIN select * from user where school_id = (select school_id from school where school_id = (select school_id from class where class_id = 2))

![2ee3069d0b104258bee8a00835e70e10.png][]

### 2.3 table ###

table显示查询操作基于哪张表的。

EXPLAIN select user.* from user where school_id = (select school_id from school where school_id = 2)
    union 
    select user.* from user where class_id = (select class_id from class where class_id = 2)

![70264aeadd334e95968c48a6ac1eb220.png][]

table列并不一定是真实存在的表，比如上面出现<union1,3>，一般来说会出现下面的取值：

* <union a,b>：输出结果中编号为 a 的行与编号为 b 的行的结果集的并集。
 * < derived a>：输出结果中编号为 a 的行的结果集，derived 表示这是一个派生结果集，如 FROM 子句中的查询。
 * < subquery a>：输出结果中编号为 a 的行的结果集，subquery 表示这是一个物化子查询。

### 2.4 partitions ###

partitions列是查询时匹配到的分区信息，对于非分区表值为`NULL`，当查询的是分区表时，`partitions`显示分区表命中的分区情况。

### 2.5 type ###

type显示的是访问类型，是较为重要的一个指标，性能从最好到最坏依次是 system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。

<table> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:bottom;width:115px;">类型</td> 
 <td style="text-align:center;vertical-align:bottom;">说明</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">system</td> 
 <td style="vertical-align:bottom;">这是const连接类型的一种特例，表仅有一行满足条件。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">const</td> 
 <td style="vertical-align:bottom;">当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">eq_ref</td> 
 <td style="vertical-align:bottom;">最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">ref</td> 
 <td style="vertical-align:bottom;">一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或&lt;=&gt;操作符的带索引的列。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">fulltext</td> 
 <td style="vertical-align:bottom;">查询时使用 fulltext 索引。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">ref_or_null</td> 
 <td style="vertical-align:bottom;">对于某个字段既需要关联条件，也需要null 值的情况下。查询优化器会选择用ref_or_null 连接查询。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">index_merge</td> 
 <td style="vertical-align:bottom;">在查询过程中需要多个索引组合使用，通常出现在有or 关键字的sql 中。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">unique_subquery</td> 
 <td style="vertical-align:bottom;">该联接类型类似于index_subquery。子查询中的唯一索引。在某些in子查询里，用于替换eq_ref</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">index_subquery</td> 
 <td style="vertical-align:bottom;">利用索引来关联子查询，不再全表扫描。用于非唯一索引，子查询可以返回重复值。类似于unique_subquery，但用于非唯一索引</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">range</td> 
 <td style="vertical-align:bottom;">范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 &lt;&gt;、&gt;、&gt;=、&lt;、&lt;=、IS NULL、&lt;=&gt;、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">index</td> 
 <td style="vertical-align:bottom;">和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:115px;">all</td> 
 <td style="vertical-align:bottom;">全表扫描，性能最差。</td> 
 </tr> 
 </tbody> 
</table>

### 2.6 possible\_keys ###

查询时可能使用的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。注意是可能，实际查询时不一定会用到。

### 2.7 key ###

查询时实际使用的索引，没有使用索引则为NULL。**查询时若使用了覆盖索引，则该索引只出现在key字段中。**

### 2.8 key\_len ###

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。**在不损失精确性的情况下，长度越短越好**。

key\_len显示的值是索引字段可能的最大长度，并非实际使用长度，即key\_len是根据表定义计算得到，不是通过表内检索。

key\_len 字段能够帮你检查是否充分的利用上了索引。ken\_len 越长，说明索引使用的越充分。

注意：`key_len`只计算`where`条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到`key_len`中。

### 2.9 ref ###

显示索引的哪一列被使用了，常见的取值有：const， func，null，字段名。

*  当使用常量等值查询，显示`const`，
 *  当关联查询时，会显示相应关联表的`关联字段`
 *  如果查询条件使用了`表达式`、`函数`，或者条件列发生内部隐式转换，可能显示为`func`
 *  其他情况`null`

### 2.10 rows ###

rows 列表示 MySQL 认为它执行查询时可能需要读取的行数，一般情况下这个值越小越好！

### 2.11 filtered ###

`filtered` 是一个百分比的值，表示符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

在`MySQL.5.7`版本以前想要显示`filtered`需要使用`explain extended`命令。`MySQL.5.7`后，默认`explain`直接显示`partitions`和`filtered`的信息。

### 2.12 Extra ###

Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

<table> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:bottom;width:160px;">类型</td> 
 <td style="text-align:center;vertical-align:bottom;width:688px;">说明</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Using filesort</td> 
 <td style="vertical-align:bottom;width:688px;">MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Using temporary</td> 
 <td style="vertical-align:bottom;width:688px;">用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Not exists</td> 
 <td style="vertical-align:bottom;width:688px;">MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Using index</td> 
 <td style="vertical-align:bottom;width:688px;">说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Using index condition</td> 
 <td style="vertical-align:bottom;width:688px;">这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Using where</td> 
 <td style="vertical-align:bottom;width:688px;">使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">Using join buffer</td> 
 <td style="vertical-align:bottom;width:688px;">使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">impossible where</td> 
 <td style="vertical-align:bottom;width:688px;">where子句的值总是false，不能用来获取任何元组</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">select tables optimized away</td> 
 <td style="vertical-align:bottom;width:688px;">在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:160px;">distinct</td> 
 <td style="vertical-align:bottom;width:688px;">优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作</td> 
 </tr> 
 </tbody> 
</table>

[sql]: https://so.csdn.net/so/search?q=sql%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[d93288abfe2a4854a524aa0d1e97d0a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/d6bf69d129354a5da6282c43126db917.png
[2de529ef734d4e4191929f31eb091344.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/2d5624e89ed24a9a99550ccb69cd9e48.png
[d6f249ba1a144799a2223f40196293d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/b2e2e9218e974b1795d2dd092774ad3d.png
[40746e87b99a43f89fef9fbf9364360c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/4f6abc33b2f24ae4a6f7c8b220adf8f9.png
[2ee3069d0b104258bee8a00835e70e10.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/11349b18129449f481f6def4437be08e.png
[70264aeadd334e95968c48a6ac1eb220.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/21/eb994c1bee334480b2a5df6fa311aea2.png