xyZGHio

樱花同载酒，终似少年游

0%

MySQL之InnoDB存储引擎：执行计划之Explain命令

发表于 更新于 分类于 阅读数： 评论数：
本文字数： 9.2k 阅读时长 ≈ 18 分钟

在MySQL中，查询优化器会对用户提交的SQL语句进行调整优化并最终生成执行计划。具体地，可通过Explain命令来进行查看

abstract.png

基本使用

关于Explain命令的使用方法，在前面已经多次出现了。直接在我们的SQL语句前添加explain，即可查看该SQL语句的执行计划。下面即是一个查看select语句执行计划的示例。事实上，Explain命令亦可用于delete、insert、update等其它类型的SQL语句上，只不过日常我们更关心查询语句的执行计划

figure 1.png

字段释义

上面，我们知道了如何查看执行计划。现在我们来了解下查询语句的执行计划中各字段的含义

id字段

对于查询语句而言，在执行计划中其会对每一个select关键字分配一个唯一的id值

  • 单表查询

例如下面是一个单表查询的select语句

1
explain select * from access_method4;

由于是对单表的查询，所以执行计划中只有一条记录，符合预期

figure 2.png

  • 连接查询

对于下面的连接查询而言，其涉及到对两张表的查询

1
2
3
explain
select * from access_method3 left join testbyaaron.access_method4
on access_method3.id = access_method4.id;

从执行计划的结果中可以看出，虽然有两条记录。但是由于只含一个select关键字，故两条记录均使用同一个id值

figure 3.jpeg

Note

对于连接查询的执行计划而言，前面的记录用于表示驱动表，后面的记录用于表示被驱动表

  • 子查询

对于一个SQL查询语句而言，其可能会有不止一个select关键字。典型地，有所谓的子查询

1
2
3
4
5
explain
select * from access_method3
where id = (
    select access_method4.id from access_method4
    where access_method3.name = access_method4.name );

从下图的执行计划中可以看出，由于存在两个select关键字，故两条记录的id值不一样

figure 4.png

值得一提的是对于子查询而言，查询优化器有时候会将其优化为连接查询(准确地说，是semi-join半连接)。这样我们在查看它的执行计划时即会发现各记录的id值是一样的

  • Union联合查询

在Union联合查询中，其也会存在多个select关键字。例如下面的SQL语句

1
2
3
4
explain
select * from access_method3 where id = 2
union all
select * from access_method3 where id = 3;

结果符合预期，两条记录的id值不一样

figure 5.png

现在我们再来看另外一个联合查询。与上面SQL不同的是，由于其没有使用all关键字，故该联合查询需要对重复的记录进行去重

1
2
3
4
explain
select * from access_method3 where id = 2
union
select * from access_method3 where id = 3;

现在，我们来看看该联合查询的执行计划。可以看出，其多了一条记录且id值为NULL。原因在于MySQL为了对两条select命令的结果进行去重合并，需要建立一个名为 <union1, 2>的临时表。所以这第三条记录的id值为NULL

figure 6.png

select_type字段

从上面的测试结果可以看到，执行计划中会生成各数据表相应的记录。而select_type字段即表示对数据表进行select操作的类型。下面介绍几种常见的类型

  • SIMPLE

简单select，即未使用UNION或子查询。比如对于单表查询、连接查询，其就是SIMPLE类型

  • PRIMARY

对于含联合查询或子查询的SQL语句而言，其会有多个select关键字。则SQL语句中第一个(即最左边)的select类型就是PRIMARY

  • UNION

对于联合查询而言，除了SQL语句中第一个select类型为PRIMARY，SQL语句中的其余select类型即为UNION

  • UNION RESULT

对于联合查询而言，如果需要对联合查询的结果集进行去重，则其对临时表的select类型即为UNION RESULT

  • SUBQUERY

对于子查询而言，如果查询优化器无法将其优化为semi-join半连接查询，且该子查询为不相关子查询。则该子查询中第一个select的类型即为SUBQUERY

  • DEPENDENT SUBQUERY

对于子查询而言，如果查询优化器无法将其优化为semi-join半连接查询，且该子查询为相关子查询。则该子查询中第一个select类型即为DEPENDENT SUBQUERY

  • DEPENDENT UNION

对于一个通过union连接、内部含有多个select的子查询而言，且子查询内部各select语句均与外层查询相关时，则该子查询除了第一个select类型为DEPENDENT SUBQUERY，其余select类型均为DEPENDENT UNION。SQL语句示例如下所示

1
2
3
4
5
6
7
8
9
explain
select * from access_method3
where id = (
    -- 该子查询内部通过union连接2个select关键字
    select access_method4.id from access_method4
    where access_method3.name = access_method4.name and access_method4.id>3
    union all
    select access_method4.id from access_method4
    where access_method3.name = access_method4.name and access_method4.id=1 );

执行计划如下所示，符合预期

figure 7.jpeg

  • DERIVED

对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select类型即为DERIVED。示例SQL如下所示

1
2
3
4
explain
select *  
from (select count(*) as count from access_method3) as t1
where count>996;

执行计划如下所示，符合预期

figure 8.jpeg

table字段

该字段即是执行查询时所作用的数据表的表名

type字段

关于type字段，其表示的就是进行查询时所使用的访问方法。之前我们已经介绍MySQL进行单表查询时一些常用的Access Method访问方法了。这里我们再对其他常见的访问方法进行补充说明

  • system

前面我们提到MySQL会对表中的记录数进行统计。在表中只有一条记录的情况下，此时查询的访问方法即为system。需要注意其有一个前提条件，即要求存储引擎对表中记录数的统计必须是准确值，而不能是估计值。换言之，该访问方法对InnoDB存储引擎不适用，适用于MyISAM、Memory等存储引擎。下面的SQL语句，即建立了一个基于MyISAM的数据表并向其中插入一条记录

1
2
3
4
5
6
7
8
9
10
-- 创建基于MyISAM的数据表
create table t1 (
    name varchar(255),
    age int,
    primary key (name)
) Engine=MyISAM;
-- 插入一条记录
insert into t1(name, age) values ('Aaron', 25);
-- 查看查询SQL的执行计划
explain  select * from t1;

结果如下，符合预期

figure 9.png

  • fulltext

使用全文索引

  • unique_subquery

有时候查询优化器会对in子查询进行优化进而转换为exists子查询，例如下面的SQL使用了in子查询，其中access_method3的主键为id字段

1
2
3
4
5
6
7
explain
select * from access_method4
where access_method4.name in (
    select access_method3.id from access_method3
    where access_method3.iphone_number=110
    )
or access_method4.type='staff';

现在我们将其转换为exists子查询，可以看到现在对于exists子查询而言，可以利用access_method3表的主键(聚簇索引)进行等值查询。这也是为啥查询优化器要进行如此优化的原因所在

1
2
3
4
5
6
7
explain
select * from access_method4
where exists(
    select 1 from access_method3
    where access_method3.iphone_number=110
    and access_method3.id = access_method4.name )
or access_method4.type='staff';

故当将查询优化器将in子查询优化为exists子查询时，如果子查询可以利用主键索引进行等值查询，则该子查询的访问方法即为unique_subquery。现在我们查看上文in子查询的执行计划

figure 10.png

  • index_subquery

其与unique_subquery类似，只不过其是在查询优化器将in子查询优化为exists子查询的条件下，子查询可以利用二级索引进行等值查询时的访问方法。例如下面的SQL语句，其中对access_method3表的name字段建立了一个二级索引

1
2
3
4
5
6
7
explain
select * from access_method4
where access_method4.name in (
    select access_method3.name from access_method3
    where access_method4.type = access_method3.country
    )
or access_method4.type='staff';

执行结果如下，符合预期

figure 11.png

possible_keys字段

故名思义，该字段只是列出该查询可以利用的索引。需要注意的是该字段所列出的索引越多，则查询优化器在计算、比较各查询方案成本时所需的时间也就越多

key字段

前面我们提了possible_keys字段只是给出在查询过程可以利用的索引，而实际真正地使用哪些索引进行查询则体现在key字段中

key_len字段

该字段意为当使用索引进行查询时，该索引的最大长度字节数。具体地：

  1. 对于固定长度类型的索引字段而言，则其占用的最大长度就是该固定值
  2. 对于指定字符集的变长类型的索引字段而言，例如对utf8字符集下的varchar(10)而言，则其所占用的最大长度为3*10=30个字节
  3. 对于变长类型的索引字段而言，其还需占用2个字节用于存储变长类型的实际长度
  4. 若该索引字段允许NULL值，则其key_len值比不允许NULL值时多1个字节

说了这么多，那这个字段到底有什么用呢？为此，我们先来建立一张表并插入一些数据。其中该数据表使用utf8字符集

1
2
3
4
5
6
7
8
create table user_info(
    id int auto_increment,
    name varchar(10) null,
    sex varchar(10) null,
    age int	null,
    primary key (id),
    index index1(name, sex, age)
);

好了，现在我们查看下面的SQL查询语句的执行计划

1
2
3
explain
select name from user_info
where name='aaron';

执行计划中的key_len值为33,为什么呢？其实也很好理解。首先对于上面的SQL语句而言，如果使用index1索引，最多也只会用到name索引字段，则此时该索引记录的最大长度即为10*3 + 2(变长类型) + 1(允许为NULL) = 33

figure 12.png

如果我们同时使用index1索引中的name、sex索引字段是什么效果呢

1
2
3
explain
select name from user_info
where name='aaron' and sex='man';

可以看到此时该key_len值变为66了。由于name字段与sex字段的类型在数据表的定义中完全一致，故此处就不再具体解释这个66是怎么计算出来的，可以参照上文对name字段的计算过程

figure 13.png

至此，相信大家应该知道key_len字段的用处了。通过该字段即可让用户知道，在通过索引查询时到底使用了联合索引的几个索引字段

ref字段

该字段表示索引字段进行等值查询过程中”值”的类型。例如对于下面的SQL查询语句而言

1
2
3
explain
select name from user_info
where name='aaron';

这里对索引index1的name索引字段进行了等值查询，其中值为’aaron’。即是一个常数。从执行计划中我们可以看到其ref字段为const，即常数

figure 14.png

此外该字段还可以是某个列的列名。例如对于下面的SQL语句而言，在对被驱动表user_info进行单表查询时，是以tagList表中记录的tag字段作为等值查询时的值

1
2
3
explain
select * from tagList left join user_info
on user_info.name=tagList.tag;

则从执行计划中我们可以看到，结果符合预期

figure 15.jpeg

此外该字段的值还可以是func，即等值匹配的类型是一个函数。SQL实例如下所示

1
2
3
explain
select * from tagList left join user_info
on user_info.name = lower( tagList.tag );

执行计划结果如下，符合预期

figure 16.jpeg

rows字段

该字段表示预计查询需要扫描的记录数(行数)。具体地，当采用全表扫描的方式进行查询时，其指的就是数据记录的行数；当通过索引进行查询时，其指的是索引记录的行数

filtered字段

该字段是一个以百分比%为单位的估计值，其表示的是 通过查询条件过滤后符合要求的记录数 占 预计扫描的记录数(即row字段值) 的百分比。换言之，预计结果记录数量为 filtered值 / 100 * rows值

这里向user_info数据表插入一些数据，具体结果如下。其中name字段上建立了一个二级索引index1

figure 17.png

示例SQL语句如下

1
2
3
explain
select name from user_info
where name='aaron' and id>5;

从执行计划中我们可以看出，符合上述查询要求的记录数的估计值为 6 * 28.57% = 1.7142，通过观察我们可以看到真正满足查询要求的记录数是2条。这也进一步执行计划中的结果只是一个估计值，而非精确值

figure 18.png

Extra字段

该字段是对执行计划信息的补充和完善，这里就一些常见的补充信息进行说明

No tables used

没有数据表，即未使用from子句。示例SQL如下所示

1
explain select 1;

结果如下，符合预期

figure 19.png

Impossible WHERE

where子句中条件恒为false。示例SQL如下所示

1
2
3
explain
select * from user_info
where 1=3;

结果如下，符合预期

figure 20.png

Zero limit

limit子句的参数为0，即查询语句返回的记录数为0条。示例SQL如下所示

1
explain select * from user_info limit 0;

结果如下，符合预期

figure 21.png

Using index

出现索引覆盖，即不需要进行回表操作。为了方便演示，这里先建立一张表，并在name、age字段上建立了一个联合索引index1

1
2
3
4
5
6
7
8
create table staff(
    id int auto_increment,
    name varchar(10) null,
    age int	null,
    salary double null,
    primary key (id),
    index index1(name, age)
);

现在我们就来写一个发生索引覆盖情况的SQL，示例如下

1
2
3
explain
select age from staff
where name = 'aaron';

执行计划如下所示，符合预期

figure 22.png

相应的，如果我们的索引index1不是一个覆盖索引时，既不会存在该补充信息，示例SQL如下

1
2
3
explain
select salary from staff
where name = 'aaron';

从下面的执行计划可以看到Extra为NULL，符合预期

figure 23.png

Using index condition

对于利用二级索引进行查询的过程我们已经很熟悉了，基本是先利用 索引字段的查询条件 获取 二级索引记录，然后再通过回表操作获取完整记录。那问题来了，对于存在一些无法使用索引的索引字段的查询条件。该何时使用呢？具体地，即是在获得二级索引的记录后使用该查询条件进行过滤，还是在回表操作后获取到完整的记录时使用该查询条件进行过滤呢？在MySQL 5.6之前，对于该查询条件的使用时机是在回表后获取到完整记录的时候。而在MySQL 5.6中引入了 Index Condition Pushdown(ICP, 索引条件下推) 特性，该优化将 索引字段的查询条件 的使用时机放在获取到二级索引的记录时，此举大大减少了不必要的回表操作。故如果某个查询语句使用了ICP特性，这即会出现补充信息Using index condition

这么说可能有点抽象，下面通过具体的实例作进一步的解释。其中，name字段上存在一个二级索引index1。可以看到对于查询条件 name>’aa’ 而言，是可以利用到索引index1。但对于查询条件 name like ‘%ron’，由于是前缀匹配，故该索引字段的查询条件无法使用索引。但MySQL可以先利用查询条件name > ‘aa’在索引index1的B+树中获取符合条件的记录，然后再对这些二级索引的记录进一步使用 name like ‘%ron’ 条件进行过滤，最后再利用二级索引记录中主键进行回表获取完整的用户记录

1
2
3
explain
select * from staff
where  name > 'aa' and name like '%ron';

执行计划结果如下所示，符合预期

figure 24.png

Using where

在全表扫描时或二级索引回表后，使用where子句中的查询条件进行过滤。SQL示例如下所示，其中name字段上建有二级索引index1

1
2
3
explain
select * from staff
where name='bob' and salary>123;

执行计划的结果如下，符合预期

figure 25.png

Not exists

对于外连接查询而言，如果在where子句要求被驱动表中某个不允许为NULL字段的值为NULL。则会在被驱动表执行计划的Extra字段中出现Not exists信息。因为在此种场景下，驱动表中的记录只有在被驱动表中无法匹配到记录时，才会被加入到最终的结果集。换言之，对于驱动表中的某条记录A1来说，如果其通过on子句在被驱动表中找到一条匹配的记录后，即可提前停止对A1记录的匹配。因为A1记录肯定不会被加入到最终的结果集中。显然通过提前停止匹配的操作，大大减少不必要的性能浪费

对于下面的左外连接查询SQL语句而言，除staff表的id字段不允许为NULL外，其余字段均可为NULL

1
2
3
4
explain
select * from user_info left join staff
on user_info.age = staff.age
where staff.id is null;

执行计划的结果如下，符合预期

figure 26.jpeg

Using intersect、Using union、Using sort_union

我们在介绍单表查询的Access Method访问方法时，介绍过一种同时使用多个索引的访问方法——index merge。在该访问方法下，具体有Intersection、Union、Sort-Union等算法。故当执行计划的type字段值为index merge时，Extra字段即会给出具体算法的补充信息。对应于上面提到三种不同算法，分别为Using intersect、Using union、Using sort_union。并在后面的括号中会给出涉及到的索引名称

这里以Using intersect为例进行介绍，首先建立如下的数据表并插入一些数据

1
2
3
4
5
6
7
8
9
create table access_method4 (
    id              int auto_increment,
    recv_time       datetime       null,
    create_time     varchar(255)   null,
    name            varchar(255)   null,
    PRIMARY KEY (id),
    index index1(create_time),
    index index2(recv_time)
);

则SQL查询的示例如下所示

1
2
3
4
explain
select * from access_method4
where create_time='2019-03-26 23:59:35.492'  -- 对index1索引中所有字段进行等值查询
and recv_time='2019-03-26 23:59:32';            -- 对index2索引中所有字段等进行值查询

则执行计划的结果如下所示

figure 27.jpeg

Using filesort

众所周知，可以利用索引直接实现某些排序需求。例如对于下面的SQL而言，其在name、age字段上建立了一个联合索引

1
2
3
4
explain
select * from staff
where name = 'a'
order by age;

则其执行计划如下所示，Extra字段值空空如也

figure 28.png

但大多数时候我们无法利用索引直接实现有序，而只能在内存或磁盘中进行排序。前者内存中适用于记录较少时，后者磁盘中适用于记录较多时。不论是在内存中还是在硬盘中实现排序，在MySQL下均被称作file sort文件排序。例如对于下面的SQL语句而言，即无法通过索引直接实现排序，而只能通过file sort排序实现

1
2
3
4
explain
select * from staff
where name = 'a'
order by salary;

执行计划结果如下，符合预期

figure 29.png

Using temporary

当MySQL进行去重、排序、分组等操作时，可能需要在内部建立临时表来完成。值得一提的是，由于建立、维护临时表的成本并不低，必要时可以通过索引来避免MySQL使用临时表。示例SQL如下所示

1
explain select distinct salary from staff;

执行计划结果如下所示

figure 30.png

参考文献

  1. MySQL是怎样运行的
推荐文章
请我喝杯咖啡捏～

欢迎关注我的微信公众号：青灯抽丝