MySQL索引优化，explain详细讲解

前言：这篇文章主要讲explain如何使用，还有explain各种参数概念

一、Explain用法

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，如果是连接查询，则会显示关联的字段。

#1.tb_emp表为非唯一性索引扫描，实际使用的索引列为idx_name，由于tb_emp.name='rose'为一个常量，所以ref=const。

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

十、rows

根据表信息统计以及索引的使用情况，大致估算说要找到所需记录需要读取的行数，rows越小越好

十一、extra

不适合在其他列显示出来，但在优化时十分重要的信息

（1）using  fileSort（重点）

俗称 " 文件排序 " ，在order by或者在group by排序的过程中，order by的字段不是索引字段，或者select查询字段存在不是索引字段，或者select查询字段都是索引字段，但是order by字段和select索引字段的顺序不一致，都会导致fileSort

（2）using temporary（重点）

使用了临时表保存中间结果，常见于order by和group by中。

（3）USING index（重点）

表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

（4） Using where

表明使用了where过滤

（5） using join buffer

使用了连接缓存

（6） impossible where

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

（7） select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
查询执行计划生成的阶段即完成优化。

（8） distinct

优化distinct，在找到第一匹配的元组后即停止找同样值的工作

下篇文章讲讲如何优化MySQL性能

关注微信公众号：IT老哥

回复Java全套教程，即可领取：Java基础、Java web、JavaEE全部的教程，包括spring boot等

回复：简历模板，即可获取100份精美简历

回复：Java学习路线，即可获取最新最全的一份学习路线图

回复：Java电子书，即可领取13本顶级程序员必读书籍