举例解释一下explain各字段的含义

2022-02-16 00:00:00 索引 查询 字段 执行 用到
前言

之前文章介绍索引失效的场景(聊一聊MySQL索引失效的问题),用到了explain执行计划,执行计划返回执行过程中每一步的信息,而不是执行它。

通过返回的一行或多行信息,显示出执行计划中的每一部分和执行的次序,从而可以从分析结果中,找到查询语句或是表结构的性能瓶颈。

今天我们举一些实际的例子,来解释一下explain执行计划各字段的含义。
CREATE TABLE table_user(
id INT AUTO_INCREMENT,
user VARCHAR(30),
pwd VARCHAR(30),
description VARCHAR(90),
PRIMARY KEY (id));

CREATE TABLE table_role(
id INT AUTO_INCREMENT,
name VARCHAR(30),
description VARCHAR(90),
PRIMARY KEY (id));

CREATE TABLE table_relation(
id INT AUTO_INCREMENT,
user_id INT,
role_id INT,
FOREIGN KEY (user_id) REFERENCES table_user (id),
FOREIGN KEY (role_id) REFERENCES table_role (id),
PRIMARY KEY (id));

CREATE TABLE table_partitions(
id INT AUTO_INCREMENT,
name VARCHAR(30),
age INT,
address VARCHAR(30),
PRIMARY KEY (id))PARTITION BY HASH(id) PARTITIONS 2;


首先建立 用户,角色,关系,分区表。
插入一些数据。
insert into table_user(user,pwd,description) value('tony','abc123','admin');insert into table_user(user,pwd,description) value('tom','123456','general user');insert into table_user(user,pwd,description) value('jerry','123456','general user');
insert into table_role(name,description) value('admin','admin role');insert into table_role(name,description) value('general','general role');
insert into table_relation(user_id,role_id) value(1,1);insert into table_relation(user_id,role_id) value(2,2);insert into table_relation(user_id,role_id) value(3,2);
insert into table_partitions(name,age,address) value('wang',21,'shenzhen');insert into table_partitions(name,age,address) value('zhang',23,'shanghai');insert into table_partitions(name,age,address) value('li',26,'beijing');

创建索引。
CREATE INDEX index_age ON table_partitions(age);CREATE INDEX index_name_age ON table_partitions(name,age);


一、id字段

select查询的序列号,表示的是查询中执行select子句或者是操作表的顺序,id值越大优先级越高,越先被执行。
explain select * from table_role,table_user; #因为排版问题,去掉了一些信息+----+-------------+------------+------------+------+---------------+------+| id | select_type | table      | partitions | type | possible_keys | key  |+----+-------------+------------+------------+------+---------------+------+|  1 | SIMPLE      | table_role | NULL       | ALL  | NULL          | NULL ||  1 | SIMPLE      | table_user | NULL       | ALL  | NULL          | NULL |+----+-------------+------------+------------+------+---------------+------+

explain select * from table_relation where role_id=(select id from table_role where name='admin');+----+-------------+----------------+------------+------+---------------+---------+| id | select_type | table          | partitions | type | possible_keys | key     |+----+-------------+----------------+------------+------+---------------+---------+|  1 | PRIMARY     | table_relation | NULL       | ref  | role_id       | role_id ||  2 | SUBQUERY    | table_role     | NULL       | ALL  | NULL          | NULL    |+----+-------------+----------------+------------+------+---------------+---------+


二、select_type字段

查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

2.1 simple:简单查询,简单的select查询,查询中不包含子查询或者union查询,请参考上一步执行结果。

2.2 primary:主键查询,查询中若包含任何复杂的子部分,外层查询则被标记为primary,请参考上一步执行结果。

2.3 subquery:子查询,在select或者where列表中包含子查询,请参考上一步执行结果。

2.4 derived:临时表,在from表中包含临时表的子查询被标记为derived(衍生)。
explain select * from (select version())temp;+----+-------------+------------+------------+--------+---------------+------+| id | select_type | table      | partitions | type   | possible_keys | key  |+----+-------------+------------+------------+--------+---------------+------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL ||  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL |+----+-------------+------------+------------+--------+---------------+------+

2.5 union:联合查询,第二个select出现被标记为union查询。
explain select description from table_user union select description from table_role;+------+--------------+------------+------------+------+---------------+------+| id   | select_type  | table      | partitions | type | possible_keys | key  |+------+--------------+------------+------------+------+---------------+------+|  1   | PRIMARY      | table_user | NULL       | ALL  | NULL          | NULL ||  2   | UNION        | table_role | NULL       | ALL  | NULL          | NULL || NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL |+------+--------------+------------+------------+------+---------------+------+

2.6 union result:联合查询中查询的结果,从union表获取结果的select查询,请参考2.5执行结果。


三、talbe字段

表示 explain 的一行需要查询的表名。可能为临时表<derived N>,或者联合查询的结果<union M,N>。如果不涉及对数据表的操作,显示为NULL。请参考前几步执行结果。


四、partitions字段

表示 explain 的一行需要访问哪个表的分区。
explain select * from table_partitions where id=1;+----+-------------+------------------+------------+-------+---------------+---------+| id | select_type | table            | partitions | type  | possible_keys | key     |+----+-------------+------------------+------------+-------+---------------+---------+|  1 | SIMPLE      | table_partitions | p1         | const | PRIMARY       | PRIMARY |+----+-------------+------------------+------------+-------+---------------+---------+


五、type字段

表示关联类型或访问类型,该字段是sql查询优化中一个很重要的指标。

5.1 null:不访问任何表和索引,直接返回结果
explain select version();+----+-------------+-------+------------+------+---------------+------+| id | select_type | table | partitions | type | possible_keys | key  |+----+-------------+-------+------------+------+---------------+------+|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL |+----+-------------+-------+------------+------+---------------+------+

5.2 system:只有一条数据的系统表 或 衍生表只有一条数据的主查询,请参考2.4执行结果。

5.3 const:表示通过primary key 或者 unique 索引一次就找到了。
explain select * from table_partitions where id=1;+----+-------------+------------------+------------+-------+---------------+---------+| id | select_type | table            | partitions | type  | possible_keys | key     |+----+-------------+------------------+------------+-------+---------------+---------+|  1 | SIMPLE      | table_partitions | p1         | const | PRIMARY       | PRIMARY |+----+-------------+------------------+------------+-------+---------------+---------+

5.4 eq_ref:使用的是索引,使用主键的关联查询,关联查询出的记录只有一条。
explain select * from table_relation join table_user where table_user.id=table_relation.user_id;+----+-------------+----------------+------------+--------+---------------+---------+| id | select_type | table          | partitions | type   | possible_keys | key     |+----+-------------+----------------+------------+--------+---------------+---------+|  1 | SIMPLE      | table_relation | NULL       | ALL    | user_id       | NULL    ||  1 | SIMPLE      | table_user     | NULL       | eq_ref | PRIMARY       | PRIMARY |+----+-------------+----------------+------------+--------+---------------+---------+

5.5 ref:使用普通索引或者性索引的部分前缀,可能会找到多个符合条件的行。
explain select * from table_partitions where name='zhang';+----+-------------+------------------+------------+------+----------------+----------------+| id | select_type | table            | partitions | type | possible_keys  | key            |+----+-------------+------------------+------------+------+----------------+----------------+|  1 | SIMPLE      | table_partitions | p,p1      | ref  | index_name_age | index_name_age |+----+-------------+------------------+------------+------+----------------+----------------+

5.6 range:索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。
explain select * from table_partitions where name like 'zhang';+----+-------------+------------------+------------+-------+----------------+----------------+| id | select_type | table            | partitions | type  | possible_keys  | key            |+----+-------------+------------------+------------+-------+----------------+----------------+|  1 | SIMPLE      | table_partitions | p,p1      | range | index_name_age | index_name_age |+----+-------------+------------------+------------+-------+----------------+----------------+

5.7 index:索引全表扫描,把索引从头到尾扫一遍。
explain select name from table_partitions;+----+-------------+------------------+------------+-------+---------------+----------------+| id | select_type | table            | partitions | type  | possible_keys | key            |+----+-------------+------------------+------------+-------+---------------+----------------+|  1 | SIMPLE      | table_partitions | p,p1      | index | NULL          | index_name_age |+----+-------------+------------------+------------+-------+---------------+----------------+

5.8 all:扫描全表数据文件。
explain select * from table_partitions;+----+-------------+------------------+------------+------+---------------+------+| id | select_type | table            | partitions | type | possible_keys | key  |+----+-------------+------------------+------------+------+---------------+------+|  1 | SIMPLE      | table_partitions | p,p1      | ALL  | NULL          | NULL |+----+-------------+------------------+------------+------+---------------+------+


六、possible_keys字段

可能使用到的索引。
explain select * from table_partitions where name='zhang' and age=20;+----+-------------+------------------+------------+------+--------------------------+-----------+| id | select_type | table            | partitions | type | possible_keys            | key       |+----+-------------+------------------+------------+------+--------------------------+-----------+|  1 | SIMPLE      | table_partitions | p,p1      | ref  | index_age,index_name_age | index_age |+----+-------------+------------------+------------+------+--------------------------+-----------+


七、keys字段

实际使用到的索引,参考上一步执行结果。


八、key_len字段

表示索引中使用的字节数。显示的值为索引字段的大可能长度,并非实际使用长度,理论上越短越好。
explain select name from table_partitions;+----+-------+----------------+---------+------+------+----------+-------------+| id | type  | key            | key_len | ref  | rows | filtered | Extra       |+----+-------+----------------+---------+------+------+----------+-------------+|  1 | index | index_name_age | 128     | NULL |    1 |   100.00 | Using index |+----+-------+----------------+---------+------+------+----------+-------------+

explain select age from table_partitions;+----+-------+-----------+---------+------+------+----------+-------------+| id | type  | key       | key_len | ref  | rows | filtered | Extra       |+----+-------+-----------+---------+------+------+----------+-------------+|  1 | index | index_age | 5       | NULL |    1 |   100.00 | Using index |+----+-------+-----------+---------+------+------+----------+-------------+

通过对索引字段index_name_age,index_age的对比,可以看出INT型字段索引长度短了很多。


九、ref字段

显示用什么内容来和索引列比较,可能是空,或者某个表的列,或者常量。

这个字段很多介绍的文章经常写错,以为是比较用到的列,比如描述成:显示索引的那一列被使用了,如果可能,是一个常量。

上一步我们没有用到比较字段,显示就为NULL,如果对索引比较加上常量字段,显示的就是常量。
explain select name from table_partitions where name='zhang';+----+------+----------------+---------+-------+------+----------+-------------+| id | type | key            | key_len | ref   | rows | filtered | Extra       |+----+------+----------------+---------+-------+------+----------+-------------+|  1 | ref  | index_name_age | 123     | const |    1 |   100.00 | Using index |+----+------+----------------+---------+-------+------+----------+-------------+

如果对索引比较某个表的列,显示的就是某个表的列。
explain select table_relation.id from table_relation,table_role where role_id=table_role.id;+----+-------+---------+---------+--------------------+------+----------+-------------+| id | type  | key     | key_len | ref                | rows | filtered | Extra       |+----+-------+---------+---------+--------------------+------+----------+-------------+|  1 | index | PRIMARY | 4       | NULL               |    2 |   100.00 | Using index ||  1 | ref   | role_id | 5       | mydb.table_role.id |    1 |   100.00 | Using index |+----+-------+---------+---------+--------------------+------+----------+-------------+


十、row字段

根据表统计信息及索引选用情况,估算出找到所需的记录,需要读取的行数。
explain select age from table_partitions where age>18;+----+-------+-----------+---------+------+------+----------+--------------------------+| id | type  | key       | key_len | ref  | rows | filtered | Extra                    |+----+-------+-----------+---------+------+------+----------+--------------------------+|  1 | index | index_age | 5       | NULL |    3 |   100.00 | Using where; Using index |+----+-------+-----------+---------+------+------+----------+--------------------------+


十一、filtered字段

表示存储引擎返回的数据过滤后,剩下多少满足查询的记录数量的比例。单位是百分比,表示数据没有被过滤。
explain select * from table_user where description='admin';+----+------+---------------+------+---------+------+------+----------+-------------+| id | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+------+---------------+------+---------+------+------+----------+-------------+|  1 | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |+----+------+---------------+------+---------+------+------+----------+-------------+


十二、extra字段

显示额外的信息。

可能值包括:
  • Using index查询到的列被索引覆盖,实际上就是覆盖索引的使用。


  • Using where查询未用到可用的索引,通过where条件过滤数据。


  • Using where,Using index通过where条件过滤数据,并且查询用到了覆盖索引。


  • Using index condition查询使用到了索引,但是需要回表查询。


  • Using temporary查询后结果需要使用临时表来存储,一般在排序或者分组查询时用到。


  • Using filesort无法利用索引完成的排序操作,也就是ORDER BY的字段没有索引。


  • Using join buffer在我们联表查询的时候,如果表的连接条件没有用到索引,需要有一个连接缓冲区来存储中间结果。


  • Impossible where在我们用不太正确的where语句,导致没有符合条件的行。


  • No tables used我们的查询语句中没有FROM子句,或者有FROM DUAL子句。


END


相关文章