Explain说明

Explain关键字介绍

        使用ecplain关键字可以模拟优化器执行SQL语句，分析你的查询语句或结构；在 select 语句中使用 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL；

        注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中；

        测试表如下：

索引分别为actor：PRIMARY KEY (`id`)PRIMARY KEY (`id`)

                  film：PRIMARY KEY (`id`), KEY `idx_name` (`name`)

                  film_actor：PRIMARY KEY (`id`), KEY `idx_film_actor_id` (`film_id`,`actor_id`)

 接下来结合一些sql语句来说明explain的常见用法；

每查询一张表会得到一行结果，如果通过join连接查询，会根据join语句得到>2行的解析结果；      

Explain中每一列展示的信息：

 1. id列

        id的编号是select的序列号，id也表示了select语句执行的优先级，id越大，执行的优先级就越高，id相同则从上到下依次执行，id为null的优先级最低；

 2. select_type列和table列

        1. simple：简单查询。查询不包含子查询和union

        2. primary：复杂查询中最外层的 select

        3. subquery：包含在 select 中的子查询（不在 from 子句中）

        4. derived：包含在 from 子句中的子查询。查询结果存放在一个临时表中，也称为派生表;

接下来用一个例子来解释primary，subquery，derived的含义；

        table列可以理解为正在查询的那一张表； 

        首先执行的是select * from film where id = 1，为查询中from之后的子查询，其次执行的是select 1 from actor where id = 1，这条查询为from之前的子查询，之后执行的是最外层的查询，对应的select_type是primary，tanle是，即在衍生表中查询出结果，3为衍生表所在表对应的id值（衍生表实在film中查询而出的）；

3. type列

        表示关联类型或者访问的类型，即MySQL决定如何查找表中的行，查找行记录的大致范围。

        从优到差的效率依次为：system>const>eq_ref>ref>range>index>all;

Null：mysql可以在分析优化阶段就可以得到查询的结果，在执行阶段不必去真正的去访问表或者索引。如：在索引列中选最小值，可以单独查找索引完成，在执行不需要去访问数据库中的表；

 const，system：mysql可以对查询的一部分进行优化并且可以将其转化为一个常量，用primary key 或 unique key 的所有列与常数比较时，表中最多只有一个匹配的结果行，只读取一行，速度很快。system是const中特殊的存在，表示查询的表中只有一条数据。

 eq_ref：primary key 或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的结果行，简单的查询不会出现eq_ref这种type；

 ref：不使用primary key 或 unique key索引，而是使用普通索引或者使用primary key 或 unique key索引的部分前缀，索引和一个值相比较，会匹配出>1的结果行；

        （1）使用普通索引的简单select查询；

         （2）关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor表的的联合索引中左边前缀film_id部分。

 range：范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。

index：扫描全索引就会得到结果，一般是扫描某一个二级索引，这种扫描一般不会从索引树的根节点开始查找，二是直接对二级索引的叶子节点遍历和扫描，速度相对来所较慢，这种索引一般是为了使用覆盖索引，二级索引是比主键索引要小的，所以这种查询通常比ALL全表扫描快一些；

ALL：即全部扫描，扫描聚簇索引的所有叶子节点。查询速度非常慢，一般需要进行优化；

4. possible_keys列

这一列表示Mysql分析查询可能会使用索引，但不是绝对的，这只是一个理论的分析；

5. key列

这一列表示Mysql实际采用哪个索引来优化对该表的访问，如果没有使用索引，则该列是 NULL。

6. key_len列

这一列表示Mysql在索引中实际使用的字节数，可以通过这个值计算Mysql实际使用了那些索引。举例来说明，flim_actor的联合索引idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，每个int占4个字节，通过显示的结果key_len=4可以推断出这条查询只使用了一个int值，说明只使用了联合索引的第一个字段查询出结果列。

key_len计算规则如下：

• 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

• • char(n)：如果存汉字长度就是 3n 字节
  • varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

• 数值类型

• • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节　　

• 时间类型　

• • date：3字节
  • timestamp：4字节
  • datetime：8字节

• 如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

7. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）， 字段名（例：film.id）

8. rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

9. Extra列

（1）Using index：使用覆盖索引                                                                                                           覆盖索引：Mysql的执行计划中使用了索引，查询的结果可以从这个索引的索引树中获取，这种情况一般是使用了覆盖索引，；覆盖索引一般针对的是辅助索引，查询通过辅助索引就可以得到想要的结果，不需要通过主键再去主键索引中查找，即不需要回表。

（2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

（3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

（4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

（5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

（6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段。