MySQL高级性能优化---Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
通过EXPLAIN，我们可以分析出以下结果：
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

1. Explain使用方法

explain sql语句
示例: EXPLAIN select * from employee WHERE cus_id = 2

2. 分析包含信息

2.1 id

select查询的序列号
包含一组数字，当sql复杂的时候会有多个数字，表示查询中执行select子句或操作表的顺序

id值的三种情况:

1. id相同

EXPLAIN select * from employee e, department d WHERE e.dep_id = d.id

id相同时执行顺序由上而下

2. id不同

EXPLAIN SELECT * from department where id = (SELECT id from employee WHERE id = 4)

如果是子查询，id序号会递增，id值越大优先级越高，优先被执行
3. id相同不同，同时存在
可以认为是一组，从上往下执行。在所有组中，id值越大优先级越高，越先执行。deriverd衍生出来的虚表

EXPLAIN SELECT * from employee e,
(SELECT * from department GROUP BY id) t
where e.dep_id = t.id

总结：
相同，顺序从上往下走。不同看谁大，谁大谁先执行。大的先执行

2.2 select_type

作用: 查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询
结果值

2.2.1 SIMPLE

简单select查询，查询中不包含子查询或UNION

2.2.2 PRIMARY

查询中若包含任何复杂的子查询，最外层查询被标记为PRIMARY

2.2.3 SUBQUERY

查询中的子查询

2.2.4 DERIVED

在from列表中包含的子查询被标记为DERIVED(衍生)，把结果放在临时表中

2.2.5 UNION， UNION RESULT

从union表获取结果select，两个union合并的结果集的最后

2.3 table

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

2.4 partitions

如果查询是基于分区表的话，会显示查询访问的分区

2.5 type（重点）

type代表访问表的类型，分辨全表的扫描还是条件的扫描还是范围的扫描等

type结果值讲解顺序以最好到最差的顺序讲解

2.5.1 system

表中有一行记录(系统表)，一次性就找到了这个记录，就会出现system类型。 这是const类型的特例，平时不会出现

2.5.2 const

表示通过索引一次就找到了。const用于比较primary或者unique索引。直接查询主键或者唯一索引。因为只匹配一行数据，所以很快。

2.5.3 ref (查询的结果是多行不是唯一对应)

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问。它返回所有匹配某个单独值的行。可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

下边的dep_id列创建的普通索引 （一个普通索引，另一个主索引）

2.5.4 eq_ref（查询的结果唯一对应）

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

（两个建都是唯一索引或主键）

2.5.5 range

范围查找
一般就是你在where语句中出现 between > < in等查询时会出现，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点和结束于另一点，不用扫描全部索引

2.5.6 index

查询索引。 我们以主键，普通索引，普通字段来举例
index与all区别为index类型只遍历索引树，通常比all要快，因为索引文件通常比数据文件要小。all和index都是读全表，但index是从索引中读取，all是从硬盘当中读取

2.5.7 ALL

全表扫描。当出现all的时候一定要对sql进行优化

2.6 possible_keys 理论应该用到的索引

2.7 key（重要） 实际用到的索引

2.6 possible_keys 和 2.7 keys 一起讲解

key与possible_keys 主要作用是查看是否使用了建立的索引，也即判断索引失效，在建立多个索引的情况下，mysql最终用到了哪一个索引。
实际使用的索引，如果为NULL,则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。possible_keys与key的关系是理论应该被用到的索引和实际用到了哪些索引。
覆盖索引：查询的字段和建立的字段刚好吻合，这种情况我们称为覆盖索引

2.8 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度
可以使用key_len来判断索引是否失效。当查询的条件是索引，但是key_len是null时表示索引失效

2.9 ref

索引是否被引用到，到底用了哪几个索引，引用了哪个表的哪个字段

2.10 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。每张表有多少行被优化器查询过。根据建立索引和没有建立索引的情况不同

2.11 fitered

满足查询的记录数的比例，注意是百分比，不是具体记录数。值越大越好，fitered列的值依赖统计信息，并不十分准确。

2.12 Extra

2.12.1 Using filesort（需要优化）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行排序，mysql中无法利用索引完成排序操作成为文件排序。出现这个情况需要优化

2.12.2 Using temporary（需要优化）

使用了临时表保存中间结果，Mysql在对查询结果排序时，使用了临时表，常见于排序order by和分组查询group by

2.12.3 using index

出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错！

2.12.4 using where

这说明服务器在存储引擎收到行后将进行过滤。有些where中的条件会有属于索引的列，当它读取使用索引的时候，就会被过滤，所以会出现有些where语句并没有在extra列中出现using where这么一个说明。

2.12.3 using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

2.12.4 impossible where（需要优化）

where子句的值永远是false，不能用来获取任何数据

SELECT * FROM t_user WHERE id = '1' and id = '2'