SQL执行效率的分析-explain分析慢查询

定位慢SQL

在工作中，某个功能或者某个接口需要很久才返回结果，我们应该去确定是不是因为慢查询导致的。定位慢SQL有如下两种方案

• 查看慢查询日志，确定已经执行完的慢查询
• show processlist查看正在执行的慢查询

两个场景介绍

通过慢查询日志

如果需要定位慢查询，一般的方法是通过慢查询日志来查询的，MySQL的慢查询日志用来记录在MySQL中响应超过参数long_query_time（单位秒，默认值10）设置的值并且扫描记录数不小于min_examined_row_limit（默认值为0）的语句，能够帮助我们找到执行完的慢查询，方便我们对这些SQL进行优化

知识拓展

默认情况下，慢查询日志不会记录管理语句，可以通过设置log_slow_admin_statement = on让管理语句中的慢查询也会记录到慢查询日志中

通常情况下，也不会记录查询时间不超过long_query_time但是不使用索引的语句，可以通过配置log_queries_not_using_indexes = on让不使用索引的SQL都被记录到慢查询日志中（即使查询时间没有超过long_query_time配置的值）

如果需要使用慢查询日志，一般分为四步：开启慢查询日志、设置慢查询阈值、确定慢查询日志路径、确定慢查询日志的文件名。如下分析：

• 首先开启慢查询日志，由参数slow_query_log决定是否开启，在MySQL命令行下输入下面的命令：

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

默认情况下，慢查询是关闭的

• 设置慢查询阈值

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

知识拓展
MySQL 中 long_query_time 的值如何确定呢

线上业务一般建议把long_query_time设置为1s。如果某个业务的MySQL要求比较高的QPS，可设置慢查询为0.1s。发现慢查询及时优化或者提醒开发者改写。

一般测试环境的long_query_time设置的阈值比生产环境的要小，比如生产环境是1s，则测试环境为0.5s。便于在测试环境中及时发现一些效率低的SQL

甚至某个重要的业务，测试环境设置long_query_time为0s，以便记录所有的语句。并留意慢查询日志的输出，上线前的功能测试完成之后，分析慢查询日志每类语句的输出，重点关注Rows_examined（语句执行期间从存储引擎获取的行数），提前优化

• 确定慢查询日志路径

慢查询日志的路径默认是MySQL的数据目录

mysql> show global variables like "datadir";
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /data/mysql/data/3306/ |
+---------------+------------------------+

1 row in set (0.00 sec)

• 确定慢查询日志的文件名

mysql> show global variables like "slow_query_log_file";
+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+

1 row in set (0.00 sec)

根据上面的查询结果，可以直接查看/data/mysql/data/3306/mysql-slow.log文件获取已经执行完的慢查询

[root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log

Time: 2019-05-21T09:15:06.255554+08:00

User@Host: root[root] @ localhost []  Id: 8591152

Query_time: 10.000260  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1558401306;
select sleep(10);

执行结果详细描述

• tail -n5 ：只查看慢查询文件的最后5行
• Time：慢查询发生的时间
• User@Host：客户端用户和IP
• Query_time：查询时间
• Lock_time：等待表锁的时间
• Rows_sent：语句返回的行数
• Rows_examined：语句执行期间从存储引擎读取的行数

上面是系统自带的慢查询日志查看的，可以通过pt-query-digest或者mysqldumpslow等工具对慢查询日志进行分析

通过show processlist

有时候，慢查询正在运行，已经导致数据库负载偏高了，而由于慢查询还没有执行完毕，因此慢查询日志还是看不到任何语句。此时可以使用show processlist命令判断正在执行的慢查询。show processlist显示哪些线程正在运行。如果有PROCESS权限，则可以看到所有线程。否则，只能看到当前会话的线程。

知识拓展
如果不使用FULL关键字，在info字段中只显示每个语句的前100个字符，如果想看语句的全部内容可以使用full修饰（show full processlist）。

• 执行结果如下

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost:49799
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
1 row in set (0.00 sec)

解释

• Time：表示执行的时间
• Info：表示SQL语句

我们这里可以通过它的执行时间（Time）来判断是否是慢SQL

使用explain分析慢查询

可以通过explain、show profile、trace等诊断工具来分析慢查询。

expalin

explain可以获取MySQL中的SQL语句的执行计划，比如语句是否使用了关联查询，是否使用了索引、扫描行数等。可以帮助我们选择更好的索引和写出更优的SQL。使用方法：在查询语句前面加上explain就可以了

• 实例

为了便于理解，先创建两张测试表，建表以及数据写入语句如下

CREATE DATABASE muke;           /* 创建测试使用的database，名为muke */
use muke;                       /* 使用muke这个database */
drop table if exists t1;        /* 如果表t1存在则删除表t1 */

CREATE TABLE `t1` (             /* 创建表t1 */
  `id` int(11) NOT NULL auto_increment,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()        /* 创建存储过程insert_t1 */
begin
  declare i int;                    /* 声明变量i */
  set i=1;                          /* 设置i的初始值为1 */
  while(i<=1000)do                  /* 对满足i<=1000的值进行while循环 */
    insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
    set i=i+1;                      /* 将i加1 */
  end while;
end;;
delimiter ;                 /* 创建批量写入1000条数据到表t1的存储过程insert_t1 */
call insert_t1();           /* 运行存储过程insert_t1 */

drop table if exists t2;    /* 如果表t2存在则删除表t2 */
create table t2 like t1;    /* 创建表t2，表结构与t1一致 */
insert into t2 select * from t1;   /* 将表t1的数据导入到t2 */

下面尝试使用explain分析一条SQL，例子如下

mysql> explain select * from t1 where b=100;

• explain结果各字段解释如下
  

  加粗的列为需要重点关注的列

• 介绍几个比较重要的列常包含的值：select_type、type、Extra
  

  select_type

type

Extra