全部笔记All notes

MySQL 慢查询日志

阅读 3m 24s3m 24s read

🧠 一、慢查询日志是干什么的？

MySQL 的 慢查询日志（Slow Query Log）是诊断 SQL 性能瓶颈的重要工具。

📌 它会记录所有 执行时间超过 long_query_time 阈值 的 SQL 查询语句（不包括数据复制、EXPLAIN、SHOW 等元数据操作）。

版本兼容性说明

MySQL 版本慢查询日志特性
MySQL 5.1+基础慢查询日志功能
MySQL 5.6+支持微秒级 long_query_time
MySQL 5.7+log_output 默认值为 FILE
MySQL 8.0+增强的监控功能和性能优化

⚙️ 二、慢查询日志的核心配置项解释

变量含义示例
slow_query_log是否开启慢查询日志ON / OFF
slow_query_log_file日志文件的存储路径/var/lib/mysql/mysql-slow.log
long_query_time判断“慢查询”的时间阈值（秒）1、0.1 等
log_output日志记录输出方式（表/文件）FILE、TABLE、FILE,TABLE
log_queries_not_using_indexes是否记录未使用索引的查询ON / OFF（默认 OFF）

你可用如下命令检查这些配置：

SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE '%log_output%';

🧪 三、运行流程与实验说明

以下是你执行的操作流程的详解。

✅ 第一步：确认慢查询是否开启

SHOW VARIABLES LIKE '%slow_query_log%';

  • 确认当前慢查询功能是否开启。

  • 如果结果是 OFF，则必须显式开启：

SET GLOBAL slow_query_log = ON;

✅ 第二步：设置慢查询判断的阈值

SET GLOBAL long_query_time = 1;

  • 表示执行时间超过 1 秒的查询就会被记录为慢查询。

  • 最小精度是微秒级（例如 0.01 表示 10ms）

  • 默认值为 10 秒

⚠️ 注意：long_query_time 是浮点数。

✅ 第三步：指定日志输出形式

SET GLOBAL log_output = 'TABLE';

  • 可选值有：

    • 'FILE'：日志输出到文件，配合 slow_query_log_file

    • 'TABLE'：输出到 mysql.slow_log 表

    • 'FILE,TABLE'：同时输出到文件和表

✅ 第四步：执行慢 SQL 模拟

SELECT SLEEP(5);

  • 该语句会“睡眠”5秒，模拟耗时 SQL。

  • 只要慢查询阈值设置在 5 秒以内（如你设置了 1），这个 SQL 就会被记录。

✅ 第五步：查看慢查询记录

如果是表输出

SELECT * FROM mysql.slow_log\G

💡 提示: \G 仅在 MySQL 命令行客户端中使用，用于纵向显示结果。在其他工具中请使用普通的 SELECT * FROM mysql.slow_log;

  • 会返回诸如以下字段：
字段说明
start_time查询开始时间
user_host用户名和客户端信息
query_time执行耗时
lock_time锁等待时间
rows_sent返回结果行数
rows_examined扫描行数
db查询使用的数据库
sql_text实际执行的 SQL
last_insert_id / insert_id插入 ID 信息

📄 四、慢日志文件格式（FILE 模式）

如果你设置了 log_output = FILE，日志会记录在 slow_query_log_file 指定的文件中，内容格式类似：

# Time: 2025-04-30T10:05:41.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 5.001214  Lock_time: 0.000112  Rows_sent: 0  Rows_examined: 0
SET timestamp=1682930741;
SELECT SLEEP(5);

🛠️ 五、配置持久化方式（防止重启失效）

在 my.cnf（或 my.ini）文件中增加配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
log_output = TABLE

然后重启 MySQL：

# CentOS/RHEL 系统
sudo systemctl restart mysqld

# Ubuntu/Debian 系统
sudo systemctl restart mysql

# macOS（使用 Homebrew）
brew services restart mysql

📊 六、慢日志的应用价值

应用场景描述
查询调优识别慢 SQL，使用 EXPLAIN 优化索引
问题诊断高频慢查询可能是故障诱因
自动分析可用 pt-query-digest 工具做聚合分析
日常监控联动监控告警系统，实时触发慢查询告警

🧩 七、慢查询日志分析建议工具

1. 使用 pt-query-digest

安装方法：
# CentOS/RHEL
sudo yum install percona-toolkit

# Ubuntu/Debian
sudo apt-get install percona-toolkit

# macOS
brew install percona-toolkit
使用示例：
pt-query-digest /var/lib/mysql/mysql-slow.log > analysis.txt

  • 按查询模板聚合

  • 显示执行次数、平均耗时、总耗时、95分位、标准差等

🧷 八、注意事项与最佳实践

项建议
性能影响log_output=TABLE 会略增加 I/O 负载，建议仅在开发或分析阶段开启
时间精度long_query_time=0 表示记录所有 SQL，慎用
大表频率避免使用 SELECT * FROM big_table，容易变成慢 SQL
SQL 优化慢日志只是入口，根本优化需要 EXPLAIN + 索引 + SQL 重写

相关文章

MySQL 基础系列

性能优化相关