explain | SQL优化的这把绝世好剑，你真的会用吗？

原创凡尘dba 2023-03-31

604

对 MySQL 来讲，一条 SQL 语句从客户端发出，到数据库端返回结果，一般会经历几个阶段：词法解析、语法解析、语义解析、逻辑优化、物理优化、最终执行并返回结果。

在这几个阶段，我们 DBA 能参与的也就是两个阶段：逻辑优化以及少许物理优化。而我们 DBA 对 SQL 语句优化最常用的利器就是 explain 命令，然而，我们真的会使用这把绝世宝剑吗？

explain 用法详解

首先看下最常见的explain命令结果集：

结果集中每列的详细说明如下：

id (select唯一标识）

该列表示select的唯一标识，通常执行计划包含的信息 id 由一组数字组成，表示一个查询中各个子查询的执行顺序：

id相同执行顺序由上至下。
id不同，id值越大优先级越高，越先被执行。
id相同和不同都有时，先执行序号大的，先从下而上执行。遇到序号相同时，再从上而下执行。
id为null时表示一个结果集，不需要使用它查询，常出现在包含UNION等查询语句中。

select type (select类型）

该列表示select的类型。具体包含了如下11种类型：

但是常用的其实就是下面几个：

详细说明如下：

table (表名称）

显示这一行的数据是关于哪张表的。某些情况，不一定是业务表名，例如：当从衍生表中查数据时会显示 x 表示对应的执行计划id，或者partitions 表分区。即可以是以下值之一：

<unionM,N>：具有和id值的行的M并集N。
<derivedN>：用于与该行的派生表结果id的值N。派生表可能来自（例如）FROM子句中的子查询。
<subqueryN>：子查询的结果，其id值为N。

partitions (匹配的分区）

该列的值表示查询将从中匹配记录的分区。

type (连接类型）

这是重要的列，显示连接使用了何种类型。包含如下类型：

其中type的顺序如下：

system(无需磁盘IO) > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

详细说明如下：

我们需要重点掌握的是下面几种类型：

1、const

通过一次索引就能找到数据，一般用于主键或唯一索引作为条件的查询sql中。因为只有一行，这个值实际就是常数（const）。

2、eq_ref

在连接中，MySQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，常用于主键或唯一索引扫描。

此时，有人可能感到不解，const和eq_ref都是对主键或唯一索引的扫描，有什么区别？

答：const只索引一次，而eq_ref主键和主键匹配，由于表中有多条数据，一般情况下要索引多次，才能全部匹配上。

3、ref

常用于非主键和非唯一索引扫描。这个连接类型只有在查询使用了不是唯一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。

对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少——越少越好。

4、range

这个连接类型使用索引返回一个范围中的行，常用于范围查询，比如：between ... and 或 In 等操作。

5、index

全索引扫描（比ALL更好，因为索引一般小于表数据）。

6、ALL

全表扫描。这一般比较糟糕，应该尽量避免。

possible_keys (可能的索引选择）

显示可能应用在这张表中的索引。如果为空，即表示没有可能使用的索引。

PS：possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key (实际用到的索引）

实际使用的索引。如果为NULL，则表示没有使用索引。很少的情况下，MySQL会选择优化不足的索引。

这种情况下，可以在 select 语句中使用 force index（indexname）来强制使用一个索引或者用 ignore index（indexname）来强制MySQL忽略索引。

PS：force index和use index功能类似，都是告诉MySQL去使用某些索引。

force index和use index的区别是，如果使用force index，那么全表扫描就会被假定为需要很高代价，除非不能使用索引，否则不会考虑全表扫描。

而使用use index的话，如果mysql觉得全表扫描代价更低的话，仍然会使用全表扫描。

key_len (实际索引长度）

使用的索引的长度。在不损失精确性的情况下，长度越短越好。可以根据表定义大概计算出索引的最大可能长度，可用于复合索引的实际使用字段情况。

索引长度计算规则可见此文章：MySQL执行计划key_len的计算规则

PS：MySQL在执行计划中输出 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列。

（复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用）。

ref (与索引比较的列）

该列表示索引命中的列或者常量。

rows (预计要检查的行数）

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数。

如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

这有可能是个精确值，也可能是个估算值，计算方法有 index dive 和基于统计索引信息的估算。

filtered (按表条件过滤的行百分比）

对于单表查询来说：

如果是全表扫描，filtered 值代表满足 where 条件的行数占表总行数的百分比；
如果是使用索引来执行查询，filtered 值代表从索引上取得数据后，满足其他过滤条件的数据行数的占比。

mysql> explain select * from t1 where a<100 and b > 100;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |   99 |    33.33 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+

上面示例中，从 a 索引上取得 99 行数据，优化器估算认为这 99 行数据中有 33.33% 满足 b > 100 这个条件。对于单表查询来说，这个意义不大。

对于关联查询来说，驱动表的 rows*(filtered/100) 代表优化器认为的扇出，对于关联查询的成本估算有很大的影响。举例：

mysql> explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL              | 9688 |    10.00 | Using where |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s1.key1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+

从执行计划中可以看出来，查询优化器打算把 s1 当作驱动表，s2 当作被驱动表。

我们可以看到驱动表 s1 表的执行计划的 rows 列为 9688， filtered 列为 10.00，这意味着驱动表 s1 的扇出值就是 9688 × 10.00% = 968.8，这说明还要对被驱动表执行大约 968 次查询。

Extra (附加信息）

该列表示MySQL解析查询的额外信息。extra列返回的描述具体说明如下：

总结

1、explain命令结果集各列的说明总结如下，sql优化重点关注type，rows和extra这三个字段。

2、最后，如果通过explain命令查看执行计划依然无法优化sql，可以通过show profiling来定位一下到底是哪个环节出现的问题，具体操作可见文章：MySQL优化：profiling和Optimizer Trace

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

❤ 欢迎关注我的公众号【凡尘读书楼】，一起学习新知识！
————————————————————————————
公众号：凡尘读书楼
墨天轮：https://www.modb.pro/u/399450
知识星球：凡尘dba人生有限公司
————————————————————————————

墨力计划墨力原创作者计划 mysql explain

最后修改时间：2024-07-07 19:13:30

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者