MySQL 通过 EXPLAIN 分析SQL的执行计划

r囧r小猫 2023-02-21 06:43 78阅读 0赞

转载自：[https://www.cnblogs.com/songwenjie/p/9409852.html][https_www.cnblogs.com_songwenjie_p_9409852.html]

## 叙述 ##

在MySQL中，我们可以通过**EXPLAIN**命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

![mark][]

## 分析 ##

下面分别对**EXPLAIN**命令结果的每一列进行说明：

**select\_type**:表示SELECT的类型，常见的取值有：

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单表，不使用表连接或子查询</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>主查询，即外层的查询</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>UNION中的第二个或者后面的查询语句</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>子查询中的第一个</td> 
  </tr> 
 </tbody> 
</table>

**table**:输出结果集的表（表别名）

**type**:表示MySQL在表中找到所需行的方式，或者叫访问类型。常见访问类型如下，从上到下，性能由差到最好：

<table> 
 <thead> 
  <tr> 
   <th>ALL</th> 
   <th>全表扫描</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>index</strong></td> 
   <td><strong>索引全扫描</strong></td> 
  </tr> 
  <tr> 
   <td><strong>range</strong></td> 
   <td><strong>索引范围扫描</strong></td> 
  </tr> 
  <tr> 
   <td><strong>ref</strong></td> 
   <td><strong>非唯一索引扫描</strong></td> 
  </tr> 
  <tr> 
   <td><strong>eq_ref</strong></td> 
   <td><strong>唯一索引扫描</strong></td> 
  </tr> 
  <tr> 
   <td><strong>const,system</strong></td> 
   <td><strong>单表最多有一个匹配行</strong></td> 
  </tr> 
  <tr> 
   <td><strong>NULL</strong></td> 
   <td><strong>不用扫描表或索引</strong></td> 
  </tr> 
 </tbody> 
</table>

**type=ALL，全表扫描，MySQL遍历全表来找到匹配行**

一般是没有where条件或者where条件没有使用索引的查询语句

**`EXPLAIN SELECT * FROM customer WHERE active=0;`**

![mark][mark 1]

**type=index，索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表**

一般是查询的字段都有索引的查询语句

**`EXPLAIN SELECT store_id FROM customer;`**

![mark][mark 2]

**type=range，索引范围扫描，常用于<、<=、>、>=、between等操作**

**`EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;`**

![mark][mark 3]

注意这种情况下比较的字段是需要加索引的，如果没有索引，则MySQL会进行全表扫描，如下面这种情况，create\_date字段没有加索引：

**`EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;`**

![mark][mark 4]

**type=ref，使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行**

`store_id`字段存在普通索引（非唯一索引）

**`EXPLAIN SELECT * FROM customer WHERE store_id=10;`**

![mark][mark 5]

**ref**类型还经常会出现在join操作中：

**customer**、**payment**表关联查询，关联字段`customer.customer_id`（主键），`payment.customer_id`（非唯一索引）。表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表，然后再通过非唯一索引寻找其他关联表中的匹配行，以此达到表关联时扫描行数最少。

![mark][mark 6]

因为**customer**、**payment**两表中**customer**表的记录行数最少，所以**customer**表进行全表扫描，**payment**表通过非唯一索引寻找匹配行。

**`EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;`**

![mark][mark 7]

**type=eq\_ref，类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配**

**eq\_ref**一般出现在多表连接时使用primary key或者unique index作为关联条件。

**film、film\_text**表关联查询和上一条所说的基本一致，只不过关联条件由非唯一索引变成了主键。

**`EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;`**

![mark][mark 8]

**type=const/system，单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理**

**const/system**出现在根据主键primary key或者 唯一索引 unique index 进行的查询

根据主键primary key进行的查询：

**`EXPLAIN SELECT * FROM customer WHERE customer_id =10;`**

![mark][mark 9]

根据唯一索引unique index进行的查询：

**`EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';`**

![mark][mark 10]

![mark][mark 11]

**type=NULL，MySQL不用访问表或者索引，直接就能够得到结果**

![mark][mark 12]

**possible\_keys**: 表示查询可能使用的索引

**key**: 实际使用的索引

**key\_len**: 使用索引字段的长度

**ref**: 使用哪个列或常数与key一起从表中选择行。

**rows**: 扫描行的数量

**filtered**: 存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)

**Extra**: 执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

最主要的有一下三种：

<table> 
 <thead> 
  <tr> 
   <th>Using Index</th> 
   <th>表示索引覆盖，不会回表查询</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>Using Where</strong></td> 
   <td><strong>表示进行了回表查询</strong></td> 
  </tr> 
  <tr> 
   <td><strong>Using Index Condition</strong></td> 
   <td><strong>表示进行了ICP优化</strong></td> 
  </tr> 
  <tr> 
   <td><strong>Using Flesort</strong></td> 
   <td><strong>表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果</strong></td> 
  </tr> 
 </tbody> 
</table>

## 什么是ICP？ ##

MySQL5.6引入了**Index Condition Pushdown（ICP）**的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

**`EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;`**

在5.6版本之前：

优化器首先使用复合索引idx\_rental\_date过滤出符合条件`rental_date='2005-05-25'`的记录，然后根据复合索引idx\_rental\_date回表获取记录，最终根据条件`customer_id>=300 AND customer_id<=400`过滤出最后的查询结果（在服务层完成）。

在5.6版本之后：

MySQL使用了ICP来进一步优化查询，在检索的时候，把条件`customer_id>=300 AND customer_id<=400`也推到存储引擎层完成过滤，这样能够降低不必要的IO访问。Extra为`Using index condition`就表示使用了ICP优化。

![mark][mark 13]

## 参考 ##

《深入浅出MySQL》

[https_www.cnblogs.com_songwenjie_p_9409852.html]: https://www.cnblogs.com/songwenjie/p/9409852.html
[mark]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0ZBNGVLRkpnZmYucG5n?x-oss-process=image/format,png
[mark 1]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0xlZGg4NjBsaDAucG5n?x-oss-process=image/format,png
[mark 2]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0gxYzVJMTA5aEoucG5n?x-oss-process=image/format,png
[mark 3]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0FGamI5YWk4OGEucG5n?x-oss-process=image/format,png
[mark 4]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0VERGlGZWYwRWUucG5n?x-oss-process=image/format,png
[mark 5]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0hGbWg1NmJhZmUucG5n?x-oss-process=image/format,png
[mark 6]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0MwM0EwR21IYjgucG5n?x-oss-process=image/format,png
[mark 7]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4LzdlZ0JtQTU2QzIucG5n?x-oss-process=image/format,png
[mark 8]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4LzRIMDNMQmw0TGoucG5n?x-oss-process=image/format,png
[mark 9]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4LzZoOTVjOWFnOEcucG5n?x-oss-process=image/format,png
[mark 10]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L0o0a2xiMGNkOTcucG5n?x-oss-process=image/format,png
[mark 11]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI4L2g3STVmMEpKSmEucG5n?x-oss-process=image/format,png
[mark 12]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI5LzFGOWdtMmg5NTAucG5n?x-oss-process=image/format,png
[mark 13]: https://imgconvert.csdnimg.cn/aHR0cDovL3Nvbmd3ZW5qaWUudmlwL2Jsb2cvMTgwNzI5L0ZER0MzRjNFSjEucG5n?x-oss-process=image/format,png