登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›mysql explain详解

mysql explain详解

各卧唯 2025-6-1 18:25:37
前言

一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的 执行计划 ，这个执行
计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行
查询等等。
正文

看一个执行计划:
1.png

这里有一堆参数。
2.png

这些都不重要，一个一个来看就完了。

  • table
EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表
的表名
如果是连接呢？
那么会怎么写呢？
3.png


  • id
这个id 很容易我们就想到这样，第一个是1，然后第二个是2，以此类推。
然后这里的id并非这样设计的，可以理解为groupid，一组的意思。
比如说:
  1. SELECT * FROM s1 INNER JOIN s2
  2. ON s1.key1 = s2.key1
  3. WHERE s1.common_field = 'a';
复制代码
这其实是一个查询，而不是子查询。
所以显示是这样的。
4.png

在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出
现在前边的表表示驱动表，出现在后边的表表示被驱动表。
那么哪些是子句呢?
对于包含子查询的查询语句来说，就可能涉及多个 SELECT 关键字，所以在包含子查询的查询语句的执行计划
中，每个 SELECT 关键字都会对应一个唯一的 id 值，比如这样：
  1. EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
复制代码
查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。
  1. EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field =
  2. 'a');
复制代码
5.png

可以看到，虽然我们的查询语句是一个子查询，但是执行计划中 s1 和 s2 表对应的记录的 id 值全部是 1 ，这就
表明了查询优化器将子查询转换为了连接查询。
对于包含 UNION 子句的查询语句来说，每个 SELECT 关键字对应一个 id 值也是没错的，不过还是有点儿特别的
东西，比方说下边这个查询：
6.png

这个语句的执行计划的第三条记录是个什么鬼？为毛 id 值是 NULL ，而且 table 列长的也怪怪的？大家别忘了
UNION 子句是干嘛用的，它会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重呢？ MySQL
使用的是内部的临时表。正如上边的查询计划中所示， UNION 子句是为了把 id 为 1 的查询和 id 为 2 的查询的
结果集合并起来并去重，所以在内部创建了一个名为  的临时表（就是执行计划第三条记录的 table
列的名称）， id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。
跟 UNION 对比起来， UNION ALL 就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录
合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中，就没
有那个 id 为 NULL 的记录，如下所示：
7.png


  • select_type
这个比较多，又是一个分支。
不要急一个一个来介绍。
  1. simple
复制代码
首先是simple这个东西，什么是simple呢？
就是简单呗。
比如：
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
这种不就是简单语句啊。
  1. primary
复制代码
接下来就是primary 初级的意思。
那就是上了一点难度呗。
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
8.png

从结果中可以看到，最左边的小查询 SELECT * FROM s1 对应的是执行计划中的第一条记录，它的
select type 值就是 PRIMARY 。
  1. union
复制代码
对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以
外，其余的小查询的 select_type 值就是 UNION ，可以对比上一个例子的效果，这就不多举例子了。
  1. UNION RESULT
复制代码
MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION
RESULT ，例子上边有，就不赘述了。
  1. SUBQUERY
复制代码
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询
优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查
询的 select_type 就是 SUBQUERY ，比如下边这个查询：
9.png

可以看到，外层查询的 select_type 就是 PRIMARY ，子查询的 select_type 就是 SUBQUERY 。需要大家注意
的是，由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍。
  1. DEPENDENT SUBQUERY
复制代码
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询
的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY ，比如下边这个查询：
  1. EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s
  2. 2.key2) OR key3 = 'a';
复制代码
10.png

select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。
所以呢，看的时候得看执行了多少次，而不是简单的累加。
  1. DEPENDENT UNION
复制代码
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a'
UNION SELECT key1 FROM s1 WHERE key1 = 'b');
11.png

这个SELECT key1 FROM s2 WHERE key1 = 'a' 是dependent query
那么下面的union 查询呢？ 就是dependent union.
  1. DERIVED
复制代码
对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED ，比方
说下边这个查询：
  1. EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS d
  2. erived_s1 where c > 1;
复制代码

从执行计划中可以看出， id 为 2 的记录就代表子查询的执行方式，它的 select_type 是 DERIVED ，说明该
子查询是以物化的方式执行的。 id 为 1 的记录代表外层查询，大家注意看它的 table 列显示的是
，表示该查询是针对将派生表物化之后的表进行查询的。
  1. materialized
复制代码
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对
应的 select_type 属性就是 MATERIALIZED ，比如下边这个查询：
13.png

执行计划的第三条记录的 id 值为 2 ，说明该条记录对应的是一个单表查询，从它的 select_type 值为
MATERIALIZED 可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的 id 值
都为 1 ，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的 table 列的值是
，说明该表其实就是 id 为 2 对应的子查询执行之后产生的物化表，然后将 s1 和该物化表进
行连接查询。

  • partitions
不要关心这个先，一般用不到

  • type
我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，其中的 type 列就表明了
这个访问方法是个啥，比方说下边这个查询：
14.png

再来会议一下。
  1. system
复制代码
我们基本是见不到的，如果我们使用的是inno db的话。
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的
访问方法就是 system 。
  1. const
复制代码
就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法
就是 const
  1. eq_ref
复制代码
在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者
唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）
15.png
  1. ref
复制代码
当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref ，最开
始举过例子了，就不重复举例了。
  1. fulltext
复制代码
全文索引
  1. ref_or_null
复制代码
当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是
ref_or_null ，比如说：
  1. EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
复制代码
大致就是如此。
  1. index_merge
复制代码
一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可
以使用 Intersection 、 Union 、 Sort-Union 这三种索引合并的方式来执行查询
16.png

从执行计划的 type 列的值是 index_merge 就可以看出， MySQL 打算使用索引合并的方式来执行对 s1 表的
查询。
  1. unique_subquery
复制代码
类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语
句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的
话，那么该子查询执行计划的 type 列的值就是 unique_subquery ，比如下边的这个查询语句：
mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.
key1) OR key3 = 'a';
17.png
  1. index_subquery
复制代码
index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引，比如这样：
18.png
  1. range
复制代码
使用索引获取某些 范围区间 的记录
  1. index
复制代码
当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index
  1. ALL
复制代码
最熟悉的全表扫描，就不多唠叨了
一般来说，这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了 All 这个访问方法外，其余的访问方
法都能用到索引，除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。

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

  • key_len
key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：
a. 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的
变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占
用的最大存储空间就是 100 × 3 = 300 个字节。
b. 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
c. 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
有的同学可能有疑问：你在前边唠叨 InnoDB 行格式的时候不是说，存储变长字段的实际长度不是可能占用1个字
节或者2个字节么？为什么现在不管三七二十一都用了 2 个字节？这里需要强调的一点是，执行计划的生成是在
MySQL server 层中的功能，并不是针对具体某个存储引擎的功能，设计 MySQL 的大叔在执行计划中输出
key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，而不是为了准确的说明针对某
个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。

  • ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、
unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的东东是个啥，比如
只是一个常数或者是某个列。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
19.png

可以看到 ref 列的值是 const ，表明在使用 idx_key1 索引执行查询时，与 key1 列作等值匹配的对象是一个常
数，当然有时候更复杂一点：
  1. EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
复制代码
可以看到对被驱动表 s2 的访问方法是 eq_ref ，而对应的 ref 列的值是 xiaohaizi.s1.id ，这说明在对被驱动
表进行访问时会用到 PRIMARY 索引，也就是聚簇索引与一个列进行等值匹配的条件，于 s2 表的 id 作等值匹配
的对象就是 xiaohaizi.s1.id 列（注意这里把数据库名也写出来了）。
20.png

我们看执行计划的第二条记录，可以看到对 s2 表采用 ref 访问方法执行查询，然后在查询计划的 ref 列里输出
的是 func ，说明与 s2 表的 key1 列进行等值匹配的对象是一个函数。

  • rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行
数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。比如下边这个查询：
  1. EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
复制代码
我们看到执行计划的 rows 列的值是 266 ，这意味着查询优化器在经过分析使用 idx_key1 进行查询的成本之
后，觉得满足 key1 > 'z' 这个条件的记录只有 266 条。

  • filtered
a. 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底
有多少条。
b. 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条
件外的其他搜索条件的记录有多少条。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
  1. 从执行计划的 key 列中可以看出来，该查询使用 idx_key1 索引来执行查询，从 rows 列可以看出满足 key1 >
  2. 'z' 的记录有 266 条。执行计划的 filtered 列就代表查询优化器预测在这 266 条记录中，有多少条记录满足其
  3. 余的搜索条件，也就是 common_field = 'a' 这个条件的百分比。此处 filtered 列的值是 10.00 ，说明查询优
  4. 化器预测在 266 条记录中有 10.00% 的记录满足 common_field = 'a' 这个条件。
复制代码
对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的
filtered 值，比方说下边这个查询：
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field =
'a';
21.png

从执行计划中可以看出来，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。我们可以看到驱动表 s1 表的
执行计划的 rows 列为 9688 ， filtered 列为 10.00 ，这意味着驱动表 s1 的扇出值就是 9688 × 10.00% =
968.8 ，这说明还要对被驱动表执行大约 968 次查询。
结

还得继续。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
各卧唯
2025-6-1 18:25:37

0

粉丝关注

18

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫