10 分钟教会你如何看懂 MySQL 执行计划

迷南。 2023-10-03 20:59 1阅读 0赞

### 1. Explain 作用 ###

explain 命令主要用于查看 SQL 语句的执行计划，该命令可以模拟优化器执行 SQL 查询语句，可以帮助我们编写和优化 SQL。那么 explain 具体可以提供哪些信息，帮助我们如何去优化 SQl 的呢？

1.  表的读取顺序
2.  数据读取操作的操作类型
3.  哪些索引可以使用
4.  哪些索引被实际使用
5.  表之间的引用
6.  每张表有多少行被优化器查询

### 2. Explain 如何使用 ###

使用方式： **explain + 待执行的sql**

![format_png][]

explain 会返回一个待执行 SQL 的执行计划列表，列表包含了 12 个字段，字段共同描述了 SQL 在执行计划中将会采取何种方式执行。以下列表详细描述了执行计划表的字段含义：

<table> 
 <thead> 
  <tr> 
   <th>字段名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>执行 select 语句查询的序列号，决定表的读取顺序</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>查询的类型，也就是数据读取操作的操作类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>查询的表名</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>表分区</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>访问类型</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可使用的索引。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用到。如果这个字段为 null 但是字段 key 不为 null，这种情况就是在查找时没有可以使用的二级索引树，但是二级索引中包含了需要查询的字段，于是就不再查找聚簇索引（聚簇索引比较大），转而扫描这个二级索引树（二级索引树比较小），并且此时一般访问类型 type 为 index，及扫描整棵索引树。</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际扫描使用的索引。如果为 null，则没有使用索引；查询中若使用了覆盖索引，则该索引仅出现在key列表中；</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>索引中使用的字节数。可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好；key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的；</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>显示索引的哪一列被使用了。如果可能的话，是一个常数，哪些列或常量别用于查找索引列上的值；</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数；</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>搜索条件过滤后剩余数据的百分比。</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>包含不适合在其它列中显示但十分重要的额外信息</td> 
  </tr> 
 </tbody> 
</table>

### 3. 关键字段分析 ###

#### （1）id ####

执行 select 语句查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序，它有三种情况：

<table> 
 <thead> 
  <tr> 
   <th>类型名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id相同</td> 
   <td>执行顺序由上至下</td> 
  </tr> 
  <tr> 
   <td>id不同</td> 
   <td>如果是子查询，id 的序号会递增，id 值越大优先级越高,越先被执行</td> 
  </tr> 
  <tr> 
   <td>id相同不同，同时存在</td> 
   <td>如果 id 相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行</td> 
  </tr> 
 </tbody> 
</table>

#### （2）select\_type ####

就是数据读取操作的操作类型，他一共有以下几种：

<table> 
 <thead> 
  <tr> 
   <th>类型名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>simple</td> 
   <td>简单的 select 查询，查询中不包含子查询或者 union；</td> 
  </tr> 
  <tr> 
   <td>primary</td> 
   <td>查询中若包含任何复杂的子查询，最外层查询则被标记；</td> 
  </tr> 
  <tr> 
   <td>subquery</td> 
   <td>在 select 或者 where 列表中包含了子查询；</td> 
  </tr> 
  <tr> 
   <td>dependent subquery</td> 
   <td>子查询中的第一个 SELECT， 取决于外面的查询。 即子查询依赖于外层查询的结果。</td> 
  </tr> 
  <tr> 
   <td>derived</td> 
   <td>在 from 列表中包含的子查询被标记为 DERIVED（衍生表），mysql 会递归执行这些子查询，把结果放临时表中；</td> 
  </tr> 
  <tr> 
   <td>union</td> 
   <td>若第二个 select 出现在 union 之后，则被标记为 union，若 union 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED；</td> 
  </tr> 
  <tr> 
   <td>union result</td> 
   <td>从 union 表（即 union 合并的结果集）中获取 select 查询的结果；</td> 
  </tr> 
  <tr> 
   <td>meterialized</td> 
   <td>物化表，子查询关联查询时，子查询结果存储在物化临时表，然后根据临时表中的数据去主表匹配。</td> 
  </tr> 
  <tr> 
   <td>dependent union</td> 
   <td>UNION 中的第二个或后面的查询语句，取决于外面的查询</td> 
  </tr> 
 </tbody> 
</table>

#### （3）table ####

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为 null，也可以是以下之一：

<table> 
 <thead> 
  <tr> 
   <th>类型名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&lt;derived<code>N</code>&gt;</td> 
   <td>表示这个是临时表，后边的N就是执行计划中的 id，表示结果来自于这个查询产生。</td> 
  </tr> 
  <tr> 
   <td>&lt;union<code>M</code>,<code>N</code>&gt;</td> 
   <td>与&lt;derived<code>N</code>&gt;类似，也是一个临时表，表示这个结果来自于 union 查询的 id 为 M,N 的结果集。</td> 
  </tr> 
  <tr> 
   <td>&lt;subquery<code>N</code>&gt;</td> 
   <td>该行是指与物化子查询该行的结果 id 的值 N。</td> 
  </tr> 
 </tbody> 
</table>

#### （4）partitions ####

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

#### （5）type ####

依次从好到差：

> `system`>`const`>`eq_ref`>`ref`>`ref_or_null`>`range`>`index`>`ALL`

除了`all`之外，其他的`type`都可以使用到索引，除了`index_merge`之外，其他的`type`只可以用到一个索引。

我们自己创建一系列表来实验下：

![format_png 1][]

![format_png 2][]

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for goods
    -- ----------------------------
    DROP TABLE IF EXISTS `goods`;
    CREATE TABLE `goods`  (
      `id` int(11) NOT NULL,
      `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of goods
    -- ----------------------------
    INSERT INTO `goods` VALUES (1, 'sn123456', '衣服');
    
    -- ----------------------------
    -- Table structure for sku
    -- ----------------------------
    DROP TABLE IF EXISTS `sku`;
    CREATE TABLE `sku`  (
      `id` int(11) NOT NULL,
      `goods_id` int(11) NOT NULL,
      `status` int(11) NOT NULL,
      `deleted` int(11) NOT NULL,
      `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `index_2`(`name`) USING BTREE,
      INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of sku
    -- ----------------------------
    INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色');
    
    SET FOREIGN_KEY_CHECKS = 1;
    复制代码

system

表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，这个也可忽略不计；

const

表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行记录，所以很快。 如果将主键置于 where 列表中，mysql 就能将该查询转换成一个常量；

EXPLAIN SELECT * FROM sku WHERE id=1;
    复制代码

![format_png 3][]

eq\_ref

唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描；此类型通常出现在多表的 join 等值查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，查询效率较高。

EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;
    复制代码

![format_png 4][]

ref

非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；

EXPLAIN SELECT * FROM sku WHERE goods_id=1;
    复制代码

![format_png 5][]

ref\_or\_null

二级索引等值比较同时限定 is null 。

EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;
    复制代码

![format_png 6][]

range

只检索给定范围的行，使用一个索引来选择行，key列显示使用哪个索引，一般就是在你的 where 语句中出现了 between、<、>、in 等的查询；这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一个点，结束于另一个点，不用扫描全部索引；

EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;
    复制代码

![format_png 7][]

index

index 和 all 区别为 index 类型只遍历索引树，这通常比 all 快，因为索引文件通常比数据文件小；也就是说虽然 all 和 index 都是读写表，但 index 是从索引中读取的，而 all 是从硬盘中读的；

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;
    复制代码

![format_png 8][]

all

也就是全表扫描；

EXPLAIN SELECT * FROM sku WHERE deleted=0;
    复制代码

![format_png 9][]

#### （6）possible\_keys ####

查询可能使用到的索引都会在这里列出来。

#### （7）key ####

查询真正使用到的索引，`select_type`为`index_merge`时，这里可能出现两个以上的索引，其他的`select_type`这里只会出现一个。

#### （8）key\_len ####

key\_len 表示该列计算查询中使用的索引的长度。例如：`SELECT * FROM table where age = 1 and name like 'xx'`，假设 age 是 int 类型且不可为 null；name 是 varchar(20) 类型且可以为 null，编码为 utf8。若以这两个字段为索引查询，那么 key\_len 的值为 `4 + 3 * 20 + 2 + 1 = 67`。具体计算规则如下表所示：

<table> 
 <thead> 
  <tr> 
   <th>值类型</th> 
   <th>值名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>字符串</td> 
   <td>CHAR(n)</td> 
   <td>n 字节长度</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>VARCHAR(n)</td> 
   <td>如果是 utf8 编码，则是 3 n + 2字节;；如果是 utf8mb4 编码，则是 4 n + 2 字节。</td> 
  </tr> 
  <tr> 
   <td>数值类型</td> 
   <td>TINYINT</td> 
   <td>1字节</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>SMALLINT</td> 
   <td>2字节</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>MEDIUMINT</td> 
   <td>3字节</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>INT</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>BIGINT</td> 
   <td>8字节</td> 
  </tr> 
  <tr> 
   <td>时间类型</td> 
   <td>DATE</td> 
   <td>3字节</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>TIMESTAMP</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>DATETIME</td> 
   <td>8字节</td> 
  </tr> 
  <tr> 
   <td>字段属性</td> 
   <td>NULL 属性 占用一个字节。如果一个字段是 NOT NULL 的， 则不占用。</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### （9）ref ####

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

#### （10）rows ####

这里是执行计划中估算的扫描行数，不是精确值。

#### （11）filtered ####

使用`explain extended`时会出现这个列，`5.7`之后的版本默认就有这个字段，不需要使用`explain extended`了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

#### （12）Extra ####

这个列可以显示的信息非常多，有几十种，常用的有：

1.  **distinct**：在`select`部分使用了`distinct`关键字
2.  **no tables used**：不带`from`字句的查询或者`From dual`查询。使用`not in()`形式子查询或`not exists()`运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
3.  **using filesort**：说明`mysql`会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。`mysql`中无法利用索引完成的排序操作称为“文件排序”。排序时无法使用到索引时，就会出现这个。常见于`order by`语句中，需要尽快优化
4.  **using index**：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
5.  **using join buffer（block nested loop），using join buffer（batched key accss）** ：`5.6.x`之后的版本优化关联查询的`BNL`，`BKA`特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
6.  **using sort\_union，using\_union，using intersect，using sort\_intersection：**

*  using intersect：表示使用`and`的各个索引的条件时，该信息表示是从处理结果获取交集
 *  using union：表示使用`or`连接各个使用索引的条件时，该信息表示从处理结果获取并集
 *  using sort\_union和using sort\_intersection：与前面两个对应的类似，只是他们是出现在用`and`和`or`查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。

1.  **using temporary**：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看`status`变量，`used_tmp_table`，`used_tmp_disk_table`才能看出来。常见于`order by`和分组查询`group by`。`group by`一定要遵循所建索引的顺序与个数。需要尽快优化
2.  **using where**：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在`server`层进行过滤。查询条件中分为限制条件和检查条件，`5.6`之前，存储引擎只能根据限制条件扫描数据并返回，然后`server`层根据检查条件进行过滤再返回真正符合查询的数据。`5.6.x`之后支持`ICP`特性(index condition pushdown，索引下推)，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。`extra`列显示**using index condition**
3.  **firstmatch(tb\_name)** ：`5.6.x`开始引入的优化子查询的新特性之一，常见于`where`字句含有`in()`类型的子查询。如果内表的数据量比较大，就可能出现这个
4.  **loosescan(m..n)** ：`5.6.x`之后引入的优化子查询的新特性之一，在`in()`类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

### 4. Explain 主要关注点 ###

总的来说，我们只需要关注结果中的几列：

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>type</td> 
   <td>本次查询表联接类型，从这里可以看到本次查询大概的效率</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>最终选择的索引，如果没有索引的话，本次查询效率通常很差</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>本次查询用于结果过滤的索引实际长度</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>预计需要扫描的记录数，预计需要扫描的记录数<strong>越小越好</strong></td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>额外附加信息，主要确认是否出现<code>Using filesort</code>、<code>Using temporary</code>这两种情况</td> 
  </tr> 
 </tbody> 
</table>

再来看下`Extra`列中需要注意出现的几种情况：

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，<strong>需要添加合适的索引</strong></td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行<code>GROUP BY</code>时，或者<code>ORDER BY</code>里的列不都在索引里，<strong>需要添加合适的索引</strong></td> 
  </tr> 
  <tr> 
   <td>Using index</td> 
   <td>表示<code>MySQL</code>使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。注意不要和<code>type</code>中的<code>index</code>类型混淆</td> 
  </tr> 
  <tr> 
   <td>Using where</td> 
   <td>通常是进行了全表/全索引扫描后再用<code>WHERE</code>子句完成结果过滤，<strong>需要添加合适的索引</strong></td> 
  </tr> 
  <tr> 
   <td>Impossible WHERE</td> 
   <td>对<code>Where</code>子句判断的结果总是false而不能选择任何数据，例如<code>where 1=0</code>，无需过多关注</td> 
  </tr> 
  <tr> 
   <td>Select tables optimized away</td> 
   <td>使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如<code>MIN()\MAX()</code>，这种也是比较好的结果之一</td> 
  </tr> 
 </tbody> 
</table>

[format_png]: https://img-blog.csdnimg.cn/img_convert/8b7978a9c00507a2b5b9b8849e2430ae.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/f9fa21c669e3036345b23b04ff5ff06f.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/67dfe42df4091b2ad74f7d309ba4f70b.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/6ed2d6f93e0e50e1fcbd1ef8fe15cd6d.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/a113258cb2754b4976802adc1b13c2bd.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/bbae6373156e0c433ccbdf5b50d7f79d.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/1e82931592a792dcc0c8adc8ababa3de.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/be1b70061f99c481f4141f07ceff30b7.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/b14fb1760e28ac723a5befc5c364a207.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/b95ccc33dc1004b8b364a1988213ea58.webp?x-oss-process=image/format,png