MySQL—EXPLAIN分析SQL的具体执行计划

ゞ浴缸里的玫瑰 2023-08-17 15:28 132阅读 0赞

**简述：**在MySQL中，我们可以通过**EXPLAIN**命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。但是很多时候都是用的时候才想起那个字段代表了什么？因此记录一个文档来看看每个Explain值的代表。

![20190926151344182.png][]

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

### 一、select\_type ###

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

<table style="margin-left:22.5pt;"> 
 <thead> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 类型 </td> 
 <td style="border-color:#c0c0c0;"> 说明 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> SIMPLE </td> 
 <td> 简单表，不使用表连接或子查询 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> PRIMARY </td> 
 <td> 主查询，即外层的查询 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> UNION </td> 
 <td> UNION中的第二个或者后面的查询语句 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> SUBQUERY </td> 
 <td> 子查询中的第一个 </td> 
 </tr> 
 </tbody> 
</table>

### 二、**table** ###

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

### 三、**type** ###

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

<table style="margin-left:22.5pt;"> 
 <thead> 
 <tr> 
 <td style="border-color:#c0c0c0;"> ALL </td> 
 <td style="border-color:#c0c0c0;"> 全表扫描 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> index </td> 
 <td> 索引全扫描 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> range </td> 
 <td> 索引范围扫描 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> ref </td> 
 <td> 非唯一索引扫描 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> eq_ref </td> 
 <td> 唯一索引扫描 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> const,system </td> 
 <td> 单表最多有一个匹配行 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> NULL </td> 
 <td> 不用扫描表或索引 </td> 
 </tr> 
 </tbody> 
</table>

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

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

EXPLAIN SELECT \* FROM customer WHERE active=0;

![20190926151839865.png][]

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

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

EXPLAIN SELECT store\_id FROM customer;

![20190926151915923.png][]

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

EXPLAIN SELECT \* FROM customer WHERE customer\_id>=10 AND customer\_id<=20;

![20190926151932649.png][]

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

EXPLAIN SELECT \* FROM customer WHERE create\_date>='2006-02-13' ;

![20190926151951407.png][]

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

store\_id字段存在普通索引（非唯一索引）

EXPLAIN SELECT \* FROM customer WHERE store\_id=10;

![20190926152005741.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70][]

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

EXPLAIN SELECT \* FROM customer customer INNER JOIN payment payment ON customer.customer\_id = payment.customer\_id;

![20190926152039325.png][]

*  **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;

![20190926152058866.png][]

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

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

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

EXPLAIN SELECT \* FROM customer WHERE customer\_id =10;

![20190926152113272.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 1][]

![20190926152144591.png][]

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

![20190926152158261.png][]

### 四、**possible\_keys** ###

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

![20190926152401452.png][]

### 五、key ###

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

![20190926152439124.png][]

### 六、**key\_len** ###

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

### 七、**ref** ###

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

### 八、**rows** ###

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

### 九、**filtered** ###

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

### 十、**Extra** ###

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

最主要的有一下三种：

<table style="margin-left:22.5pt;"> 
 <thead> 
 <tr> 
 <td style="border-color:#c0c0c0;"> Using Index </td> 
 <td style="border-color:#c0c0c0;"> 表示索引覆盖，不会回表查询 </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> Using Where </td> 
 <td> 表示进行了回表查询 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> Using Index Condition </td> 
 <td> 表示进行了ICP优化 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> Using Flesort </td> 
 <td> 表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果 </td> 
 </tr> 
 </tbody> 
</table>

### 十一、扩展：如果我们有多个索引，某个索引效率会更高，那么该怎么做： ###

**1：平常情况**

EXPLAIN  
   SELECT family\_id FROM family\_member   
       WHERE family\_id = 16943  ;

![20190926153309382.png][]

**2：我只用某个索引情况**

EXPLAIN  
    SELECT family\_id FROM family\_member   
          FORCE INDEX( family\_info\_id ) WHERE family\_id = 16943  ;

![20190926153507188.png][]

这样就只用某一个索引了。

### 第十二：扩展 **什么是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优化。

![20190926153645204.png][]

文献参考来自：

[MYSQL-通过EXPLAIN分析SQL的执行计划][MYSQL-_EXPLAIN_SQL]

[强制指定使用某一索引的例子][Link 1]

[20190926151344182.png]: /images/20230810/f1e7b6a0fbb54dd3a072756611441912.png
[20190926151839865.png]: /images/20230810/73463c9583834313b74e1ef889dec90b.png
[20190926151915923.png]: /images/20230810/23f56a3d691943c59ef16f3fc45eea88.png
[20190926151932649.png]: /images/20230810/f3ead467fd1d4f2ab4c1c5f13fa6d3d2.png
[20190926151951407.png]: /images/20230810/ffbb4086c45e4b3fbf392285d4c43b40.png
[20190926152005741.png]: /images/20230810/daaa32fe692b4e228186a55ff50e594d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70]: /images/20230810/761f0563676c4a69978c415ed5f0701b.png
[20190926152039325.png]: /images/20230810/7698a4457d9e4bbd918fc5e093dce279.png
[20190926152058866.png]: /images/20230810/afdc0b30b40f425997b23339f0e4ee5d.png
[20190926152113272.png]: /images/20230810/b394c0cbb0b64b3c89a5e84f37b86d16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 1]: /images/20230810/08f2e828dff94a4da98a476df5328b9a.png
[20190926152144591.png]: /images/20230810/5a2f4e5e213a467b85b32900b699d5f6.png
[20190926152158261.png]: /images/20230810/6cae496125dc4a34a7d85221892db596.png
[20190926152401452.png]: /images/20230810/7e99892997584efda2da07230d7d5e5c.png
[20190926152439124.png]: /images/20230810/69973ae52b084d7ba7fd2d1105d265ce.png
[20190926153309382.png]: /images/20230810/99d45d9bb9444a78ba63782b6462a831.png
[20190926153507188.png]: /images/20230810/3d116ce10fef413c84e6de20490e738a.png
[20190926153645204.png]: /images/20230810/f1080466695542c3a961124e5e3505e0.png
[MYSQL-_EXPLAIN_SQL]: https://www.cnblogs.com/songwenjie/p/9409852.html
[Link 1]: https://segmentfault.com/a/1190000007281696