3、mysql索引优化一：Explain关键字分析

作为一个Java开发，在我们日常工作中，对于mysql的使用中涉及最多的恐怕就是写SQL了，一定遇到过sql执行慢的问题，那么SQL优化工作就显得格外重要。有了对mysql执行过程以及索引实现原理了解的基础，如果不熟悉可以参考之前两篇博客：《mysql架构组成》、《mysql索引实现原理》，接下来我会就日常工作中一些常见的SQL使用场景进行优化分析讲解，不过在讲解之前，需要先熟悉Explain关键字的使用，Explain是mysql为我们提供的一个非常重要的SQL分析工具：Explain关键字，借助它我们能够很轻松地对我们写出的sql的执行过程以及性能进行分析、优化。
本文主要参考mysql官网对Explain的介绍整理而成，大家如果想要了解关于Explain关键字更多更详细的内容，它的官网就是不错的资料，非常详细：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

一、Explain字段解析
先来看下Explain返回的信息都长啥样，使用很简单，只要在sql前面加上Explain即可：
字段说明：
1、id列
id列看似简单，相信很多人都会有一个误区：认为每个查询语句就会对应一个id，其实并不是，而是每个select关键字就会有一个id，对于简单sql只有一个select关键字，所以只有一个id值，这很容易理解，但是对于子查询、关联查询就不一定了，比如：
（1）连接查询
对于连接查询，一个select关键字会关联多张表，每张表都会生成一条记录，但是它们的id是一样的，前面的即user表示驱动表，后面的即role表是被驱动表。

（2）子查询
可以看到这条子查询语句包含了两个select关键字，执行计划生成了两个id；

（3）id为NULL
不知道大家在工作中有没有留意过这种情况：id值为NULL，官网中也对这个有说明：

The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.

意思就是如果行引用其他行的联合结果，该值可以为NULL。就会生成一个表名为<unionM,N>之类的值，表示该行引用id值为M和N的行并集。举例说明一下：
如图所示，在UNION查询中，第3条记录的id值为NULL，table列为<union1,2>，代表对id为1和2查询结果进行合并而且去重而生成的一个临时表的表名，这一行只是对结果集进行合并，所以id值为NULL。之所以要创建一个临时表，就是因为还要去重操作，如果是UNION ALL，就不需要临时表，也就不会有id为NULL的记录：

注意：
id值是按照select出现的顺序递增的，id值越大，执行优先级越高；id值相同，则按照执行计划中出现的顺序从上到下依次执行；如果id为NULL，则最后执行。

2、select_type列
通过select_type列可以判断当前查询sql是简单查询，还是复杂查询（这里把UNION查询、子查询等都称为复杂查询），以及对于复杂查询，每一条记录在整个查询过程中有什么作用，充当什么角色等。select_type列有如下取值：

• SIMPLE
  简单查询，不包含子查询、UNION查询等，注意连接查询也属于SIMPLE。

• PRIMARY
  简单理解就是外层的查询，对于一个复杂查询来说，比如有多层嵌套子查询、UNION，那么最外层的那个就是PRIMARY；见第1小节第（2）个例子；

• UNION
  对于UNION或者UNION ALL查询来说，查询语句中的第二个或之后的SELECT语句的select_type列的值就是UNION，因为第一个是PRIMARY，其余的都是UNION，见第1小节第（3）个例子；

• DEPENDENT UNION
  UNION中的第二个或后一个SELECT语句，取决于外部查询；

• UNION RESULT
  UNION查询结果是去重之后的，MySQL在执行UNION查询的时候会生成一个临时表，针对该临时表的查询的select_type就是UNION RESULT，见第1小节第（3）个例子；

• SUBQUERY
  子查询，是指包含在select子句中的子查询，官方的解释是子查询中的第一个select，见第1小节第（2）个例子；

• DEPENDENT SUBQUERY
  子查询中的第一个SELECT，取决于外部查询，如：
  

• DERIVED
  通俗地讲它也是子查询的一种方式，不过它包含在from字句中，mysql会将该子查询的结果生成一个派生表来存放，如：
  

id为1的查询的table列是，就是在派生表的基础上查询的；

select_type列还有其它一些取值，如MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION，就不一一介绍了，有需要的可以到mysql官网了解。

3、type列
type列描述表是如何连接的，即访问类型，表示mysql是如何查询的，这个字段在SQL优化中具有非常重要参考价值，它很大程度上表示了你的SQL语句的执行性能。它也有很多取值类型：

• system
  当表中只有一条数据，并且存储引擎是Myisam、Memory时，type列就是system，比如我建了一个Myisam表：myisam_test2，里面插了一条数据：
  

• const
  跟system类似，准确地说，system是const的特殊情况，system和const一般是使用主键索引或者唯一索引与常数做等值查询时，只能查到一条符合条件的记录。system的情况是表中只有一条数据；而const则不一定，但是它查询出来的数据只有一条。

• eq_ref
  在连接查询时，被驱动表是通过主键或者唯一索引做等值查询时，则被驱动表的type列就是eq_ref，eq_ref是除了system和const之外性能最好的访问方式。需要注意的是，如果主键索引或者唯一索引是多个列组成的联合索引，则联合索引的每一列都必须做等值匹配。可能有人会在网上看到很多说eq_ref只会返回一条符合条件的记录，其实说的不够准确，容易让人误解，准确地说应该是对于驱动表的每一行记录，被驱动表（即type列是eq_ref的那一行查询只会有一条记录与之匹配），举个例子：
  role表数据：
  
  user表数据：
  
  连接查询：
  
  执行计划：
  

• ref
  当使用非主键索引、或者使用唯一索引的最左前缀（不是全部列，只是用部分前缀列）来做等值查询，并且对于驱动表（type列非ref的查询）的每一行，被驱动表（type列为ref的查询）匹配的结果可能会有多行，如（ref_col和other_col列都建了普通索引）：
  

• fulltext
  当使用fulltext索引查询时，type列的值就是fulltext；

• ref_or_null
  跟ref类似，只不过索引列的值也可以是null的时候，那么type列就可能是ref_or_null；

• index_merge
  此连接类型表明使用了索引合并优化，Index Merge访问方法检索具有多个范围扫描的行，并将它们的结果合并为一个。这种访问方法只合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以产生其底层扫描的并集、交集或交集的并集。
  

• unique_subquery
  该类型替换了以下形式的一些IN子查询的eq_ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

Unique_subquery只是一个索引查找函数，它完全替代了子查询，以提高效率。这是官网上的解释，通俗一点说就是mysql的查询优化器会将in子查询进行转换成类似EXISTS的子查询，子查询使用主键索引进行等值查询。例：

• index_subquery
  这个连接类型类似于unique_subquery，只是子查询使用的是普通索引，它适用于以下形式的子查询中的非唯一索引:

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range
  一般使用索引进行范围查询时，type列就是range，如使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()等操作符进行查询，如：
  

• index
  这种查询方式一般为索引覆盖，即只需要扫描辅助索引树即可得到结果，这种情况下性能要比ALL要快一些，但是有一点需要注意，就是index查询需要扫描整棵辅助索引树，即直接从叶子节点开始全部扫描，所以性能要比range要差，我们在进行SQL优化的时候，例：
  
  但是如果将sql进行稍微变动：
  
  

可以看到，type列就变成了range或者ref了，因为这两种情况会从索引树的根节点开始进行等值查找，并不用扫描整棵索引树就能得到结果。

• ALL
  全表扫描，这种性能最差，尽量避免这种查询。
  
  type列小结：
  执行计划中的type列是我们日常工作中SQL调优时其中一个非常重要的参考指标，一般来说，SQL的查询性能从高到低依次为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，我们优化SQL时，尽量避免ALL和index级别，至少要优化到range以上级别。

4、possible_keys列和key列
possible_keys表示该查询可能会使用到的索引都有哪些，但不保证最终一定会使用到possible_keys中列出的索引（这时候key列就是null）。key列表是最终实际使用到的索引。

5、key_len列
表示实际使用的索引的长度，单位是字节，比如该索引是多列组成的联合索引，但是实际上可能只用到了左边的部分列，那么可以通过key_len列的值再结合索引列的数据类型来推算出使用了索引的哪些列。推算方法如下（对于非字符串类型，每种数据类型都有固定的长度，就不介绍了，这里主要介绍定长和变长字符串类型）：
（1）mysql里字符串主要分为定长和变成两种类型，即char(n)和varchar(n)，并且采用的字符集编码不同，占用的存储空间也不同，如常用的utf-8编码，如果是中文，一个字符占3个字节，如果是英文或者数字，一个字符占1个字节；GBK编码中文则占2个字节。以下我就统一以utf-8编码，并且是中文的情况来说明；
（2）所以对于char(n)数据类型，则一列占3n字节；如果该列允许null，则一列占3n + 1个字节；
（3）变长字符串就稍微复杂点，会有单独的2个字节存储该列的实际长度，所以一列占3n + 2个字节；如果该列允许null，则一列占3n + 2 + 1个字节；

6、ref列
ref列显示哪些列或常量与键列中指定的索引（key列中用到的索引）进行比较，即当使用索引做等值匹配时，与索引列进行等值匹配的的常量或者列就显示在ref列中。如：

7、rows列
rows列表示mysql估计该查询所需扫描的行数，注意这只是一个估算值，并不代表结果集里的实际行数。

8、filtered列
该列表示经过本次查询的基础上，有多少百分比的数据符合接下来的一次查询，一般在连接查询或者子查询中该列才有参考意义，连接查询或者子查询的执行计划一般会有多条，计算方式是用该查询的rows列乘以filtered列，即rows*filtered就是下一次查询中符合条件的记录数，注意这只是一个估算值。举例说明吧：

如图是一个in子查询，生成了两条执行计划，id为2的先执行，可以看到other_table表的执行计划中rows=25，filtered=10%，那么接下来id为1的查询大约会有25*10%=2.5条记录符合条件。

再看一个链接查询的例子：

可以看到，b表（即user表）会执行大约2*50%=1次的查询。

说明：
在连接查询中，执行计划优先执行的是驱动表，后执行的是被驱动表，上图中a表（即role表）是驱动表，b表（即user表）是被驱动表，一般连接查询的优化时其中一个原则是尽量让小表驱动大表。

9、Extra列
顾名思义，该列是执行计划中一些额外的信息，在SQL调优中也是非常重要的一个参考列，该列值非常复杂，很多在我们工作中可能都没有遇到过，我就介绍一些常见的情况：

• Using index
  使用索引覆盖优化时，Extra列就是Using index，如：
  
  我这个例子中name是单值索引，如果是多列组成的联合索引也一样的道理，即select的列存在于使用到的索引列中（执行计划的key列值），在使用辅助索引查询时，mysql只需要扫描索引树就能得到想要的结果，不需要回表。

• Using filesort
  当进行排序查询时，可能会是Using filesort，为什么说可能是呢？这个取决于多个因素，比如如果排序字段没有创建索引，或者即使创建了索引，但是表中记录较少，那么Extra列都会出现Using filesort，注意不管是在内存中排序还是在磁盘上排序，都是Using filesort，区别就是当表中数据较少时会使用内存排序，表中数据较多是就需要在磁盘中排序。Using filesort一般来说需要进行优化，比如给排序字段加上索引，或者使用索引覆盖优化，进而达到Using index。举几个例子：
  （1）identity_card列没有索引，使用文件排序
  

（2）age字段有索引，但是也使用Using filesort，这是因为该查询没有使用到索引，key列为null；

（3）基于（2）的情况，使用索引覆盖来优化一下sql：

或者：

可以看到，Extra列变成了Using index或者Using index condition了，当然，这需要根据具体的业务场景来决定是否做这样的优化。

• Using index condition
  当Extra列出现这个值是，表示查询优化器使用了索引下推优化（IPC）。为了更好地解释索引下推，用一个例子来说明：
  

如上图中的sql，我这里age和identity_card是联合索引（即使identity_card字段没有索引，也会使用索引下推），在mysql5.7之前，是这样执行的：
（1）首先查询优化器会先扫描辅助索引树，筛选出age < 30的所有记录（假设有10条）；
（2）然后把这10条数据通过回表返回到mysql server层；
（3）最后在mysql server层再筛选满足identity_card like '3404061%'条件的记录（假设有5条）

前面的博客《mysql架构组成》中介绍了mysql的组成架构，索引的优化工作是在server层进行的，sql执行过程即返回最终数据这个过程是在存储引擎层进行的，所以上面这个执行过程需要回表10次；但是5.7版本之后引入了索引下推优化，这个执行过程就变成了：
（1）查询优化器先筛选出age < 30的所有记录，并且在server层进一步筛选出同时满足identity_card like '3404061%'条件的记录（5条）；
（2）然后再通过回表将最终数据返回到server层；

这样优化之后，相当于把identity_card like '3404061%'这个条件给下沉到存储引擎层来执行了，大大减少了回表次数，提高了sql执行效率。

• Using where
  SQL语句中有where子句，但是where字句中的条件列没有索引，或者使用了全表扫描，那么Extra就是Using where
  

• Using temporary
  mysql需要创建临时表来保存查询结果，比如DISTINCT、UNION、GROUP BY等查询，如果没有使用到索引，Extra列都可能会是Using temporary，例：
  
  Using temporary一般需要优化sql，尽量不要让mysql产生临时表，比如可以使用索引优化，下面的sql中，name字段有索引，所以Extra就是Using index，就不会创建临时表了。
  

• Using index for group-by
  与Using index类似，并且对于DISTINCT或者GROUP BY查询可以使用索引覆盖，即只需要扫描索引就能得到全部结果，不需要回表。如：
  
  

上图中age和identity_card列是联合索引。

Extra主要就介绍这几种场景，其实还有很多其它情况，详细的可以到mysql官网了解：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information

Extra列小结：
Extra也是SQl优化时重要参考项，以上介绍的几种Extra取值情况，它们的性能从高到低依次为：
Using index > Using index condition > Using index for group-by > Using where > Using filesort > Using temporary，我们日常优化时，一般建议要尽量优化到 Using where及以上级别。

总结：
以上就是对Explain关键字的介绍，凡是做过SQL调优的同学应该都很清楚Explain关键字的重要性，所以在接下来的SQL调优实战的介绍里，都是通过Explain关键字为基础来进行的。