mysql explain的用法

MySQL的EXPLAIN命令显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
一、通过expalin可以得到
1、表的读取顺序
2、表的读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

二、如何调用expalin命令

1、只需要在SELECT前面加上EXPLAIN即可，例如

explain select surname,first_name form a,b where a.id=b.id 

2、在语句结尾(;之前)加上\G能够更清晰的查看.
3、需要说的是EXPLAIN只对SELECT查询作解释,INSERT,UPDATE,DELETE不会

三、explain显示的列
1、id
语句的执行顺序标识，如果在语句中没有子查询或联合,说明只有一个SELECT,于是这个列显示为1,否则内层的SELECT会顺序编号.

2、select_type
显示了对应的查询是简单还是复杂SELECT，主要有以下几种查询类型

1)、simple 简单类型
语句中没有子查询或union

2)、primary
最外层的select ，不是主键
这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。

3)、union
union是在select 语句中第二个select语句后面所有的select，第一个select 为primary
这是UNION 语句其中的一个SQL 元素。

4)、dependent subquery
子查询中内层中第一个select语句
这个select-type 值是为使用子查询而定义的。下面的SQL语句提供了这个值： mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

5)、dependent union
子查询中union且为union中第二个select开始的后面所有select，依赖于外部的结果集。

6)、SUBQUERY
子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。

7)、devived
派生表的查询语句
当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的
示例：

 mysql> EXPLAIN SELECT MAX(id)
 -> FROM (SELECT id FROM users WHERE first = 'west') c;

8)、uncacheable subquery
结果集无法缓存的子查询

9)、union result
union中合并的结果
这是一系列定义在UNION 语句中的表的返回结果。当select_type 为这个值时，经常可以看到table 的值是<unionN,M>， 这说明匹配的id 行是这个集合的一部分。下面的SQL产生了一个UNION和UNION RESULT select-type：

mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE 'a%' 
-> UNION 
-> SELECT p.* FROM parent p WHERE p.id > 5;

**3、table **

1)、显示对应行正在访问哪个表
2)、当FROM子句中有子查询或UNION时,table列是<derivedN>,其中N是id列对应的值

**4、type **
联合查询所使用的类型，type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

这列很重要,显示了连接使用了哪种类别,有无使用索引。主要包括以下几种类型

1)、all
全表扫描，效果是最不理想的。
2)、const
const是在where条件以常量作为查询条件，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
3)、eq_ref
最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。一般会出现在连接查询的语句中。
4)、fulltext
进行全文索引检索。
5)、index
全索引扫描。MySQL在扫描表时按索引次序进行而不是行。
6)、index_merge
查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
7)、index_subquery
子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
8)、rang
索引范围扫描。一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行(显而易见的范围扫描.即带有BETWEEN或在WHERE子句中带有>的查询,当MySQL使用索引去查找一系列值的时候,如IN()和OR列表，也为显示的范围扫描)
9)、ref
也叫索引查找,他返回所有匹配某单个值的行,它可能会找到多个符合条件行。
10)、ref_or_null
与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
11)、system
系统表，表中只有一行数据；
12)、unique_subquery
子查询中的返回结果字段组合是主键或唯一约束。

5、possible_keys
这一列显示了查询可以使用哪些索引,是基于查询访问的列和使用的比较操作符来判断的.
如果没有任何索引可以使用，就会显示成null

6、key
显示了MySQL决定采用哪个索引来优化对该表的访问

7、key_len
1)、key_len列显示mysql决定使用的键长度，如果键是null，则长度为null。
2)、显示MySQL在索引里使用的字节数.举个例子就是在查询中使用到了主键,而主键的数据类型为INT,则为4,SMALLINT则为2
3)、使用的索引长度，一般越短越好。

key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
此列的一些示例值如下所示： 此列的一些示例值如下所示：
key_len: 4 // INT NOT NULL
key_len: 5 // INT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NULL CHARSET=utf8

从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了 如何以最佳方式使用带有定义好的表索引的SQL 语句：

CREATE TABLE `wp_posts` (  
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',  
 `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,  
 `post_type` varchar(20) NOT NULL DEFAULT 'post',  
 PRIMARY KEY (`ID`),  
 KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)  
) DEFAULT CHARSET=utf8  
  
 CREATE TABLE `wp_posts` (  
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',  
 `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,  
 `post_type` varchar(20) NOT NULL DEFAULT 'post',  
 PRIMARY KEY (`ID`),  
 KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)  
) DEFAULT CHARSET=utf8  

这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01'; 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的 最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：

mysql> EXPLAIN SELECT ID, post_title  
-> FROM wp_posts  
-> WHERE post_type='post'  
-> AND post_status='publish'  
-> AND post_date > '2010-06-01';  

在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date 三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索 引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。 相关的QEP 列还包括带有Using index 值的Extra 列。

8、Ref
显示了之前的表在key列记录的索引中查询值所用到的列或常量
。

9、rows
显示的是MySQL为了找到所需的值而要读取的行数.

10、extra
在此显示的是在其他列不适合显示的额外信息
，主要可能会是以下内容：
1)、Distinct
查找distinct 值，当mysql找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。

2)、Full scan on NULL key
子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。

3)、Using index
表优化器只需要通过访问索引就可以获取到需要的数据

4)、Using where
表示优化器需要通过索引回表查询数据(需要通过访问数据块获取结果集)

5)、Using temporary
意味着MySQL在对查询结果排序时会用到一个临时表.
这个值表示使用了内部临时(基于内存的)表。一个查询可能 用到多个临时表。有很多原因都会导致MySQL 在执行查询期间 创建临时表。两个常见的原因是在来自不同表的列上使用了 DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列。 想了解更多内容可以访问http://forge.mysql.com/wiki/Overview_ of_query_execution_and_use_of_temp_tables。 可以强制指定一个临时表使用基于磁盘的MyISAM 存储引 擎。这样做的原因主要有两个：  内部临时表占用的空间超过min(tmp_table_size，max_ heap_table_size)系统变量的限制  使用了TEXT/BLOB 列

6)、Using filesort
看到这个的时候，查询就需要优化了。意味着MySQL会对结果使用一个外部索引排序,而不是按索引次序从表里读出来.
这是ORDER BY 语句的结果。这可能是一个CPU 密集型的过程。 可以通过选择合适的索引来改进性能，用索引来为查询结果排序。详细过程请参考第4 章。

7). Using join buffer
这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。 如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。
这篇博客中有Using join buffer的详细说明
https://my.oschina.net/xinxingegeya/blog/495897

这篇博客中有join的详细说明
http://blog.csdn.net/tonyxf121/article/details/7796657

8). Impossible where
这个值强调了where 语句会导致没有符合条件的行。请看下面的示例： mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

9). Select tables optimized away
这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

10). Index merges
当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
 Using sort_union(...)
 Using union(...)
 Using intersect(...)

11). Using where, Using index .....
说明该查询有部分数据需要通过回表方式获取有部分数据可以直接通过索引获取