MySQL 慢查询日志开启与问题排查指南

MySQL 慢查询日志开启与问题排查指南

MySQL 的慢查询日志（Slow Query Log）是优化数据库性能的重要工具。它可以帮助我们识别执行时间较长的 SQL 语句，从而有针对性地进行优化。然而，在实际使用中，可能会遇到一些意外情况，比如执行时间很短的查询也被记录到慢查询日志中。本文将详细介绍如何开启慢查询日志，并解决常见问题。

一、什么是慢查询日志？

慢查询日志是 MySQL 提供的一种日志功能，用于记录执行时间超过指定阈值的 SQL 语句。通过分析这些慢查询，我们可以发现潜在的性能瓶颈，并采取相应的优化措施。

二、如何开启慢查询日志？

1. 检查慢查询日志状态

登录 MySQL 后，执行以下命令查看慢查询日志是否已启用：

SHOW VARIABLES LIKE 'slow_query_log';

• 如果结果为 ON，表示慢查询日志已启用。
• 如果结果为 OFF，需要手动启用。

2. 临时启用慢查询日志

在 MySQL 中执行以下命令：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 设置慢查询阈值（单位：秒）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  -- 指定日志文件路径

3. 永久启用慢查询日志

编辑 MySQL 配置文件（通常为 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf），添加或修改以下内容：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  -- 执行时间超过 2 秒的查询将被记录
log_queries_not_using_indexes = 1  -- 记录未使用索引的查询（可选）

保存后重启 MySQL 服务：

sudo systemctl restart mysqld

三、如何查看慢查询日志？

1. 查看日志文件

慢查询日志默认保存在 /var/log/mysql/slow.log（路径可通过 slow_query_log_file 变量查看）。使用以下命令查看日志内容：

sudo cat /var/log/mysql/slow.log

2. 使用 mysqldumpslow 分析日志

MySQL 提供了 mysqldumpslow 工具，用于分析慢查询日志并生成统计信息。以下是常用命令示例：
• 查看最慢的 10 条查询：

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

• 查看未使用索引的查询：

sudo mysqldumpslow -g 'not_using_index' /var/log/mysql/slow.log

• 按执行次数排序：

sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

四、常见问题及解决方案

1. 执行时间很短的查询被记录

默认情况下，MySQL 只会记录执行时间超过 long_query_time 的查询。如果执行时间很短的查询也被记录，可能是以下原因：

(1) 启用了 log_queries_not_using_indexes

如果启用了该选项，MySQL 会记录所有未使用索引的查询，即使它们的执行时间很短。

检查是否启用：

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

禁用该选项：

SET GLOBAL log_queries_not_using_indexes = 'OFF';

(2) long_query_time 设置过低

如果 long_query_time 设置得非常低（例如 0.001 秒），即使是执行时间很短的查询也会被记录。

检查当前设置：

SHOW VARIABLES LIKE 'long_query_time';

调整阈值：

SET GLOBAL long_query_time = 2;

(3) 查询涉及 INFORMATION_SCHEMA 表

某些工具生成的查询（如 INFORMATION_SCHEMA.PROFILING）可能会被记录，即使它们的执行时间很短。

优化建议：
• 避免频繁查询 INFORMATION_SCHEMA。
• 使用缓存减少查询频率。

2. 慢查询日志文件过大

如果慢查询日志文件未被正确轮转或清理，可能会导致日志文件过大。

清理日志文件：

echo "" > /var/log/mysql/slow.log

启用日志轮转：
创建 /etc/logrotate.d/mysql-slow 文件：

/var/log/mysql/slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        /usr/bin/mysql -e "FLUSH SLOW LOGS;"
    endscript
}

五、优化慢查询的建议

1. 添加索引：确保查询字段已建立索引。

   CREATE INDEX idx_name ON table_name(column_name);
   

2. 优化 SQL 语句：避免全表扫描，使用 EXPLAIN 分析查询计划。

   EXPLAIN SELECT * FROM table_name WHERE condition;
   

3. 调整表结构：将大表拆分为小表，或使用分区表。
4. 缓存结果：对于频繁查询且数据变化较少的场景，使用缓存（如 Redis）。

六、总结

慢查询日志是优化 MySQL 性能的重要工具，但需要正确配置和使用。通过本文的指南，您可以轻松开启慢查询日志，并解决常见问题。如果仍有疑问，欢迎在评论区留言讨论！

参考文档：
• MySQL 官方文档
• mysqldumpslow 使用指南