EXPLAIN分析查询语句
使用explain关键字可以模拟优化器执行SQL查询语句，从而指导MySQL是如何处理你的SQL语句，分析你的查询语句或是表结构的性能瓶颈
EXPLAIN select * from table
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
三个查询列刚好覆盖了所有的索引列且顺序一致
使用了覆盖索引后，该索引只存在于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 条件索引下推
练习一下
9381
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
