一文看懂MySQL中explain关键字的作用

explain干嘛的？

explain（执行计划）：可以模拟 优化器 执行SQL查询语句，并不会去真正的执行这条SQL，从而知道 MySQL 是如何处理你的SQL语句的。可用来分析你的查询语句或是表结构的性能瓶颈。

作用

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

如何使用？

使用方法非常简单，主要是要看的懂每个关键字的含义，下面将逐一的介绍每个关键字的作用。

explain + SQL语句

1、id

含义：select 查询的系列号，表示查询中执行 select 子句或操作表的顺序

id常见的3种情况

1、id相同，执行顺序从上到下

2、id不同，如子查询，id的序号会递增，id的值越大优先级越高，越先被执行

3、id相同不同都有，id值越大越先被执行，相同从上到下顺序执行

来看一个小小的案例吧
判断下列这个SQL的执行顺序

答案：执行顺序为：4 ⇒ 3 ⇒ 2 ⇒ 1 ⇒ null

2、select_type

含义：表示该条SQL查询的类型，如子查询、联合查询等，常见值如下：

• simple
  简单的 select 查询，查询中不包含子查询或 union
• primary
  查询中若包含任何的子查询，最外层的查询被标记为 primary
• subquery
  在 select 或 where 中包含子查询
• derived
  在 from 后包含的子查询被标注为 derived（衍生），MySQL 会递归执行这些子查询，把结果放在临时表里
• union
  若第二个 select 出现在 union 之后，则被标记为 union，若 union 包含在 from 后面的子查询中，外层的 select 将被标记为：DERIVED
• union result
  从 union 表获取结果的 select
• update
  更新
• insert
  插入
• delete
  删除

3、table

含义：表示用到了哪几张表，若出现了 derived 的情况，则表示产生了中间表，常见产生中间表的情况有子查询和联合查询等。

4、type

type 显示的是访问类型，较为重要，结果值从最好到最坏依次为：system > const > eq_ref > ref > range > index > all，一般来说，最好保证达到 range 级别，最好达到 ref

4.1、type的取值

• system：
  表中只有一行记录（等同于系统表），这是const 类型的特列，平时不会出现，可以忽略不计

• const：
  表示通过索引一次就查找到了这条记录，用于比较 primary key 或者 unique 索引，因为只匹配一行数据，所以很快
  如：将主键置于where后面，MySQL就能将该查询转换为一个常量

• eq_ref：
  唯一索引扫描，对于每个索引建，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

• ref：
  非唯一索引扫描，返回匹配某个单独值的所有行，本质也是一种索引访问，它返回某个匹配值的多行数据

• range：
  只检索指定范围的行，使用一个索引来选择行，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引

• index：
  full index scan，index 与 all 的区别为 index 类型只遍历索引树，这通常比 all 快，因为索引文件通常比数据文件小，也就是说 index 和 all 虽然都是读全表，但index 是从索引中读取的，而all使用硬盘中读取

• all：
  full table scan，将遍历全表以找到匹配的行

5、possible_keys

显示可能应用在这张表中的索引，一个或多个，但不一定实际用到，实际用到的在key上显示

6、key

显示实际用到的索引，每张表的一次查询只会用到一个索引

7、key_len

显示索引字段中使用的最大可能的字节数，并非实际的长度，可通过该参数计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好，

8、ref

显示哪些索引列或常量被引用了，优先 const，

9、rows

根据表统计信息及索引选用情况，大致估算出所优化的行数，越少越好

10、extra

包含不适合在其他列显示但又十分重要的额外信息

10.1、常见值

• Using filesort
  文件内排序，即MySQL无法利用索引列进行排序，从而导致性能低下
  一般出现在排序列未建索引或索引截断的情况，效率差
  常见索引截断的情况（以下情况索引顺序都为：col1、col2、col3）：
  1、直接匹配col2
  … order by col2
  2、中间某一列没使用，导致中间截断
  … where col1=“xxx” order by col3
  3、使用范围查找导致索引截断
  … where col1=‘xxx’ and col2>xxx order by col3

• Using temporary
  使用了临时表保存中间结果，对查询结果排序时使用了临时表，常见于排序 order by 和分组查询 group by。效率很差
  

• using index
  表示相应的select操作中使用了 覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
  如果同时出现using where，表明索引被用来执行索引键值的查找;
  如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

• Using where
  表明使用了where过滤

• Using join buffer
  使用了连接缓存，

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

• distinct
  优化 distinct 操作，在找到第一匹配的元组后即停止同样值的操作
  若有不当之处，望大佬指出！！！