explain详解

MYSQL explain详解

explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

先解析一条sql语句：

EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address

FROM uchome_space AS s,uchome_spacefield AS f

WHERE 1 

AND s.groupid=0

AND s.uid=f.uid

1、id

select 识别标识符。这是select 查询序列号，这个不重要，查询序列号即为sql语句执行的顺序，继续看下边这条sql语句

explain select * from（Select * from uchome_space limit 10）as s

他的执行结果：

2、select_type

select 类型，他有以下几种值

2.1 simple 他表示简单的select ，没有union和子查询

2.2 primary 最外面的select ，在有子查询的语句中，最外面的select 查询就是primary，

2.3 union union语句的第二个或者说是后面那一个，现执行一条语句，

explain select * from uchome_space limit 10 union select *from uchome_space limit 10,10

结果为：

2.4 dependent union union 中的第二个或者后面的select语句，取决于外面的查询

2.5 union result union 的结果如上所示

3、table

输出的行所用的表，这个参数显而易见，容易理解

4、type

连接类型，有多个参数，先从最佳类型到最差类型介绍

4.1 system

表示仅有一行，这是const类型的特例，平时不会出现，和这个也可以忽略不计

4.2 const

表最多有一行匹配，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，记住一定是用到primary key或者unique，并且只检索出两条数据的情况下才会是const，看下面这条语句

explain select * from ‘asj_admin_log’ limit 1

结果是：

虽然只搜索一条数据，但是因为没有用到指定的索引，所以不会使用const，继续看看下面这个

explain select * from ‘asj_admin_log’ where log_id=111;

log_id是主键，所以使用了const，所以说可以理解为copnst是最优化的；

4.3 eq_ref

对于er_ref的解释，mysql手册是这样说的：“对于每个来自前面的表的行组合，从该表中读取一行。这可能是最好的连接类型，除了const类型。他用在一个索引的所有部分被联接使用并且索引是unique或者primary key”. er_ref可以使用=比较呆索引的列，看下面语句

explain select *from uchome_spacefield ,uchome_space where uchome_spacefield.uid=uchome_space.uid,得到结果：

4.4 ref

对于每个来自于前面的表的行组合，所有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是unique或primary key ，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

EXPLAIN select * from uchome_space where uchome_space.friendnum=0

得到结果如下，这条语句能搜出1w条数据。

4.5 ref_or_null

该联接类型如同ref，但是添加了mysql可以专门搜索包含NULL值得行。在解决子查询中经常使用该联接类型的优化。

4.6 index_merge

该联接类型表示使用了索引合并优化方法，在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

4.7 unique_subquery

4.8 index_subquery

4.9 range

给定范围内的检索，使用一个索引来检查行。看下面两条语句

explain select * from uchome_space where uid in(1,2)

explain select * from uchome_space where groupid in(1,2)

uid有索引，groupid没有索引，结果是第一条语句的联接类型是range，第二个是ALL，以为是一定范围，所以像between也可以这样联接，很明显

explain select *from uchome_space where friendnum=17

这样的语句是不会使用range的，它会使用更好的联接类型就是上面介绍的ref。

4.10 index

该联接类型与ALL相同，除了只有索引树被扫描，这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读取的）

当查询只使用作为单索引部分的列时，Mysql可以使用该联接类型。

4.11 ALL

对于每个来自于优先前的表的行组合，进程完整的表扫描。如果标识第一个没标记const的表，这通常不好，并且通常在它情况下很差，通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或值被检索出。

5、possible_keys 提示使用哪个索引会在该列表中找到行，不太重要

6、keys mysql 使用的索引，简单且重要

7、key_len mysql使用的索引长度

8、ref ref列显示使用哪列或常数与key一起从表中选择行。

9、rows 显示mysql 执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引。

10、extra 该列包含mysql解决查询的详细信息

10.1 distinct mysql发现第一个匹配行后，停止为当前的行组合搜索更多的行

10.2 Not exists

10.3 range checked each record

没有找到适合的索引。

10.4 using filesort

mysql手册是这么解释”mysql 需要额外的一次传递，以找出如何安排顺序检索行，通过根绝联接类型浏览所有行并为所有匹配where子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行“

10.5 using index

只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。

explain select * from uspace_uchome where uid=1的extra为using index(uid建有索引)

explain select * from uspace_uchome where uid=1的extra为using index(groupid 未建立索引)

10.6 using temporary

为了解决查询，mysql需要创建一个临时表来容纳结果，典型情况如查询包含客户以按不同情况列出列的group by和order by子句时.出现using temporary就说明语句需要优化。

10.7 using where

where 子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果extra值不为using where 并且表示联接类型为ALL或index，查询可能会有一些错误。

10.8 using sort_union(), using union（…）,using intersect(…)

这些函数说明如何为index_merge联接类型合并索引扫描

10.9 using index fro group-by

类似于访问表的using index方式，using index for group-by 表示mysql发现了一个索引，可以用查询group by或distinct查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便于对每个组，只读取少量索引条目。