📚 课程目录第2章慢查询日志配置

2. 慢查询日志配置：MySQL慢查询日志开启、阈值设置、日志格式解析

慢查询日志是MySQL数据库性能调优的“第一现场”。没有准确的慢查询日志，后续的索引复盘和SQL优化就无从谈起。本节将围绕日志开启、阈值设置和日志格式解析三个核心环节，给出可直接落地的配置方案与解读方法。

2.1 慢查询日志的开启与基本配置

MySQL默认关闭慢查询日志，需要通过参数显式开启。以下为最常用的配置项及其说明：

参数名	默认值	说明	推荐生产值
`slow_query_log`	OFF	是否开启慢查询日志	ON
`slow_query_log_file`	主机名-slow.log	慢查询日志文件路径	/var/log/mysql/slow-query.log
`long_query_time`	10.000000	查询执行时间超过此值（秒）即记录	1 或 0.5
`log_queries_not_using_indexes`	OFF	记录未使用索引的查询（即使很快）	ON
`log_slow_admin_statements`	OFF	记录慢管理语句（ALTER TABLE等）	ON
`min_examined_row_limit`	0	扫描行数超过此值才记录（防刷屏）	100

生产建议：不要将 long_query_time 设置为 0，否则会记录所有查询，导致日志文件暴涨，影响磁盘IO。推荐从 1 秒开始，逐步下调至 0.5 秒或 0.2 秒。

动态开启（无需重启MySQL）：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- 设置阈值：超过1秒即记录
SET GLOBAL long_query_time = 1;

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

注意：动态设置的参数在MySQL重启后会失效。如需持久化，请将上述参数写入 my.cnf 或 my.ini 的 [mysqld] 段。

2.2 阈值设置策略：从业务出发

long_query_time 的取值直接影响诊断效率。过大会漏掉慢SQL，过小则产生大量噪音。以下为不同业务场景的推荐阈值：

OLTP 高并发交易系统（如订单、支付）：推荐 0.1 ~ 0.5 秒。此类系统对响应时间极其敏感，超过 200ms 的查询就应纳入分析。
内部管理系统 / 报表系统：推荐 1 ~ 3 秒。用户对等待时间容忍度较高，重点抓取明显拖慢页面的查询。
数据仓库 / 批处理任务：推荐 5 ~ 10 秒。ETL 或大查询本身耗时较长，阈值过小会导致日志量过大。
全量审计模式（调试阶段）：设置 long_query_time = 0 并配合 log_queries_not_using_indexes = ON，用于短时间内的全量SQL审计。

  关键指标：设置阈值后，观察慢查询日志的日均条目数。如果日均超过 5000 条，说明阈值可能过松，需要收紧或优先优化 Top-N 慢查询。

2.3 慢查询日志格式深度解析

一条完整的慢查询日志记录包含头部元数据、执行统计和SQL语句三部分。以下是一条真实日志的拆解：

# Time: 2025-06-15T10:23:45.123456Z
# User@Host: app_user[app_user] @  [10.0.1.100]  Id: 98765
# Query_time: 2.345678  Lock_time: 0.001234  Rows_sent: 100  Rows_examined: 50000
# Thread_id: 98765  Schema: order_db  Last_errno: 0  Killed: 0
# Query_time distribution:
#   1% in 0.1s to 0.5s
#  99% in 2.0s to 5.0s
# SET timestamp=1749983025;
SELECT o.id, o.amount, u.name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
  AND o.create_time > '2025-01-01'
ORDER BY o.create_time DESC
LIMIT 100;

各字段含义如下：

字段	含义	诊断价值
`Time`	查询执行的时间戳（UTC）	用于关联业务高峰时段
`User@Host`	执行查询的用户和客户端IP	定位是哪个应用服务器发起的慢查询
`Query_time`	查询实际执行时间（秒）	核心指标，决定是否属于慢查询
`Lock_time`	等待表锁或行锁的时间（秒）	如果占比高，说明存在锁竞争
`Rows_sent`	返回给客户端的行数	如果远小于 Rows_examined，说明扫描了大量无用行
`Rows_examined`	MySQL扫描的行数（存储引擎层）	衡量查询的“工作量”，是索引优化的直接依据
`Schema`	查询所在的数据库	多库环境下快速定位
`SET timestamp`	查询开始时的Unix时间戳	用于 pt-query-digest 等工具的时间序列分析

避坑指南：不要只看 Query_time。一个 Rows_examined 高达 100 万行但只返回 10 行的查询，即使执行时间只有 0.8 秒，也属于典型的“索引失效”或“扫描过度”问题，必须纳入优化范围。

2.4 慢查询日志分析流程（SVG）

以下流程图展示了从日志开启到定位慢SQL的完整链路：

2.5 日志格式的常见变体与工具适配

不同MySQL版本和配置下，日志格式略有差异。以下为两种常见变体：

MySQL 5.6 及以下：时间戳为本地时间，无 # Thread_id 和 # Query_time distribution 行。
MySQL 8.0 及以上：时间戳默认使用UTC，新增 # Query_time distribution 分布信息，便于快速判断查询延迟的集中区间。

推荐使用 pt-query-digest（Percona Toolkit）对慢查询日志进行自动化解析和汇总，它会自动识别上述格式变体，输出按总耗时排序的查询指纹报告。

  实战命令：

  pt-query-digest /var/log/mysql/slow-query.log > slow_report.txt

  该命令会生成一份包含查询频率、平均耗时、总耗时、响应时间占比的排名报告，是慢查询复盘的第一步。

2.6 配置检查清单

在进入下一节索引分析之前，请确认以下配置项已就绪：

检查项	期望状态	验证SQL
慢查询日志是否开启	ON	`SHOW VARIABLES LIKE 'slow_query_log';`
日志文件路径是否正确	文件存在且可写	`SHOW VARIABLES LIKE 'slow_query_log_file';`
阈值是否合理	1 秒或更低	`SHOW VARIABLES LIKE 'long_query_time';`
是否记录未用索引的查询	ON	`SHOW VARIABLES LIKE 'log_queries_not_using_indexes';`
日志是否在滚动	文件大小持续增长	`ls -lh /var/log/mysql/slow-query.log`

小技巧：使用 SELECT SLEEP(2); 手动制造一条慢查询，然后检查日志文件中是否出现该记录，这是验证配置是否生效的最快方法。