`MySQL`的`慢`查询`日志`：`long_query_time`和`log_slow_admin_statements`的`配置`。

MySQL 慢查询日志：long_query_time 和 log_slow_admin_statements 配置详解

大家好，今天我们来深入探讨 MySQL 慢查询日志，重点关注两个核心配置参数：long_query_time 和 log_slow_admin_statements。 慢查询日志是定位性能瓶颈，优化数据库性能的关键工具。 理解并正确配置这两个参数，可以帮助我们更有效地监控和诊断 MySQL 数据库的性能问题。

1. 慢查询日志概述

慢查询日志是 MySQL 提供的一种用于记录执行时间超过指定阈值的 SQL 语句的日志。 通过分析慢查询日志，我们可以找出执行效率低的 SQL 语句，进而进行优化，提高数据库整体性能。

慢查询日志的作用：

• 发现性能瓶颈： 快速识别执行时间长的 SQL 语句，定位潜在的性能问题。
• 优化 SQL 语句： 针对慢查询进行分析，优化 SQL 语句的结构、索引等，提高执行效率。
• 监控数据库性能： 跟踪慢查询的出现频率和执行时间，监控数据库的性能趋势。
• 诊断系统问题： 慢查询可能是由于硬件资源不足、配置不当等系统问题引起的，通过分析慢查询日志可以帮助诊断这些问题。

2. long_query_time 参数：慢查询阈值的设定

long_query_time 参数用于设置 SQL 语句执行时间超过多少秒才会被记录到慢查询日志中。 默认值为 10 秒。

long_query_time 的重要性：

long_query_time 的设置直接影响到慢查询日志中记录的 SQL 语句的数量。 如果设置得过高，可能会忽略一些需要优化的 SQL 语句； 如果设置得过低，可能会导致慢查询日志过于庞大，增加分析的难度。

配置 long_query_time：

可以通过以下两种方式配置 long_query_time：

• 全局配置： 修改 MySQL 配置文件（例如 my.cnf 或 my.ini），并重启 MySQL 服务。

  [mysqld]
  long_query_time = 2

• 会话配置： 在当前会话中临时修改 long_query_time，不会影响其他会话。

  SET GLOBAL long_query_time = 2; -- 修改全局变量，需要SUPER权限
  SET SESSION long_query_time = 2; -- 仅修改当前会话的变量

查看 long_query_time 的值：

可以通过以下 SQL 语句查看当前的 long_query_time 值：

SHOW VARIABLES LIKE 'long_query_time';

long_query_time 设置的建议：

long_query_time 的设置应该根据实际情况进行调整。 一般来说，可以从一个较低的值开始，例如 1 秒或 2 秒，然后根据慢查询日志的记录情况进行调整。

• 初期监控： 设置较低的值，例如 1 秒，以便收集更多的慢查询信息。
• 生产环境： 根据业务特点和系统性能，设置一个合适的值。 频繁出现慢查询，则适当降低 long_query_time；慢查询较少，可以适当提高 long_query_time。
• 定期调整： 随着业务发展和数据量的增加，需要定期评估和调整 long_query_time 的值。

示例：

假设我们需要将 long_query_time 设置为 2 秒，并查看是否生效：

-- 设置全局 long_query_time 为 2 秒
SET GLOBAL long_query_time = 2;

-- 查看 long_query_time 的值
SHOW VARIABLES LIKE 'long_query_time';

-- 执行一条超过 2 秒的 SQL 语句（这里假设 sleep(3) 执行时间超过 2 秒）
SELECT SLEEP(3);

执行完上述 SQL 语句后，如果启用了慢查询日志，并且日志文件配置正确，那么该 SELECT SLEEP(3) 语句将被记录到慢查询日志中。

3. log_slow_admin_statements 参数：管理语句的记录

log_slow_admin_statements 参数用于控制是否将管理语句（例如 ALTER TABLE、ANALYZE TABLE、OPTIMIZE TABLE 等）记录到慢查询日志中。 默认值为 OFF。

log_slow_admin_statements 的重要性：

管理语句通常会对数据库的性能产生较大的影响，例如锁定表、重建索引等。 记录这些语句的执行时间，可以帮助我们发现潜在的性能问题，例如长时间的表锁定、索引重建等。

配置 log_slow_admin_statements：

可以通过以下两种方式配置 log_slow_admin_statements：

• 全局配置： 修改 MySQL 配置文件（例如 my.cnf 或 my.ini），并重启 MySQL 服务。

  [mysqld]
  log_slow_admin_statements = ON

• 会话配置： 在当前会话中临时修改 log_slow_admin_statements，不会影响其他会话。

  SET GLOBAL log_slow_admin_statements = ON; -- 修改全局变量，需要SUPER权限
  SET SESSION log_slow_admin_statements = ON; -- 仅修改当前会话的变量

查看 log_slow_admin_statements 的值：

可以通过以下 SQL 语句查看当前的 log_slow_admin_statements 值：

SHOW VARIABLES LIKE 'log_slow_admin_statements';

log_slow_admin_statements 设置的建议：

是否启用 log_slow_admin_statements 取决于实际需求。 如果需要监控管理语句的执行时间，以便发现潜在的性能问题，则建议启用该参数。

• 开发环境： 建议启用 log_slow_admin_statements，以便及时发现潜在的性能问题。
• 生产环境： 根据实际情况决定是否启用。 如果管理语句的执行频率较高，并且对性能有较大影响，则建议启用该参数。

示例：

假设我们需要启用 log_slow_admin_statements，并执行一条管理语句：

-- 启用 log_slow_admin_statements
SET GLOBAL log_slow_admin_statements = ON;

-- 查看 log_slow_admin_statements 的值
SHOW VARIABLES LIKE 'log_slow_admin_statements';

-- 执行一条管理语句（这里假设 OPTIMIZE TABLE 执行时间超过 long_query_time）
OPTIMIZE TABLE your_table;

执行完上述 SQL 语句后，如果启用了慢查询日志，并且日志文件配置正确，那么该 OPTIMIZE TABLE 语句将被记录到慢查询日志中。 需要注意的是，OPTIMIZE TABLE 的执行时间是否超过 long_query_time 取决于表的大小和结构。

4. 慢查询日志的启用和配置

除了 long_query_time 和 log_slow_admin_statements 之外，还需要配置慢查询日志的启用和日志文件的存储位置。

启用慢查询日志：

可以通过以下两种方式启用慢查询日志：

• 全局配置： 修改 MySQL 配置文件（例如 my.cnf 或 my.ini），并重启 MySQL 服务。

  [mysqld]
  slow_query_log = ON
  slow_query_log_file = /var/log/mysql/mysql-slow.log

• 会话配置： 在当前会话中临时启用慢查询日志，不会影响其他会话。

  SET GLOBAL slow_query_log = 'ON'; -- 修改全局变量，需要SUPER权限
  SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; -- 修改全局变量，需要SUPER权限

查看慢查询日志的启用状态：

可以通过以下 SQL 语句查看当前的慢查询日志启用状态：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

慢查询日志文件的配置：

• slow_query_log_file： 指定慢查询日志文件的存储路径。 建议将慢查询日志文件存储在一个单独的目录下，方便管理和分析。
• log_output： 指定慢查询日志的输出方式。 可以选择 FILE（输出到文件）或 TABLE（输出到 mysql.slow_log 表）。 建议使用 FILE 方式，因为 TABLE 方式可能会影响数据库的性能。
• long_query_time： 设置慢查询的阈值，上面已经详细讨论。

示例：

假设我们需要启用慢查询日志，并将日志文件存储在 /var/log/mysql/mysql-slow.log：

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询日志文件
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 查看慢查询日志的启用状态
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

5. 慢查询日志的分析

启用慢查询日志后，我们需要定期分析日志文件，找出需要优化的 SQL 语句。

慢查询日志分析工具：

• mysqldumpslow： MySQL 自带的慢查询日志分析工具。 可以按照执行时间、访问次数等对慢查询日志进行排序和统计。
• pt-query-digest： Percona Toolkit 提供的慢查询日志分析工具。 功能更加强大，可以提供更详细的分析报告。

mysqldumpslow 的使用示例：

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

• -s t：按照执行时间排序。
• -t 10：显示前 10 条慢查询。

pt-query-digest 的使用示例：

pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt

6. 常见问题和解决方案

问题： 慢查询日志文件过大，导致磁盘空间不足。

解决方案：

• 定期清理： 使用 logrotate 等工具定期清理慢查询日志文件。
• 调整 long_query_time： 适当提高 long_query_time 的值，减少慢查询日志的记录数量。
• 归档日志： 将历史的慢查询日志文件归档到其他存储介质。

问题： 慢查询日志中出现大量的 SELECT SLEEP() 语句。

解决方案：

• 检查应用程序： 检查应用程序是否存在不必要的 SELECT SLEEP() 语句。
• 限制 SLEEP() 函数的使用： 可以通过配置 MySQL 参数限制 SLEEP() 函数的使用。

问题： 启用 log_slow_admin_statements 后，慢查询日志中出现大量的 ALTER TABLE 语句。

解决方案：

• 优化 ALTER TABLE 操作： 尽量避免在大表上执行 ALTER TABLE 操作。 如果必须执行，可以考虑使用在线 DDL 工具，例如 pt-online-schema-change。
• 调整执行时间： 在业务低峰期执行 ALTER TABLE 操作。

总结：

参数	作用	建议
long_query_time	设置慢查询的阈值，超过该阈值的 SQL 语句会被记录到慢查询日志中。	根据实际情况调整，初期监控时可以设置较低的值，生产环境需要定期评估和调整。
log_slow_admin_statements	控制是否将管理语句记录到慢查询日志中。	开发环境建议启用，生产环境根据实际情况决定。
slow_query_log	启用/禁用慢查询日志	生产环境建议启用
slow_query_log_file	指定慢查询日志文件的存储路径	建议存储在一个单独的目录下，方便管理和分析。

7. 优化慢查询的思路

• 索引优化： 确保查询语句使用了合适的索引。 可以使用 EXPLAIN 命令分析 SQL 语句的执行计划，查看是否使用了索引，以及索引的使用情况。
• SQL 语句优化： 优化 SQL 语句的结构，避免全表扫描、复杂的连接操作等。
• 硬件资源优化： 检查服务器的硬件资源，例如 CPU、内存、磁盘 I/O 等，是否足够。
• 数据库配置优化： 调整 MySQL 的配置参数，例如 innodb_buffer_pool_size、key_buffer_size 等，以提高数据库的性能。
• 代码层面优化： 检查应用程序是否存在性能瓶颈，例如频繁的数据库连接、大量的 ORM 操作等。

8. 善用慢查询日志，提升数据库性能

今天我们详细讨论了 MySQL 慢查询日志的 long_query_time 和 log_slow_admin_statements 配置，以及慢查询日志的启用、配置和分析。 记住，慢查询日志是优化数据库性能的利器。 通过合理配置和定期分析慢查询日志，我们可以及时发现和解决性能问题，提升数据库的整体性能。 持续的监控和优化，是保证数据库系统稳定高效运行的关键。