Mysql深度讲解 – explain关键字（二）

最新推荐文章于 2022-06-22 21:55:23 发布

Smallc0de

最新推荐文章于 2022-06-22 21:55:23 发布

阅读量375

点赞数 2

分类专栏： Mysql 数据结构

本文链接：https://blog.csdn.net/Smallc0de/article/details/111375073

版权

数据结构同时被 2 个专栏收录

15 篇文章 2 订阅

订阅专栏

Mysql

14 篇文章 4 订阅

订阅专栏

前言

上一篇【Mysql深度讲解 – explain关键字（一）】对explain中的关键字table、id、select_type、type等四个字段做了详细的解释，本篇就把剩余的possible_keys、key、key_len、ref、rows、filtered、Extra几个字段说清楚他们的作用。在本篇最后附录中有笔者创建的演示表，方便大家对照阅读。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

Explain 关键字列表

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

possible_keys和key

possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些；而key列表示该查询语句查询时，实际用到的索引有哪些，比如：
explain select * from t1 where a=1 and b=6;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘const’	'PRIMARY	idx_t1_bcd’	‘PRIMARY’	‘4’	‘const’	‘1’	‘100.00’

这里查询的结果从possible_keys看可能要用到PRIMARY,idx_t1_bcd两个索引，但是实际上只需要用到key中显示的主键索引PRIMARY。要注意的是possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

key_len字段表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由下面三个部分构成的：

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

也就是说如下一个sql，其key_len的值为b(int)+c(int)+1(null)+1(null)=4+4+1+1=10。要注意这里计算的是索引的长度，对于没有索引的字段该值为0。

explain select * from t1 where b=1 and c=1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ref’	‘idx_t1_bcd’	‘idx_t1_bcd’	‘10’	'const	const’	‘1’	‘100.00’

比如表里的 e列 (varchar(20),utf8mb4)，其索引值为20(varchar)*4(utf8mb4)+1(null)+2(可变)=83。

explain select * from t1 where e='1';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ref’	‘idx_t1_e’	‘idx_t1_e’	‘83’	‘const’	‘1’	‘100.00’	NULL

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、 unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东西是什么，比如只是一个常数或者是某个列。基本上属于没什么用就展示一下是什么东西的字段。

rows

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

filtered

代表查询优化器预测在这扫描的记录中，有多少条记录满足其余的搜索条件，比如：
explain select * from t1 where a > 1 and e = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘range’	'PRIMARY	idx_t1_e’	‘PRIMARY’	‘4’	NULL	‘8’	‘11.11’

可以看到这个sql执行的预估结果使用了主键索引，而且大概要扫描8行(rows=8)索引记录，而在这个8行的结果集当中，有大概有11.11%概率满足sql中第二个条件(e = 1)。

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在join查询中驱动表对应的执行计划记录的 filtered值，比如：
explain select * from t1 join t2 on t1.a = t2.a where t1.e = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ALL’	'PRIMARY	idx_t1_e’	NULL	NULL	NULL	‘9’	‘11.11’
‘1’	‘SIMPLE’	‘t2’	NULL	‘eq_ref’	‘PRIMARY’	‘PRIMARY’	‘4’	‘world.t1.a’	‘1’	‘100.00’	NULL

从执行计划中可以看出来，查询优化器打算把t1当作驱动表，t2当作被驱动表。我们可以看到驱动表t1表的执行计划的rows=9， filtered有11.11%的概率满足t1.e = 1，那么驱动表t1表经过条件过滤后有9 × 11.11% = 0.9999条记录要查询，对被驱动表来说只需要执行大约1次查询，就可以查询出结果。通过以上计算结果，可以看出驱动表过滤的数据越多sql的查询效率就会越高。要注意的是这个数据也是估算出来的，用于计算查询成本的参数之一。因此filtered可以作为我们评估当前join是否有最优的条件，一般来说驱动表的filtered应该尽量的小，以减少被驱动表被查询的次数。

Extra

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。这部分在介绍的时候会涉及到一些join内容【Join语句】或者一些子查询优化的内容【子查询优化】。

No table used：没有被使用的表。当查询语句的没有FROM子句时将会提示该信息，比如：
explain select 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	‘No tables used’

Impossible WHERE：不可能的WHERE条件。查询语句中WHERE条件永远为false时将会提示该信息，比如：
explain select b from t1 where 1=0;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	‘Impossible WHERE’

No matching min/max row：没有匹配的最大/最小行。当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该信息，比如：
explain select max(a) from t1 where a=100;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	‘No matching min/max row’

Using index：使用覆盖索引。当查询列以及搜索条件中只包含属于某个索引的列，也就是当前查询列可以被索引覆盖时，会提示信息。这其实也是一种优化手段，比如 d列就在索引里，直接查询 d列会比查询 * 要速度快的多。比如：
explain select d from t1 where b =1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ref’	‘idx_t1_bcd’	‘idx_t1_bcd’	‘5’	‘const’	‘2’	‘100.00’	‘Using index’

Using index condition：有条件的使用索引，也叫做索引下推。有些搜索条件中虽然出现了索引列，但却不能使用到索引。比如下面的sql，b列可以利用到辅助索引，但是却不符合最左原则。此时Mysql会把所有符合条件的辅助索引值找出来，然后使用b like '%1'条件过滤，最终把过滤后的索引值再去主键索引中查找真实数据：
explain select * from t1 where b =1 and b like '%1';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ref’	‘idx_t1_bcd’	‘idx_t1_bcd’	‘5’	‘const’	‘2’	‘100.00’	‘Using index condition’

Using where：使用where语句。当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，会提示这个信息。重点在于这个查询无法利用到索引，可以作为一个潜在优化项看待，比如：
explain select * from t1 where e = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ALL’	‘idx_t1_e’	NULL	NULL	NULL	‘9’	‘11.11’	‘Using where’

Using join buffer：使用join buffer。针对没有使用索引的join语句来说，在查询执行过程中，被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，比如：
explain select * from t1 join t2 on t1.e = t2.e; -- e字段必须不可以有索引才可显示此extra

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t2’	NULL	‘ALL’	NULL	NULL	NULL	NULL	‘3’	‘100.00’	NULL
‘1’	‘SIMPLE’	‘t1’	NULL	‘ALL’	NULL	NULL	NULL	NULL	‘9’	‘11.11’	‘Using where; Using join buffer (Block Nested Loop)’

可以在对t2表的执行计划的Extra列显示了两个提示：

Using join buffer (Block Nested Loop)：因为对表t2的访问不能有效利用索引。为了提高性能，使用join buffer减少对t2表的访问次数。
Using where：可以看到查询语句中有一个t1.e = t2.e条件，因为t1是驱动表，t2是被驱动表。所以在访问t2表时，t1.e的值已经确定下来了，所以实际上查询t2表的条件就是t2.e = 常数，所以提示了Using where。
很明显这个查询语句还有很大的优化空间，比如给 e列建一个索引。

Using filesort：使用文件排序。但是注意Mysql中的文件，并不是真的文件，很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序。这种在内存中或者磁盘上进行排序的方式统称为文件排序（filesort）。如果某个查询需要使用文件排序的方式执行查询，就会显示Using filesort提示，比如：
explain select * from t1 order by e;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ALL’	NULL	NULL	NULL	NULL	‘9’	‘100.00’	‘Using filesort’

既然使用了排序，那肯定有索引会比直接在内存或者磁盘里排序更快。所以具有这种提示的sql语句可以考虑给要排序的字段建一个索引。

Using temporary：使用临时表。比如一个去重的语句，当没有索引的时候就会使用临时表去加载结果集然后再去重，如果使用了索引效率就高一些。对于一个有索引的字段去重，Mysql自动优化到使用索引的方式进行去重。
explain select distinct e from t1; -- 没有索引，使用临时表

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘ALL’	NULL	NULL	NULL	NULL	‘9’	‘100.00’	‘Using temporary’

explain select distinct b from t1; -- 有索引，使用索引

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘index’	‘idx_t1_bcd’	‘idx_t1_bcd’	‘15’	NULL	‘9’	‘100.00’	‘Using index’

Start temporary&End temporary：查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，当执行策略为 DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询将显示Start temporary，被驱动表查询将显示End temporary。对于这种sql其实只是提示了Mysql半连接中半连接的过程，已经经过Mysql的优化，其sql本身并没有什么可以优化的地方。
explain select * from t1 where a in (select e from t2 where e = 1);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t2’	NULL	‘ALL’	NULL	NULL	NULL	NULL	‘3’	‘33.33’	‘Using where; Start temporary’
‘1’	‘SIMPLE’	‘t1’	NULL	‘eq_ref’	‘PRIMARY’	‘PRIMARY’	‘4’	‘world.t2.e’	‘1’	‘100.00’	‘Using where; End temporary’

FirstMatch：在将In子查询转为semi-join时，如果采用的是FirstMatch执行策略,被驱动表就会显示 FirstMatch(table_name)。这种提示也是已经经过Mysql的优化，其sql本身并没有什么可以优化的地方。关于半连接的内容可以参考上面的join语句详解的博客链接，比如：
explain select * from t1 where a in (select c from t2 where c = 1);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
‘1’	‘SIMPLE’	‘t1’	NULL	‘const’	‘PRIMARY’	‘PRIMARY’	‘4’	‘const’	‘1’	‘100.00’	NULL
‘1’	‘SIMPLE’	‘t2’	NULL	‘ALL’	NULL	NULL	NULL	NULL	‘3’	‘33.33’	‘Using where; FirstMatch(t1)’

Extra性能排序

Using index：用了覆盖索引，第一。
Using index condition：用了条件索引（索引下推），第二。
Using where：从索引查出来数据后继续用where条件过滤，第三。
Using join buffer (Block Nested Loop)：join的时候利用了join buffer（优化策略：去除外连接、增大join buffer大小），第四。
Using filesort：用了文件排序，排序的时候没有用到索引，第五。
Using temporary：用了临时表（优化策略：增加条件以减少结果集、增加索引，思路就是要么减少待排序的数量，要么就提前排好序），第六。
Start temporary, End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表来去重，不参与排序。
FirstMatch(table_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重，不参与排序。

总结

到此Mysql中explain关键字的内容、含义、出现条件就全部介绍完了。根据这些提示的内容，我们可以分析出一条sql执行的逻辑是什么，我们如何优化使得一个sql的查询效率更高。

附：本例中构建的表：

create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;
create index idx_t1_bcd on t1(b,c,d);
create index idx_t1_e on t1(e);
insert into t1 values(12,1,2,4,'a');
insert into t1 values(6,4,5,4,'b');
insert into t1 values(9,1,1,1,'c');
insert into t1 values(1,6,7,4,'d');
insert into t1 values(15,2,2,5,'e');
insert into t1 values(7,9,3,6,'f');
insert into t1 values(4,2,1,7,'g');
insert into t1 values(3,3,3,3,'h');
insert into t1 values(10,5,5,5,'ss');

create table t2(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;

insert into t2 values(1,6,7,4,'d');
insert into t2 values(4,2,1,7,'g');
insert into t2 values(3,3,3,3,'h');

Smallc0de

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql深度讲解 – explain关键字（二）

上篇对explain中的关键字table、id、select_type、type等四个字段做了详细的解释，本篇就把剩余的possible_keys、key、key_len、ref、rows、filtered、Extra几个字段说清楚他们的作用。
复制链接

扫一扫

专栏目录