Explain执行计划

有了慢查询语句后，就要对语句进行分析。一条查询语句在经过MySQL查询优化器的各种基于成本的规则的优化后会生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式。比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂 EXPLAIN 的各个速出项都是干啥使的，从而可以有针对性地提升我们查询语句的性能。
通过使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过 EXPLAIN 我们可以知道表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询。

执行计划的语法

执行话的语法其实非常简单：在SQL查询前面加上 EXPLAIN 关键字就行。

explain select * from tableName;

重点就是 EXPLAIN 后面你要分析的SQL语句。
除了以 select 开头的查询语句，其余的 delete 、 insert 、replace 以及 update 语句前面都可以加上 explain 关键字， 用来查看这些语句的执行计划，不过我们这里只对 select 语句更感兴趣，所以暂时只会以 select 语句为例来描述 EXPLAIN 语法的用法。

执行计划详解

为了让大家先有一个感性的认识，我们把 EXPLAIN 语句输出的各个列的租用先大致罗列下

explain select * from order_exp;

• id: 在一个大的查询语句中每个 select * 关键字都对应一个唯一的ID
• select_type: select * 关键字对应的那个查询类型
• table 表名
• partitions: 匹配的分区信息
• type: 针对单表的访问方法
• possible_keys: 可能用到的索引
• key: 实际上使用的索引
• key_len: 实际使用到的索引长度
• ref: 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
• rows：预估需要读取的记录条数
• filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
• Extra：一些额外的信息

ID

我们知道我们写的查询语句一般都以 select 关键字开头，比较简单的查询语句里只有一个 select 关键字，稍微复杂一点的连接查询也只有一个 select 关键字，比如

select * from s1
inner join s2 on s1.id = s2.id
where s1.order_status = 0;

但是下边两种情况下在一条查询语句中出现多个 select 关键字

1. 查询中包含子查询的情况
   比如下面这个查询语句中就包含2个 select 关键字

select * from s1 where id in (select id from s2);

2. 查询中包含union语句的情况
   比如下面这个查询也包含2个select关键字

select * from s1 
union select * from s2;

查询语句中每出现一个 select 关键字，MySQL就会为它分配一个唯一的ID值。这个ID值就是 EXPLAIN 语句的第一列。

单 Select 关键字

下面的查询中只有一个 select 关键字，所以 EXPLAIN 的结果也就只有一条 ID 列为1的记录

explain select * from s1 where order_no = 'a';

连接查询
对于连接查询来说，一个 select 关键字后边的 from 子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但这些记录的 ID 值都是相同的，比如：

explain select * from s1 
join s2 on s1.id = s2.id
where s1.order_no = 'a';

可以看到，上述连接查询中参与连接的 s1 和 s2 表分别对应的一条记录，但是这两条记录对应的 ID 值都是1。这里需要大家记住的是，在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 ID 值是相同的。

包含子查询
对于包含子查询的查询语句来说，就可能涉及多个 select 关键字，所以在包含子查询的查询语句的执行计划中，每个 select 关键字都会对应一个唯一的 ID 值。

explain select * from s1 where id in (select id from s2) or order_no = 'a';

但这里大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行了重写，从而转换为连接查询。所以我们想知道查询优化器对某个包含子查询的语句是否进行了重写，将子查询转换为了连接查询。

包含UNION子句
对于包含UNION 子句的查询语句来说，每个 select 关键字对应一个 ID 值也是没错的，不过还是有点儿特别的东西，比如

explain 
select * from s1 union select * from s2;

这个语句的执行计划的第三条为啥这样？ UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重？MySQL使用的是内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把 ID 为1的和 ID 为2的查询结果集合并起来并去重，所以在内部创建了一个名为 <union1,2>的临时表（就是执行计划第三条记录的table列的名称），ID 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。

跟UNION对比起来，UNION All 就不需要为最终的结果集进行去重，所以也就不需要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中，就没有那个 ID 为 NULL 的记录。

explain 
select * from s1 union all select * from s2;

table

不论我们查询的语句有多复杂，里面包含了多少表，到最后也是需要对每个表进行单表访问的，MySQL规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。


由图可知，只涉及对s1表的单表查询，所以 EXPLAIN 输出中只有一条记录，其中的table列的值是s1，而连接查询的执行计划中有两条记录，这两条记录的table列分别只有s1和s2。

partitions
和分区表有关，一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。

type
我们前面说过执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：
出现较多的是:system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref。

• system
  当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是system。

explain select * from test_myisam;

当然，如果改成使用 innoDB存储引擎，试试看执行计划的 type 列的值是什么。

• const
  当我们根据主键或唯一二级索引列与常熟进行等值匹配时，对单表的访问方法就是const。因为只匹配一行数据，所以很快。

# ID为主键
explain
select * from s1 where id = 716;

B+树叶子节点的记录是按照索引列排序的。对于聚簇索引来说，它对应的B+树叶子节点的记录就是按照ID列排序的。B+树矮胖，所以这样根据主键定位一条记录的速度很快。类似的，我们根据唯一二级索引来定位一条记录的速度也很快。

select * from order_exp where insert_time = ''
and order_status = '' and expire_time = '';

这个查询的执行分两步，第一步先从u_idx_day_status 对应的B+树索引中根据索引列于常数的等值比较条件定位到一条二级索引记录，然后再根据该记录的 ID 值到聚簇索引中获取完整的记录。
MySQL把这种通过主键或唯一二级索引来定位一条记录的访问方法定义为：const,意思是常数级别的，代价是可以忽略不计的。
不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时有效，如果主键或唯一二级索引时有多个列构成的，组成索引的每一列都是于常数进行等值比较的，这个const 访问方法才有效。
对于唯一二级索引来说，查询该列为 NULL 值得情况比较特殊，因为唯一二级索引列并不限制 NULL 值得数量，所以上述语句可能访问到多条记录，也就是 is null 不可以使用const访问方法来执行。

• eq_ref
  在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该驱动表的访问才是eq_ref。

explain 
select * from s1 inner join s2 on s1.id = s2.id;

从执行计划结果可看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是 eq_ref ,表明在访问 s1 表的时候可通过主键的等值匹配来进行访问。

驱动表和被驱动表扩展：A 表和 B 表join连接查询，如果通过A表结果集作为循环基础，然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据，然后合并结果。那么我们称 A 表为驱动表，B 表为被动驱动表。通常，较小的表或具有有效索引的表更适合作为驱动表。

• ref
  当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能使ref。
  本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体。

explain
select * from s1 where order_no = 'a';

对于这个查询，我们当然可以选择全表扫面来逐一对比搜索条件是否满足要求，我们也可以先使用二级索引找到对应的记录的ID值，然后再回表到聚簇索引中查找完整的用户记录。

由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的。所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的方法称为 ref 。

对于普通二级索引来说，通过索引列进行等值比较厚可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种 ref 访问方法比 const 要差些，但再二级索引等值比较匹配的记录数较少时，其效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。

• range
  如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法，一般就是在你的 where 出现了 between、< 、> 、in 等查询。
  这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

explain 
select * from s1 where order_no in ('a','b','c');

explain
select * from s1 where order_no  > 'a‘ and order_no < 'b';

这种利用索引进行范围匹配的访问方法称之为 range。
此处说的使用索引进行范围匹配中的“索引”可以是聚簇索引，也可以是二级索引。

• index
  当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

explain
select insert_time from s1 where expire_time = '2021-03-22 18:36:47';

• all
  最熟悉的全表扫描，将遍历全表以找到匹配的行

explain
select * from s1;

possible_keys与key
在 explain 语句输出的执行计划中，possible_keys 列表表示某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，如果为 NULL，则没有使用索引，比如

explain 
select order_note from s1 where insert_time = '2021-03-22 18:36:47';

上述执行计划的possible keys 列的值表示该查询可能用到的索引有u_idx_day_status, idx_insert_time ，然后key列的值是u_idx_day_status，表示经过查询优化器计算使用不同索引的成本后，最后决定使用 u_idx_day_status来执行查询比较划算。

key_len
key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，计算方式时这样的：

• 对于使用固定长度类型的索引来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型时 varchar(100) ,使用的字符集是utf8，那么该列实际占用的最大存储空间就是 100 x 3 = 300个字节。
• 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。
• 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
  比如

explain
select * from s1 where id = 718;

mysql

由于ID列的类型是bigint，并不可以存储NULL值，所以在使用该列的索引时 key_len 大小就是8。
对于可变长度的索引来说，比如以下查询

explain
selecct * from s1 where order_no = 'a';

由于order_no列的类型时 varchar(50)，所以该列实际最多占用的存储空间就是 50 x 3 = 150个字节，又因为该列可变长度列，所以 key_len 需要加上2，因此最后 key_len 的值就是152。

MySQL在执行计划中输出的 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列（复合索引有最左侧前缀的特性，若复合索引能全部用上，则是复合索引字段的索引长度之和，这也可以用来判断复合索引是否部分使用，还是全部使用），而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。

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

explain 
select * from s1 where order_no > 'z';

explain 
select *  from s1 where order_no > 'a';

我们看到执行计划的 rows 列的值分别是1和10573，着意味着查询优化器在经过分析使用 idx_order_no 进行查询的成本之后，觉得满足 order_no > ’a‘ 这个条件的记录只有1条，觉得满足 order_no > ‘a’ 这个条件的记录有10573条。

filtered
查询优化器预测有多少条记录满足其余的搜索条件，什么意思呢？看具体的语句

explain 
select * from s1 where id > 5890 and order_note = 'a';

从执行计划的 key 列中可以看出来，该查询使用 primary 索引来执行查询，从 rows 列可以看出满足 id > 5890 的记录有5286条。执行计划的 filtered 列就代表查询优化器预测在这 5286 条记录中，有多少条记录满足其余的搜索条件，也就是 order_note = ‘a’ 这个条件的百分比。此处 filtered 列的值是 10.0，说明查询优化器预测在5286条记录中有10%的记录满足 order_note = ‘a’ 这条件。

对于单表查询来说，这个 filtered 列的值没什么意思，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值，比方说下面的查询

explain
select * from s1 
inner join s2 on s1.order_no = s2.order_no 
where s1.order_note > '你好，李焕英';

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

Extra
顾名思义，Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确地理解MySQL到底将如何执行给定的查询语句的。