【MySQL高级】如何定位拖累系统的慢SQL【慢查询日志使用与分析】

分析慢SQL的步骤

1. 慢查询的开启并捕获：开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，至少跑1天，看看生产的慢SQL情况，并将它抓取出来
2. explain + 慢SQL分析
3. show Profile。（比explain还要详细，可以查询SQL在MySQL数据库中的执行细节和生命周期情况）
4. 运维经理 OR DBA，进行MySQL数据库服务器的参数调优。（后端程序员没有这个权限）

慢查询日志（定位慢SQL）

基本介绍

慢查询日志是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

• long_query_time的默认值为10，意思是运行10秒以上的语句
• 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析

特别说明

**默认情况下，MySQL数据库没有开启慢查询日志，**需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看慢查询日志是否开以及如何开启

• 查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
• 开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

-- 指定数据库
mysql> use advanced_mysql_learning;
Database changed

--  查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------------------------------------------------+
| Variable_name       | Value                                                                     |
+---------------------+---------------------------------------------------------------------------+
| slow_query_log      | OFF                                                                       |
| slow_query_log_file | D:\Development\Sql\Mysql\mysql8\exe\mysql-8.0.27-winx64\data\dam-slow.log |
+---------------------+---------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

--  开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)

如果要使慢查询日志永久开启（不推荐，浪费性能），需要修改my.cnf文件，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 开启慢查询
slow_query_log=ON  
# 指定存储慢查询日志的文件。如果这个文件不存在，会自动创建
slow_query_log_file=/var/lib/mysql/slow.log

设置慢SQL的时间阈值

查看阈值

时间阈值是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

注意：是超过阈值才会被记录，等于不会被记录

设置阈值

--  设置阈值
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

--  可以发现设置没有成功
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

也可以不重启连接，使用如下命令直接查看

 show global variables like 'long_query_time';

也直接在my.cnf配置文件中修改

[mysqld]
long_query_time=1

查询慢查询日志文件中的总记录条数

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

日志分析

模拟慢查询

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.01 sec)

# Time: 2023-06-22T03:40:45.171751Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 4.004906  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use advanced_mysql_learning;
SET timestamp=1687405241;
select sleep(4);

• use advanced_mysql_learning：使用的数据库
• Query_time：实际查询时间，单位是秒
• Lock_time：锁时间
• select sleep(4)：超时的语句

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。比如有100条慢sql，如何快速找出出现频次最高的前5条。

查看mysqldumpslow的帮助文档

在Linux命令行窗口执行mysqldumpslow --help

mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  # 按照何种方式排序
                al: average lock time # 平均锁定时间
                ar: average rows sent # 平均返回记录数
                at: average query time # 平均查询时间
                 c: count  # 访问次数
                 l: lock time  # 锁定时间
                 r: rows sent  # 返回记录
                 t: query time  # 查询时间 
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries  # 返回前面多少条记录
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string  
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

• s: 是表示按照何种方式排序；
• c: 访问次数
• l: 锁定时间
• r: 返回记录
• t: 查询时间
• al: 平均锁定时间
• ar: 平均返回记录数
• at: 平均查询时间
• t: 即为返回前面多少条的数据；
• g: 后边搭配一个正则匹配模式，大小写不敏感的；

常用命令案例

日志文件地址：/var/lib/mysql/slow.log

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

# 另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

文章说明

本文章为本人学习尚硅谷的学习笔记，文章中大部分内容来源于尚硅谷视频（点击学习尚硅谷相关课程），也有部分内容来自于自己的思考，发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识，如有侵权请联系删除，最后对尚硅谷的优质课程表示感谢。