小菜鸟

java菜鸟号正在起航

0%

慢查询日志

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 3.6k 阅读时长 ≈ 3 分钟

MySQL 慢查询日志：配置、分析与优化实践

慢查询日志是 MySQL 性能优化的重要工具，用于记录执行时间超过阈值的 SQL 语句。通过分析慢查询日志，可定位低效查询，针对性优化，提升数据库性能。本文详细介绍慢查询日志的配置、分析工具及使用技巧。

慢查询日志的基本概念

慢查询日志的核心作用是追踪执行耗时过长的 SQL 语句，帮助开发者发现性能瓶颈。其关键参数包括：

  • long_query_time：慢查询阈值（单位：秒），默认值为 10 秒（即执行时间 ≥ 该值的 SQL 会被记录）。
  • slow_query_log：是否开启慢查询日志（0 关闭，1 开启），默认关闭（因会产生一定性能开销）。

慢查询日志的配置

查看当前配置

通过以下命令查看慢查询相关参数：

1
2
3
4
5
6
7
8
9
10
11
-- 查看是否开启慢查询日志
show variables like '%slow_query_log%';

-- 查看慢查询阈值（long_query_time）
show variables like '%long_query_time%';

-- 查看慢查询日志文件路径
show variables like '%slow_query_log_file%';

-- 查看未使用索引的SQL是否被记录
show variables like '%log_queries_not_using_indexes%';

临时开启与配置（即时生效，重启失效）

1
2
3
4
5
6
7
8
9
10
11
-- 开启慢查询日志
set global slow_query_log = 1;

-- 设置慢查询阈值（如改为 2 秒，需重新连接生效）
set global long_query_time = 2;

-- 记录未使用索引的SQL（即使执行时间未达阈值）
set global log_queries_not_using_indexes = 1;

-- 设置慢查询日志文件路径（需指定绝对路径，且MySQL有写入权限）
set global slow_query_log_file = '/var/log/mysql/slow.log';

永久配置（重启生效）

在 MySQL 配置文件（my.cnf 或 my.ini）的 [mysqld] 段添加以下配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件路径
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值（秒）
long_query_time = 2
# 记录未使用索引的SQL
log_queries_not_using_indexes = 1
# 限制每分钟记录未使用索引的SQL数量（避免日志过大）
log_throttle_queries_not_using_indexes = 10
# 扫描行数超过该值的SQL会被记录（即使执行时间未达阈值）
min_examined_row_limit = 1000

配置后重启 MySQL 服务生效：

1
sudo systemctl restart mysqld  # Linux

关键参数说明

  • log_queries_not_using_indexes：
    开启后，未使用索引的 SQL 会被记录（无论执行时间），但需配合 log_throttle_queries_not_using_indexes 限制频率（默认 0，无限制）。
  • min_examined_row_limit：
    若 SQL 扫描行数超过该值（默认 0），即使执行时间未达 long_query_time，也会被记录（适合发现扫描大量数据的低效查询）。
  • log_output：
    日志输出方式（默认 FILE），可选 TABLE（存储在 mysql.slow_log 表中）或 FILE,TABLE（同时输出到文件和表）。

慢查询日志的分析工具

慢查询日志为文本格式，直接查看效率低，需借助工具分析。

官方工具：mysqldumpslow（简单易用）

mysqldumpslow 是 MySQL 自带的慢查询分析工具，可汇总相似查询（忽略参数差异），按执行次数、时间等排序。

常用参数：
参数 含义
-s 排序方式（c：次数，t：总时间，l：锁定时间，r：返回行数，默认 at：平均时间）
-t 显示前 N 条结果
-g 正则匹配（如 -g 'select' 只分析 SELECT 语句）
-a 显示原始 SQL（不隐藏数字、字符串参数）
示例：
1
2
3
4
5
6
7
8
# 查看执行次数最多的前10条慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 查看总执行时间最长的前5条SELECT语句
mysqldumpslow -s t -t 5 -g 'select' /var/log/mysql/slow.log

# 显示原始SQL（不替换参数）
mysqldumpslow -a -t 3 /var/log/mysql/slow.log
注意：
  • 若提示 bad interpreter: No such file or directory，需安装 Perl 环境：sudo yum install perl（CentOS）或 sudo apt install perl（Ubuntu）。

进阶工具：pt_query_digest（功能强大）

pt_query_digest 是 Percona 工具集的一员，支持分析慢查询日志、binlog、进程列表等，提供更详细的统计（如执行频率、平均耗时、锁等待等）。

安装：
1
2
3
4
5
# CentOS
sudo yum install percona-toolkit

# Ubuntu
sudo apt install percona-toolkit
基本用法：
1
2
3
4
5
6
7
8
# 分析慢查询日志，输出到屏幕
pt_query_digest /var/log/mysql/slow.log

# 分析结果保存到文件
pt_query_digest /var/log/mysql/slow.log > slow_analysis.txt

# 只分析SELECT语句
pt_query_digest --filter '$event->{arg} =~ m/^select/i' /var/log/mysql/slow.log
输出解读：

  • 总体统计：总查询数、总耗时、平均耗时等。

  • 详细列表：按 “查询指纹”（参数化后的查询模板）分组，展示每条查询的执行次数、占比、最大 / 最小耗时等。

  • 示例：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    # Query 1: 0.00 QPS, 0.00x concurrency, ID 0x...
    # Scores: V/M = 0.00
    # Time range: 2024-05-01 00:00:00 to 00:30:00
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time            10s      2s      3s    2.5s    3.0s    0.5s    2.5s
    # Lock time             0s       0s       0s       0s       0s       0s       0s
    # Rows sent            100       20      30      25      30       5      25
    # Rows examine       10000    2000    3000    2500    3000     500    2500
    # String:
    # Databases           test
    # Hosts               192.168.1.1
    # Users               app_user
    # Query_time distribution:
    #   1s-10s: 100%  (4)
    # Tables
    #    `test`.`user`
    # EXPLAIN /*!50100 PARTITIONS*/
    select * from user where age > ?;

    上述结果显示：一条select * from user where age > ?的查询执行了 4 次，平均耗时 2.5 秒，扫描行数 2500 行，需优化（如添加age索引）。

慢查询日志的使用技巧

  1. 合理设置阈值：
    生产环境中 long_query_time 建议设为 1~2 秒（根据业务敏感度调整），避免日志过大。
  2. 定期分析：
    结合 cron 定时执行 pt_query_digest，将结果存档，跟踪慢查询变化趋势。
  3. 聚焦关键指标：
    分析时重点关注：
    • 执行次数多且总耗时高的查询（高频低效）。
    • 扫描行数远大于返回行数的查询（全表扫描或索引失效）。
    • 锁定时间长的查询（可能引发并发阻塞）。
  4. 结合执行计划优化：
    对慢查询使用 EXPLAIN 分析执行计划，优化索引或改写 SQL（如避免 SELECT *、拆分大查询）。

注意事项

  1. 性能开销：
    开启慢查询日志会产生一定性能损耗（尤其是记录所有未使用索引的 SQL 时），建议非高峰时段开启，或仅在排查问题时启用。
  2. 日志轮转：
    慢查询日志会持续增长，需配置日志轮转（如 logrotate），避免磁盘占满。
  3. 敏感信息：
    日志可能包含 SQL 中的参数（如用户密码），需限制日志文件权限（如仅 mysql 用户可读写）。
相关文章

欢迎关注我的其它发布渠道