本文是对于MySQL执行计划的解析,主要解释了MySQL执行计划中的各个参数及含义。
十三、Extra
产生的值
存在六种情况:
Using filesort、Using temporary、use index、using where、using join buffer、impossible where
1、Using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行,
Mysql中无法利用索引完成排序操作称为"文件排序"。
EXPLAIN
SELECT * FROM EMPLOYEE ORDER BY SALARY;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.02 sec)
根据salary字段进行排序,并且salary字段上没有索引,那么此时会使用文件排序,extra为using filesort。
2、Using temporary
使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,常见于排序orderby 和分组查询group by。
查看dep_id的数据分布:
EXPLAIN
SELECT DEP_ID
,COUNT(*)
FROM EMPLOYEE
GROUP BY DEP_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 | Using temporary |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.01 sec)
对dep_id进行了group by,此时需要对中间的结果进行保存,所以会使用临时表,extra为using temporary。
3、use index
表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好。
如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
对工资升序查找数据(有索引):
EXPLAIN
SELECT SALARY FROM EMPLOYEE ORDER BY SALARY;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
使用salary进行排序,并且有索引,那么通过该列的索引即可查出数据,索引只是用来读取数据的,也避免了排序,
此时extra显示为using index。
4、using where
表明使用了where过滤(过滤字段没有索引或者不能使用索引)。
Dep_id字段没有索引。
EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_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 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
当有使用where进行过滤时,在extra里使用using where表示。
dep_id创建索引:
CREATE INDEX idx_emp_02 ON employee ( dep_id );
当可用正常使用索引时:
EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_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 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
通过索引进行访问,标记为Using index condition。
不可以使用索引时:
EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_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 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
标记为Using where
5、Using index condition
在使用where条件的索引时,会标记为Using index condition。
EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_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 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
通过索引进行访问,标记为Using index condition。
6、impossible where
where条件导致没有返回的行。
EXPLAIN
SELECT * FROM EMPLOYEE WHERE ID IS NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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 | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
Id是主键,所以不会有空值,在进行一个id为空的查询时,是没有结果的,因此extra会表示为impossible where。