mysql执行计划中的extra字段

对于mysql的执行计划，主要是针对select查询优化而备的，通过在查询语句前加explain就可以显示此次查询的执行计划，当然查询计划中包括了id，select_type，type...字段，这里不逐一论述，主要是extra字段里面的字段进行解释。

create table user1(
id int not null primary key,
age int not null,
height int not null,
weight int not null
) engine=Innodb;

-----------------------
create index myindex on user1(age,height,weight);

extra字段存在的值

NULL: 没有用到额外的附加条件

Using filesort：查询语句中包含了oder by语句，索引无法完成排序，数据量小的时候在内存中完成排序，否者在磁盘中完成排序。

Using temporary：使用到了临时表。

Using Index：使用覆盖索引进行数据返回(Innodb引擎才存在)，不会进行回表查找。

Using Where：查询语句中存在where范围查找，会回表查找数据。

Using Where Using Index：覆盖索引中存在范围查找，但是也不会进行回表，直接在索引上就能拿到数据。

Using Index Condition：跟Using Where Using Index有一些差别，它的查找用到了联合索引，查找的数据有不在索引中的字段，所以会进行回表查找数据。

具体sql

先往表中插入数据

insert into user1(id,age,height,weight,name) values(1,4,3,1,'小太'),(2,1,2,7,'小计'),(3,1,5,2,'小红'),(4,2,1,8,'小明')，(5,1,5,2,'小弄');

null情况

explain select * from user1;

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user1 | ALL  | NULL         | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

Using Index

explain select age,height,weight,id from user1 where age = 2 and height = 1 and weight = 8;

+----+-------------+-------+------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | user1 | ref  | myindex       | myindex | 15      | const,const,const |    1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-------------------+------+-------------+

Using Where Using Index

explain select age,height,weight,id from user1 where age = 2 and height > 1 and weight = 8;

+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user1 | ref  | myindex       | myindex | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+

Using Index Condition

explain select age,height,weight,name from user1 where age = 2 and height > 1 and weight = 8;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user1 | range | myindex       | myindex | 10      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+