【Mysql性能优化系列】查询执行计划EXPLAIN使用详解

柔光的暖阳◎ 2024-04-07 14:52 86阅读 0赞

#### 目录 ####

*  前言
 *  EXPLAIN简介
 *  EXPLAIN输出格式
 *   *  输出列说明
     *  连接类型type
     *  扩展 EXPLAIN 输出格式
 *  获取连接中的执行计划信息
 *  查询性能估算

## 前言 ##

> 人生病了需要医生通过各种医疗工具分析病情，那么SQL“**生病**了”该怎么办呢？本文将通过执行计划**EXPLAIN**了解SQL的“**身体状况**”。

**本文以MySQL5.7为例。**

## EXPLAIN简介 ##

> 当EXPLAIN与可解释语句一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。。

**EXPLAIN可以与 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句一起使用。**

## EXPLAIN输出格式 ##

> EXPLAINSELECT为语句中使用的每个表返回一行信息 。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。MySQL使用嵌套循环连接方法解析所有连接。

也可以使用MySQL Workbench 具有的 Visual Explain 功能，可提供 EXPLAIN输出的可视化表示.  
![在这里插入图片描述][b854747e38a94c918f8508cbc53d8e14.png]

### 输出列说明 ###

EXPLAIN SELECT * FROM TEST

![在这里插入图片描述][1530844162934f789a9bd558a82e82db.png]

<table>      
 <thead> 
  <tr> 
   <th>列属性</th> 
   <th>JSON 名称</th> 
   <th>意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th><a rel="nofollow"><code>id</code></a></th> 
   <td><code>select_id</code></td> 
   <td><code>SELECT</code>标识符_</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>select_type</code></a></th> 
   <td>无</td> 
   <td><code>SELECT</code>类型_</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>table</code></a></th> 
   <td><code>table_name</code></td> 
   <td>输出行的表</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>partitions</code></a></th> 
   <td><code>partitions</code></td> 
   <td>匹配的分区</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>type</code></a></th> 
   <td><code>access_type</code></td> 
   <td>联接类型</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>possible_keys</code></a></th> 
   <td><code>possible_keys</code></td> 
   <td>可供选择的索引</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>key</code></a></th> 
   <td><code>key</code></td> 
   <td>实际选择的索引</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>key_len</code></a></th> 
   <td><code>key_length</code></td> 
   <td>所选密钥的长度</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>ref</code></a></th> 
   <td><code>ref</code></td> 
   <td>与索引比较的列</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>rows</code></a></th> 
   <td><code>rows</code></td> 
   <td>估计要检查的行数</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>filtered</code></a></th> 
   <td><code>filtered</code></td> 
   <td>按表条件过滤的行百分比</td> 
  </tr> 
  <tr> 
   <th><a rel="nofollow"><code>Extra</code></a></th> 
   <td>无</td> 
   <td>附加信息</td> 
  </tr> 
 </tbody> 
</table>

**id（JSON 名称 select\_id：）**

> 代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样谁在前谁先执行。id为null则最后执行。

**select\_type（JSON 名称：无）**

SELECT的类型，可以是下表中显示的任何一种。

<table>      
 <thead> 
  <tr> 
   <th><code>select_type</code>类型</th> 
   <th>JSON 名称</th> 
   <th>意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th><code>SIMPLE</code></th> 
   <td>无</td> 
   <td>简单<a title="13.2.9 SELECT 语句" rel="nofollow"><code>SELECT</code></a>（不使用 <a title="13.2.9.3 UNION 子句" rel="nofollow"><code>UNION</code></a>或子查询）</td> 
  </tr> 
  <tr> 
   <th><code>PRIMARY</code></th> 
   <td>无</td> 
   <td>最外层<a title="13.2.9 SELECT 语句" rel="nofollow"><code>SELECT</code></a></td> 
  </tr> 
  <tr> 
   <th><a title="13.2.9.3 UNION 子句" rel="nofollow"><code>UNION</code></a></th> 
   <td>无</td> 
   <td>中的第二个或以后<a title="13.2.9 SELECT 语句" rel="nofollow"><code>SELECT</code></a>的语句 <a title="13.2.9.3 UNION 子句" rel="nofollow"><code>UNION</code></a></td> 
  </tr> 
  <tr> 
   <th><code>DEPENDENT UNION</code></th> 
   <td><code>dependent</code> (<code>true</code>)</td> 
   <td>中的第二个或后面<a title="13.2.9 SELECT 语句" rel="nofollow"><code>SELECT</code></a>的语句 <a title="13.2.9.3 UNION 子句" rel="nofollow"><code>UNION</code></a>，取决于外部查询</td> 
  </tr> 
  <tr> 
   <th><code>UNION RESULT</code></th> 
   <td><code>union_result</code></td> 
   <td>的结果<a title="13.2.9.3 UNION 子句" rel="nofollow"><code>UNION</code></a>。</td> 
  </tr> 
  <tr> 
   <th><a title="子查询优化器提示" rel="nofollow"><code>SUBQUERY</code></a></th> 
   <td>无</td> 
   <td><a title="13.2.9 SELECT 语句" rel="nofollow"><code></code></a>子查询语句 跟在select 关键字后面的select查询语句</td> 
  </tr> 
  <tr> 
   <th><code>DEPENDENT SUBQUERY</code></th> 
   <td><code>dependent</code> (<code>true</code>)</td> 
   <td>首先<a title="13.2.9 SELECT 语句" rel="nofollow"><code>SELECT</code></a>在子查询中，依赖于外部查询</td> 
  </tr> 
  <tr> 
   <th><code>DERIVED</code></th> 
   <td>无</td> 
   <td>派生表</td> 
  </tr> 
  <tr> 
   <th><code>MATERIALIZED</code></th> 
   <td><code>materialized_from_subquery</code></td> 
   <td>物化子查询</td> 
  </tr> 
  <tr> 
   <th><code>UNCACHEABLE SUBQUERY</code></th> 
   <td><code>cacheable</code> (<code>false</code>)</td> 
   <td>一个子查询，其结果无法缓存，必须为外部查询的每一行重新计算</td> 
  </tr> 
  <tr> 
   <th><code>UNCACHEABLE UNION</code></th> 
   <td><code>cacheable</code> (<code>false</code>)</td> 
   <td><a title="13.2.9.3 UNION 子句" rel="nofollow"><code>UNION</code></a> 属于不可缓存子查询 的第二个或以后的选择（请参阅 参考资料<code>UNCACHEABLE SUBQUERY</code>）</td> 
  </tr> 
 </tbody> 
</table>

**table（JSON 名称 table\_name：）**

输出行所引用的表的名称。也可以是以下值之一：

> <unionM,N>：该行是指具有 和 id值 的行的M并 集N。
> 
> < derivedN >：该行是指值为 的行的派生表id结果 N。例如，派生表可能来自 FROM子句中的子查询。
> 
> < subqueryN>：该行指的是id 值为的行的具体化子查询的结果N。

**partitions（JSON 名称 partitions：）**

> 查询将匹配记录的分区。该值适用NULL于非分区表。

**type（JSON 名称 access\_type：）**

> 连接类型。

**possible\_keys（JSON 名称 possible\_keys：）**

> 这个字段显示的是sql在查询时可能使用到的索引，但是不一定真的使用，只是一种可能。

如果此列是NULL（或在 JSON 格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查WHERE 子句来检查它是否引用了适合索引的某些列或列，从而提高查询的性能。如果是这样，请创建一个适当的索引并 EXPLAIN再次检查查询。

查看表有哪些索引

SHOW INDEX FROM tbl_name

**key（JSON 名称key：）**

> 该key列指示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用其中一个possible\_keys索引来查找行，则该索引被列为键值。

**要强制 MySQL 使用或忽略列中列出的索引 ，请在查询中possible\_keys使用 FORCE INDEX、USE INDEX或IGNORE INDEX。**

**key\_len（JSON 名称 key\_length：）**

> 用到的索引字段的长度，通过这个字段可以显示具体使用到了索引字段中的哪些列（主要针对联合索引）：计算公式如下

字符串  
char(n)：n字节长度

varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

数值类型  
tinyint：1字节

smallint：2字节  
    
         int：4字节  
    
         bigint：8字节

时间类型  
date：3字节

timestamp：4字节  
    
           datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL  
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

**ref（JSON 名称ref：）**

> 表示那些列或常量被用于查找索引列上的值

如果值为func，则使用的值是某个函数的结果。

**rows（JSON 名称 rows：）**

> 表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。

对于InnoDB表格，这个数字是一个估计值，可能并不总是准确的。

**filtered（JSON 名称 filtered：）**

该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有过滤行。从 100 开始减小的值表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示与下表连接的行数。例如，如果 rows是 1000 并且 filtered是 50.00 (50%)，则要与下表连接的行数是 1000 × 50% = 500。

**Extra（JSON 名称：无）**

> 展示一些额外信息。

### 连接类型type ###

输出列描述type了 EXPLAIN表是如何连接的。  
以下列表描述了连接类型，按从最佳到最差的顺序排列：

> system > const > eq\_ref > ref > range > index > all;

一般来说我们优化到range就可以了 最好到ref。

详细如下，按从最佳到最差的顺序排列：

**system**

> 该表只有一行（= 系统表）。这是连接类型const 的一个特例 。

**const**

> 该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以这一行中列的值可以被优化器的其余部分视为常量。const表非常快，因为它们只被读取一次。

**eq\_ref**

> 在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询，结果返回一条数据，则type的值为一般为eq\_ref。

**ref**

> 相比较eq\_ref,不使用主键索引或者唯一索引，使用的是普通索引或者唯一索引的部分前缀，索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行。

**fulltext**

> 连接是使用FULLTEXT 索引执行的。

**ref\_or\_null**

> 这种连接类型类似于 ref，但另外 MySQL 会额外搜索包含NULL值的行。这种连接类型优化最常用于解析子查询。

**index\_merge**

> 此连接类型表明使用了索引合并优化。在这种情况下，key输出行中的列包含所用索引的列表，并 key\_len包含所用索引的最长键部分的列表。

**unique\_subquery**

> 此类型替换 以下形式eq\_ref的某些 IN子查询：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique\_subquery只是一个索引查找功能，完全替代子查询以提高效率。

**index\_subquery**

> 此连接类型类似于 unique\_subquery. 它替换IN子查询，但它适用于以下形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

**range**

> 通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索。

SELECT * FROM tbl_name
      WHERE key_column = 10;
    
    SELECT * FROM tbl_name
      WHERE key_column BETWEEN 10 and 20;
    
    SELECT * FROM tbl_name
      WHERE key_column IN (10,20,30);
    
    SELECT * FROM tbl_name
      WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

**index**

> index连接类型与ALL相同 ，只是扫描了索引树。这有两种方式：

*  如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，该Extra列 显示Using index。
 *  使用从索引中读取以按索引顺序查找数据行来执行全表扫描。Extra不显示 Uses index。

当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。

**ALL**

> 这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描。

### 扩展 EXPLAIN 输出格式 ###

> 对于SELECT语句， EXPLAIN语句会产生额外的（“扩展”）信息，这些信息不是 EXPLAIN输出的一部分，但可以通过在.`SHOW WARNINGS` 后面发出语句来查看EXPLAIN。`SHOW WARNINGS`输出中的 Message值显示优化器如何限定 SELECT语句中的表名和列名，SELECT应用重写和优化规则后的样子，以及可能有关优化过程的其他注释。

扩展 EXPLAIN输出的示例：

mysql> EXPLAIN
           SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: t1
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 4
         filtered: 100.00
            Extra: Using index
    *************************** 2. row ***************************
               id: 2
      select_type: SUBQUERY
            table: t2
             type: index
    possible_keys: a
              key: a
          key_len: 5
              ref: NULL
             rows: 3
         filtered: 100.00
            Extra: Using index
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Note
       Code: 1003
    Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
             <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
             ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
             from `test`.`t2` where 1 having 1 ),
             <primary_index_lookup>(`test`.`t1`.`a` in
             <temporary table> on <auto_key>
             where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
             IN (SELECT t2.a FROM t2)` from `test`.`t1`
    1 row in set (0.00 sec)

SHOW WARNINGS扩展输出中的特殊标记：

**< auto\_key>**

> 为临时表自动生成的键。

**< cache>(expr)**

> 表达式（例如标量子查询）执行一次，结果值保存在内存中供以后使用。对于包含多个值的结果，可能会创建一个临时表，可能会看到它 < temporary> table>。

**< exists>(query fragment)**

> 将子查询谓词转换为 EXISTS谓词，并对子查询进行转换，以便它可以与 EXISTS谓词一起使用。

**<in\_optimizer>(query fragment)**

> 这是一个没有用户意义的内部优化器对象。

**<index\_lookup>(query fragment)**

> 使用索引查找来处理查询片段以查找符合条件的行。

**< if>(condition, expr1, expr2)**

> 如果条件为真，则计算为 expr1，否则 为expr2。

**<is\_not\_null\_test>(expr)**

> 验证表达式不计算为 的测试 NULL。

**< materialize>(query fragment)**

> 使用子查询实现。

**`materialized-subquery`.col\_name**

> col\_name对内部临时表中 列的引用具体 化以保存评估子查询的结果。

**<primary\_index\_lookup>(query fragment)**

> 使用主键查找来处理查询片段以查找符合条件的行。

**<ref\_null\_helper>(expr)**

> 这是一个没有用户意义的内部优化器对象。

*/ select\#N / select\_stmt*

> SELECT与非扩展EXPLAIN输出中id值为 的行相关联N。

**outer\_tables semi join (inner\_tables)**

> 半连接操作。 inner\_tables显示未拉出的表。

**< temporary table>**

> 这表示为缓存中间结果而创建的内部临时表。

当某些表属于const 或者system类型时，涉及这些表中的列的表达式由优化器提前求值，而不是显示语句的一部分。但是，使用 时FORMAT=JSON，某些 const表访问会显示为ref使用 const 值的访问。

## 获取连接中的执行计划信息 ##

获取在连接中执行的可解释语句的执行计划：

EXPLAIN [options] FOR CONNECTION connection_id;

> EXPLAIN FOR CONNECTION返回EXPLAIN当前用于在给定连接中执行查询的信息。

`connection_id`是从 `INFORMATION_SCHEMA` `PROCESSLIST`表或 SHOW `PROCESSLIST`语句中获得的连接标识符。如果有PROCESS权限，可以指定任何连接的标识符。否则，只能为自己的连接指定标识符。

如果命名连接没有执行语句，则结果为空。否则，EXPLAIN FOR CONNECTION 仅当在命名连接中执行的语句是可解释的时才适用。这包括 SELECT、 DELETE、 INSERT、 REPLACE和 UPDATE。（但是， EXPLAIN FOR CONNECTION不适用于准备好的语句，即使是那些类型的准备好的语句。）

如果命名连接正在执行一个可解释的语句，那么输出就是您 EXPLAIN在语句本身上使用所获得的结果。

如果命名连接正在执行无法解释的语句，则会发生错误。例如:

mysql> SELECT CONNECTION_ID();
    +-----------------+
    | CONNECTION_ID() |
    +-----------------+
    |             373 |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> EXPLAIN FOR CONNECTION 373;
    ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported
    only for SELECT/UPDATE/INSERT/DELETE/REPLACE

> Com\_explain\_otherstatus 变量表示执行的 语句EXPLAIN FOR CONNECTION数。

## 查询性能估算 ##

在大多数情况下，可以通过计算磁盘寻道次数来估计查询性能。对于小型表，通常可以在一次磁盘查找中找到一行（因为索引可能已缓存）。对于更大的表，可以使用 B-tree 索引估计，需要这么多次查找才能找到一行：

**. log(row\_count) / log(index\_block\_length / 3 \* 2 / (index\_length + data\_pointer\_length)) + 1**

在 **MySQL** 中，索引块通常为 `1,024` 字节，数据指针通常为 `4` 字节。对于一个 `500,000` 行的表，键值长度为 `3` 个字节（大小为 MEDIUMINT），公式表示 `log(500,000)/log(1024/3*2/(3+4)) + 1= 4seeks。`

该索引需要大约 `500,000 * 7 * 3/2 = 5.2MB` 的存储空间（假设典型的索引缓冲区填充率为 2/3），可能在内存中有很多索引，因此只需要一两次调用读取数据以查找行。

**对于写入，需要四个查找请求来查找放置新索引值的位置，需要两次查找来更新索引并写入行。**

![在这里插入图片描述][9f80c58679d34a179ca3e89e79f28a58.png]  
 点赞 收藏 关注

[b854747e38a94c918f8508cbc53d8e14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/4dd801c71f474f55b8ccc91174fdbf6f.png
[1530844162934f789a9bd558a82e82db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/502ab9690ae947cb972f339c4ee1fe4a.png
[9f80c58679d34a179ca3e89e79f28a58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/3ce8bd7102c04507a081ee120ace1f09.png