MySQL慢查询日志

1 什么是慢查询

慢查询日志是MySQL提供的一种日志记录功能，它能够记录执行时间超过预设阈值（long_query_time）的SQL查询语句，并将这些信息写入到日志文件中。

2 查看慢查询日志的设置和状态

2.1 慢查询日志的开启状态和日志文件位置

show variables like '%slow_query_log%';

• slow_query_log：慢查询日志开启状态，ON为开启，OFF为关闭，默认为关闭OFF
• slow_query_log_file：慢查询日志存储路径

2.2 查看慢查询阈值

show variables like '%long_query_time%';

• long_query_time：慢查询阈值，默认10s

2.3 查询系统中慢查询记录的数量

show global status like '%Slow_queries%';

3 如何开启慢查询日志

3.1 临时开启慢查询日志

（1）设置慢查询日志状态
临时开启只对当前数据库会话有效，并且重启数据库后设置会失效：

#开启慢查询日志：slow_query_log=ON 或者 slow_query_log=1
set global slow_query_log = ON;
set global slow_query_log = 1;

#关闭慢查询日志：slow_query_log=OFF 或者 slow_query_log=0
set global slow_query_log = OFF;
set global slow_query_log = 0;

（2）设置慢查询阈值

set long_query_time = 3;

3.2 永久开启慢查询日志

步骤一：修改my.cnf配置文件，添加：

[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/lib/mysql/localhost-slow.log

• slow_query_log：慢查询日志开启状态，ON或1为开启，OFF或0为关闭
• long_query_time：慢查询阈值
• slow_query_log_file：慢查询日志存储路径

步骤二：重启mysql服务

service mysqld restart

4 实战慢查询

（1）检查是否开启慢查询日志

show variables like '%slow_query_log%';
show variables like '%long_query_time%';

如上图所示，目前已开启慢查询日志，且慢查询阈值设置为2s。

（2）运行慢查询sql，查看慢查询日志
运行sql：

select sleep(3);

查看日志：

tail -f  /var/lib/mysql/localhost-slow.log

慢查询阈值设置为2s，sql实际运行3s，故生成慢查询日志：

慢查询日志字段说明：

• Time：查询开始的时间。
• Id：服务器进程ID。
• User：执行查询的MySQL用户。
• Host：发送查询的客户端主机。
• Query_time：查询执行的时间。
• Lock_time：查询在锁定资源过程中花费的时间。
• Rows_sent：查询结果中发送的行数。
• Rows_examined：查询执行过程中检查的行数。
• Query：执行的SQL语句

（3）查询当前系统有多少条慢查询记录

show global status like '%Slow_queries%';

5 日志分析工具mysqldumpslow

mysqldumpslow是MySQL提供的一个命令行工具，专门用于分析MySQL的慢查询日志（slow query log），帮助我们识别和优化执行效率低下的SQL语句。

通过 --help命令，就能知道如何使用Mysqlsumpslow

参数说明：
（1）基本排序参数

• -s / --sort=：指定排序方式。后面可以跟不同的参数来指定按照何种方式进行排序。
• t：以查询总时间排序（默认）。
• c：以查询出现次数（即相同的查询语句执行了多少次）排序。
• l：以查询锁的总时间排序。
• r：以查询返回的记录数排序。
• at：以查询平均时间排序。
• al：以查询锁的平均时间排序。
• ar：以查询平均返回记录数排序。

（2）输出限制参数

• -t / --top=N：限制输出的慢查询数量，即仅显示最慢的N条查询。N是一个正整数，用户可以根据需要指定。

（3）正则表达式匹配参数

• -g / --grep=PATTERN：仅显示包含指定模式的查询。PATTERN是一个正则表达式，用于匹配SQL语句中的特定文本。该参数是大小写不敏感的，有助于筛选出包含特定关键词或模式的慢查询语句。

示例：
获取返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

获取访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

获取按时间排序的前10条包含左连接的查询语句：

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log

建议在使用这些命令时结合| 和more 使用 ，否则有可能出现刷屏的情况

mysqldumpslow -s r -t 20 /var/lib/mysql/localhost-slow.log | more