mysql explain是什么_MySQL Explain详解

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。(QEP：sql生成一个执行计划query Execution plain)

mysql> explain select * from servers;

+----+-------------+---------+------+---------------+------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+------+---------------+------+---------+------+------+-------+

| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |

+----+-------------+---------+------+---------------+------+---------+------+------+-------+

1 row in set (0.03 sec)

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：

一、 id

含义

select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id的情况有三种，分别是：

id相同表示加载表的顺序是从上到下。

id不同id值越大，优先级越高，越先被执行。id有相同，也有不同，同时存在。

id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

实战

第一步、预备工作(创建部门表、员工表)

第二步：分别分析一下id的不同情况。

1、id相同的情况

小总结：id相同，表示加载表的顺序是从往下，从上图看出，首先加载dept表，再加载emp表。

2、id不相同的情况

小总结：id不相同，id的值越大，优先级越高越先被执行。从上图可以看出，首先是执行的是id=2的这一行，也就子查询，执行的是表dept，然后再执行emp表。

3、id存在相同，也存在不同，同时存在。

小总结：这个就是id存在相同，也存在不同，是同时存在的。首先可以把它们分为两组，id相同的为一组，id不同的为一组，这两组中，id的值越大的越先执行，首先执行的id=2的这一行，去加载emp表，然后，再去执行id=1的组，id相同，从上往下执行，那么执行的应该是“”，这个“derived”表示的是衍生的意思，而其中的“2”表示的是id=2的这一行。

二、select_type

显示查询中每个select子句的类型

(1)SIMPLE

简单的SELECT语句(不包括UNION操作或子查询操作)

(2)PRIMARY/UNION

PRIMARY:查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION)

UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句没有依赖关系)

(3)DEPENDENT UNION/UNIOIN RESULT

DEPENDENT UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句有依赖关系)

UNION RESULT:UNION操作的结果,id值通常为NULL

(4)SUBQUERY/DEPENDENT SUBQUERY

SUBQUERY:子查询中首个SELECT(如果有多个子查询存在):

DEPENDENT SUBQUERY:子查询中首个SELECT,但依赖于外层的表(如果有多个子查询存在)

(5)DERIVED/MATERIALIZED

DERIVED:被驱动的SELECT子查询(子查询位于FROM子句)

MATERIALIZED:被物化的子查询

(6)UNCACHEABLE SUBQUERY/UNCACHEABLE UNION

UNCACHEABLE SUBQUERY:对于外层的主表,子查询不可被物化,每次都需要计算(耗时操作)

UNCACHEABLE UNION:UNION操作中,内层的不可被物化的子查询(类似于UNCACHEABLE SUBQUERY)

三、table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |

| 2 | DERIVED | | system | NULL | NULL | NULL | NULL | 1 | |

| 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

四、type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有：ALL, index,  range, ref, eq_ref, const, system, NULL(从左到右，性能从差到好)

ALL：Full Table Scan， 全表扫描

index: Full Index Scan，索引全扫描，index与ALL区别为index类型只遍历索引树

range: 索引范围扫描，只检索给定范围的行，使用一个索引来选择行，常用语=,between等操作

ref: 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

NULL: MySQL不访问任何表或索引，直接返回结果

五、possible_keys

该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一项内容对于优化时候索引的调整非常重要；

六、Key

MySQL Query Optimizer 从possible_keys 中所选择的实际使用的索引；

如果没有选择索引，键是NULL。

要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的)

不损失精确性的情况下，长度越短越好。

八、ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

九、rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra

十一、总结

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的，并非精确值

• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。