MySQL(五)-explain

一、作用

explain可以模拟优化器执行SQL语句，从而知道mysql是怎么执行你的SQL语句的

二、语句

explain SQL
explain select * from user;

三、字段分析

1、id

1. 如果是连接查询，id相同，执行顺序由上至下
2. 如果是子查询，id互不相同，id值越大，优先级越高，会被优先执行
3. 如果id相同和不同同时存在，优先级高的先执行，优先级相同的顺序执行

所以，id决定表的读取和加载顺序

2、select_type

常见如下六种

1. SIMPLE

普通查询，不包含子查询和union

2. PRIMARY

包含子查询，最外层部分为primary，就是from 后

3. SUBQUERY

子查询部分

4. DERIVED

在from表中包含的子查询会被标记为DERIVED，数据会被保存在临时表中

5. UNION

UNION后的select表，若union出现在子查询中，最外层的表会被标记为DERIVED

6. UNION RESULT

多表UNION后的结果表

所以，select_type标识数据读取操作的操作类型

3、type

显示查询使用了何种类型
查询从最好到最差：
system>const>eq_ref>ref>range>index>ALL

• system：表中只有一条数据，const的特例
• const：通过索引一次就查询到了，一般常见primary和unique，如将主键=xx置于where条件
• eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之对应
• ref：非唯一性索引，返回匹配某个索引的全部行，如查询部门的所有程序员（职务是索引），或者联合索引，只用了其中的一部分
• range：检索给定范围的行，只使用了一个索引，id between 10 and 20
• index：只遍历了索引树，如select key from t
• ALL：扫描了全表，select * from t

一般业务中，能优化到range就非常不错了，尽可能优化到ref

4、key、possible_keys、key_len

• possible_keys：表示会用到的索引，但不一定实际使用，如联合索引，只用了其中一部分
• key：表示实际使用的索引，常常用来判断索引失效(NULL表示没有使用索引)，查询过程中若使用了覆盖索引，那么该索引仅出现在key中

覆盖索引：查询字段与索引字段一致，select key from table，select col1,col2 from table col1和col2是联合索引

• key_len：表示索引使用的字节长度，但实际可能并没有使用到，尽量做到保证索引长度小且搜索精度高；用了一个条件肯定是没有使用两个条件精度高的

5、ref

显示索引引用了什么常数或者哪些列
如上，t1.col1=t2.col1，ref显示引用了shared库的t2表的col1(shared.t2.col1），已经const(常数 t1.col2=‘ac’)

6、rows

每张表有多少行被优化器查询

7、Extra

表示无法被其他列表示的其他信息

• Using filesort：使用文件排序，无法通过表内索引进行排序，而在外部对索引进行了排序

select col1,col2 from t1 where col1>2 group by col2
col1和col2是联合索引，但是只根据col2进行分组

• Using temporary：使用临时表保存中间结果。常见于order by 排序和group by

select col1,col2 from t1 where col1 in('x','y','z') group by col2

前两种最有效的解决方式就是按索引顺序进行分组

select col1 from t1 where col1 ='x' group col1,col2

• Using index：表示相应查询中使用了覆盖索引，避免访问表的数据行

如果同时出现Using where 表示索引被用来进行索引键值的查找

select col1,col2 from t1 where col1=2

如果没有出现，表明索引用来读取，并没有执行查找

select col1,col2 from t1