mysql之explain使用详解

引言

explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句

EXPLAIN中的列介绍

1. id

id列的编号是select的序列号，有几个select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按照执行计划列从上往下执行，id为空则是最后执行

2. select_type

主要是用于区别普通查询、联合查询、子查询等的复杂查询

2.1 simple

不包含子查询和union的简单查询,如果查询中有任何复杂的子部分，则最外层部分标记为PRIMARY

2.2 primary

复杂查询中最外层的select

2.3 subquery

包含在select中的子查询（不在from的子句中）,下图展示primary和subquery类型

2.4 derived

包含在from子句中的子查询，mysql会将查询结果放入一个临时表中，此临时表也叫衍生表,服务器内部称其为派生表，因为该临时表是从子查询中派生来的

2.5 union

在union中的第二个和随后的select，UNION RESULT为合并的结果

2.6 union result

表示用来从UNION的匿名临时表检索结果的select

除了这些值，SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。DEPENDENT意味着select依赖于外层查

3. table

显示这一行的数据是关于哪张表的，通常是表名或者该表的别名

4. type

这是重要的列，显示连接使用了 何种类型，从最好到最差的连接类型为 const > eq_ref > ref > range > index > all ，一般来说，得保证查询至少达到 range级别，最好能达到 ref

4.1 NULL

MySQL能在优化阶段分解查询语句，在执行阶段不用再去访问表或者索引

4.2 system、const

表示通过索引 一次就找到了，表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数(MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）)，因为mysql先读这个值然后把它当做常数来对待, system是const的一个特例，表示表里只有一条元组匹配时为system

4.3 eq_ref

唯一性索引扫描，最多只返回一条符合条件的记录,常见于 主键或唯一索引扫描,简单的select查询不会出现这种type: 在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

4.4 ref

非唯一性索引扫描，只有当使用 非唯一性索引或者唯一性索引的 非唯一性前缀时才会发生,索引和某个值比较,返回匹配某个单独值的所有行，可能会找到多个符合条件的行,这个类型严重依赖于根据索引匹配的记录多少— 越少越好。ref_or_null是ref的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目

4.5 range

只检索 给定范围的行，比如使用带有 between或在 where子句里带有 >或 <， in查找东西时发生的情况，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引

4.6 index

索引全扫描：这个连接类型对前面的表中的每一个记录联合进行完全扫描（一般是扫描某个二级索引,比all更好，因为索引一般小于表数据），只是扫描时 按索引次序进行而不是行，主要 优点是避免了排序，最大 缺点是要承担按索引次序读取整个表的开销。如果在extra列中看到Using index，说明MySQL正在使用覆盖索引，只扫描索引的数据，而不是按索引次序的每一行，它比按索引次序全表扫描的开销要少很多

4.7 all

全表扫描,应该尽量避免,有个例外如使用了limit或者extra列中显示了Using distinct/not exists

5. possible_keys

显示可能应用在这张表中的索引,如果为空，没有可能的索引,可以通过检查where子句看是否可以添加一个适当的索引来提高性能.这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的,这个列是在优化过程的早期创建的，因此有些罗列出来的的索引可能对于后续优化过程是没用的

6. key

实际使用的索引:如果为NULL，则没有使用索引，很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引.MySQL决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中，那么MySQL选用它是出于另外的原因如可能选择了一个覆盖索引，哪怕没有where子句。换言之，possible_keys揭示了哪一个索引有助于高效行查找，而key显示的是优化采用的哪一个索引可以最小化查询成本

7. key_len

使用的索引的长度,在不损失精确性的情况下，长度越短越好.索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录

key_len计算规则：
字符串：
char(n)：n个数字或者字母占n个字节，汉字占3n个字节
varchar(n)： n个数字或者字母占n个字节，汉字占3n+2个字节。+2字节用来存储字符串长度。
数字类型：
tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节
时间类型
date：3字节 timestamp：4字节 datetime：8字节

8. ref

显示索引的哪一列被使用了，如果可能的话，是一个常数,常见的有const、字段名

9. rows

必须检查的用来返回请求数据的行数,这个数字是MySQL认为它要检查的行数, 这里不是结果集的行数

10. Extra

显示以上信息之外的其他信息

Distinct

MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行

Not exists

MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行

Using index for group-by

类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表

只有using index

意思就是此查询使用了覆盖索引(Covering Index)，查询的内容可以直接在索引中拿到
，即通过索引就能返回结果，无需访问表,若没显示"Using index"表示读取了表数据

只有using where

使用 where 语句来处理结果,表示MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”

同时有using where和using index

表明用到了索引，索引也覆盖了，using where只是过滤元组，和是否读取数据文件或索引文件没有关系

Using index condition

查询的列不完全被索引覆盖，where条件中是一个查询的范围

Using temporary

使用到临时表，为了解决查询,MySQL需要创建一个临时表来容纳结果，说明查询就需要优化了

Select tables optimized away

Select tables optimized away MySQL根本没有遍历表或索引就返回数据了，表示已经优化到不能再优化了

Using filesort

说明查询就需要优化了，表示MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。MySQL有两种文件排序算法，两种都可以在内存或磁盘上完成。但explain不会告诉你MySQL使用的是哪种，也不会告诉你排序是在内存还是磁盘上完成；

Range checked for each record（index map：N）

表示没有好用的索引，但发现如果来自前面的表的列值已知,可能部分索引可以使用，新的索引将在联接的每一行上重新估算。N是显示在possible_keys列中索引的位图，并且是冗余的；

Using union

表示MySQL运用了索引合并策略，索引合并策略有时是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕

Using sort_union(…), Using union(…), Using intersect(…)

这些函数说明如何为index_merge联接类型合并索引扫描

出现using filesort、using temporary, Using union,Range checked for each record（index map：N）,说明 影响系统的性能需要优化