如何在 MySQL 和 MariaDB 中启用慢查询日志

慢查询日志是 MySQL 和 MariaDB 内置的诊断功能，用于记录执行时间超过可配置阈值的每条 SQL 语句。它捕获查询持续时间、锁定时间、检查行数、发送行数以及完整的 SQL 文本——为数据库管理员和开发人员提供精确的、基于文件的审计记录，追踪每一条影响应用程序性能的查询。

启用慢查询日志是数据库性能调优期间可采取的最高效措施之一。与通用监控工具不同，慢查询日志能精确定位导致延迟的具体语句，使其在任何服务器上都不可或缺——无论是单租户 VPS 托管环境，还是多节点专用数据库集群——对于索引优化、查询重构和容量规划都至关重要。

为何慢查询日志的价值超越基础监控

大多数团队在用户报告系统缓慢后，才被动地使用 EXPLAIN 或 SHOW PROCESSLIST。慢查询日志则主动发挥作用：它在数小时或数天的真实流量中积累证据，捕获在手动检查窗口期间从未出现的间歇性问题查询。

主要运营优势包括：

• 瓶颈隔离——通过 Query_time 与 Lock_time 的比率，区分 CPU 密集型全表扫描与锁竞争问题
• 索引缺口分析——log_queries_not_using_indexes 标志可发现每一个执行全表扫描的查询，无论其原始执行时间如何
• 回归检测——比较部署前后的日志快照，可揭示新代码是否引入了更慢的查询模式
• 容量规划依据——Rows_examined 值远高于 Rows_sent 数个数量级，表明索引缺失或使用不当，在负载下会进一步加剧

MySQL 与 MariaDB：慢查询日志功能对比

两种数据库引擎共享继承自 MySQL 5.1 的相同核心慢查询日志基础架构，但 MariaDB 在多个重要方面进行了扩展。

MariaDB 中的 log_slow_verbosity 和 log_slow_rate_limit 选项在高吞吐量生产环境中尤为有价值，因为在这类环境中，记录每一条慢查询本身可能成为性能负担。

第一步：定位配置文件

MySQL 和 MariaDB 根据发行版和安装方式的不同，从不同的默认路径读取配置。

MySQL：

/etc/my.cnf（基于 RPM：RHEL、CentOS、AlmaLinux、Rocky Linux）
/etc/mysql/my.cnf（Debian/Ubuntu）
/etc/mysql/mysql.conf.d/mysqld.cnf（使用 mysql-server 软件包的 Ubuntu）

MariaDB：

/etc/my.cnf.d/server.cnf（基于 RPM）
/etc/mysql/mariadb.conf.d/50-server.cnf（Debian/Ubuntu）
/etc/mysql/mariadb.cnf（较旧的 Debian 布局）

如果不确定哪个文件处于活动状态，可查询正在运行的进程：
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
这将打印守护进程在启动时读取的文件的精确有序列表，包括任何 !includedir 目录。
使用您偏好的编辑器打开主配置文件：
sudo nano /etc/my.cnf
第二步：将慢查询日志指令添加到 [mysqld]
所有慢查询日志参数均属于 [mysqld] 部分。如果该部分不存在，请在文件顶部创建它。
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
参数说明：

slow_query_log = 1——激活该功能；设置为 0 可在不删除配置块的情况下禁用
slow_query_log_file——日志文件的绝对路径；MySQL/MariaDB 进程用户（mysql）必须对父目录具有写入权限
long_query_time = 1——以秒为单位的阈值，接受小数值（例如，0.5 表示 500 毫秒）；默认的 10 秒对于 Web 应用程序来说几乎总是过于宽松
log_queries_not_using_indexes——无论 long_query_time 如何，都记录全表扫描查询；与 min_examined_row_limit 结合使用，可抑制来自小表的噪音
min_examined_row_limit——查询必须至少检查这么多行，才能在 log_queries_not_using_indexes 下符合记录条件；防止微不足道的单行查找污染日志
log_slow_admin_statements——捕获会阻塞表的模式级操作，这类操作经常被忽视为延迟来源

值得在生产环境中启用的 MariaDB 专属附加配置：
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain 将优化器的执行计划直接附加到每条日志条目中，无需在事后手动重新运行 EXPLAIN——在诊断仅在生产负载模式下出现的查询时，可节省大量时间。
第三步：创建日志文件并设置权限
如果目标目录不存在，请在重启服务之前创建它并分配所有权。跳过此步骤是慢查询日志静默失败、无法激活的最常见原因之一。
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
在启用 SELinux 的系统（RHEL、CentOS、AlmaLinux）上，还必须正确设置文件上下文：
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
未能设置正确的 SELinux 上下文会导致守护进程成功启动，但静默跳过写入日志文件——这是一个令人沮丧的边缘情况，在 /var/log/messages 中不会产生明显错误。
第四步：重启数据库服务
通过重启服务来应用配置更改。在基于 systemd 的发行版上（任何现代 Linux 服务器的标准配置）：
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
在较旧的基于 init.d 的系统上：
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
重启后，检查服务是否正常启动：
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
my.cnf 中的任何配置错误都将阻止启动，并出现在日志输出中。
第五步：在运行时启用慢查询日志（无需重启）
对于重启会造成中断的生产服务器，MySQL 和 MariaDB 支持通过 SET GLOBAL 动态启用慢查询日志。以这种方式所做的更改会立即生效，但除非同时写入 my.cnf，否则在服务重启后不会持久保留。
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
这是对实时系统进行紧急诊断的正确方法——启用它，在流量高峰期间采集 15–30 分钟的样本，然后在不修改配置文件或重启守护进程的情况下将其禁用。
第六步：验证配置
连接到 MySQL 或 MariaDB 客户端：
mysql -u root -p
然后对系统变量表运行模式匹配：
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
正确配置的实例的预期输出：
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
您还可以通过检查慢查询计数器来确认日志正在被写入：
SHOW GLOBAL STATUS LIKE 'Slow_queries';
每当查询超过 long_query_time 时，该计数器就会递增，无论文件日志记录是否处于活动状态——这对于在花时间分析空日志文件之前确认慢查询确实正在发生非常有用。
第七步：读取和解读原始日志
在负载测试或流量高峰期间，使用 tail 实时监控日志：
sudo tail -f /var/log/mysql/slow-query.log
典型的日志条目如下所示：
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
每个字段的含义：

Query_time——以秒为单位的总挂钟执行时间
Lock_time——等待获取表锁或行锁所花费的时间；Lock_time 与 Query_time 的高比率指向竞争问题，而非缺少索引
Rows_sent——返回给客户端的行数
Rows_examined——存储引擎为产生结果而扫描的行数；Rows_examined / Rows_sent 超过 100:1 是缺少索引或索引选择性差的强烈信号
Bytes_sent——存在于 MariaDB 扩展详细模式中；用于识别返回不必要大结果集的查询

在上面的示例中，该查询检查了 847,293 行才返回 1 行。在 customer_email 上添加索引可将 Rows_examined 减少到约 1，将执行时间从 4.5 秒缩短到毫秒以下。
第八步：使用 mysqldumpslow 和 pt-query-digest 分析日志
大规模读取原始日志文件并不实际。有两种工具可以按总体影响聚合和排名慢查询。
使用 mysqldumpslow（随 MySQL/MariaDB 捆绑）
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow 对查询参数进行规范化处理（将字面值替换为 N 或 S），使得具有不同参数值但结构相同的查询被归为一组——这对于识别高频模式至关重要。
使用 pt-query-digest（Percona Toolkit——推荐用于生产环境）
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest 生成一份排名报告，显示每个查询的指纹、总执行时间、平均时间、调用次数和百分位分布。它比 mysqldumpslow 强大得多，是专业 DBA 进行慢日志分析的标准工具。
第九步：使用 logrotate 配置日志轮转
如果不进行轮转，慢查询日志将无限增长。在繁忙的服务器上，将 long_query_time 设置为 1 秒时，文件可能在数天内达到数 GB。
创建专用的 logrotate 配置：
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
关键指令说明：

rotate 14——保留 14 天的压缩归档；根据您的磁盘预算和审计要求进行调整
compress / delaycompress——对轮转后的文件进行 gzip 压缩，但延迟一个周期再压缩，以避免压缩守护进程可能仍处于打开状态的文件
postrotate——轮转后运行 mysqladmin flush-logs，向守护进程发出信号，关闭当前日志文件句柄并打开新文件；如果没有此配置，MySQL/MariaDB 将继续向重命名后的文件写入，直到下次重启

强制手动轮转以测试配置：
sudo logrotate -f /etc/logrotate.d/mysql-slow
第十步：不再需要时禁用慢查询日志
在高流量服务器上以低阈值（例如 0.5 秒）持续记录慢查询会增加可测量的 I/O 开销。收集到足够数据后，请将其禁用：
通过配置文件（持久）：
[mysqld]
slow_query_log = 0
然后重启服务：
sudo systemctl restart mysqld   # or mariadb
通过运行时变量（立即生效，非持久）：
SET GLOBAL slow_query_log = 'OFF';
运行时方法在生产时段更为可取——它在毫秒内生效，零停机时间。
进阶：将 performance_schema 作为补充使用
慢查询日志捕获超过时间阈值的查询。performance_schema events_statements_summary_by_digest 表捕获每种不同查询模式的聚合统计信息，无论执行时间如何。同时使用两者可获得完整的视图。
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
此查询可发现整个语句历史中耗时最多的前 10 种查询模式——包括运行数百万次、累计占用大量 CPU 时间的快速查询，而这些查询慢查询日志永远不会捕获。
托管环境注意事项
最佳的 long_query_time 阈值在很大程度上取决于服务器的角色和资源配置：

共享托管环境——通常无法直接访问 my.cnf；如果托管提供商授予 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限，可使用 SET GLOBAL，或通过控制面板请求慢日志访问权限
VPS 环境——完整的 root 访问权限意味着对所有配置参数的完全控制；安装了 带 cPanel 的 VPS 可通过 WHM 的 MySQL 配置编辑器访问慢查询日志设置，该编辑器直接写入 my.cnf

如果您的应用程序栈包含通过 VPS 控制面板管理的 Web 前端，将慢查询日志时间戳与应用程序的 HTTP 访问日志时间戳进行关联，是将数据库延迟追溯到特定用户请求的有效方法。

实用决策矩阵：选择合适的阈值

技术检查清单与关键要点

在结束慢查询调查之前，请验证以下每一项：

• my.cnf 中的 [mysqld] 部分包含 slow_query_log = 1、有效的 slow_query_log_file 路径，以及适合您流量配置的 long_query_time
• 日志文件及其父目录由 mysql 系统用户拥有并具有写入权限；在 SELinux 系统上，文件上下文设置为 mysqld_log_t
• SHOW VARIABLES LIKE '%slow_query%' 在服务重启后确认 slow_query_log = ON 和正确的文件路径
• SHOW GLOBAL STATUS LIKE 'Slow_queries' 显示非零且递增的计数器，确认符合条件的查询确实正在发生
• log_queries_not_using_indexes 已启用，并与 min_examined_row_limit 配合使用，以防止微不足道的单行查找充斥日志
• log_slow_admin_statements 已启用，以捕获 ALTER TABLE、OPTIMIZE TABLE 及类似的 DDL 操作，这些操作是意外表锁的常见来源
• 已配置 logrotate，并设置了调用 mysqladmin flush-logs 的 postrotate 钩子
• 您已运行 pt-query-digest 或 mysqldumpslow 对日志进行聚合，并按总执行时间识别出前 3–5 个查询
• 每个已识别的查询已使用 EXPLAIN（或 MySQL 8.0+ 中的 EXPLAIN ANALYZE）进行分析，并已添加适当的索引或重构了查询逻辑
• 优化周期完成后，慢查询日志已被禁用或 long_query_time 已提高，以最小化持续的 I/O 开销

常见问题

启用慢查询日志会影响数据库性能吗？

在典型生产工作负载下，阈值为 1 秒或更高时，开销可以忽略不计——通常不超过总查询执行时间的 1%。只有当 long_query_time 设置低于 0.1 秒，或在包含许多小型未索引表的模式上启用 log_queries_not_using_indexes 时，开销才会变得可测量。使用 log_slow_rate_limit（MariaDB）或提高 min_examined_row_limit 可缓解此问题。

我可以在不重启 MySQL 或 MariaDB 的情况下启用慢查询日志吗？

可以。在任何具有 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限的 MySQL 客户端会话中，使用 SET GLOBAL slow_query_log = 'ON' 和 SET GLOBAL long_query_time = 1。更改立即生效。将相同的值写入 my.cnf 可使其在重启后持久保留。

慢查询日志中 Query_time 和 Lock_time 有什么区别？

Query_time 是从服务器接收查询到向客户端发送最后一行的总挂钟时间。Lock_time 是该总时间中等待获取表锁或行锁所花费的部分。Lock_time 接近 Query_time 的查询是锁竞争问题，而非索引问题——解决方法涉及事务设计或减少锁范围，而非添加索引。

为什么即使 slow_query_log = ON，我的慢查询日志文件仍然是空的？

最常见的原因是：（1）实际上还没有查询超过 long_query_time——使用 SHOW GLOBAL STATUS LIKE 'Slow_queries' 进行验证；（2）日志文件路径不存在，或 mysql 用户缺少写入权限；（3）在 SELinux 系统上，文件上下文不正确；（4）slow_query_log_file 变量指向的路径与您正在检查的文件不同——使用 SHOW VARIABLES LIKE 'slow_query_log_file' 确认。

如何在慢查询日志中找到危害最大的单个查询？

运行 pt-query-digest 并按 R/Call（每次调用检查的行数）或 Response time（总累计时间）排序。Response time 排名顶部的查询消耗了最多的聚合数据库时间，应作为 EXPLAIN 分析和索引优化的首要目标。如果 pt-query-digest 不可用，使用 mysqldumpslow -s t -t 1 提取总时间最高的单个查询。