Explain执行计划

Explain+SQL语句查看SQL的执行计划

EXPLAIN SELECT * FROM student

Explain 作用

1、查看表的读取顺序

2、数据读取操作的操作类型

3、查看哪些索引可以使用

4、查看表之间的引用

5、查看每张表有多少行被优化器执行

Explain返回列

id

在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id,id大的先执行，id相同从上到下执行。

select_type

表示查询语句执行查询操作类型

1、simple：简单查询，查询不包含子查询和union

#例
SELECT * FROM student

2、primary：复杂查询中最外层的select

3、derived：包含在from子句中的子查询

4、union：在union中的第二个和随后的select

5、union result：从union临时表检索结果的select

6、subquery：包含在select中的子查询

table

这一列表示explain的一行正在访问哪个表

partitons

• 对于查询分区表，显示查询的分区ID

• 对于非分区表，显示NULL

tpye（重要）

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

tpye列的值执行效率system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

下面介绍常见的值

值	含义
system	查询的表只有一行记录,这是const类型的特例,平时不会出现。
const	primary key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
eq_ref	primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。
ref_or_null	类似于ref类型的查询，但是增加加了对NULL值列的查询。
range	范围扫描通常出现在in (), between and ,> ,<，>=等操作中。使用一个索引来检索给定范围的行。
index	和ALL一样,不同就是只需扫描索引树，这通常比ALL快一些。
ALL	全表扫描，意味着sql需要从头到尾去查找所需要的行，执行效率差。

一般进行SQL优化能到range或者ref

possible_keys

这一列显示查询可能使用哪些索引来查找。

key

实际用到的索引

#如果在表中没有可用的索引，那么key列 展示，possible_keys是NULL，这说明查询到覆盖索引。
#例，phone是索引列
EXPLAIN SELECT phone FROM student

#可能用到索引，实际没有用到索引
#例，course表中的teacher_id是索引列
EXPLAIN SELECT * FROM teacher t,course c WHERE t.teacher_id = c.teacher_id
#可能用到inx_teacher_id索引，实际并没有用到索引，因为是查询所有列，所以并没有用到索引，改成查询t.teacher_id,c.teacher_id即可。

key_len

表示索引中使用的字节数可通过该列计算查询中使用的索引长度。

ALTER TABLE course ADD INDEX inx_teacher_id(teacher_id,course_num,course_name)
#如果使用联合索引时，如上，总字节是长度是123个字节，那么key_len值数据可能少于123字节，这就说明了查询中并没有使用联合索引的所有列，而只是利用到1列或者2列。

ref

索引是否被引入，到底引用到了哪几个索引。

#例
EXPLAIN
SELECT * 
FROM student s,course c,course_selection cs 
WHERE s.`student_id` = 1
AND cs.`student_id` = s.`student_id`
AND c.`course_id` = cs.`course_id`
#图中s表的ref值是const，说明索引列(student_name)引入的时常量，cs表和c表的ref值是其他表的索引列。

rows和filtered

rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。

filtered:满足查询数量的比例（比例越高越好）

#例
EXPLAIN
SELECT * 
FROM course_selection cs 
WHERE cs.student_id = 1 
#当course_selection表中student_id不是索引时，如图扫描5条数据有一条符合查询条件
#当course_selection表中student_id是索引时，如图根据索引扫描一条

Extra

1、Using filesort：说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行，sql无法利用索引进行排序，使用了文件排序（出现这个时需要对sql进行优化）。

#例
EXPLAIN 
SELECT *
FROM score
ORDER BY total_grade
#如图total_grade并没有利用索引进行排序，使用文件进行排序，查询的列改为total_grade就会利用索引排序。

2、Using temporary：使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。 常见于排序 order by 和分组查询 group by 。

3、Using index:使用了覆盖索引进行查询。

4、Using Where：表明使用了 where 过滤。

5、Using join buffer：使用了连接缓存。

6、impossible where：where 子句的值总是 false , 获取不了数据。

一般出现Using filesort、Using temporary、impossible where需要对SQL进行优化。