mysql之EXPLAIN优化分析

 一.EXPLAIN查看SQL执行计划

EXPLAIN语句解析

expain解析出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

1. id:选择标识符
2. select_type:表示查询的类型。
3. table:输出结果集的表
4. type:表示表的连接类型
5. possible_keys:表示查询时,可能使用的索引,如果为空,说明没有可用的索引。
6. key:key列显示MySQL实际决定使用的键（索引）,必然包含在possible_keys中,如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
7. key_len:索引字段的长度,可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好 
8. ref:列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
9. rows:扫描出的行数(MySQL根据表统计信息及索引选用情况,估算的行数)
10. Extra:执行情况的描述和说明

1.id

　　SELECT语句的标识符，一般为数字，表示对应的SELECT语句在原始语句中的位置。没有子查询或联合的整个查询只有一个SELECT语句，因此其id通常为1。在联合或子查询语句中，内层的SELECT语句通常按它们在原始语句中的次序进行编号。但UNION操作通常最后会有一个id为NULL的行，因为UNION的结果通常保存至临时表中，而MySQL需要到此临时表中取得结果。

1.1id 相同执行顺序由上到下，即下面sql表的执行顺序是t3,t1,t2.

 1.2如果是子查询，id序号会自增，id值越大优先级就越高，越先被执行。

1.3id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2.select_type 数据读取操作的操作类型

• SIMPLE(简单SELECT，不使用UNION或子查询等)
• PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
• UNION(若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED)
• UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
• SUBQUERY(在select或者where里包含了子查询)
• DERIVED(派生表的SELECT, FROM子句的子查询-衍生，mysql会递归这些子查询，把结果放到临时表里)
• UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
• DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
• DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

3.table 输出行所引用的表

4.type访问类型  由好到差system > const > eq_ref > ref > range > index > ALL 

链接类型	说明
system	表只有一行，MyISAM引擎。
const	表中至多有一个匹配的行，该行仅在查询开始时读取一次，因此，该行此字段中的值可以被优化器看作是个常量(constant)；当基于PRIMARY KEY或UNIQUE NOT NULL字段查询，且与某常量进行等值比较时其类型就为const，其执行速度非常快；
eq_ref	类似于const，表中至多有一个匹配的行，但比较的数值不是某常量，而是来自于其它表；ed_ref出现在PRIMARY KEY或UNIQUE NOT NULL类型的索引完全用于联结操作中进行等值(=)比较时； 这是除了system和const之外最好的访问类型；ref 　　　　查询时的索引类型不是PRIMARY KEY或UNIQUE NOT NULL导致匹配到的行可能不惟一，或者仅能用到索引的左前缀而非全部时的访问类型；ref可被用于基于索引的字段进行=或<=>操作；
fulltext	用于FULLTEXT索引中用纯文本匹配的方法来检索记录。
ref_or_null	与ref类似，但包括NULL
index_merge	使用“索引合并优化”的记录访问类型，相应地，其key字段(EXPLAIN的输出结果)中会出现用到的多个索引，key_len字段中会出现被使用索引的最长长度列表；将多个“范围扫描(range scan)”获取到的行进行合并成一个结果集的操作即索引合并(index merge)。
unique_subquery	在in子查询中，就是value in (select…)把形如select unique_key_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
index_subquery	类似于unique_subquery，但子查询中键值不惟一；
range	带有范围限制的索引扫描，而非全索引扫描，它开始于索引里的某一点，返回匹配那个值的范围的行；相应地，其key字段(EXPLAIN的输出结果)中会输出所用到的索引， key_len字段中会包含用到的索引的最长部分的长度；range通常用于将索引与常量进行=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或IN()类的比较操作中；
index	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描(full table scan)

5.Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

1. Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
3. Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
4. Using joinbuffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
5. Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
7. No tables used：Query语句中使用from dual 或不含任何from子句