4. MySQL 慢查询日志配置与分析方法

慢查询日志是 MySQL 数据库性能调优的“第一现场”。对于 GoBFF 这类高并发业务，没有慢查询日志，调优就是盲人摸象。本节将深入讲解如何精准配置慢查询日志、捕获全表扫描查询，以及使用官方工具 mysqldumpslow 快速定位性能瓶颈。

4.1 核心参数配置

MySQL 慢查询日志的行为由三个关键参数控制：slow_query_log、long_query_time 和 log_queries_not_using_indexes。下表列出了它们的默认值与生产推荐值：

参数名 默认值 生产推荐值 说明
slow_query_log OFF ON 开启慢查询日志记录
long_query_time 10.000000（秒） 0.5 或 1.0（秒） 超过此阈值的查询被记录；GoBFF 接口通常要求 P99 < 200ms，建议设为 0.5s
log_queries_not_using_indexes OFF ON 记录所有未使用索引的查询（即使执行很快）
log_slow_admin_statements OFF ON 记录慢速管理语句（如 ALTER TABLE）
log_output FILE FILE,TABLE 同时写入文件与 mysql.slow_log 表，便于程序化分析
生产配置示例（MySQL 8.0+）： 
-- 动态生效（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_slow_admin_statements = ON;
SET GLOBAL log_output = 'FILE,TABLE';

-- 确认配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

注意：long_query_time 设置后，已存在的连接不会立即生效，需要新建连接或等待下次查询。建议在配置文件中持久化：my.cnf 的 [mysqld] 段加入上述参数。

4.2 慢查询日志分析工具：mysqldumpslow

MySQL 官方提供了 mysqldumpslow 工具，用于对慢查询日志文件进行聚合统计。它能够将相似的查询（仅参数不同）归为一类，并按照执行时间、锁定时间、扫描行数等维度排序，帮助 DBA 快速定位“最慢的 N 条查询”。

4.2.1 常用命令与参数

命令示例 作用
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log 按查询时间排序，显示前 10 条最慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log 按出现次数排序，显示前 10 条最频繁的慢查询
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log 按锁定时间排序，显示前 10 条锁等待最严重的查询
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log 按扫描行数排序，显示前 10 条扫描数据最多的查询
mysqldumpslow -g "user_order" /var/log/mysql/slow.log 只显示包含 user_order 的慢查询（正则过滤）
mysqldumpslow -a /var/log/mysql/slow.log 不将数字抽象为 N，显示原始参数值（调试时使用）

4.2.2 输出解读

以下是一个典型的 mysqldumpslow -s t -t 5 输出片段：

Reading mysql slow query log from /var/log/mysql/slow.log
Count: 123  Time=2.34s (287s)  Lock=0.01s (1.2s)  Rows=1500.0 (184500), root[root]@localhost
  SELECT * FROM `order` WHERE `user_id` = N AND `status` = N ORDER BY `create_time` DESC LIMIT N;

Count: 89   Time=1.87s (166s)  Lock=0.02s (1.8s)  Rows=3200.0 (284800), app[app]@10.0.0.1
  SELECT o.*, u.name FROM `order` o LEFT JOIN `user` u ON o.user_id = u.id WHERE o.create_time BETWEEN N AND N;

Count: 45   Time=0.95s (42s)   Lock=0.00s (0.0s)  Rows=8000.0 (360000), app[app]@10.0.0.2
  SELECT * FROM `product` WHERE `category_id` = N ORDER BY `sales` DESC;

各字段含义：

  • Count：该模式查询被记录的次数（123 次）。
  • Time=2.34s (287s)：平均执行时间 2.34 秒，总耗时 287 秒。
  • Lock=0.01s (1.2s)：平均锁等待 0.01 秒，总锁等待 1.2 秒。
  • Rows=1500.0 (184500)：平均扫描行数 1500 行，总扫描行数 184500 行。
  • SELECT ...：抽象后的查询模板，N 代表具体参数值被替换。
GoBFF 实战经验：在 GoBFF 项目中，我们通常将 mysqldumpslow 集成到 CI/CD 流水线中。每次上线前，对比当前分支与主干的慢查询聚合结果，如果新增了“全表扫描”或“平均耗时 > 1s”的查询，则阻断发布。这能有效防止性能退化进入生产环境。

4.3 慢查询日志分析流程

下图展示了从配置开启到定位根因的完整分析链路：

慢查询日志分析流程 慢查询日志分析流程 1. 配置慢查询参数 2. 捕获慢查询日志 3. mysqldumpslow 聚合 4. 按时间/次数排序定位 5. EXPLAIN 根因分析 关键操作提示 • long_query_time 建议 0.5s • 开启 log_queries_not_using_indexes • 日志文件定期轮转（logrotate） • 使用 -s t 按时间排序 • 使用 -s c 按频率排序 • 关注 Rows 扫描行数 • 对 TOP N 执行 EXPLAIN • 检查索引是否缺失 • 检查是否使用了临时表 • 检查是否使用了文件排序 • 修复后重新验证

4.4 实战：GoBFF 慢查询日志配置清单

在 GoBFF 项目中，我们推荐按以下步骤完成慢查询日志的标准化配置：

  1. 修改配置文件：在 my.cnf 的 [mysqld] 段添加：
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = ON
log_slow_admin_statements = ON
log_output = FILE,TABLE
min_examined_row_limit = 100
  1. 重启 MySQL 服务（或动态设置后持久化）。
  2. 验证配置生效：
mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+
  1. 模拟慢查询：执行 SELECT SLEEP(1); 确认日志文件有记录。
  2. 配置日志轮转：使用 logrotate 避免日志文件无限增长：
/var/log/mysql/slow.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}
避坑指南：
  • 不要在生产环境设置 long_query_time = 0：这会记录所有查询，导致日志文件暴涨，拖垮磁盘 IO。
  • 注意 log_queries_not_using_indexes 的副作用：对于小表（如配置表），即使没有索引，全表扫描也很快。建议配合 min_examined_row_limit 使用，只记录扫描行数超过阈值的未使用索引查询。
  • 定期清理 mysql.slow_log 表：如果使用 TABLE 输出，该表是 CSV 引擎，不支持索引，查询会变慢。建议定期 TRUNCATE mysql.slow_log。

4.5 进阶：使用 pt-query-digest 替代 mysqldumpslow

虽然 mysqldumpslow 是官方工具，但 Percona Toolkit 中的 pt-query-digest 提供了更强大的分析能力：

特性 mysqldumpslow pt-query-digest
输出格式 纯文本 文本 / JSON / 报告
查询指纹 简单数字替换 智能归一化（忽略注释、空格）
多维度排序 支持 支持，且可自定义
EXPLAIN 集成 不支持 自动对 TOP 查询执行 EXPLAIN
历史对比 不支持 支持两个时间段的差异分析
安装方式 MySQL 自带 需单独安装 Percona Toolkit

在 GoBFF 项目中，我们建议：日常快速排查使用 mysqldumpslow，深度分析或生成报告时使用 pt-query-digest。两者结合，可以覆盖从“快速定位”到“根因分析”的全场景需求。

← 上一章 📖 返回目录 下一章 →