Explain用法

在公司中，编写SQL语句，首先要先将需求写出来，不要管优化的事儿。运行一段时间后，查找出来SQL效率不高的语句，单独分析。数据库都有一个功能，叫做慢查询。可以开启慢查询，它就会在运行过程中，将执行速度比较慢的SQL语句给你展示出来，这个时候再针对这样的SQL语句进行分析，看慢在了哪里？

如何分析呢？ 使用 Explain 的关键字。

mysql> explain select * from tbl_dept  where id = 1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_dept | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

如何分析这个结果中的10个关键字

1、id

id:   select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序.
id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行

 

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

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

2、select_type 

查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询
SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY：在SELECT或WHERE列表中包含了子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT

3、table

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

如果是衍生表，一般是derivered+id作为表名。

以上面的SQL为例，video 的id 是2 , 所以 ID = 1 的 table 叫做 derived2

4、type（重点）

这个指标是说明我们这个SQL是好还是坏的一个非常重要的指标；

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

按照上面的顺序，SQL从 好变为坏。

最坏的就是 type = ALL 表示 SQL语句全表扫描了。

以上这个指标的值太多了，一般我们也见不到这么多的值，可以掌握如下几个即可：

system > const > eq_ref > ref > range > index>all 

一般来说，得保证查询至少达到range级别，最好能达到ref。 所以经常见的这几个值是 ref、range、index、all

System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计，单表单记录可以出现。

Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量，比如主键查询语句：

Select * from user where id=1

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

执行的顺序是：先对t2表进行一个全表的扫描查询，然后根据t2表中的id值查找与之对应的t1的数据。

以上SQL写的是否合理，取决于t2表是大表还是小表。

最好的结果是小表先查询，再查询大表，小表驱动大表。

t2 中的 type = ALL表示进行了全表扫描，如果这个t2 表的数据特别多，这个查询就非常的烂，如果t2的数据没有t1 的多，这个SQL就是一个好的SQL。

好比是Student 和 Course ,肯定课程表是小表，Student 表是大表。

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

 如果根据一个索引查找出来了一条数据，那么是eq_ref ,如果使用到了索引，但是查询出的结果可能是多条，ref.

------------------------------------------------

range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

Index: 称之为覆盖索引

Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

假如有一个表  user    name 和 phone 组合成了一个联合索引。
select * from  user ; // 这个肯定是全表扫描了，效果非常差。
select name,phone from user; // 这个时候使用到了索引的。  name和phone 都在索引库中，通过索引库就可以查找到所有的数据，不需要去 文件中查找数据，这个就称之为覆盖索引。

index类型指的是你要查找的数据刚好在索引库中存在，你不需要区数据库中查找了，这种sql就是index类型的。所以也给我们一个启发：写sql,用到什么字段就写什么字段，不要使用*。

 

ALL: Full Table Scan，将遍历全表以找到匹配的行,没有使用到索引。

总结：通过explain 进行SQL语句分析的时候，如果查看到 type 是ALL就说明这个SQL语句进行了全表扫描，需要优化（数据量比较大）。

eq_ref > ref > range > index > ALL

5、possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
比如 有可能用到   三个索引，实际可能使用到了一个。

6、key

实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引和查询的select字段重叠

7、key_len

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

长度的计算是一个非常复杂的过程，可以作为了解：

 长度的计算是一个复杂的算法：跟字符集有关系，跟数据类型，跟是否为varchar类型有关系，跟是否为null有关系。
以上面这个算法为例：
    name 列是索引列，  name="zhangsan"  那肯定用到了这个索引。
    这个长度是 78
因为name 是字符 ，由于我们的数据库采用的是utf-8  一个字符需要三个字节
    24 * 3 = 72
由于存放字符的是varchar 需要再加上2  ，如果 是char(24) 不需要+2
    72 + 2 = 74
再由于我们的这个name 不允许为null   ,所以不需要  + 1  ，如果这个字段允许为null   ,就需要长度 再加 +1

8、ref

索引被使用的具体情况，和key不太一样，key说明有没有用到索引，ref指的是使用索引的具体情况。

此处的ref 是一个字段，跟前面讲的ref 不一样，前面的type字段的一个值。

ref 表示使用到了哪些索引。key 表示使用到了索引，索引的具体情况没有说，需要查看ref 才能够知道。

9、rows

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

简单来讲，就是查询出来这一条数据，查询了多少条。

10、extra（重点） 

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

1）Using filesort （出现这个说明非常不好）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”
说白了就是你在进行order by 的时候没有使用到索引建立的梯子，中间有断层
    
首先我新建一张表t1,创建了一个复合索引：col1,col2,col3

2） Using temporary （出现这个说明非常不好）

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

 3）USING index 说明还可以，还不错

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

 

关于覆盖索引：
覆盖索引（Covering Index）,一说为索引覆盖。
理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

 4）Using where

表名使用了where过滤

5）using join buffer 使用了连接缓存

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

比如：select * from user where name=“张三” and name=”李四”

总结:

通过explain 进行sql分析的时候，其实就是查看这个大的SQL拆分以后，每一个小的SQL语句是如何执行的。
并且查看每一个小的SQL语句它的type类型 和 Extra 这两个字段的值
查看type类型是否为ALL
查看Extra 这个字段是否出现 filesort temporary