EXPLAIN分析SQL语句

EXPLAIN是什么及其作用

  EXPLAIN显示了MySql如何使用索引来处理select语句以及连接表。使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，继而可以帮助选择更好的索引和写出更优化的查询语句。

EXPLAIN使用方式

  EXPLAIN + select的SQL语句

  上图显示了EXPLAIN的结果集，接下来我们一步步了解每个字段代表的含义。

EXPLAIN结果集分析

id

  id表示读取顺序。id越大，越先被读取；相同id时，由上至下读取。

select_type

  select_type显示查询类型，常见查询类型如下：

select_type	含义
SIMPLE	简单查询（不包含子查询和联合查询）
PRIMARY	复杂查询中最外层的查询
UNION	联合查询中的第二个或者后面的查询
UNION RESULT	联合查询的结果
SUBQUERY	在select或者where列表中包含了子查询
DERIVED	衍生表查询（跟在from后的子查询）

table

  table显示表名

partitions

  partitions记录与查询匹配的分区

type

  type显示扫描类型，常见访问类型如下，从上到下，性能由好到坏：

type	含义
system	表只有一行记录
const	索引匹配常量时，const用于比较primary key或者unique索引
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行
range	通过索引检索给定范围的行，一般就是在你的where语句中出现了between、<、>、in等的查询
index	通过索引全扫描
all	全表扫描

possible_keys

  possible_keys显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

  key显示实际使用的索引。如果为NULL，则没有使用索引。

key_len

  key_len显示索引中使用的字节数， 该字段可以检查是否充分的利用上了复合索引，可通过该列计算查询中使用的索引的长度。

• key_len( int )：4+1(null)
• key_len( char(n) ) = n * 编码字节数 + 1(null)
• key_len( varchar(n) ) = n * 编码字节数 + 2(可变字符串) + 1(null)

varchar和char的区别：char是固定长度字符串，varchar是可变长度字符串，varchar的原理是用前两位字节记录varchar所需字节数达到可变长度的目的。

以下编码默认utf-8(utf-8编码字节数为3)

字段	公式	key_len
id int	4+1(null)	5
id int not null	4+1(null)	4
name char(20)	n * 3 + 1(null)	61
name char(20) not null	n * 3 + 1(null)	60
name varchar(20)	n * 3 + 2 + 1(null)	63
name varchar(20) not null	n * 3 + 2 + 1(null)	62

ref

  ref显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

  rows显示MySQL认为它执行查询时必须检查的行数。

fillered

  fillered显示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)

Extra

  包含不适合在其他列中显示但十分重要的额外信息，常用信息如下

Extra	含义
Using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary	使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
Using index	表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
Using where	表明使用了where过滤
Using join buffer	使用了连接缓存
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。