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

常用命令案例

日志文件地址：/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