1 前言

在应用的开发过程中，由于开发初期的数据量一般都比较小，所以开发过程中一般都比较注重功能上的实现，但是当完成了一个应用或者系统之后，随着生产数据量的急剧增长，那么之前的很多sql语句的写法就会显现出一定的性能问题，对生产的影响也会越来越大，这些不恰当的sql语句就会成为整个系统性能的瓶颈，为了追求系统的极致性能，必须要对它们进行优化。

这篇文章开始后面的几篇文章将结合自己平时工作和学习中的知识记录下，当面对一个有sql性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题、解决问题。

2 正文

上一篇文章：几个必须掌握的SQL优化技巧(二)：如何定位低效率执行SQL介绍了如何通过慢查询日志和show processlist命令来定位低效率的执行语句，然后就可以针对这些执行效率比较低的sql语句，通过Expalin或者Desc命令获取MySQL如何执行Select语句的信息，包括在Select语句执行过程中表如何连接和连接的顺序，从而后续对其进行优化。

今天这篇文章将介绍如何使用Explain命令来分析select语句的执行计划。

Explain命令的使用方式如下：

EXPLAIN + select语句
复制代码

在任何的select sql语句之前都可以加上explain命令分析执行计划，比如：

EXPLAIN select * from jdbc.`user` where id=1
复制代码

结果如下：

通过上面的执行结果，我们可以发现结果有以下几个字段：

id、select_type、table、type、possible_keys、key、key_len、ref、rows、extra。

以上字段就是select语句的执行计划。通过分析一个复杂的多表联查的select查询语句来说明以上几个字段分别的含义：这里有三张关联的表:user、role、info表；user和role表都有id字段，indo表的id是主键，其userid和roleid分别是user和role表的id：

EXPLAIN select * from jdbc.`user` u,jdbc.`role` r,jdbc.`info` i where u.id=i.userid and r.id=i.roleid
复制代码

explain分析查询结果如下：

id：select查询的序列号，表示的是查询中执行select子句或者是操作表的顺序，如果id相同则表示从上至下的查询顺序，id不同的则id越大，优先级越高，则先被执行，上面的语句就是先查询user表，再查询info表，最后查询role表；

再看这样的语句：

EXPLAIN SELECT * from jdbc.`user` where id =(select userid from info where roleid=(select id from role where rolename='皇帝'))
复制代码

结果如下：

上面的就表示id不同的则id越大，优先级越高，则先被执行：先查询role表，再查询info表，最后查询user表。

**select_type：**表示select的类型，常见的值有：

simple：简单查询，即不使用表连接或者子查询的；

**primary：**主查询，查询中若包含任何复杂的子查询，那么最外层的查询为primary；

subquery： 在select或者where列表中包含了子查询；

DERIVED：在FROM列表中包含的子查询被标记为：**DERIVED（衍生）**用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中；

UNION：该临时表是从子查询中派生出来的,若第二个SELECT出现在UNION之后，则被标记为UNION；

UNION RESULT：从UNION表获取结果的SELECT被标记为：UNION RESULT

比如第一个语句就是简单的simple查询，而第二个语句user表的查询就是primary查询，而info和role表就是subquery子查询；

DERIVED如下：

**table：**输出结果集的表，展示这行数据是关于那一张表的，比如第一个语句则分别是user、info、role表；

**type：**表示的是表连接的类型，连接类型按照性能由好到差的顺序依次为：NuLL、system、const、eq_ref、ref、ref_or_null、index_merge、index_subquery、range、index、all。

它们的含义分别表示如下：

1、ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行；

2、index：Full Index Scan，index与ALL区别为index类型只遍历索引树；

3、range: 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

4、ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

5、eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件；

6、const：表示通过一次索引就找到了，const用于比较primary key和unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将查询转换为一个常量，const将主键和唯一索引的所有部分与常量值进行比较。

7、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system，一般不会出现；

8、NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

一般来说，最好能够使得查询达到ref级别，至少需要保证能够达到range级别。

**possible_keys和key：**分别表示查询时可能使用的索引和实际使用的索引，这里的值都是primary；

**key_len：**表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）；

**ref：**表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

**rows：**扫描行的数量；

**extra：**执行情况的说明和描述，包含不适合在其他列中显示但十分重要的额外信息，可能的值有：

1、Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index）；覆盖索引（Covering Index）表示MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index），如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降；

2、Using where：表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。

3、Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：

1)、内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制

2)、使用了TEXT/BLOB 列

4、Using filesort：表示MySQL中无法利用索引完成的排序操作称为“文件排序”，效率较低。

5、Using join buffer：表示强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

6、Impossible where：这个值强调了where语句会导致没有符合条件的行。

7、Select tables optimized away：表示仅通过使用索引，优化器可能仅从聚合函数结果中返回一行.

8、 Index merges：表示当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。比如：Using sort_union()；Using union()；Using intersect()；

3 总结

这篇文章主要介绍了expalin命令的使用。通过expalin命令，可以对select语句的执行计划进行分析，根据命令分析的结果可以帮助我们了解SQL优化器是如何工作的、SQL如何使用索引，连接查询的执行顺序，查询的数据行数等等，从而有助于我们后面对select查询语句进行优化。

后面将会继续分享关于sql优化的其他方法、步骤。