人人都会AI编程

11.1 慢查询日志：开启、配置、分析工具

更新时间：2026-07-11

慢查询日志是 MySQL 提供的一种记录执行时间超过设定阈值的 SQL 语句的机制。它是优化工作的起点：在生产环境中，你不可能逐条检查所有 SQL，慢查询日志可以帮你精准定位那些消耗大量时间或资源的“问题语句”，让优化工作有的放矢。

11.1.1 慢查询日志的作用

当一条 SQL 的实际执行时间超过预设的“慢查询阈值”时，MySQL 会自动将这条语句的详细信息写入日志文件或表中。这些信息通常包括：

  • 语句的完整文本及执行时间
  • 扫描的行数、返回的行数
  • 执行时的时间戳、用户、数据库
  • 是否使用了索引（若配置了相关参数）

对于 DBA 和开发人员来说，慢查询日志的价值体现在：

  • 发现性能瓶颈：快速找出哪些 SQL 最耗时，哪些接口拖垮了数据库响应。
  • 验证优化效果：对 SQL 或索引调整后，观察同样的 SQL 是否从日志中消失或执行时间下降。
  • 预警异常负载：突然大量慢查询出现，可能意味着新上的功能存在未优化查询，或业务高峰导致锁等待。

11.1.2 开启与参数配置

慢查询日志默认是关闭的，因为记录日志本身会消耗少量 I/O 性能。在生产环境中，建议根据实际情况开启，并合理设定阈值。核心参数如下：

1. 总开关

SET GLOBAL slow_query_log = 1;

也可在配置文件 my.cnf 中永久开启：

[mysqld]
slow_query_log = 1

2. 日志输出方式

log_output 参数控制日志是写入文件（FILE）还是写入 mysql.slow_log 表（TABLE），或者两者同时使用（FILE,TABLE）。

  • 文件输出：性能开销最小，适合生产环境，但分析需要借助工具。
  • 表输出：方便直接用 SQL 查询，但写入表本身会产生额外的数据库负载，高并发下不建议使用。

生产环境通常使用 FILE 模式，并配合外部分析工具。

3. 日志文件路径

slow_query_log_file = /var/log/mysql/mysql-slow.log

文件应存储在有足够磁盘空间的目录，并设置合理的日志轮转（logrotate），避免单个文件无限膨胀。

4. 慢查询阈值：long_query_time

这是最关键的一个参数。单位是秒，表示超过该执行时长的 SQL 会被记录。

SET GLOBAL long_query_time = 0.5;  -- 0.5 秒

实际常见的设置是 0.1 秒（100 毫秒）或 0.5 秒。阈值太低会记录大量正常业务 SQL，增加日志量和分析成本；太高则会遗漏有问题的语句。需要根据业务对响应时间的要求来权衡。注意：新设置的 long_query_time 对已有连接不生效，需要退出当前会话重新连接，或者使用 SET long_query_time = 0.5 只对当前会话生效。

5. 记录未使用索引的查询

log_queries_not_using_indexes = 1

当开启此选项后，即使执行时间未超过 long_query_time，只要 SQL 没有使用任何索引且扫描行数达到 min_examined_row_limit 设置的阈值，也会被记录。这个参数可以帮助发现那些全表扫描的语句，对早期性能优化很有用。注意：它会显著增加日志量，生产环境需谨慎评估。

6. 其他实用参数

  • min_examined_row_limit：配合 log_queries_not_using_indexes，设置扫描行数下限，避免记录扫描极少量行的表。
  • log_slow_admin_statements：记录管理类语句，如 OPTIMIZE TABLE、ANALYZE TABLE、ALTER TABLE 等，这些操作可能造成长时间阻塞，有必要纳入监控。

一个典型的生产配置示例（my.cnf）：

slow_query_log = 1
slow_query_log_file = /data/mysql/mysql-slow.log
long_query_time = 0.3
log_queries_not_using_indexes = 0   # 暂不开启，按需测试
log_slow_admin_statements = 1
min_examined_row_limit = 1000

重启 MySQL 实例后配置生效，或者通过 SET GLOBAL 动态修改（无需重启）。使用 SHOW VARIABLES LIKE '%slow%'; 和 SHOW VARIABLES LIKE 'long_query_time'; 检查当前设置。

11.1.3 分析工具

慢查询日志是纯文本，日志量稍大就很难人工阅读。必须借助工具进行统计和排序，以下是最常用的三种选择。

1. 自带工具 mysqldumpslow

MySQL 安装包自带该 Perl 脚本，可以快速汇总慢查询日志中的 SQL 模式：

# 按平均执行时间排序，显示前10条
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

# 按执行次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# 按总时间（执行次数×平均时间）排序
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

它会自动将 SQL 中的具体数值替换为 N，字符串替换为 S，便于分类统计。优点是零依赖，能快速给出哪些类型的 SQL 需要重点关注。但是，它功能比较基础，无法分析出详细的执行时间分布、锁等待时间、扫描行数统计等。

2. Percona Toolkit 的 pt-query-digest

这是业界最流行的慢查询分析工具，功能强大，可以分析慢查询日志、通用查询日志，甚至 tcpdump 抓取的 MySQL 协议包。它是 DBA 进行深入优化的必备利器。

基本用法：

# 分析慢查询日志文件
pt-query-digest /var/log/mysql/mysql-slow.log

# 监控当前活跃查询，实时分析
pt-query-digest --processlist h=127.0.0.1,u=root,p=password

# 分析从上一次分析以来的增量日志（支持时间戳定位）
pt-query-digest --since=24h /var/log/mysql/mysql-slow.log

报告会自动生成，包含以下关键信息：

  • 整体统计概览：总查询数，唯一语句数，时间范围等。
  • 按查询时间排名：哪些 SQL 类别的总耗时最多、执行次数最多。
  • 详细的单个查询分析：典型的 SQL 文本示例，执行时间分布（最小、最大、95% 分位），锁等待时间占比，扫描行数与返回行数的对比（Rows examine 远高于 Rows sent 往往表示缺少合适索引），以及执行计划的建议。

常见输出片段示例：

# Query 1: 1.2k QPS, 9.3s avg response time ...
SELECT * FROM orders WHERE user_id = ? AND status = ? ...

# Response time distribution
  95%  9.8s
  99% 12.4s

pt-query-digest 还可以把结果输出到可视化报告或存入数据库表中，便于定期巡检。它需要安装 Percona Toolkit 包（yum install percona-toolkit 或 apt-get install percona-toolkit），依赖 Perl 和部分数据库驱动。

3. 借助 Performance Schema（sys 库）

MySQL 5.7 及以上自带的 Performance Schema 和 sys 库提供了语句执行统计表，可以替代或补充传统慢查询日志。优势是不需要额外的文件 I/O，直接通过 SQL 查询分析。

  • sys.statement_analysis：汇总所有规范化 SQL 的执行次数、总延迟、锁延迟、扫描行数等。
  • sys.statements_with_runtimes_in_95th_percentile：关注长尾延迟语句。

查询最近消耗总时间最多的 SQL：

SELECT * FROM sys.statement_analysis 
ORDER BY total_latency DESC 
LIMIT 10;

但 Performance Schema 的启用也有一定资源开销，需要监控。它在持续性能诊断和实时分析场景下非常有用，通常与现代监控平台（如 PMM）结合使用。

11.1.4 实际优化闭环

慢查询日志只是手段，真正的价值在于形成优化闭环：

  1. 采集：开启慢查询日志，设定合理阈值，收集 1~7 天的日志。
  2. 分析：使用 pt-query-digest 或 mysqldumpslow 找出 Top N 慢查询。
  3. 定位：用 EXPLAIN 检查这些 SQL 的执行计划，寻找索引缺失、全表扫描等问题。
  4. 优化：添加或调整索引，改写 SQL，调整参数。
  5. 验证：观察优化后，同一 SQL 是否从慢查询中消失，或监控系统显示响应时间下降。
  6. 迭代：不断重复，持续压低慢查询比例。

一个容易忽视的细节：不要只看执行时间，还要关注执行次数。一个执行 10 毫秒但每秒跑 1000 次的 SQL，对系统的总压力可能远大于一个执行 5 秒但每分钟只跑一次的报表 SQL。pt-query-digest 会同时展示总耗时（执行次数×平均时间），这个指标比平均时间更反映整体影响。

最后，慢查询日志分析应该是日常运维的一部分，而不是出了故障才想起的工具。将它集成到监控系统（如 ELK 收集日志并告警），或者使用云厂商提供的慢查询分析界面（如 RDS 控制台），可以把被动救火变为主动预防。