举例解释一下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);
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 |
+----+-------------+----------------+------------+------+---------------+---------+
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 |
+----+-------------+------------+------------+--------+---------------+------+
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 |
+------+--------------+------------+------------+------+---------------+------+
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 |
+----+-------------+------------------+------------+-------+---------------+---------+
explain select version();
+----+-------------+-------+------------+------+---------------+------+
| id | select_type | table | partitions | type | possible_keys | key |
+----+-------------+-------+------------+------+---------------+------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+
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 |
+----+-------------+------------------+------------+-------+---------------+---------+
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 |
+----+-------------+----------------+------------+--------+---------------+---------+
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 |
+----+-------------+------------------+------------+------+----------------+----------------+
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 |
+----+-------------+------------------+------------+-------+----------------+----------------+
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 |
+----+-------------+------------------+------------+-------+---------------+----------------+
explain select * from table_partitions;
+----+-------------+------------------+------------+------+---------------+------+
| id | select_type | table | partitions | type | possible_keys | key |
+----+-------------+------------------+------------+------+---------------+------+
| 1 | SIMPLE | table_partitions | p,p1 | ALL | NULL | NULL |
+----+-------------+------------------+------------+------+---------------+------+
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 |
+----+-------------+------------------+------------+------+--------------------------+-----------+
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 |
+----+-------+-----------+---------+------+------+----------+-------------+
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 |
+----+-------+---------+---------+--------------------+------+----------+-------------+
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 |
+----+-------+-----------+---------+------+------+----------+--------------------------+
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 |
+----+------+---------------+------+---------+------+------+----------+-------------+
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子句。
相关文章