Mysql之explain详解

1. explain作用

使用explain可以展示出sql语句的执行计划，再根据sql的执行计划去判断这条sql有哪些点可以进行优化，从而让sql的效率达到最大化。

2. 执行计划各列含义

（1）id：id列是select的序列号，这个sql执行过程中会执行几次select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按照执行计划列从上往下执行。

（2）select_type：该列表示查询的类型，是简单查询还是复杂查询。

1. simple：不包含子查询和union的简单查询。
   
2. primary：包含子查询的复杂查询中最外层的查询。
3. subquery：包含子查询的复杂查询中的子查询。
   从下面sql执行计划中可以看出外层查询user表的类型为PRIMARY，where后面的子查询roles表类型为SUBQUERY。
   
4. derived：衍生查询，查询临时表中的数据。
5. union：在union中的第二个和随后的查询。
6. union result：表示合并的结果，最后的extra列中的Using temporary表示会创建一个临时表来存储合并结果。
   从下面sql的执行计划可以看出，第一条的类型为PRIMARY，就是复杂查询中的最外层的查询。
   第二条的类型为DERIVED，即在from子句的子查询中的结果放到一个临时表中，也就是对r1表的查询结果会衍生出一个临时表，所以对r1表的查询类型为DERIVED。第三条是对r2表的查询，由于是在union的后面，所以查询类型为UNION。第四条则是对2，3条查询的合并结果当作最终的子查询结果并存放在衍生出来的临时表中。
   

（3）table：该列表示当前行访问的是哪张表，通常是表名或者该表的别名。

（4）partitions：该列表示查询将匹配记录的分区。 对于非分区表，该值为 NULL。

（5）type：该列表示关联类型或访问类型。该列的值决定了这条sql的查询性能，从最优到最差分别为：system > const > eq_ref > ref > range > index > all。

1. system ：表示整个表中只有一条记录，这种情况几乎不会出现。
2. const ：表示整个表中通过该字段查找只有唯一的一条记录，一般会出现在主键索引或者唯一索引的字段。
   
3. eq_ref ：一般是关联查询的时候，主表用于关联的索引字段在被关联的副表中有且只有唯一一条记录。被关联的副表字段一般为主键或者唯一索引字段。
   
4. ref ：一般是使用普通索引进行查询，查询的结果会存在多个符合条件的记录。
   
5. range ：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
   
6. index ：从创建的索引文件中扫描全部索引数据，通常比ALL快一点。
   
7. all：在磁盘从头到尾的扫描全表数据来找到所需要的数据，查询速率最差。
   

（6）possible_keys：该列表示在查询中可能用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到。如果该列为NULL，则表示没有相关索引。

（7）key：该列表示真正使用到的索引。

（8）key_len：该列表示sql查询语句中索引使用到的字节数，这个字节数并不是实际的长度，而是通过计算查询中使用到的索引中的长度得出来的，显示的是索引字段最大的可能长度。key_len是越小越好。

（9）ref：该列表示在key列记录的实际使用的索引中，表查找值时使用到的列或常量。常见的有const、字段名。

（10）rows：该列表示估算的要扫描的行数，注意这个并不是实际结果集的行数。

（11）Extra：该列表示是sql查询的额外信息，主要有以下几种情况：Using index、Using where、Using temporary、Using filesort、Impossible where、Select tables optimized away、Using index condition。

1. Using index：表示查询的列被索引覆盖，索引被正确的使用，想要查询的信息在索引里面可以找到，不用再回表查询，这个是查询性能比较高的体现。
   
2. Using where：表示查询的列并没有被索引覆盖，where条件后面使用的是非索引的前导列，它仅仅是使用了where条件而已。
   
3. Using temporary：表示使用了临时表存储中间的结果，一般在进行合并查询的时候会使用临时表。
   
4. Using filesort：表示文件排序，说明Mysql对数据使用了外部的索引进行排序，并没有使用表中的索引进行排序。
   
5. Impossible where：表示where后的条件一直为false。
   
6. Select tables optimized away：表示使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。
   
7. Using index condition：表示查询中使用了索引，但有部分条件无法使用到索引，会先根据索引搜索一遍再从刚查询的数据中去匹配无法使用索引的条件，从而避免无法使用索引的部分进行全表扫描。