MySQL性能优化 —— Explain

程序员对性能优化都不陌生，今天来复习一下 Explain，顺便写个随手笔记，方便后续参考。

Explain（查看执行计划）

1. 首先这个东西是什么？

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。
分析你的查询语句或是表结构的性能瓶颈

2. 如何使用？

很简单，explain + SQL 语句
例如： EXPLAIN SELECT * FROM test_article WHERE views = '2';

来看一下执行之后的结果：

先对结果的所有列有一个简单的认知，下面进行更加详细的介绍；

3. 针对上面的结果，我们可以看出来什么？也就是 Explain 能干嘛？

①可以看到表的读取顺序  —>  看ID值，越大优先级越高
②数据读取操作的操作类型  —> 看select_type查询类型 
③哪些索引可以使用   —> 看 possible_keys
④哪些索引被实际使用   —> 看 key
⑤表之间的引用  —> 看 ref
⑥每张表有多少行被优化器查询  —> 看 rows

4. Explain 之 ID 说明

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

包含三种情况：
a：ID 相同：执行顺序由上至下（table 列可以看到对应的执行表顺序，先执行了哪张表，后执行了哪张表）
b：ID 不相同，ID 的序号会递增，ID 的值越大，优先级越高
c：ID 相同、不同(如下图)，相同的可以看成为一组，从上至下执行，ID 越大的越先执行（此时的执行顺序是，2->1->1）

5. Explain 之 select_type 说明

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
常用六种形式：

Simple  -> 简单的 select查询，查询中不包含子查询或者 union
Primary   ->  查询中若包含任何 复杂的子部分，最外层查询则被标记为
Subquery  -> 在 select 或 where 列表中包含了子查询
Derived  -> 在 from 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放到临时表里。
Union  -> 若第二个 select 出现在 union 之后，则被标记为 union；若 union 包含再 from 子句的子查询中，外层 select 将被标记为：DERIVED
Union result  -> 从 union 表获取结果的 select

6. Explain 之 table 说明

显示这一行的数据是关于哪张表的

7. Explain 之 type 说明

访问类型排列，显示了查询使用了何种类型。
共有8种类型：All、index、range、ref、eq_ref、const,system、null

从最好到最差依次是： system -> const -> eq_ref -> ref -> rang -> index -> ALL

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

8 种类型的意义：

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

8. Explain 之 possible_keys 说明

显示可能应用再这张表中的索引，一个或者多个。查询设计到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

9. Explain 之 key 说明

实际使用的索引。如果为 NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在 key 列表中。

10. Explain 之 key_len 说明

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

11. Explain 之 ref 说明

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

12. Explain 之 rows 说明

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
值越小，说明效率越高！

13. Explain 之 Extra 说明（1、2、3 项重点）

包含不适合在其他列中显示，但十分重要的额外信息。主要有以下几种：

1、Using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作，称之为“文件排序”；

出现这种情况时，需要及时优化，九死一生现象！！！

2、Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时，使用临时表。常见于排序 order by 和分组查询group by.

出现这种情况，十死无生，火烧眉毛，必须优化！！！

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

覆盖索引：也可以说是索引覆盖。

就是 select的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，换句话说->查询列要被所建的索引覆盖。
如果要使用覆盖索引，一定要注意select 列表中的只取出需要的列，不可 select * ，因为如果将所有字段一起做索引会导致文件过大，查询性能下降。

4、Using where : 表明使用了 where 过滤

5、Using join buffer : 表明使用了连接缓存

6、impossible where : where子句的值总是 false，不能用来获取任何元素

7、select tables optimized away :  在没有group by 子句的情况下，基于索引优化min\max 操作或者对于myisam存储引擎优化 count(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8、distinct : 优化 distinct 操作，在找到第一匹配的元素后即停止找同样的值的动作

本篇文章主要介绍 Explain 的用法，以及各个字段的含义及说明，掌握好基础知识，后面在进行优化的时候，可以轻车熟路的处理。

下一篇，性能优化实战！！