Mysql Explain超实用中文手册

简介：整理了Explain较偏于手册版的文章，让小伙伴们既知道Expain的好处，干什么用的，也知道它的语法和具体使用过程中，Expailn所输出的QEP都代表了什么，如你接触过本篇博客，并基于Explain来优化了SQL查询，且提升了不错的性能，可以联系我，咱们合力一起力推最好的文章送给所有开发者们。

一.Explain的作用:

     如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

  我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

二.语法：

    1、EXPLAIN TableName 

     即DESCRIBE TableName 或 SHOW COLUMNS FROM TableName的一个同义词。

    2、EXPLAIN [EXTENDED] SELECT select_options

     如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。

三.表格参数详解

id	SELECT识别符。这是SELECT的查询序列号(id值越大，优先级越高，越先执行)
select_type	SELECT类型,可以为以下任何一种:（简单OR复杂） SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT,查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:从UNION表获取结果的SELECT SUBQUERY:子查询中的第一个SELECT,在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的 UNCACHEABLE:意味着select中的某些 特性阻止结果被缓存于一个item_cache中。
table	输出的行所引用的表（记住一点即可：explain输出的每一行所在的哪个表查询到的信息）
type	表示MySQL在表中找到所需行的方式，又称“访问类型”: null，system，const，eq_ref，ref，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL（按照从最佳类型到最坏类型进行排序） NULL:MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。。 system:表仅有一行(系统表)。这是const联接类型的一个特例。 const: 表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! 如将主键置于where列表中，MySQL就能将该查询转换为一个常量(system和const是性能最佳的类型) eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行。索引范围扫描,索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的 index(Full Index Scan):该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。 ALL(Full Table Scan):对于每个来自于先前的表的行组合,进行完整的表扫描。遍历全表以找到匹配的行
possible_keys	指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref	显示使用哪个列或常数与key一起从表中选择行。
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含MySQL解决查询的详细信息(十分重要) Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序” Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。该值表示相应的select操作中使用了覆盖索引（Covering Index） Using temporary:表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询 Using where:表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。 Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不用额外搜索硬盘访问实际的表。 Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 Impossible where：这个值强调了where语句会导致没有符合条件的行。 Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行.

四.补充：

覆盖索引（Covering Index）

MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件

包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

聚簇索引(Clustered Index)

聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

五.例子：

mysql > explain select * from t1 where name='atlas';

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

| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |

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

|  1 | SIMPLE      | t1    | ref  | name          | name | 60      | const |    1 | Using index condition |

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

row in set (0.03 sec)

六.总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

七.参考：

     1.SQL之EXPLAIN语法详解(1)  http://database.51cto.com/art/201108/285710.htm

      2.mysql explain用法和结果的含义  http://blog.chinaunix.net/uid-540802-id-3419311.html

      3.EXPLAIN命令详解   http://www.cnblogs.com/gomysql/p/3720123.html(推荐学习)