前言
上一篇【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');