MySQL索引优化，explain详细讲解

MySQL索引优化，explain详细讲解
前⾔：这篇⽂章主要讲 explain 如何使⽤，还有 explain 各种参数概念，之后会讲优化
⼀、Explain ⽤法
模拟Mysql优化器是如何执⾏SQL查询语句的，从⽽知道Mysql是如何处理你的SQL语句的。

分析你的查询语句或是表结构的性能瓶颈。

语法：Explain + SQL 语句;
如：Explain select * from user; 会⽣成如下 SQL 分析结果，下⾯详细对每个字段进⾏详解
⼆、id
是⼀组数字，代表多个表之间的查询顺序，或者包含⼦句查询语句中的顺序，id 总共分为三种情况，依次详解
id 相同，执⾏顺序由上⾄下
id 不同，如果是⼦查询，id 号会递增，id 值越⼤优先级越⾼，越先被执⾏
id 相同和不同的情况同时存在
三、select_type
select_type 包含以下⼏种值
simple
primary
subquery
derived
union
union result
simple
简单的 select 查询，查询中不包含⼦查询或者 union 查询
primary
如果 SQL 语句中包含任何⼦查询，那么⼦查询的最外层会被标记为 primary
subquery
在 select 或者 where ⾥包含了⼦查询，那么⼦查询就会被标记为 subQquery，同三.⼆同时出现
derived
在 from 中包含的⼦查询，会被标记为衍⽣查询，会把查询结果放到⼀个临时表中
union / union result
如果有两个 select 查询语句，他们之间⽤ union 连起来查询，那么第⼆个 select 会被标记为 union，union 的结果被标记为 union result。

它的 id 是为 null 的
四、table
表⽰这⼀⾏的数据是哪张表的数据
五、type
type 是代表 MySQL 使⽤了哪种索引类型，不同的索引类型的查询效率也是不⼀样的，type ⼤致有以下种类
system
const
eq_ref
ref
range
index
all
system
表中只有⼀⾏记录，system 是 const 的特例，⼏乎不会出现这种情况，可以忽略不计
const
将主键索引或者唯⼀索引放到 where 条件中查询，MySQL 可以将查询条件转变成⼀个常量，只匹配⼀⾏数据，索引⼀次就找到数据了
eq_ref
在多表查询中，如 T1 和 T2，T1 中的⼀⾏记录，在 T2 中也只能找到唯⼀的⼀⾏，说⽩了就是 T1 和 T2 关联查询的条件都是主键索引或者唯⼀索引，这样才能保证 T1 每⼀⾏记录只对应 T2 的⼀⾏记录
举个不太恰当的例⼦，EXPLAIN SELECT * from t1 , t2 where t1.id = t2.id
ref
不是主键索引，也不是唯⼀索引，就是普通的索引，可能会返回多个符合条件的⾏。

range
体现在对某个索引进⾏区间范围检索，⼀般出现在 where 条件中的 between、and、<、>、in 等范围查找中。

index
将所有的索引树都遍历⼀遍，查找到符合条件的⾏。

索引⽂件⽐数据⽂件还是要⼩很多，所以⽐不⽤索引全表扫描还是要快很多。

all
没⽤到索引，单纯的将表数据全部都遍历⼀遍，查找到符合条件的数据
六、possible_keys
此次查询中涉及字段上若存在索引，则会被列出来，表⽰可能会⽤到的索引，但并不是实际上⼀定会⽤到的索引
七、key
此次查询中实际上⽤到的索引
⼋、key_len
表⽰索引中使⽤的字节数，通过该属性可以知道在查询中使⽤的索引长度，注意：这个长度是最⼤可能长度，并⾮实际使⽤长度，在不损失精确性的情况下，长度越短查询效率越⾼
九、ref
显⽰关联的字段。

如果使⽤常数等值查询，则显⽰ const，如果是连接查询，则会显⽰关联的字段。

tb_emp 表为⾮唯⼀性索引扫描，实际使⽤的索引列为 idx_name，由于 tb_='rose'为⼀个常量，所以 ref=const。

tb_dept 为唯⼀索引扫描，从 sql 语句可以看出，实际使⽤了 PRIMARY 主键索引，ref=db01.tb_emp.deptid 表⽰关联了 db01 数据库中 tb_emp 表的 deptid 字段。

⼗、rows
根据表信息统计以及索引的使⽤情况，⼤致估算说要找到所需记录需要读取的⾏数，rows 越⼩越好
⼗⼀、extra
不适合在其他列显⽰出来，但在优化时⼗分重要的信息
using fileSort（重点优化）
俗称 " ⽂件排序 " ，在数据量⼤的时候⼏乎是“九死⼀⽣”，在 order by 或者在 group by 排序的过程中，order by 的字段不是索引字段，或者select 查询字段存在不是索引字段，或者 select 查询字段都是索引字段，但是 order by 字段和 select 索引字段的顺序不⼀致，都会导致fileSort
using temporary（重点优化）
使⽤了临时表保存中间结果，常见于 order by 和 group by 中。

USING index（重点）
表⽰相应的 select 操作中使⽤了覆盖索引（Coveing Index）,避免访问了表的数据⾏，效率不错！如果同时出现 using where，表明索引被⽤来执⾏索引键值的查找；如果没有同时出现 using where，表⾯索引⽤来读取数据⽽⾮执⾏查找动作。

Using wher
表明使⽤了 where 过滤
using join buffer
使⽤了连接缓存
impossible where
where ⼦句的值总是 false，不能⽤来获取任何元组
select tables optimized away
在没有 GROUPBY ⼦句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执⾏阶段再进⾏计算，查询执⾏计划⽣成的阶段即完成优化。

distinct
优化 distinct，在找到第⼀匹配的元组后即停⽌找同样值的⼯作
下篇⽂章讲讲如何优化 MySQL 索引。