CentOS 系统下 MySQL 慢查询日志的开启与深度分析指南

2015-08-14 · Ryan · 发表评论

网上关于 MySQL 慢查询配置的文章存在大量转载，且部分内容已过时。本文将提供一份适用于 CentOS 系统下 MySQL 5.5 及以上版本的清晰、可操作的慢查询开启与分析指南。

一、开启 MySQL 慢查询日志

慢查询日志用于记录执行时间超过指定阈值的 SQL 语句，是数据库性能优化的关键工具。

1. 修改 MySQL 配置文件

使用文本编辑器（如 vi）打开 MySQL 的主配置文件：

vi /etc/my.cnf

在 [mysqld] 配置段末尾，添加以下参数：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 2
log_queries_not_using_indexes = 1

参数说明：

slow_query_log = 1：启用慢查询日志。
slow_query_log_file：指定慢查询日志文件的完整路径。请确保 MySQL 进程用户（通常是 mysql）对该路径有写入权限。
long_query_time = 2：定义“慢查询”的阈值，单位为秒。此处设置为 2 秒，即执行时间超过 2 秒的 SQL 将被记录。您可以根据实际情况调整。
log_queries_not_using_indexes = 1：记录未使用索引的查询语句（即使其执行时间未超过阈值）。这有助于发现潜在的索引缺失问题。

注意： 原文中使用的 log-slow-queries 参数在 MySQL 5.5 及以后版本中已被 slow_query_log 和 slow_query_log_file 取代。请使用新参数以确保兼容性。

2. 重启 MySQL 服务使配置生效

保存并退出配置文件后，需要重启 MySQL 服务：

# 使用 service 命令（CentOS 6）
service mysqld restart

# 或使用 systemctl 命令（CentOS 7+）
systemctl restart mysqld

您也可以使用 reload 命令（如原文所述），但重启服务是确保所有配置生效的最可靠方式。

3. 验证慢查询日志是否生效

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

或者，直接查看日志文件是否生成并有新内容：

tail -f /var/log/mysql/slow_query.log

重要提示： 在生产环境中，长期开启慢查询日志（尤其是记录未使用索引的查询）可能会产生大量日志并占用磁盘空间。建议在问题排查期间开启，分析完毕后根据实际情况调整参数或关闭。

二、使用 mysqldumpslow 工具分析日志

MySQL 自带了 mysqldumpslow 工具，用于解析和汇总慢查询日志，生成更易读的报告。

1. 查找工具路径

通常，mysqldumpslow 随 MySQL 客户端安装。您可以使用 find 命令查找：

find / -name mysqldumpslow 2>/dev/null

常见路径为 /usr/bin/mysqldumpslow 或 MySQL 安装目录下的 bin/ 文件夹。

2. 常用分析命令示例

假设工具路径为 /usr/bin/mysqldumpslow，日志文件为 /var/log/mysql/slow_query.log。

获取访问次数最多的 10 条慢 SQL：

/usr/bin/mysqldumpslow -s c -t 10 /var/log/mysql/slow_query.log

获取平均执行时间最长的 10 条慢 SQL：

/usr/bin/mysqldumpslow -s t -t 10 /var/log/mysql/slow_query.log

获取按照锁定时间排序的前 10 条慢 SQL：

/usr/bin/mysqldumpslow -s l -t 10 /var/log/mysql/slow_query.log

获取包含特定关键词（如 ‘LEFT JOIN’）的慢 SQL：

/usr/bin/mysqldumpslow -g "LEFT JOIN" /var/log/mysql/slow_query.log

3. mysqldumpslow 参数详解

-s ORDER  排序方式。可选值：
          c: 查询次数
          t: 平均查询时间（默认）
          l: 平均锁定时间
          r: 平均返回记录数
          at: 平均查询时间（同 t）
          al: 平均锁定时间
          ar: 平均返回记录数
          ac: 查询次数
          在字母前加 'r' 表示逆序（如 -s rt 表示按时间倒序）。
-t NUM    仅显示前 NUM 条结果。
-a        不要将数字抽象为 'N'，字符串抽象为 'S'，显示完整值。
-g PATTERN  使用正则表达式过滤，仅显示匹配模式的语句。
-h HOSTNAME  指定服务器主机名（用于过滤日志）。
--help    显示完整帮助信息。

三、更强大的分析工具：pt-query-digest

对于更深入、更专业的分析，推荐使用 Percona Toolkit 中的 pt-query-digest 工具。它比 mysqldumpslow 功能更强大，能提供更详细的报告，包括查询时间分布、执行计划建议等。

安装 Percona Toolkit：

# CentOS 6/7
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install percona-toolkit

# 或直接下载 RPM 包安装
rpm -ivh percona-toolkit-*.rpm

基本使用：

# 分析慢查询日志并生成报告
pt-query-digest /var/log/mysql/slow_query.log > slow_report.txt

# 查看报告
cat slow_report.txt

报告将包含：最耗时的查询、查询执行时间直方图、索引建议等宝贵信息。

总结

开启并分析 MySQL 慢查询日志是数据库性能调优的基础步骤。关键流程为：1）正确配置 my.cnf 并重启服务；2）使用 mysqldumpslow 进行初步快速分析；3）对于复杂环境，使用 pt-query-digest 进行深度分析。定期审查慢查询日志，优化其中发现的 SQL 语句和索引，能有效提升数据库整体性能。