本文是对于MySQL执行计划的解析，主要解释了MySQL执行计划中的各个参数及含义。

产生的值

存在六种情况：

Using filesort、Using temporary、use index、using where、using join buffer、impossible where

说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行，

Mysql中无法利用索引完成排序操作称为"文件排序"。

EXPLAINSELECT*FROMEMPLOYEEORDERBYSALARY;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+|1|SIMPLE|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingfilesort|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+1rowinset,1warning(0.02sec)

根据salary字段进行排序，并且salary字段上没有索引，那么此时会使用文件排序，extra为using filesort。

使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,常见于排序orderby 和分组查询group by。

查看dep_id的数据分布：

EXPLAINSELECTDEP_ID,COUNT(*)FROMEMPLOYEEGROUPBYDEP_ID;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|SIMPLE|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+1rowinset,1warning(0.01sec)

对dep_id进行了group by，此时需要对中间的结果进行保存，所以会使用临时表，extra为using temporary。

表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好。

如果同时出现using where，表明索引被用来执行索引键值的查找；

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

对工资升序查找数据（有索引）：

EXPLAINSELECTSALARYFROMEMPLOYEEORDERBYSALARY;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+|1|SIMPLE|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingfilesort|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+1rowinset,1warning(0.00sec)

使用salary进行排序，并且有索引，那么通过该列的索引即可查出数据，索引只是用来读取数据的，也避免了排序，

此时extra显示为using index。

表明使用了where过滤（过滤字段没有索引或者不能使用索引）。

Dep_id字段没有索引。

EXPLAINSELECT*FROMEMPLOYEEWHEREDEP_ID=4;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+|1|SIMPLE|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|12.50|Usingwhere|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+1rowinset,1warning(0.00sec)

当有使用where进行过滤时，在extra里使用using where表示。

dep_id创建索引：

CREATEINDEXidx_emp_02ONemployee(dep_id);当可用正常使用索引时：EXPLAINSELECT*FROMEMPLOYEEWHEREDEP_ID>4;+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+|1|SIMPLE|EMPLOYEE|NULL|range|idx_emp_02|idx_emp_02|5|NULL|5|100.00|Usingindexcondition|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+1rowinset,1warning(0.00sec)

通过索引进行访问，标记为Using index condition。

不可以使用索引时：

EXPLAINSELECT*FROMEMPLOYEEWHEREDEP_ID+1>4;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+|1|SIMPLE|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingwhere|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+1rowinset,1warning(0.00sec)

标记为Using where

在使用where条件的索引时，会标记为Using index condition。

EXPLAINSELECT*FROMEMPLOYEEWHEREDEP_ID>4;+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+|1|SIMPLE|EMPLOYEE|NULL|range|idx_emp_02|idx_emp_02|5|NULL|5|100.00|Usingindexcondition|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+1rowinset,1warning(0.00sec)

通过索引进行访问，标记为Using index condition。

where条件导致没有返回的行。

EXPLAINSELECT*FROMEMPLOYEEWHEREIDISNULL;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+|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|ImpossibleWHERE|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+1rowinset,1warning(0.00sec)

Id是主键，所以不会有空值，在进行一个id为空的查询时，是没有结果的，因此extra会表示为impossible where。