explain

explain是什么？能干什么？

    explain命令是查询优化器如何决定执行查询的主要方法。通过explain可以帮助我们了解mysql优化器是如何工作的。要使用explain,只需要在查询语句前加explain这个词，mysql会在查询上设置一个标记。当执行查询时，这个标记会使其返回关于在执行计划中每一步的信息，而不是执行它。它会返回一行或多行信息，显示出执行计划中的每一部分和执行的顺序。下面是一个可能的最简单的explain结果：

explain select 1\G

    在查询中每个表在输出中只有一行。如果查询是两个表的联接，那么输出中将有两行。别名表单算为一个表，因此，如果把一个表与自己联接，输出中也会有两行。“表”的意义在这里相当广：可以是一个子查询，一个union结果，等等。
    认为增加explain时mysql不会执行查询，这是一个常见的错误。事实上，如果查询在from子句中包括子查询，那么mysql实际上会回执行子查询，将其结果放在一个临时表中，然后完成外层查询优化。这意味着如果语句中包含开销较大的子查询或者使用临时表算法的视图，实际上会给服务器带来大量工作。
    要意识到explain只是个近似结果，别无其他。有时候它是一个很好的近似，但在其他时候，可能与真相相差甚远。以下是一些相关的限制：

• explain根本不会告诉你触发器、存储过程或UDF会如何影响查询。
• 它并不支持存储过程，尽管可以手动抽取查询并单独地对其进行explain操作。
• 它并不会告诉你mysql在查询执行中所做的特定优化。
• 它并不会显示关于查询的执行计划的所有信息(mysql开发者会尽可能增加更多信息)。
• 它并不区分具有相同名字的事务。例如，它对内存排序和临时文件都用"filesort",并且对于磁盘和内存中的临时表都显示"Using temporary"。

explain中的列字段详解

id

这一列总是包含一个编号，表示select所属的行。如果在语句中没有子查询或联合，那么只会有唯一的select,故每一行在这个列中都将显示一个1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置。注：mysql将select查询分为简单和复杂类型，复杂类型可分为三大类：简单子查询、所谓的派生表(在from子句中的子查询)，以及union查询。
总结下来就是：如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。
我们来看几个例子：
1. id相同，执行顺序由上往下

上图查询的执行顺序是:t1、t2、t3
2. id不同，id值越大执行顺序的优先级越高上图查询的执行顺序是:t3、t1、t2(如果是子查询,id的序号会递增,id值越大优先级越高，越先被执行)
3. 以上两种情况同时存在(id的值既有相同的也有不同的)

上图的执行顺序是:t3、t1、t2(id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行)。

select_type

这一列显示了对应行是简单还是复杂select。

select_type	描述
SIMPLE	简单的select查询，查询中不包含子查询或者union
PRIMARY	如果查询有任何复杂的子部分，则最外层部分标记为primay
SUBQUERY	包含在select列表中的子查询中的select(换句话说,不在from子句中)标记为subquery
DEPENDENT SUBQUERY	子查询中的第一个select，依赖于外部查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行
DERIVED	用来表示包含在from子句中的子查询中的select。mysql会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的。
UNION	在union中的第二个和随后的select被标记为union。如果union被from子句中的子查询包含，那么它的第一个select会被标记为drived
DEPENDENT UNION	union中的第二个或随后的select语句，取决于外面的查询
UNION RESULT	用来从union的匿名临时表检索结果的select被标记为union result

table

这一列显示了对应行正在访问哪个表。

type

访问类型——换言之就是mysql决定如何查找表中的行。

访问类型	描述
ALL	全表扫描，通常意味着mysql必须扫描整张表，从头到尾，去找到需要的行。(这里也有个例外，例如在查询里使用了limit,或者在Extra列显示“Using distinct/not exits”)
index	这个跟全表扫描一样，只是mysql扫描表时按索引次序进行而不是行。它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将会非常大。如果在Extra列中看到“Using index”,说明mysql正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少很多。
range	只检索给定范围的行，使用一个索引来选择行。key列显示了使用了哪个索引，一般就是在where语句中出现了between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
ref	这是一种索引访问(有时也叫索引查找)，它返回所有匹配某个单个值的行，然而，它可能找到多个符合条件的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。ref_of_null是ref之上的一个变体，它意味着mysql必须在初次查找的结果里进行第二次查找以找出null条目。
eq_ref	使用这种索引查找，mysql知道最多只返回一条符合条件的记录。这种访问方法可以在mysql使用主键索引或者唯一性索引查找时看到，它会将它们与某个参考值做比较。mysql对于这类访问类型的优化做的比较好，因为它知道无须估计匹配行的范围或在找到匹配行后再继续查找。
const	当mysql能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型。举例来说，如果你通过将某一行的主键放入where子句里的方式来选取此行的主键，mysql就能把这个查询转换为一个常量。然后就可以高效地将表从联接执行中移除。
system	system是const类型的特例，当查询的表只有一行记录的情况下，使用system，平时不会出现，这个也可以忽略不计。
NULL	这种访问方式意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。

结果值从最好到最坏依次是：
NULL>system>const>eq_ref>ref>ref_or_null>range>index>ALL

possible_keys

这一列显示了查询可以使用哪些索引，这是基于访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的(不一定被查询实际使用)。

key

这一列显示了mysql决定采用哪个索引来优化对该表的查询。如果该索引没用出现在possible_keys列中，那么mysql选用它是出于另外的原因——例如，它可以选择了一个覆盖索引，哪怕没有where子句，换句话说，possible_keys揭示了哪一个索引能有助于高效地行查找，而key显示的是优化采用哪一个索引可以最小化查询成本。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len使根据表定义计算而得，不是通过表内检索出的。

ref

显示了key列记录的索引中查找值所用的列或常量,即哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出所需的记录所需要读取的行数。

Extra

这一列包含的是不适合在其他列显示的额外信息。
名词解释：
覆盖索引：
通常大家都会根据查询的where条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件的部分，索引确实是一种查询数据的高效方式，但是mysq也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为"覆盖索引"。

关键字	描述
Using index	此值表示mysql将使用覆盖索引，以避免访问表。注：不要把覆盖索引和index访问类型弄混了。
Using where	mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的俩，当它读取索引时，就能被存储引起检验，因此不是所有带where子句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益于不同的索引。
Using temporary	使用了临时表保存中间结果，mysql在对查询结果排序时会使用一个临时表。常见于排序order by和分组查询group by。
Using filesort	mysql会对结果使用一个外部索引排序，而不是按照索引次序从表里读取行。mysql中无法利用索引完成的排序称为"文件排序"。
Using join buffer	该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where	where子句中的值总是false,不能用来获取任何元组。
Select tables optimized away	在没有group by子句的情况下，基于索引MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
Range checked for each record(index map:N)	这个值意味着没有好用的索引，新的索引将在联接的每一行上重新估算。N是显示在possible_keys列中索引的位图，并且是冗余的。