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进行优化。