MySQL系列（三）— SQL调优工具详解

前言

前两篇文章我们详细介绍了MySQL索引底层数据结构及索引分类，索引的主要目的就是加快查询效率，学习索引的主要目的之一就是了解其原理方便更好地进行SQL优化，提高查询效率！但是这还不够，我们还需要知道哪些SQL有问题，或者SQL效率慢的问题出在哪儿，接下来我们系统地学习一下SQL优化需要用到的优化工具！

一、Explain执行计划详解

1、Explain介绍及使用

不是每一条SQL都需要优化，只有不恰当的、出问题的或者不符合预期的SQL才需要优化，那如何辨别出SQL是否需要优化呢？除了判断SQL是否遵守规范符合要求外，还可以用到一个重要的也是MySQL自带的工具Explain。使用Explain关键字可以模拟优化器执行SQL语句，分析SQL语句的执行信息或性能瓶颈，比如：这个SQL先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等。
使用方式：在select语句之前增加 explain关键字即可，此时MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

2、Explain两个变种

1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered列，是一个百分比的值，rows *filtered/100可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）。

2）explain partitions：相比explain多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。
注意：这两种变种是针对MySQL5.7之前的版本，5.7及以后的版本直接使用explain即可显示出partitions和filtered列。

3、Explain中的列详解

MySQL的explain执行计划包含的信息有：

下面我们来逐个分析每列的含义：
id列
select查询序列号，有几个select语句就有几个id，表示查询中执行select语句或操作表的顺序。可分为3种情况：
1） 、id相同：执行顺序由上至下。

2）、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

3）、id相同与不同两种情况同时存在：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

select_type列
查询类型，比如是普通查询、联合查询还是子查询等。可分为以下几种：
1）、SIMPLE：简单的select查询，查询中不包含子查询或者union
2）、PRIMARY：查询中包含任何复杂的子部分，最外层的查询就是primary
3）、SUBQUERY：在select或where中包含了子查询（不包括from中的子查询）
4）、DERIVED：在from中包含的子查询被标记为derived（衍生），MySQL会将结果放在临时表里
举个例子来了解一下primary、subquery和derived类型

5）、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived

6）、UNION RESULT：从union表获取结果的select
table列
表示当前SQL语句执行时所涉及的表。当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。当有union时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的select行的id。
partitions列
如果查询是基于分区表的话，partitions字段会显示查询要访问的分区表信息
type列
该查询使用了哪种类型，是SQL优化中一个很重要的指标；总共分为一下几种类型

从最优到最差依次为：system > const > eq_ref > ref > range > index > ALL。一般来说，得保证查询达到range级别，最好达到ref级别。
1）、NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中查询最小值，可以通过单独查找索引来完成，不需要再执行时访问表。

2）、system：表只有一行记录（等于系统表），这是const类型的特例，平时不太会出现，这个也可以忽略不计；
3）、const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

首先进行子查询得到一个结果为tmp的临时表，子查询条件为id = 1是常量，所以type是const，id为1相当于只查询一条记录，所以type为system；
4）、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键索引（primary key）或唯一索引（unique key）扫描；

5）、ref：非唯一性索引扫描，相比eq_ref来说，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行；
a、简单查询

b、关联查询

6）、range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般是在where语句中出现between、< 、>、>=、in等的查询时，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

7）、index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的；

8）、all：遍历全表以找到匹配的行，即全表扫描，扫描表的聚簇索引的所有叶子节点，通常情况下这需要进行SQL优化了。

possible_keys列
表示该查询可能使用哪些索引来查找。可能会出现possible_keys列有数据，而key列显示为NULL的情况，这是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列为NULL，表示没有使用相关的索引，这种情况下需要进行SQL优化，比如可以通过检查where 子句看是否可以创造一个合适的索引来提高查询性能。
key列
表示该SQL语句执行时MySQL实际采用的是哪个索引。如果没有使用索引，则该列是NULL，如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用：force index、ignore index即可。
key_len列
表示MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
key_len大小的计算规则是：
a、一般地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；
b、如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90 bytes；
c、若该列类型定义时允许NULL，其key_len还需要再加 1 bytes；
d、若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加 2 bytes;

表film_actor的联合索引idx_film_actor_id由film_id和actor_id两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
ref列
表示在key列记录的索引中，SQL执行时所用到的列或常量，常见的有：const（常量），字段名等。

rows列
表示MySQL估计要读取并检测的行数，注意这个不是结果集里的行数，数值越少越好。
filtered列
该列是一个百分比的值，rows * filtered/100 可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）。
Extra列
这一列展示的是比较重要的额外信息。常见的重要值如下：
1）、Using index：表示相应的select操作中使用了覆盖索引，只通过辅助索引就能拿到要查询的结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

2）、Using where：表明使用了where过滤，使用 where 语句来处理结果，并且查询的列未被索引覆盖。
3）、Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4）、Using temporary：使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
5)、Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行排序。数据较小时从内存排序，否则需要在磁盘完成排序，MySQL中无法利用索引完成的排序操作称为“文件排序”。这种情况需要进行优化。

上面的SQL语句中：actor表的name属性未创建索引，会浏览整个actor表，保存排序关键字name和对应的id，然后排序name并检索行记录。
6）、Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，在查询执行计划生成的阶段即可完成优化。
7）、Using join buffer：表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

二、Show profile工具使用详解

1、show profile介绍

通过一中的explain工具我们可以分析出SQL的外部执行情况，还有一种方式可以让我们深入到MySQL内核中，从执行线程的状态和时间来进行分析，也就是接下来要介绍的show profile工具。
Show Profile是MySQL提供的可以用来分析当前查询SQL语句执行的资源消耗情况的工具。最新版本中默认情况下处于开启状态，一般在SQL分析和优化的时候使用，只显示最近发给服务器的SQL语句，默认情况下是记录最近已执行的15条记录，可以通过profiling_history_size重新设置，最大值为100。
可以通过select @@have_profiling命令查看所使用的MySQL版本中是否支持show profile工具，比如：

查看show profile是否开启可以使用命令：SHOW VARIABLES like ‘profiling’；

2、show profile使用

Show Profile的使用及常用查询参数如下：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type 参数：
ALL：显示所有开销信息
BLOCK IO：阻塞IO的输入输出次数
CONTEXT SWITCHES：上下文切换相关开销信息
CPU：显示 CPU 的相关开销信息
IPC：接收和发送消息的相关开销信息
MEMORY ：显示内存相关的开销，目前无用
PAGE FAULTS ：显示页面错误相关开销信息
SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置 (行数)
SWAPS：显示swap交换次数的相关开销信息
比如我先执行如下三个SQL语句：

SELECT MAX(id) FROM pro_class;
SELECT min(id) FROM pro_class;
SELECT COUNT(*) FROM pro_class;

然后执行命令：SHOW PROFILES；就会出现如下图所示内容：

获取到Query_ID之后，我们再通过Show Profile for Query ID语句，就能够查看到对应Query_ID的SQL语句在执行过程中线程的每个状态所消耗的时间，然后就可以针对性地进行SQL调优了。比如：

至此SQL优化工具就介绍完了，下一篇文章我们进入SQL优化实战！敬请期待！

欢迎各位关注我的个人公众号：