详解EXPLAIN_

EXPLAIN分析查询语句

使用explain关键字可以模拟优化器执行SQL查询语句，从而指导MySQL是如何处理你的SQL语句，分析你的查询语句或是表结构的性能瓶颈

EXPLAIN select * from table
1

explain能干嘛？

• 表的读取书顺序（id）
• 查询使用了哪些查询类型子查询？连接查询等等？(select_type)
• 操作了那些表(table)
• 数据读取操作的操作类型（type）
• 哪些索引可能被使用（possible_keys）
• 哪些索引被实际使用（key）
• 索引可能使用的最大字节数(key_len)
• 表之间的引用（ref）
• 每张表有多少行被优化器查询(rows)

各字段解释

1.id

id:查询的顺序

三种情况

1.id相同都为1，自上向下的顺序

执行顺序：t1.t3,t2

2.id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行

执行顺序:t3,t1,t2

3.id如果相同，认为是同一组，从上到下顺序执行，在所有组中，id值越大，优先级越高，越先被执行

执行顺序：t3,t1,< derived2>

derived2中的2指的是id=2，即derived2表就是DERIVED衍生表

DERIVED = 衍生

2.select_type

select_type:查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

SIMPLE：简单的select查询，查询中不包括子查询或UNION

PRIMARY:查询中若包含子查询，最外层被标记为PRIMARY

SUBQUERY：被包含的子查询

DERIVED:（衍生表），在from列表中包含的子查询会被标记为DERIVED，MySQL会递归执行子查询，把结果放在临时表中

UNION：第二个select出现在union之后，会被标记为union，若union包含在from子句的子查询中，外层select将标记为：DERIVED

UNION RESULT：从union表获取结果的select

3.table

table：即使用的表

4.新增partitions

partitions:如果查询是基于分区表的话，会显示查询将访问的分区

5.type

type:访问类型

常见的访问类型如下

从最好到最差

system>const>eq_ref>ref>range>index>ALL

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

system：（系统表）表只有一行记录，这是const类型的特例，平时不会出现

const：（常量）表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

先查询t2，全表扫描t2，再查询t1,若t1表中只有一条记录与t2匹配，那么就是eq_ref访问类型

例：公司只有一个CEO，找出公司的CEO只有一条记录

ref:非唯一性索引扫描，返回匹配某个单独值的所有行,本质上也是一种索引访问，属于查询和扫描的混合体

例：研发部（单独值）有很多程序员（所有行）

包括spring容器的bean中的ref依赖注入也是类似思想

range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index:(Full Index Scan)index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)

id为主键，默认主键索引，也是全表扫描，但是查询id比其他非索引字段要快

ALL:全表扫描，效率最低

6.possible_keys

possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用

t2,t1表理论上都使用了primary主键索引，但是实际上只有t1表使用了主键索引

7.key

key:实际使用的索引，如果为NULL，则没有使用索引。

查询中若使用了覆盖索引，则该索引仅出现在key列表中

覆盖索引：假设表中创建了3个索引(col1,col2,col3)，在查询过程中

刚好查询了这三个字段且顺序与索引顺序一致，不可select * from，必须刚好一致，memory不支持覆盖索引

//创建索引
create index idx_col1_col2 on t2(col1,col2)
//查询
select col1,col2,col3 from mytable
1
2
3
4

三个查询列刚好覆盖了所有的索引列且顺序一致

使用了覆盖索引后，该索引只存在于key列表中

注：Extra中Using index表示使用了覆盖索引

这里possible_keys为null表示理论上没有索引，然而key却是PRIMARY，说明使用了覆盖索引

8.key_len

key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

key_len显示的值为最大可能长度，并非实际长度，即key_len是根据表定义计算而得，不是通过表内建所出的

第一次添加了一个条件where col1 = ‘ab’，key_len = 13

第二次添加了两个条件,key_len = 26

9.ref

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引上的值

where条件后面针对于t1表有两个条件

t1.col1 = t2.col1 and t1.col2 = 'ac’

对应ref属性下的shared.t2.col1和const

shared.t2.col1:shared数据库下的t2表的col1列

结合定义中的 显示索引的哪一列被使用了 理解

而where的第二个条件t1.col2 = ‘ac’，ac是常量，所以ref为const

由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’

10.rows

rows：找到所需记录所要读取的行数

未建索引前，找到所需记录所要读取的行数rows = 640+1行

而对t2表建立索引后,找到所需记录所要读取的行数变成了142+1行,使用索引提高了查找效率

11.新增filtered

filtered:返回结果的行占需要读到的行(rows列的值)的百分比，该值依赖于统计信息

12.Extra

Extra：（额外的，扩展的）包含不适合在其他列中显示但十分重要的额外信息

常见的额外信息有以下几种

①Using filesort：（危险，要优化）

• 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
• MySQL中无法使用索引完成的排序操作称为“文件排序”。
• MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。
• 简单理解就是内部索引失效，只能通过外部索引排序
• 最好不要出现Using filesort

第一条sql只对col3排序，出现了Using filesort，内部索引失效，需要进行优化，第二条sql对col2和col3一起排序，没有出现Using filesort，内部索引有效，

②Using temporary：（非常危险，必须优化）

• 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
• 由于使用了临时表，索引查询效率会变低，也浪费空间
• 不要出现Using temporary,会拖慢系统运行速度

第一条sql出现了Using temporary，排序时使用了临时表保存中间结果，第二条sql没有使用临时表，第二条sql性能比第一条好

③Using index：（good）

• 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，提高效率；
• 如果同时出现Using where，表明索引在where子句中实现
• 如果没有出现Using where，表明索引只是读取数据，没有where子句

④Using where：使用了where 子句

⑤using join Buffer ：使用了连接缓存

⑥impossible where：where子句的值总是false，不能用来获取任何元组。

例：where name = ‘张三’ and name = ‘李四’,结果不存在

⑦select tables optimized away：没有group by子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算。查询执行计划生成的阶段即完成优化。

⑧distinct：优化distinct操作，在找到第一匹配的元素后就停止找相同值的操作。

⑨Using index condition：查询的列不完全被索引覆盖，使用了condition index push 条件索引下推

练习一下