mysql优化之explai

来源：互联网发布：下载官方手机短信软件编辑：程序博客网时间：2024/06/05 10:24

explain的语法为：explain <table>。

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

返回的字段分别解释如下：

id：表示查询的序号，这条mysql语句里只有一次查询，因此序号为1；

select_type：表示查询的类型，simple表示简单查询。select_type有多种结果可能，下面会做详细的介绍。

table：这个较好理解，即查询结果行所在表名称。注意，如果查询没有命中任何表，此处为NULL。

type：联接类型。这里不太容易理解，可以认为是对查询语句另一个角度的类型划分。需要说明的是，这个type只有查询语句执行后才能判断结果，而select_type则是从字面语法上对查询语句的分类。和select_type一样，会在下面做详细的介绍。

possible_keys：指mysql可能使用到的索引名称，而这里列出的索引并不一定会实际使用到。

key：mysql实际使用的索引，通过key和possible_keys的比较，可以看到那些索引没有使用到，在此基础上也就可以进行相应的优化。

key_len：mysql使用的键的长度。在这里要解释一下，由于复合主键的存在，因此，一个索引里面会有多个列的存在。而key_len可以帮我们确认这个复合主键到底使用了哪些列。关于复合键，可以参考这篇文章：http://bbs.phpchina.com/blog-52440-186683.html

ref：表示与索引一起做查询条件的列。这个字段mysql官网并未做充分的说明，网上搜索也未找到资料，如果读者有了解的欢迎交流。

rows：mysql认定查询需要检查的行数。

Extra：一些提示性文本，表示该查询的详细信息。Extra字段有助于我们了解查询语句是否使用临时表、索引、文件排序等。

下面介绍select_type的种类：

simple：上面的例子返回的结果就是simple，只有一个简单的where。

primary：对于拥有子查询的sql语句，primary表示当前查询是最外面的查询。看下面的这个例子：

图1

这个sql语句由两个查询组成，最外层的对user表的查询以及里层对user_info表的查询。所以外层的查询select_type为primary。

union：union查询中的第二个查询，这很好理解，例子如下：

图 2

dependent union：子查询中的union查询的第二个查询，例子如下：

图3

union result：使用union查询的结果，如图2、图3所示。

subquery：子查询的第一个select，例子如下：

图4

dependent subquery：子查询中的第一个select。与subquery不同的是，dependent subquery表示mysql认为子查询与外面的查询发生了关联，对于 in子句里的查询结果，mysql会将其与外层表进行比较，看是否存在，存在则输出，不存在则不输出。可以参考官方文档的介绍：http://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html

derived：导出表的查询，如图5所示：

图5

下面介绍type字段的类型：

system：表示表仅有一行，为const的特殊情况。

const：表示最多命中一行。

eq_ref：表示一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

ref：联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话）。ref和eq_ref举例如图6：

图6

在图6中，ugc表有主键(id,name)，而user表有主键(id)。因此当联接为ugc.id时，为ref；联接为user.id时，为eq_ref。

ref_or_null：和ref类似，只是添加了对null的搜索。下面这个例子中，to_date字段是一个允许为null的索引字段。当使用where加or，且or的条件中有一个是is null时出现了 ref_or_null。

图13（这张图是后面补上的，所以下标为13）

index_merge：多个索引使用并集或者交集时，mysql可能会采用索引合并。这并不一定会发生，取决与mysql具体算法，该算法地址： http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#index-merge-optimization

简单的例子如下图

图7

unique_subquery：子查询中使用主键或unique索引时发生，可参看图1。

index_subquery：子查询中使用非唯一索引时发生，这个我也没找到例子（或者我的mysql并未对这种查询情况做优化？）。

range：这很好理解，当查询中出现选择范围的条件时，mysql会做range优化，如图8所示：

图8

index：扫描单个索引树，即要查询的列全部可以从索引中获取，不需要扫描原树数据表。如图9所示：

图9

all：扫描整个表，效率最低的联接。例子在上面的一些图中能找到。

下面介绍Extra字段的种类：

Distinct：一旦找到匹配行，就不继续搜索了。

not exists：mysql对left join的优化，当left join找到匹配行时，不再查找join表中另外的行。如图10所示：

图10

在图10中，ugc表的name是不允许为null的，在这里只要扫描了ugc的一行就知道ugc表中不可能存在"ugc.name is null"的行，因此此处mysql标记为not exists。

range checked for each record：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，

MySQL检查是否可以使用range或index_merge访问方法来索取行。如图11所示：

图11

在图11中，photo_id是photo表的一个索引，而ugc.id是一个范围查询，且这个范围里photo.id是photo表的唯一索引。也就是说，通过第一个查询条件photo.photo_id得到的行里含有可以被再次利用的索引photo.id，mysql这时会考虑使用range或者index_merge来获取数据。

using filesort：官方解释：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

在我看来，即指需要排序再输出时，可能需要filesort，一个例子如图12所示：

图12

using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。例子可参考图9。

using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。例子可参考图12。

using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。例子图11。

using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。例子为图7。

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

需要注意的是，相同语句在相同表上的操作得到的explain结果并不一定就前后一致，取决于mysql优化器在分析表的当前物理存储等因素后作出的执行决定。

参考文章：

mysql官方explain文档：http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain

mysql explain详解：http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

说了这么多，对于explain还是算一知半解。欢迎看到这篇文章的朋友与我交流。