explain

两点重要提示

1. Explain结果是基于数据表中现有数据的。

2. Explain结果与MySQL版本有很大的关系，不同版本的优化器的优化策略不同。

1. 定位执行效率较低的sql 语句

　　通过两种方式可以定位出效率较低的sql 语句。
　　(1) 通过上篇讲的慢日志定位，在mysqld里写一个包含所有执行时间超过 long_query_time秒的sql语句的日志文件，后面具体介绍。
　　(2) 通过show processlist 实时定位线程状态，是否锁表等，下面简单演示下show processlist：

　　　　先模拟会话1表锁，再会话2更新该表city的数据，由于会话1表锁没有释放，会话2更新会一直会等待尝试去获取更新锁，再通过show processlist查看

-- 会话 1获取city 表锁
LOCK TABLE city READ;
-- 会话2更新city表
UPDATE city SET citycode='001'

　　查看发现: 状态列中找到waiting for table metadata lock(等待 table元数据锁)，当前线程的info 信息 如下所示：

 　　2. 通过explain 来分析sql执行计划

　　通过上篇的慢日志定位和processlist 找出效率低的sql语句后，可以通过explain或者desc命令获取mysql 如何执行查询语句的信息。

--  查看执行计划（二种方式一样）
 DESC SELECT   LedRecycleInfoLogID FROM LedLogInfo WHERE LedRecycleInfoLogID=2;
 EXPLAIN SELECT   LedRecycleInfoLogID FROM LedLogInfo WHERE LedRecycleInfoLogID=2;

一该命令作用：该命令会向您展示查询是如何被执行的。

   1、各个项的含义

   2、id表示查询序列号，三种情况。规则是：序号不同，则越大越先被执行；序号相同，则越在前面的越先被执行。大前原则。

   （1）查询序列号都相同。SQL的执行顺序从上到下。即先主键表，后外键表。这里是先查询村寨，再查询楼栋，最后查询房间。房间的外键是楼栋的主键，楼栋的外键是村寨的主键。

id 列的编号是 select 的序列号，有几个 select 就有几个id，并且 id 的出现顺序是按 select 出现的顺序增长的。

id 列越大执行优先级越高，id 相同则从上往下执行，id为NULL最后执行。

######################################################################################################

   （2）查询序列号都不相同。如果是子查询语句，则id序列号递增，id越大则越先执行。

    5、key字段。实际使用的索引，如果为NULL，则没有使用索引。

###########################################################

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

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

     我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时，执行顺序由上至下

2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

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

二、select_type

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

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

如下:

三、table

有可能是

 如select * from t1;

　　表的别名 如 select * from t2 as tmp;

　　derived 如from型子查询时(来自于子查询的派生表)

　　null 直接计算得结果,不用走表，例如select 1+2

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是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     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | 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， MySQL将遍历全表以找到匹配的行 ,逐行做全表扫描.,运气不好扫描到最后一行.  (说明语句写的很失败)

index: Full Index Scan，index与ALL区别为index类型只遍历索引树,相当于data_all index 扫描所有的索引节点,相当于index_all

range:只检索给定范围的行，使用一个索引来选择行,能根据索引做范围的扫描

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值,通过索引列,可以直接引用到某些数据行

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

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

const、system、NULL指查询优化到常量级别, 甚至不需要查找时间.

all

这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。

index

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。

所以说如果type为all或者index，都应对其进行优化。甚至我们可以将其理解相当于索引无效。

五、possible_keys

注意: 系统估计可能用的几个索引,但最终,只能用1个.

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

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

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

八、ref

　　表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows

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

有时候结果集中多一列filtered
为什么Mysql explain extended中的filtered列值总是100%
执行Mysql的explain extended的输出会比单纯的explain多一列filtered(MySQL 5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered的值一直是100%，也就是说失去了意义。

参考下面mysql 5.6的代码，filtered值只对index和all的扫描有效（这可以理解，其它场合，通常rows值就等于估算的结果集大小。）。

十、Extra

　　性能从好到坏:useing index>usinh where > using temporary | using filesort

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

useing  index代表索引覆盖，就是查询的列正好在索引中，不用回物理行查询数据。

执行Mysql的explain extended的输出会比单纯的explain多一列filtered(MySQL 5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered的值一直是100%，也就是说失去了意义。

filtered值只对index和all的扫描有效（这可以理解，其它场合，通常rows值就等于估算的结果集大小。）

总结：• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

Explain 作用

Explain 提供了 MySQL 如何执行 SQL 语句的信息，通过这些信息，可以对 SQL 语句做相应的优化，提高执行效率。
Explain 字段

调用 Explain 后，MySQL 会返回一行或者多行记录，通过这些记录就可以知道 SQL 语句的执行情况了

每行记录都包含了以下几个字段

    id：执行编号，标识 select 所属的行
    select_type：select 查询的类型
    table：查询的是哪个表
    partitions：匹配的分区
    type：关联类型，或者访问类型
    possible_keys：该查询可以选用的索引
    key：该查询选用的索引
    key_len：索引中使用的字节数
    ref：显示上述表的连接匹配条件，即哪些列或常量被用于查询索引列上的值
    rows：估计为了找到所需行而要读取的行数
    filtered：按表条件过滤的行的百分比
    Extra：额外的信息
Explain 字段详解
id
该列总是包含一个编号，标识 select 所属的行。如果语句当中没有子查询或联合查询，那么只会有唯一的 select，于是每一行在这个列中都将显示一个 1。否则，内层的 select 语句一般会顺序编号，对应于原始语句中的位置。
id 值越大，越先执行。
select_type
该列取不同值的执行效率依次是
ALL < index < range < ref < eq_ref < const < system < NULL
一般来说，至少要保证查询达到 range 级别，最好能达到 ref 级别。
possible_keys
该列显示了查询可以选用哪些索引，但是列出来的索引，可能对于后续优化过程是没有用的。
key
该列显示了查询选用了哪个索引，如果该索引没有出现在 possible_keys 列中，那么选用它可能出于另外的原因，比如，它可能选择了一个覆盖索引。
key_len
该列显示了在索引里使用的字节数，当 key 列的值为 NULL 时，则该列也是 NULL。如果正在使用的只是索引里的某些列，那么可以通过用该列的值算出来具体是哪些列。
ref
该列显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来。
rows
该列显示了估计要找到所需的行而要读取的行数，这个值是个估计值，原则上值越小越好。
filtered
该列表示根据条件过滤的表行的估计百分比，和 rows 相乘，表示和查询计划里前一个表关联的行数。
Extra
该列显示了有关 MySQL 如何解析查询的其它信息。
以下为常见的取值
    Using index：使用覆盖索引，表示查询索引就可查到所需数据，不用扫描表数据文件，往往说明性能不错。
    Using Where：在存储引擎检索行后再进行过滤，使用了 where 从句来限制哪些行将与下一张表匹配或者是返回给用户。
    Using temporary：在查询结果排序时会使用一个临时表，一般出现于排序、分组和多表 join 的情况，查询效率不高，建议优化。
    Using filesort：对结果使用一个外部索引排序，而不是按索引次序从表里读取行，一般有出现该值，都建议优化去掉，因为这样的查询 CPU 资源消耗大。
注意事项
Explain 不会告诉你触发器、存储过程或用户自定义函数对查询的影响情况；
Explain 不会告诉你 MySQL 在查询执行中所做的特定优化；
Explain 只是个近似结果；
Explain 不会显示关于查询的执行计划的所有信息。
###########################################

ID    SELECT命令的序号(通常为1，子查询的话往往从序号2开始)
###################################
select_type
SIMPLE       单纯的SELECT命令
PRIMARY      最外层的SELECT命令
UNION        由UNION语句连接的SELECT命令
DEPENDENT UNION   由UNION语句连接的SELECT命令(依赖外部查询)
SUBQUERY     子查询中的SELECT命令
DEPENDENT SUBQUERY 子查询中的SELECT命令(依赖外部查询)
DERIVED     派生表(FROM语句的子查询)
##################################
Table  表名
#############################
type
表的连接类型(按效率的高低排序)
system  只存在一条记录的表(=系统表)
const    常量，拥有PRIMARY KEY/UNIQUE制约的索引(结果总为1行)
eq_ref   连接时由PRIMARY KEY/UNIQUE列进行的等值查询
ref      非UNIQUE列进行的等值查询
ref_or_null     ref中加入了[~OR列名IS NULL]的检索
range     使用索引检查一定范围的记录(=,<>,>,>=,<,<=，IS NULL，<>,BETWEEN,IN等运算符)
index     全索引扫描
ALL       全表扫描
#########################
possible keys  检索时可能使用到的索引(不存在索引时为NULL)
key  检索时真实使用到的索引(未使用索引时为NULL)
key_len  使用的索引的关键字长度(单位为bytes)
Ref   需要时与比较的列，或者定制(const)
rows  需要遍历的记录数量
Extra  查询时的追加信息 (值为index时，使用了覆盖索引，性能最好)