一、概述
MySQL慢查询日志，记录在MySQL中响应时间超过阀值的语句。
具体指运行时间超过long_query_time（默认值为10）值的SQL，会被记录到慢查询日志中。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
二、慢查询日志配置
2.1 开启慢查询日志
一般情况下，我们只需开启慢日志记录，配置下阈值时间，其余参数可按默认配置。对于阈值时间，可灵活调整，比如说可以设置为1s或3s。
# 查看是否开启慢查询日志#
show variables like \'%slow_query_log%\';
# 开启慢查询（临时,当前会话有效）#
set global slow_query_log=\'ON\';
# 查看慢查询日志存放文件位置#
show variables like \'%slow_query_log_file%\';
# 查看long_query_time阈值#
show variables like \'%long_query_time%\';
# 设置long_query_time阈值（临时,当前会话有效）#
set global long_query_time=3
直接修改配置文件（全局，需要重启服务，慎重）
#编辑配置文件
vim /etc/my.cnf
# 修改配置项（如果没有就加上）
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/my-slow.log
long_query_time = 1
log_timestamps = SYSTEM
log_output = FILE
# 重启mysqld服务
systemctl restart mysqld
# 查看mysqld服务
systemctl status mysqld
常用配置参数说明：
#慢日志常用配置项
slow_query_log 是否启用慢查询日志，默认为0，可设置为0、1，1表示开启。
slow_query_log_file 指定慢查询日志位置及名称，默认值为host_name-slow.log，可指定绝对路径。
long_query_time 慢查询执行时间阈值，超过此时间会记录，默认为10，单位为s。
log_output 慢查询日志输出目标，默认为file，即输出到文件。
log_timestamps 控制error log、slow log、genera log文件的时区，默认使用UTC时区，建议改为SYSTEM系统时区。
min_examined_row_limit 对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中，默认为0。
2.2 记录管理语句
在MySQL中，慢查询日志中默认不记录管理语句，不过可通过以下属性进行设置：
mysql> set global log_slow_admin_statements = \"ON\";
log_slow_admin_statements变量会将管理性质的慢SQL记录到慢查询日志中。管理性质的SQL语句包括：
- alter table
- analyze table
- check table
- create index
- drop index
- optimize table
- repair table
2.3 记录未走索引的SQL语句
在MySQL中，还可以设置将未走索引的SQL语句记录在慢日志查询文件中(默认为关闭状态)。通过下述属性即可进行设置：
mysql> set global log_queries_not_using_indexes = \"ON\";
Query OK, 0 rows affected (0.00 sec)
2.4 慢查询日志输出位置
在MySQL中，日志输出格式有支持：FILE(默认)，TABLE两种，可进行组合使用。如下所示:
set global log_output = \"FILE,TABLE\";
这样设置会同时在FILE，mysql库中的slow_log表中同时写入。
select * from mysql.slow_log;
2.5 关闭与删除慢查询日志
MySQL服务器停止慢查询日志功能的方法：
set global slow_query_log=\'OFF\';
[mysqld]
slow_query_log=OFF
删除慢查询日志文件：
mysql> show variables like \'%slow_query_log%\';
+-----------------------------------+--------------------------------+
| Variable_name | Value |
+-----------------------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /var/lib/mysql/zhyno1-slow.log |
| slow_query_log_use_global_control | |
+-----------------------------------+--------------------------------+
4 rows in set (0.00 sec)
通过以上查询可以看到慢查询日志的目录，在该目录下手动删除慢查询日志文件即可。或使用命令mysqladmin来删除，mysqladmin命令的语法如下：
mysqladmin -uroot -p flush-logs
执行该命令后，命令行会提示输入密码。输入正确密码后，将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志，不需要再手动删除。
通过一下命令可以查看慢查询日志的状态：
select sleep(5);
ls /var/lib/mysql/xxx-slow.log
三、慢查询日志文件分析
3.1 单条记录结构
单条记录结构：
# Time: 2024-03-01T17:12:40.156488+08:00
# User@Host: panda[panda] @ [192.168.72.1] Id: 8
# Query_time: 5.000688 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1709284355;
select sleep(5);
字段说明：
-
慢查询日志以#作为起始符。
-
Time:查询的时间。
-
User@Host：表示用户 和 慢查询查询的ip地址。
-
Query_time: 表示SQL查询持续时间， 单位(秒)。
- Lock_time: 表示获取锁的时间， 单位(秒)。
- Rows_sent: 表示发送给客户端的行数。
- Rows_examined: 表示：服务器层检查的行数。
-
set timestamp ：表示 慢SQL 记录时的时间戳。
-
select sleep(5) ：表示慢SQL语句。
3.2 慢查询日志分析-mysqldumpslow
MySQL自带了一个慢查询分析工具mysqldumpslow。
[root@localhost~]# 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 是表示按照何种方式排序
-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 top n的意思，即为返回前面多少条的数据；
-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 后边可以写一个正则匹配模式，大小写不敏感的
-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
mysqldumpslow --help
示例：
# 得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
3.3 慢查询日志分析-Navicat Monitor
官网下载就行了，跟着指引配置即可。很舒服，还可以自动提供一些运维建议。
查询分析
四、案例分析
4.1 案例一：查询执行时间过长
在某电商平台的实际运营中，随着业务规模的扩大，用户频繁反馈商品列表页加载迟缓。技术团队迅速开启慢查询日志，并将long_query_time阈值设为2秒。经过48小时的数据采集，发现了一条典型慢查询记录：
# Time: 2023-06-01T15:20:30.567890Z
# User@Host: web_user(web_server) @ 192.168.1.100 () Id: 50
# Query_time: 3.567890
Lock_time: 0.000123
Rows_sent: 100
Rows_examined: 100000
SET timestamp = 1685632830;
SELECT * FROM products WHERE category = \'electronics\' AND price > 500;
通过EXPLAIN执行计划分析，发现该查询触发了全表扫描：在包含10万条记录的products表中，由于category和price列缺乏有效索引，数据库引擎无法快速定位目标数据，导致单条查询耗时长达3.5秒。
从索引原理来看，单列索引无法同时满足多条件筛选需求，而复合索引能构建高效的B+树数据结构，显著提升查询性能。
优化方案采用覆盖索引策略，创建联合索引：
CREATE INDEX idx_category_price ON products (category, price);
优化后进行压力测试，相同查询场景下响应时间降至120毫秒，配合前端缓存机制，页面加载速度提升80%以上。同时需要注意，复合索引遵循“最左前缀原则”，查询条件必须包含category列才能有效利用该索引。
4.2 案例二：未使用索引的查询
某数据分析系统在处理订单统计时，出现响应延迟问题。通过慢查询日志定位到高频慢查询：
# Time: 2023-06-02T10:15:45.234567Z
# User@Host: analytics_user(analytics_server) @ 192.168.1.101 () Id: 60
# Query_time: 2.123456
Lock_time: 0.000234
Rows_sent: 500
Rows_examined: 50000
SET timestamp = 1685686545;
SELECT * FROM orders WHERE order_date BETWEEN \'2023-01-01\' AND \'2023-06-01\';
该查询需要扫描5万条订单记录，而order_date列缺失索引导致全表扫描。虽然日期范围查询理论上可以使用索引，但无序的扫描方式严重影响性能。
采用单列索引优化方案：
CREATE INDEX idx_order_date ON orders (order_date);
优化后配合分区表技术（按月份对订单表分区），相同查询响应时间缩短至350毫秒。值得注意的是，对于频繁写入的表，过多索引会增加写操作开销，建议通过SHOW INDEX命令监控索引使用情况，必要时进行索引重建或合并。同时，对于范围查询场景，覆盖索引比普通索引能获得更好的性能表现。
五、总结
MySQL中的慢查询日志是一个非常有用的工具，可以帮助我们发现和解决数据库性能问题。通过合理地开启、分析和利用慢查询日志，我们可以不断优化数据库的性能，提高系统的稳定性和响应速度。
