MySQL学习（1）如何定位慢查询，如何分析SQL执行效率

1.定位慢查询

实际开发过程中，如果项目的数据量很大，可能会碰到某个功能或者某个接口需要很久才能返回结果,我们就应该去确定是不是慢查询导导致的。
首先明确哪些SQL语句是有问题的：

  • 查询次数很多且每次查询占用时间长的SQL
  • IO大的SQL语句
  • 未命中索引的SQL
如何定位慢查询，有两种解决方案：
  • 查看慢查询日志确定已经执行完的慢查询
  • show processlist 查看正在执行的慢查询

1.1通过慢查询日志

如果需要定位到慢查询,一般的方法是通过慢查询日志来查询的,MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过参数 long_query_time(单位秒，默认值是10)设置的值并且扫描记录数不小于 min_examined_row_limit(默认值0)的语句,能够帮我们找到执行完的慢查询语句。 
tips：
默认情况下,慢查询日志中不会记录管理语句,可通过设置log_slow_admin_statements = 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)
tips
MySQL 中 long_query_time 的值如何确定呢?
线上业务一般建议把 long_query_time 设置为 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1 秒。发现慢查询及时优化或者提醒开发改写。
一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1秒,则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。

甚至某些重要业务测试环境 long_query_time 可以设置为 0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注 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);
对上方的查询结果详细描述一下：
  • ail -n5:只查看慢查询文件的最后5行
  • Time:慢查询发生的时间
  • User@Host:客户端用户和ip
  • Query_time:查询时间
  • Lock_time:等待表锁的时间
  • Rows_sent:语句返回的行数
  • Rows_examined:语句执行期间从存储引擎读取的行数
上面这种方式是用系统自带的慢查询日志查看的,如果觉得系统自带的慢查询日志不方便查看，可以使用 pt-query-digest 或者 mysqldumpslow 等工具对慢查询日志进行分析。

1.2通过 show processlist

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

`......`

`*************************** 10. row ***************************`

   `Id: 7651833`

   `User: one`

   `Host: 192.168.1.251:52154`

    `db: ops`

`Command: Query`

   `Time: 3`

  `State: User sleep`

   `Info: select sleep(10)`

`......`

`10 rows in set (0.00 sec)`
这里对上面结果解释一下:
  • Time:表示执行时间
  • Info:表示 SQL 语句
我们这里可以通过它的执行时间(Time)来判断是否是慢 SQL。

2.使用 explain 分析慢查询

分析 SQL 执行效率是优化 SQL 的重要手段,通过上面讲的两种方法,定位到慢查询语句，我们就要开始分析 SQL 执行效率，我们可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询。先讲解 explain 的使用,在后面介绍 show profile 和 trace 的使用。
Explain 可以获取 MySQL 中 SQL 语句的执行计划,比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。
为了便于理解,先创建两张测试表，运行SQL脚本：
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 的结果各字段解释如下:
加粗的列为需要重点关注的项。


其中 explain 各列都有各种不同的值,这里介绍几个比较重要列常包含的值:包含 select_typ、type 和 Extra。
下面将列出它们常见的一些值,可稍微过一遍,不需要完全记下来,在后续章节分析 SQL时，可以返回查询本节内容并对比各种值的区别。

select_type


type



上表的这些情况,查询性能从上到下依次是最好到最差。

extra


当Extra为Using filesort和Using temporary时说明SQL需要进行优化了。 

3.总结

本节知识点总结如下;
首先讲到如何定位慢SQL：
  • 一种方法是查询慢查询日志
  • 另一种方法是 show process 查看正在执行的 SQL
再讲到通过 explain 分析慢 SQL,explain 会返回很多字段,其中 select_type、type、key、rows、Extra 是重点关注项。
要想做好性能优化,我们必须学会使用 SQL 优化时需要的工具,进行定位和分析。由于篇幅的问题,本小节只介绍了 explain 工具的使用,在下节将补充另外两种分析慢查询的工具：show profile 和 trace。