Fork me on GitHub

rookiedev

just do it

0%

Explain 的使用解析

Posted on Edited on In

SQL 执行速度评估

假如现在有一张活动表，表结构如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table activities
(
    id bigint unsigned auto_increment comment '主键' primary key,
    name varchar(255) default '' not null comment '活动名称',
    city_code varchar(64) default '' not null comment '城市编码',
    start_time timestamp default CURRENT_TIMESTAMP not null comment '开始时间',
    end_time timestamp default CURRENT_TIMESTAMP not null comment '结束时间',
    status tinyint unsigned default '0' not null comment '状态:0-待启用,1-待生效,2-生效中,3-已结束,4-已禁用',
    create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
    update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
) comment '活动表' CHARSET=utf8mb4;
create index idx_city_code on activities(city_code);
create index idx_create_time on activities (create_time);
create index idx_start_end_time on activities (start_time, end_time);

业务中需要查询目前正在生效中的活动有哪些，查询SQL 是下面这样的：

1
select * from activities where start_time < now() and now() < end_time;

以我们对索引的了解，活动表上已经建了 start_time 和 end_time 的联合索引 idx_start_end_time，上面的 SQL 肯定会通过 idx_start_end_time 索引去查询，因此上面的 SQL 执行速度应该不会慢的，但事实上真的是这样吗，如果不是这样的话，我们该如何去评估这个 SQL 的执行速度呢，那就是通过 EXPLAIN 指令，也是我们今天要重点介绍的内容，通过 explain 的执行结果我们可以看到以下内容：

1.SQL 语句中表的查询顺序

2.SQL 的查询类型

3.查询的是哪个分区表

4.走的是哪个索引

5.查询扫描行数

6.SQL 的执行方式

通过对这些内容进行分析之后我们就可以优化我们的 SQL 语句或者表的索引结构，进一步提高我们的查询效率

EXPLAIN 语法介绍

对于 explain 的使用，其实很简单，只要在要执行的 SQL 语句前面加上 explain 关键字就可以，以上面的 SQL 为例:

1
explain select * from activities where start_time < now() and now() < end_time;

执行结果如下:
image-20220423230701278

从执行结果来看，有点类似于在查询一张包含上面输出结果中的这些字段的表一样，这些字段包括 id，select_type, table,partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra，这些字段当中有些我们稍微了解一下就可以，有些字段对于我们优化 SQL 有很大的帮助，需要重点关注

针对下面的示例，这里先准备一些表作为测试使用:

1
2
3
4
5
6
7
8
create table cities
(
    id bigint unsigned auto_increment comment '主键' primary key,
    name varchar(255) default '' comment '城市名称',
    code varchar(64) default '' not null comment '城市编码'
) comment '城市编码表';
create index idx_name on cities(name);
create unique index uk_code on cities (code);

EXPLAIN 各字段含义

上面我们提到通过 explain 的执行结果我们可以看到 SQL 执行过程中的一些关键信息，执行结果中每个字段都代表着不同的含义，在理解了这些字段的含义后，对我们优化 SQL 或索引都会起到很大的作用，我们来依次介绍这些字段的含义

id，table: id 和 table 两个字段可以放在一起来看，id 的值代表了 SQL 语句中表的读取顺序，值越大读取顺序越靠前，当 id 值相同时，table 列按照从上往下的读取顺序。

1
explain select * from activities where city_code = (select code from cities limit 1);

image-20220425204607073

1
explain select * from activities t1 join cities t2 on t1.city_code = t2.code;

image-20220425204446829

select_type: 表示 SQL 语句的查询类型，常见的值有：

1
2
3
4
5
6
SIMPLE：简单查询，不包含 UNION 或者子查询
PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY
SUBQUERY：子查询中的第一个 SELECT 查询
UNION：在 UNION 语句中，UNION 之后出现的 SELECT
DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED
UNION RESULT：UNION 查询的结果

partitions: 如果查询的表是分区表，表示命中的数据记录所在的分区，对于不是分区表，值为 NULL

type: 表示查询所执行的类型，通过它可以看出查询是如何执行的，这一列可以说是我们要重点优化的对象，它的值比较重要，主要有以下类型:

1
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

上面这些类型从左到右，查询性能依次降低，这里先不详细介绍每种类型的执行效果，先知道有这些类型即可，后面和 Extra 字段一起统一重点介绍。

possible_keys: 表示查询可能使用到的索引，也有可能里面的索引一个都没用到，取决于优化器分析之后的结果，也就是有可能该列上存在索引，但优化器分析之后决定不使用该索引

key: 表示查询过程中使用的索引，如果为 null 则表示该查询没有使用索引，该列的值有可能存在 possible_key 列中没有出现的索引，同时当查询满足覆盖索引的条件时，possible_keys 列为 NULL，索引仅在 key 列显示

key_len: 表示查询过程中使用的索引字节数

ref: 表示在查询索引时，哪些列或者常量被用来和索引的值进行比较

rows: 表示查询过程中预估需要扫描的行数，注意这是一个估算的值，并不是具体的值

filtered: 表示通过索引查询之后匹配到的数据量再经过 server 层 where 条件判断过滤之后剩余数据比例，比如说根据索引条件查询满足条件的数量是 100，经过其他条件过滤之后返回的结果数剩 60，filtered 的值就是 60%，通常这个值越大越好，直接经过索引条件查询得到的结果数就是最终查询的结果数肯定效率是更高的，但也不用过多的关注这个值，这个值对 SQL 性能的整体提升相比其他更重要的字段(type, Extra)来说并不是非常明显

Extra: 表示 SQL 语句执行过程中一些额外的关键信息，常见的值有:

1
Using where，Using index，Using index condition，Using index for group-by，Using join buffer (Block Nested Loop)，Using join buffer (hash join)，Using join buffer (Batched Key Access)，Using filesort，Using temporary

Type 列解析

对于一个 SQL 的优化，我们重点应该关注的是 Type 和 Extra 这两个字段，或者说这两个字段展示出来的信息几乎关系着整个 SQL 执行效率，先来看 Type 字段常见的值的含义:

const: 主要是在通过主键或者唯一键等值查询的时候，type 字段的值是 const

1
explain select * from cities where code = '330100';

image-20220426192431443

eq_ref: 在联表查询查询时，A 表 join B 表，B 表是通过主键或者唯一键进行关联时，也就是在 A 表中只有唯一一条记录和 B 表中数据相匹配，那 B 表的 type 字段值就是 eq_ref 类型

1
explain select * from activities t1 join cities t2 on t1.city_code = t2.code;

image-20220426193145085

ref: 通过普通索引进行等值查询，也就是表中可能有多条数据记录匹配，那 type 字段值就是 ref 类型

1
explain select * from cities where name = '杭州';

image-20220426195602499

ref_or_null: 通过普通索引进行等值查询，或者该字段为 null 的情况，注意该字段不能设置为 not null，那 type 字段值就是 ref_or_null 类型

1
explain select * from cities where name = '杭州' or name is null;

image-20220426200127808

index_merge: 在 where 条件中的字段需要利用多个索引进行查询，这时 type 字段值就是 index_merge 类型，常见于多个索引字段进行 or 查询

1
explain select * from cities where code = '330100' or name = '杭州';

image-20220426201022049

range: 索引范围查询，这个可能见的相对比较多一点，常见于带 <>，>，>=，<，<=，between，in，like 等这些查询条件语句中

1
explain select * from activities where start_time >= now();

image-20220426234344870

index: 通过遍历索引树就能够得到相应的数据，相比下面的 All 类型来说要好一些，索引文件大小肯定要比数据文件大小要小，而且索引的根节点和第二层节点大概率处于内存当中，相对来说会更快一些，但注意这是索引树扫描，相比其他索引定位查询还是要慢很多的，所以不要看到使用上了索引就以为查询不慢

1
explain select id, name from cities;

image-20220427000036479

All: 这个就是通常我们所说的没有可用的索引可以利用，直接全表扫描，相对来说性能是最差的，最直接的方法就是通过加索引来避免全表扫描

1
explain select * from activities where name = '活动一';

image-20220426235124506

Extra 列解析

上面已经介绍了 Type 字段常见的一些值的含义，但有时还需要配合 Extra 一起来看才能更好的进行优化，接下来我们一起来看 Extra 字段常见值含义:

no matching row in const table: 通常根据主键查询的数据在表中不存在，Extra 列的值是这个

image-20220430181005220

Using index: 表示利用到了我们在之前文章里面提到的索引覆盖优化功能，通常是要查询的字段以及条件字段都已经在索引中存在，也就是说通过索引树就可以满足查询条件以及结果，不需要再到主键索引树中查询该记录完整的数据，减少回表的过程

image-20220430182906088

Using index for group-by: 和 Using index 意思差不多，不过就是查询语句中如果包含了 distinct 和 group by 关键字的时候 Extra 里面的内容就是 Using index for group-by 了

image-20220430204616024

image-20220430204709766

Using index condition: 表示利用到了在我们在之前文章里面同样提到过的索引下推优化功能，在 “仅能利用最左前缀索引的场景” 下(无法能利用全部联合索引)，对不在最左前缀索引中的其他联合索引字段加以利用，在遍历索引时，由只传入可以利用到的字段值，改成了多传入下推字段值进行过滤，过滤会减少遍历索引查出的主键条数，从而减少回表次数，提升整体性能，对于 Using index 和 Using index condition 不是很熟悉的话建议去看之前的文章 <<深入索引二>>

image-20220430183309242

image-20220502110908831

Using where: 表示该查询需要在 Server 层得到存储引擎返回的结果之后需要再次进行过滤，这种情况可能使用了索引，只不过存在其他条件不在索引字段中，也有可能就完全没有使用索引

image-20220430202912973

image-20220430203040134

Using join buffer (Block Nested Loop): 表示在 join 联表查询的时候，当被驱动表的联表字段上没有索引的时候，Extra 里面就会出现这个值，但注意的是在 MySQL 8.0 版本之后 Extra 里面的值是 Using join buffer (hash join)，当出现不管是 Using join buffer (Block Nested Loop) 还是 Using join buffer (hash join) 我们都应该想办法进行优化，简单的做法就是在被驱动表的关联字段上建索引

image-20220502132957893

image-20220502122513317

Using join buffer (Batched Key Access): 表示在 join 联表查询的时，当被驱动表的联表字段上有索引，Extra 里面就会出现这个值，但注意的是需要将下面的优化开关设置如下

1
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

image-20220502121822919

Using temporary: 表示该查询需要借助临时表来存储中间查询结果才能进一步得到最终的查询结果，通常存在于 group by 的查询语句，同时又没有使用到索引的情况，这种 SQL 一般都需要优化

image-20220430210911712

Using filesort: 表示该查询没办法利用现有的索引对结果进行排序，而需要借助第三方存储空间来进行排序才能得到最终的排序结果，通常存在于包含 order by 的查询语句中，同时又没有使用到索引的情况，这种 SQL 一般都需要优化

image-20220430211338265

好了，上面主要介绍了 explain 执行结果中的一些字段含义，type 字段和 Extra 字段里面常见的一些值以及这些值的含义，通过熟悉这些我们可以大概知道 SQL 是怎么执行的，执行的速度怎么样，是否还有优化空间，下篇文章就来看两个常见的 SQL 优化案例。

wechat
扫描上面图中二维码关注微信公众号