Mysql索引（三） 使用explain命令

五、使用explain命令

   在查询的时候使用explain命令可以查看是否使用了索引，以便查看查询语句的性能和索引建立的是否合适。

   先来设定两张表：

学生基本信息表：student（id,name,sex,code,firstname,lastname）

学生课程表：study(student_id,course,timme)

给学生信息表加上索引：

主键：id

唯一索引：code,

普通索引：lastname

  下面就基于以上的信息来学习一下explain命令：

  Explain产生的主要有以下几列信息：

1.Id

 id是用来顺序标识整个查询中SELELCT 语句的，语句中涉及到到少个查询，这里就与多少个id(其实也就对应应了有多少行)，如一下语句：

显示了两条查询，其中最末的那条一般是最先执行的

2.select_type

查询语句的类型，一般可分为一下几种:

1）simple

简单的select查询语句，没有子查询、联合查询等

2） primary

最外层或最靠前的查询，如以下语句：

 

1. UNION

Union查询中第二个语句及之后的语句，如以下例子

1. DERIVED

from后中的子句,如如以下例子

1. UNION RESULT

表示一个UNION查询的查询结果

 

7) DEPARTENT UNION

  满足UNION查询，UNION中第二个子句，并且查询结果依赖于外部的查询。（也就是说这个UNION是个内部的查询），如以下例子：

 

1. SUBQUERY

子查询中的第一个查询语句：

 

1. DEPENDENT SUBQUERY

和DEPARTENT UNION原理相同

1. table

表示查询语句所涉及到的表，有些记录不一定是表，是其他查询的中间结果

例如，以上例中的第一条table就是基于第二条查询的结果，查询结果规则为：derived+id

1. type

这一列比较重要，说明了整查询语句中是如何进行表之间的关联操作，以及师傅使用到索引。可以分为以下几类(以下的类型理论上效率越来越低);

1. const

只匹配一个数据，且是使用常量数据和主键或唯一索引进行比较时。如以下语句：

以上两个查询使用了主键、唯一键，且都是和常量比较。

以下查询虽然使用了常量比较和索引，但并非主键或唯一键，故不算const类型：

 

1. system

const中的特殊情况，表示结果中只有一行数据满足情况，如：

 

以上例子中，第一条是基于在第二条的基础上上查询的，第二条已经是一个const类型，并且输出结果只有一行，所以第一条满足system的条件。

1. eq_ref 

索引被连接使用的情况（看不懂），主要一种场景就是 “=、>、<”等操作符中使用索引列，且必须为主键或者唯一键，操作符的另一边可以是常量、表达式（如果这里操作符另一边是一个常量数据，那就是const类型了），如：

  

然而这里是all,看了一下别人的说法，说是InnoDB如果查询数量较小的情况下，eq_ref会默认为all，这个我就去测了，大家有兴趣可以自己试一下。

1. ref

区别于eq_ref，ref的场景主要为：

  使用最左前缀的索引

  使用的索引为非 主键或唯一键

1. ref_or_null

  和ref类似，区别在于在查询的时候回有一些对null的指令

6) index_merger

表示查询找中使用了多个索引合并来优化查询，这种类型在后面讲到key这一列中会标注使用了那些索引来优化。针对于复杂查询，这样的类型或许是个不错的方法。

7）unique_subquery

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key（主键） FROM single_table（单表） WHERE some_expr)

unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

1. 1. index_subquery

与unique_subquery类似，不同之处在于子查询中的查询列为非唯一键，即替换的子查询为;

 value IN (SELECT normal_key（普通非唯一键） FROM single_table（单表） WHERE some_expr)

9）range

只检索给定范围的行，使用一个索引来选择行，如：

10) index

  和all差不多，要进行全表扫描，区别在于可能查询中可能存在使用排序order by，如果使用了索引来排序，则就是index类型

11）字面意思，不使用索引，全表扫描。

5.possible_keys

指出MySQL能使用哪个索引在该表中进行查询找到相应的行。

6..key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。

7.key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好 。

8.ref

显示使用哪个列或常数与key一起从表中选择行

9.rows

显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值

Extra

另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，可以根据这些信息来判断查询与和索引是否要进行优化。。这些信息信息主要包含了以下几种类型：

1. using filesort

查询语句中有排列指令时，通常有两种排序方式;一是使用排序操作；二是使用索引。当出现using filesort时，说明使用了索引排序，一般来说使用索引排序比较快。

1. Using temporary

代表使用了临时表，一般看到它说明查询需要优化了，如果可以，尽量避免临时表使用。

1. Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。

1. Using index 

说明查询是覆盖了索引的，这是好事情。MySQL直接从索引中过滤不需要的记录并返回命中的结果。这是MySQL服务层完成的，无需再回表查询记录。

5） Using where

使用了WHERE从句来限制哪些行，筛选后的行再与下一张表匹配或者是返回给用户。

 

以上就是介绍的explain指令的内容，只介绍了一些基本的，虽然写一条业务sql并没有很复杂，但是尽量考虑多一点，考虑到以后的扩展，不给种植户挖坑，而且性能这个东西，能节约一点是一点，数据量上去了，就体现出差距了。

文章中出现可能有误的地方，欢迎大家指正交流。