乐趣区

关于数据库:举例解释一下explain各字段的含义

前言

之前文章介绍索引生效的场景(聊一聊 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.1simple:简略查问,简略的 select 查问,查问中不蕴含子查问或者 union 查问,请参考上一步执行后果。

2.2primary:主键查问,查问中若蕴含任何简单的子局部,最外层查问则被标记为 primary,请参考上一步执行后果。

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

2.4derived:长期表,在 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.5union:联结查问,第二个 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.6union 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.1null:不拜访任何表和索引,间接返回后果
explain select version();
id select_type table partitions type possible_keys key
1 SIMPLE NULL NULL NULL NULL NULL


5.2system:只有一条数据的零碎表 或 衍生表只有一条数据的主查问,请参考 2.4 执行后果。5.3const:示意通过 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.4eq_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.5ref:应用一般索引或者唯一性索引的局部前缀,可能会找到多个符合条件的行。
explain select * from table_partitions where name=’zhang’;
id select_type table partitions type possible_keys key
1 SIMPLE table_partitions p0,p1 ref index_name_age index_name_age


5.6range:索引范畴扫描,常见于应用 >,<,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 p0,p1 range index_name_age index_name_age

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

5.8all:扫描全表数据文件。
explain select * from table_partitions;
id select_type table partitions type possible_keys key
1 SIMPLE table_partitions p0,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 p0,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 字段 **

示意存储引擎返回的数据过滤后,剩下多少满足查问的记录数量的比例。单位是百分比,100% 示意数据没有被过滤。
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 语句,导致没有符合条件的行。
退出移动版