mysql explain使用

使用EXPLAIN关键字可以模拟查询优化器执行SQL查询语句，从而知道MySQL数据库是如何处理SQL语句，以及应该从哪个方面对SQL语句进行优化

作用

• 查看表的读取顺序
• 数据读取的操作类型
• 哪些索引可以使用
• 实际使用了那些索引
• 表之间的引用关系
• 有多少行数据被优化器查询

 

各字段解释：

1.id

 select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序，该值一共有三种情况

a.id相同：执行顺序由上至下,如下图所示执行顺序 t1 -> t3 -> t2

b.id不同：值越大优先级越高，越先被执行,如下图所示执行顺序  t3-> t1-> t2

c.id如果相同 可以认为是一组，从上往下顺序执行

在所有组种 id值越大  优先级越高 越先执行

2.select_type

SIMPLE : 简单查询，不包含子查询或UNION
PRIMARY :主查询，该查询中包含子查询，最外层的查询被标记为PRIMARY
SUBQUERY : 子查询
DERIVED:    在FORM中包含的子查询会被标记为DERIVED（衍生）+ID，MySQL会递归执行这些子查询，并将结果集保存在临时表中
UNION :   联合查询。若第二个SELECT出现在UNION会被标记为UNION。若UNION包含在FROM的子查询中，则外层SELECT会被标记为DERIVED
UNION RESULT:    从UNION表获取结果的SELECT

3.table

4.type(重要指标)

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL ，

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

system    表只有一条记录（系统表），这是const类型的特列，平时不会出现，可以忽略不计
const       表示通过索引一次就查询成功，const用于比较primary key或者unique索引。因为只匹配一行索引，所以快。例如将主键置于where条件中，则MySQL数据库能将该查询转换为常量
eq_ref      唯一性索引扫描，对于每个索引，表中只有一条记录匹配，常见于主键或唯一索引扫描
ref          非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是索引访问，返回匹配值的所有行，它会找到符合条件的多条数据，属于查找和扫描的混合体
range      只检索给定范围的行，使用索引来选择行，key列显示使用了哪个索引，一般会出现在between、<、>、in等查询。这种范围扫描索引比全表扫描好，它开始于索引的一点，结束于另一点，而不是扫描全部索引。
index    全表索引扫描。index与all的区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小（也就是说，index和all都是读取全表，但index是从索引中读取，而all是从硬盘中读取）
all    全表扫描。遍历全表数据查找匹配的行

type 为system 和 const 实例：

eq_ref  实例：

ref 实例：

range实例：

index 实例：

 5.possible-keys

   显示可能应用到的索引，一个或多个。如果查询语句where条件的字段上存在索引，则该索引会被列出，但不一定在查询中被实际使用

6.key

实际使用的索引，若为null，则没有使用索引。

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

7.key_len 

 表示索引中使用的字节数，在不损失精确性的情况下，长度越短越好。它表示索引字段的最大可能长度，并非实际使用长度。它是根据表定义计算而得，不是通过表内检索得出的。

8 .ref

显示索引的那一列被使用了，如果可能的话，是一个常量，表示查找索引列上的值 

 如下图所示 t1 type为ref  那ref引用了：t2.col1（t1.col1 = t2.col1）和一个常量(t1.col2 = 'ac')

9.rows 

根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数，理论上越少越好

 未见索引查询：

见索引后的查询：

10.extra

包含不适合在其他列中显示但十分重要的额外信息。
Using filesort（重要，需要优化）:    文件内排序。MySQL会对数据使用外部索引排序，而不是按照表内索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为”文件排序“（order by字段不走索引会出现这种情况，速度会很慢）

建的索引是 col1_col2_col3

如下方式优化即可：

 
Using temporary（重要，需要优化）:    使用临时表保存中间结果，MySQL将查询结果保存在临时表中，极大降低系统性能，常见于order by 和group by中

优化如下：group by 后面的字段一定要跟你 建索引的字段 顺序一样 

Using index（重要，最好出现此字符）:    该操作使用了覆盖索引，避免访问数据行，如果同时出现using where，表示索引被用来进行查找（有where语句），如果没有出现using where，表示索引被用来读取数据而非进行查找（无where语句）

using where:    使用where条件过滤
impossible where:    where字句的值是false，啥也查不到（id=3 and id=5）
select tables optimized away（了解）:    在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于M有ISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再计算，查询执行计划生成的阶段即完成优化
distinct:    优化distinct操作，在找到第一匹配的数据后即停止继续查找

注意如下重点：

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据