MySQL执行计划explain

ゞ浴缸里的玫瑰 2024-04-07 13:24 102阅读 0赞

#### 文章目录 ####

*  MySQL explain介绍
 *  MySQL explain参数
 *   *  1. id
     *  2. select\_type
     *  3. table
     *  4. partitions
     *  5. type
     *  6. possible\_keys
     *  7. key
     *  8. key\_len
     *  9. ref
     *  10. rows
     *  11. filtered
     *  12. Extra
 *  MySQL 查询的执行流程

--------------------

## MySQL explain介绍 ##

MySQL提供了一个explain命令，可以用于对select语句的执行计划进行分析，并详细的输出分析结果。

通过explain命令，可以深入了解到MySQL的优化器，还可以获得很多被优化器考虑到的访问策略的细节以及运行sql语句时哪种策略预计会被优化器采用。

## MySQL explain参数 ##

![在这里插入图片描述][525c7515eaa046f49081ae0bedf269b3.png]

### 1. id ###

select的序列号列，表示查询中操作表的顺序。

*  id越大，优先级越高（先执行）
 *  id相同，执行顺序从上到下
 *  id列为null表示为结果集，不需要使用这个语句来查询

MySQL将select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

*  **简单子查询**
    
        EXPLAIN SELECT (SELECT id FROM timed_task LIMIT 1) FROM article;
    
    ![在这里插入图片描述][418466dbf94b4299a60348a2d23e7109.png]
 *  **派生表（from语句中的子查询）**
    
        EXPLAIN SELECT * FROM  (SELECT id FROM timed_task LIMIT 1) a;
    
    ![在这里插入图片描述][de61d088821248c28cabc1c2ad542362.png]
 *  **union查询**
    
        EXPLAIN SELECT * FROM timed_task UNION ALL SELECT * FROM timed_task;
    
    ![在这里插入图片描述][bbc9baa287ae4655a3f47e4c2a860ce3.png]

### 2. select\_type ###

查询类型。主要用于区别普通查询、联合查询（union、union all）、子查询等复杂查询。

select\_type取值范围：

*  **simple**：简单查询。查询不包含子查询和union
 *  **primary**：复杂查询中最外层的select（最后执行的select）
 *  **derived**：派生查询。包含在from 子句中的子查询（MySQL会将结果存放在一个临时表中，也称为派生表）
 *  **subquery**：包含在select中的子查询（非from子句中子查询）
 *  **dependent subquery**：相比于subquery，子查询的结果受到外层的影响的为dependent subquery
 *  **union**：在union中的第二个和随后的select
 *  **dependent union**：和union一样，出现在union或者union all中，但是这个查询要受到外部查询的影响
 *  **union result**：union连接的多表查询，第一个查询是primary，后面的是union, 结果集是union result

### 3. table ###

查询的表名。

*  如果查询使用了别名，那么这里显示的就是别名
 *  如果不涉及对数据表的操作，那么这里就是null
 *  如果格式为<derived N>，表示这是一个临时表。N就是执行计划的id，表示结果来自这个查询
 *  如果格式为<union n,m>，表示这是一个临时表。表示来自union查询id为n、m的结果集

### 4. partitions ###

查询将匹配记录的分区。 对于非分区表，该值为 NULL。

### 5. type ###

查询所使用的访问类型。表示MySQL如何查找表中的行。

type值的从优到差顺序为：system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL

**type的值分析**

*  **null**：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
    
    例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
    
        EXPLAIN SELECT MAX(id) FROM timed_task;
 *  **system**：表中只有一行数据或者是空表（貌似MySQL5.7版本后不会有该值了）。
 *  **const**：使用唯一索引或者主键，返回记录一定是一条的等值where条件时，通常type是const。
 *  **eq\_ref**：连接字段为主键或者唯一索引，此类型通常出现于多表的join查询，表示对于前表的每一个结果，都对应后表的唯一一条结果。并且查询的比较是=操作，查询效率比较高。
 *  **ref**：ref的三种情况：
    
    1.  非主键或者唯一键的等值查询
    2.  join连接字段是非主键或者唯一键
    3.  最左前缀索引匹配
 *  **ref\_or\_null**：和ref类似，增加了null值判断
 *  **range**：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
 *  **index**：辅助索引，减少回表次数,因为要查询的索引都在一颗索引树上。
 *  **index\_merge**：索引合并，表示查询使用了两个或者以上的索引数量，常见于and或者or查询匹配上了多个不同索引的字段
 *  **unique\_subquery**：子查询，返回唯一值
 *  **index\_subquery**：子查询，返回值可能有重复。
 *  **ALL**：全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
 *  **fulltext**：全文检索索引。

### 6. possible\_keys ###

可能使用的索引。

### 7. key ###

实际使用的索引。

*  可能出现 possible\_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
 *  如果没有使用索引，则该列是 NULL。
 *  当select\_type为index\_merge时，key列可能有多个索引，其它时候这里只会有一个。

### 8. key\_len ###

索引里使用的字节数。

当索引为组合索引时，通过这个值可以算出具体使用了索引中的哪些列。

例如：组合索引index\_column1\_column2，其中column1和column2都为bigint（8个字节）

*  如果key\_len=8，说明只是使用了索引中的column1。
 *  如果key\_len=16，说明只是使用了索引中的column1和column2。

**MySQL数据类型的字节长度**

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">字节长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">char(n)</td> 
   <td align="left">对于utf-8，如果是数字或者字母，字节长度为n；如果是汉字，字节长度为3n</td> 
  </tr> 
  <tr> 
   <td align="left">varchar(n)</td> 
   <td align="left">对于utf-8，如果是m个数字或者字母，字节长度为m+2；如果是m个汉字，字节长度为3m+2<br>（2个字节存字符串的长度）</td> 
  </tr> 
  <tr> 
   <td align="left">tinyint</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td align="left">4</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td align="left">8</td> 
  </tr> 
  <tr> 
   <td align="left">date</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">timestamp</td> 
   <td align="left">4</td> 
  </tr> 
  <tr> 
   <td align="left">datetime</td> 
   <td align="left">8</td> 
  </tr> 
 </tbody> 
</table>

`特殊：如果字段允许为 NULL，需要1个字节记录是否为 NULL。`

### 9. ref ###

关联的字段。如果是连接查询，则会显示关联的字段。

*  可以为**Null**
 *  如果是使用的常数等值查询，这里会显示**const**
 *  如果是连接查询，被驱动表的执行计划这里会显示驱动表的**关联字段**
 *  如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能会显示**func**

### 10. rows ###

执行计划估算的扫描行数，不一定是精确值。（innodb不是精确值，myisam是精确值，主要是因为innodb使用了mvcc）。

### 11. filtered ###

存储引擎返回的数据在server层过滤后，剩下的满足的记录数量的占比（百分比）。

### 12. Extra ###

不适合在其他列中显示但十分重要的额外信息。常见的值有：Using index、Using temporary、Using filesort、Using where、Using where、Using join buffer、impossible where、select tables optimized away、distinct

*  **Using index**  
    相应的select操作中使用了覆盖索引(covering index)，避免访问了表的数据行，效率不错。
    
     *  如果同时出现using where，表明索引被用来执行索引键值的查找
     *  如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
    
    **覆盖索引：**  
    查询列（包括查询条件）只用从索引中就能够取得，不必读取数据行。
    
    换句话说就是：查询列要被所建的索引覆盖，MySQL可以利用索引返回查询列，而不必根据索引再次读取数据文件。  
    **举例说明：**
    
     *  表user有一组合索引index\_name\_age（name、age两字段）
     *  查询SQL：select name from user where age=20
    
    该查询就使用了覆盖索引。
 *  **Using filesort**  
    MySQL对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
    
    排序的时候最好遵循所建索引的顺序与个数否则就可能会出现using filesort
 *  **Using temporary**  
    MySQL使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
    
    group by一定要遵循所建索引的顺序。
 *  **Using where**  
    表明使用了where过滤。
 *  **Using join buffer**  
    表明使用了连接缓存。
 *  **impossible where**  
    where子句的值总是false，不能用来获取任何元组。
 *  **select tables optimized away**  
    在没有GROUPBY子句的情况下，基于索引优化min/max操作，或者对于MyIsam存储引擎优化count(\*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
 *  **Distinct**  
    优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

## MySQL 查询的执行流程 ##

可参考文章：[MySQL查询的执行流程][MySQL]

[525c7515eaa046f49081ae0bedf269b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/a29d7ffdc2be48ca83f96e64a8466a3b.png
[418466dbf94b4299a60348a2d23e7109.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/77b5fa3fb87342939c4c0046b975642b.png
[de61d088821248c28cabc1c2ad542362.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/dd0091374a8c4272a5f447d2b90bc837.png
[bbc9baa287ae4655a3f47e4c2a860ce3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/26156c4c49a04cbbb3b171c9800eafec.png
[MySQL]: https://blog.csdn.net/JokerLJG/article/details/127086544