MySQL慢查询日志的配置与启用

MySQL慢查询日志简介

在MySQL数据库的运维和优化工作中，慢查询日志是一个极为重要的工具。它记录了执行时间超过指定阈值的SQL语句，帮助数据库管理员（DBA）和开发人员定位性能瓶颈。MySQL从5.0.3版本开始支持慢查询日志功能。

慢查询日志的存在意义在于，随着业务的发展，数据库中的SQL查询变得越来越复杂，有些查询可能会因为各种原因（如索引缺失、查询语句编写不当等）执行时间过长，从而影响整个系统的性能。通过分析慢查询日志，我们可以找出这些耗时较长的查询，针对性地进行优化，提升数据库整体的运行效率。

配置MySQL慢查询日志

查看当前MySQL慢查询日志相关配置

在配置之前，我们首先需要查看MySQL当前关于慢查询日志的配置情况。可以通过以下SQL语句来获取：

SHOW VARIABLES LIKE '%slow_query_log%';

上述语句执行后，会返回两个变量：slow_query_log 和 slow_query_log_file。slow_query_log 表示慢查询日志是否开启，0代表关闭，1代表开启；slow_query_log_file 则表示慢查询日志文件的路径。

同时，我们还可以查看慢查询的时间阈值配置，使用以下语句：

SHOW VARIABLES LIKE '%long_query_time%';

long_query_time 变量的值就是慢查询的时间阈值，单位为秒。默认值是10秒，即执行时间超过10秒的SQL语句会被记录到慢查询日志中。

修改MySQL配置文件开启慢查询日志

MySQL的配置文件在不同的操作系统和安装方式下位置可能有所不同。在Linux系统中，常见的配置文件路径为 /etc/my.cnf 或 /etc/mysql/my.cnf；在Windows系统中，通常是 my.ini 文件。

以Linux系统的 /etc/my.cnf 文件为例，我们需要在文件中添加或修改以下配置项：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

上述配置中，slow_query_log = 1 表示开启慢查询日志；slow_query_log_file 指定了慢查询日志文件的路径为 /var/log/mysql/slow-query.log，你可以根据实际情况进行调整；long_query_time = 2 将慢查询的时间阈值设置为2秒，即执行时间超过2秒的SQL语句会被记录。

修改完配置文件后，需要重启MySQL服务使配置生效。在Linux系统中，可以使用以下命令重启MySQL：

sudo systemctl restart mysql

在Windows系统中，可以通过服务管理界面找到MySQL服务并选择重启。

使用SET语句动态开启慢查询日志（临时生效）

除了修改配置文件这种永久生效的方式外，我们还可以使用 SET 语句在MySQL运行时动态开启慢查询日志，但这种方式只在当前会话或全局（需有相应权限）临时生效，MySQL重启后设置会失效。

要在当前会话中开启慢查询日志并设置时间阈值，可以使用以下语句：

SET SESSION slow_query_log = 1;
SET SESSION long_query_time = 2;

如果要在全局范围内开启慢查询日志并设置时间阈值，需要有足够的权限，使用以下语句：

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;

需要注意的是，使用 SET GLOBAL 语句修改的配置，新的连接会使用新的配置，但已有的连接不会受到影响。

慢查询日志内容解析

当我们成功配置并启用慢查询日志后，MySQL会将符合条件的慢查询记录到指定的日志文件中。慢查询日志文件的内容格式大致如下：

# Time: 230815 16:14:33
# User@Host: root[root] @ localhost []  Id:     7
# Query_time: 2.001746  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 10000
SET timestamp=1692096873;
SELECT * FROM large_table WHERE column_name = 'value';

下面对日志内容的各个部分进行详细解析：

时间部分：# Time: 230815 16:14:33 表示该慢查询执行的时间，这里是2023年8月15日16点14分33秒。
用户和主机部分：# User@Host: root[root] @ localhost [] Id: 7，User@Host 表示执行查询的用户和主机，这里是 root 用户从 localhost 连接；Id 是该连接的唯一标识符。
查询时间、锁时间、返回行数和扫描行数部分：# Query_time: 2.001746 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 10000，Query_time 是查询执行的总时间，单位为秒；Lock_time 是查询获取锁的时间；Rows_sent 是查询返回给客户端的行数；Rows_examined 是查询在执行过程中扫描的行数。
SQL语句部分：SET timestamp=1692096873; SELECT * FROM large_table WHERE column_name = 'value';，SET timestamp 语句设置了查询执行时的时间戳，后面紧跟的就是实际执行的SQL语句。

慢查询日志的高级配置

记录未使用索引的查询

除了记录执行时间超过阈值的查询外，MySQL还可以配置记录那些未使用索引的查询，这对于发现潜在的性能问题非常有帮助。要开启这个功能，同样在MySQL配置文件的 [mysqld] 段中添加以下配置：

log_queries_not_using_indexes = 1

开启此功能后，即使查询执行时间没有超过 long_query_time 设置的阈值，但如果未使用索引，也会被记录到慢查询日志中。

日志文件的轮换和清理

随着时间的推移，慢查询日志文件可能会变得非常大，占用大量的磁盘空间。为了避免这种情况，我们可以配置日志文件的轮换和清理策略。

在Linux系统中，可以结合 logrotate 工具来实现日志文件的自动轮换。首先，确保 logrotate 工具已经安装，然后在 /etc/logrotate.d/ 目录下创建一个新的配置文件，例如 mysql-slow-query，内容如下：

/var/log/mysql/slow-query.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

上述配置表示每天对 /var/log/mysql/slow-query.log 文件进行轮换，保留7天的日志文件，轮换后的日志文件进行压缩，并且在轮换后执行 mysqladmin flush-logs 命令通知MySQL重新生成日志文件。

利用慢查询日志进行性能优化

分析慢查询日志的工具

mysqldumpslow：这是MySQL自带的分析慢查询日志的工具。它可以对慢查询日志进行统计分析，帮助我们快速定位问题。例如，要统计慢查询日志中执行次数最多的10条查询，可以使用以下命令：

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

其中，-s c 表示按照执行次数排序，-t 10 表示只显示前10条。mysqldumpslow 还有很多其他的选项，如 -s t 按照查询时间排序，-s al 按照平均锁定时间排序等。 2. pt-query-digest：这是Percona Toolkit中的一个工具，功能比 mysqldumpslow 更强大。它可以生成详细的报告，包括查询的执行频率、平均执行时间、锁时间等信息。首先需要安装Percona Toolkit，然后使用以下命令分析慢查询日志：

pt-query-digest /var/log/mysql/slow-query.log

pt-query-digest 会输出一份详细的报告，帮助我们更全面地了解慢查询的情况。

优化示例

假设在慢查询日志中发现了这样一条查询：

SELECT * FROM orders WHERE customer_id = 1234;

并且从日志中得知 Rows_examined 的值非常大，说明全表扫描了 orders 表。此时，我们可以通过添加索引来优化该查询：

CREATE INDEX idx_customer_id ON orders (customer_id);

添加索引后，再次执行相同的查询，查询速度通常会有显著提升，并且在后续的慢查询日志中，这条查询可能就不会再出现了。

注意事项

性能影响：虽然慢查询日志对于性能优化非常有帮助，但开启慢查询日志会对MySQL的性能产生一定的影响，因为记录日志本身需要I/O操作。因此，在生产环境中，需要权衡利弊，合理设置时间阈值，避免记录过多不必要的查询。
日志文件安全：慢查询日志中可能包含敏感信息，如SQL语句中的表结构、数据等。因此，要确保日志文件的存储位置具有适当的权限设置，防止未经授权的访问。
动态配置的局限性：使用 SET 语句动态配置慢查询日志只在当前会话或全局临时生效，MySQL重启后设置会失效。如果需要永久生效的配置，还是要修改配置文件。

通过合理配置和启用MySQL慢查询日志，并结合分析工具对日志进行深入分析，我们可以有效地发现和解决数据库中的性能问题，提升系统的整体运行效率。在实际的数据库运维和开发工作中，慢查询日志是一个不可或缺的工具，需要我们熟练掌握和运用。