MySQL分析工具explain介绍

引言：见过很多关于MySQL的查询语句优化的文章，但是当你用过EXPLAIN后，了解其中的原理后，发现那些文章都太业余了。总之一句，EXPLAIN简直不要太好用了！

 

EXPLAIN是MySQl必不可少的一个分析工具，主要用来测试sql语句的性能及对sql语句的优化，或者说模拟优化器执行SQL语句。

简单的说是execute plan, 获取MySQL数据库的执行计划。本文重点介绍explain的使用和各参数的意义。

 

首先看一个例子，现有一张表格filesinfo，主键FileId，建立索引 create index DevInfoIndex on FilesInfo (CamID, SliceStartTime, SliceStopTime);表格除DevInfoIndex显示的字段外还有其他几个字段。目前表格有110左右条数据。

然后我们执行下面语句：

(1)

该SQL语句，对主键FileId进行排序，且只查找开始四条。而rows也是4，因为可以看到key中直接用的主键索引。

(2)我们再执行下一条语句：

 和上面比较，我们把排序改成了索引DevInfoIndex 的第一个字段。rows还是4，而key变化了，使用索引DevInfoIndex ，最后Extra显示使用了索引。

（3）接着我们继续执行：

 和上一条语句比较，我们只是修改排序的字段，SliceStartTime是索引的第二个字段。而rows变成了53。

从Extra看，虽然查找了索引，但是还用了filesort文件排序，也就是说明联合索引的数据先按第一个字段的顺序进行存储，后面的字段单独需要排序查找时，需要再对文件进行排序。

（4）继续

这次语句的变化主要是查找内容，*表示所有字段；排序字段还是索引的首个字段CamID，可是type变成了ALL，全表扫描；所以Extra显示filesort。

 

以上几步操作只是 对explain工具显示的结果的几个主要参数进行分析和展示。

下面再逐一进行介绍。

 

id： 查询的序列号；查询中执行select子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

select_type :  查询的类型，主要包括普通查询、联合查询、子查询。主要取值：

　　SIMPLE------简单的select查询,不使用union及子查询.

　　PRIMARY------最外层的select查询

　　UNION------UNION 中的第二个或随后的select查询,不依赖于外部查询的结果集

　　DEPENDENT UNION------UNION 中的第二个或随后的select查询,依赖于外部查询的结果集

　　SUBQUERY------子查询中的第一个select查询,不依赖于外 部查询的结果集

　　DEPENDENT SUBQUERY------子查询中的第一个select查询,依赖于外部查询的结果集

　　DERIVED------用于 from 子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里

　　UNCACHEABLE SUBQUERY------结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估

　　UNCACHEABLE UNION------UNION 中的第二个或随后的selec查询,属于不可缓存的子查询 

table：所访问数据库的表的名称

type：联合查询使用的类型显示的访问类型是重要指标，结果从好到坏：

　　system------系统表；表仅有一行(=系统表)。这是const连接类型的一个特例。

　　const------读常量；const用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用System。

　　eq_ref ------最多一条匹配结果，通常是通过主键访问；

　　ref------；连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。叫做ref是因为索引要 跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值。

　　fulltext------全文索引检索；

　　ref_or_null------；如同ref, 但是MySQL必须在初次查找的结果 里找出null条目,然后进行二次查找。

　　index_merge------合并索引结果集；说明索引合并优化被使用了。

　　unique_subquery------；在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)

　　index_subquery()------子查询返回的是索引，但非主键；在某些IN查询中使用此种类型，与unique_subquery类似,但是查询的是非唯一性索引：value IN (SELECT key_column FROM single_table WHERE some_expr)

　　range------索引范围扫描；只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时,可以使用range。

　　index------全索引扫描；全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

　　all------全表扫描；最坏的情况,从头到尾全表扫描。

 

possible_keys： 指出mysql能使用哪个索引有助于在该表中找到该行，如果这个值为空，则表示没有相关的索引。

key：显示mysql实际决定使用的建，如果没有索引被选择，键是null。

key_len：显示mysql决定使用索引的长度，在不损失精确性的情况 下,长度越短越好。如果key是空，则长度就是null。

ref：显示哪个字段或常数与key一起使用。如果使用了索引则显示const，否则，显示null。

rows：MYSQL 认为必须检查的用来返回请求数据的行数。

Extra： 出现以下2项意味着MYSQL根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

　　Using filesort------表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。

　　　　　　　　　　MySQL 中无法利用索引完成的排序操作称为“文件排序”。

　　Using temporary------表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

 

以上对命令explain及其参数的意义基本介绍完成。希望以后对于SQL语句的性能分析，是否需要优化，做到心中有数。