MySQL慢查询日志开启与分析命令使用指南

慢查询日志的核心作用

在数据库性能优化中，慢查询日志是发现和解决性能瓶颈的关键工具。它通过记录超过指定执行时间的SQL语句，帮助开发人员和数据库管理员精准识别低效查询。本文将详细讲解MySQL慢查询日志的配置方法、分析工具以及优化案例，为性能提升提供实用方案。

慢查询日志的核心参数配置

MySQL通过三个核心参数来控制慢查询日志的行为，可以根据实际需求进行调整：

1. 慢查询日志开关（slow_query_log）

SET GLOBAL slow_query_log = ON;

默认为关闭状态（0/OFF），开启后需要通过重启服务使配置生效。

2. 慢查询时间阈值（long_query_time）

SET GLOBAL long_query_time = 2;

默认设定为10秒，建议在生产环境中根据实际情况调整为1-5秒。MySQL 5.1及以上版本支持微秒精度设置。

3. 未使用索引查询记录（log_queries_not_using_indexes）

默认状态下关闭，用于记录未使用索引的查询。建议在排查索引问题时临时开启，但需配合log_throttle_queries_not_using_indexes参数避免日志过多。

慢查询日志的开启方法

方法一：通过配置文件永久生效

在Linux系统的my.cnf或Windows系统的my.ini文件中进行以下配置：

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

完成配置后，重启MySQL服务：

Linux系统：

sudo systemctl restart mysql

Windows系统：

net stop mysql && net start mysql

方法二：动态修改临时生效

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

注意：动态配置会在MySQL重启后失效，需同步修改配置文件。

慢查询日志的分析工具

1. 基础工具：mysqldumpslow

这是一个由MySQL提供的命令行工具，适用于快速统计高频慢查询。具体使用方式：

按总执行时间排序并显示前10条慢查询：

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

按查询次数排序并显示最频繁的5条慢查询：

mysqldumpslow -s c -t 5 /var/log/mysql/mysql-slow.log

过滤特定查询：

mysqldumpslow -g "SELECT * FROM orders" /var/log/mysql/mysql-slow.log

2. 进阶工具：pt-query-digest（Percona Toolkit）

Percona Toolkit中的pt-query-digest提供了更为详细的分析报告，支持生成HTML格式的可视化报告。

CentOS系统安装方式：

yum install percona-toolkit

生成报告命令：

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

生成HTML格式报告：

pt-query-digest --report-format html /var/log/mysql/mysql-slow.log > slow_report.html

指标解读

• 查询时间分布（Query_time distribution）：表示不同时间范围内查询的比例情况。
• 排名（Rank）：根据总执行时间对查询进行排名。
• 响应时间（Response）：表示查询的平均响应时间和最大响应时间。
• 吞吐量（Throughput）：表示单位时间内处理的查询数量。

慢查询优化实战案例

案例一：电商平台订单统计优化

背景：某电商平台订单统计接口响应时间超过5秒，涉及2000万行订单表的复杂查询。

优化步骤

1. 开启慢查询日志

在配置文件中进行如下配置：

slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log

2. 分析日志定位瓶颈

通过pt-query-digest生成报告，发现以下是慢查询的典型代表：

SELECT oi.product_id, SUM(oi.quantity) AS total_sold
FROM order_item oi
JOIN `order` o ON oi.order_id = o.id
WHERE o.status = 'COMPLETED' AND o.created_at BETWEEN '2023-01-01' AND '2023-06-30'
GROUP BY oi.product_id;

通过EXPLAIN分析可得，order表出现了全表扫描，导致查询性能严重下降。

3. 优化方案

.addComponent('添加组合索引')

ALTER TABLE `order` ADD INDEX idx_status_created_at (status, created_at);

优化后的EXPLAIN结果：

type: range, key: idx_status_created_at, rows: 50000, Extra: Using where; Using index

查询时间从5秒优化至500毫秒。

案例二：读写分离与分区表优化

背景：高并发读操作导致主库负载过高，需通过读写分离和分区表优化系统性能。

优化步骤

1. 配置读写分离

使用Sequelize搭配XORM实现读请求路由到从库：

const sequelize = new Sequelize('db', 'user', 'pass', {
dialect: 'mysql',
replication: {
read: [{ host: 'slave1', username: 'user', password: 'pass' }],
write: { host: 'master', username: 'user', password: 'pass' }
}
});

2. 分区表改造

按时间范围拆分订单表：

ALTER TABLE `order`
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION pMax VALUES LESS THAN MAXVALUE
);

在新增查询时，确保时间范围限制在单一分区内：

WHERE created_at >= '2023-01-01' AND created_at

效果

主库负载下降60%，读接口响应时间稳定在200毫秒以内。

慢查询日志管理的最佳实践

1. 日志轮转与清理

建议使用logrotate工具进行日志管理，避免磁盘空间被耗尽。

示例配置：

/var/log/mysql/mysql-slow.log {
daily
rotate 7
compress
missingok
notifempty
create 640 mysql mysql
}

2. 生产环境建议

• 避免长期开启log_queries_not_using_indexes，防止日志量异常增长。
• 结合监控系统（如Prometheus+Grafana）实时监控和报警慢查询。

3. 索引优化原则

• 对WHERE、JOIN、ORDER BY中高频出现的字段添加索引。
• 避免过度索引，额外的索引会导致写入性能下降约10%。

总结

MySQL慢查询日志犹如数据库的“黑匣子”，通过合理的日志配置、选择合适的分析工具（如mysqldumpslow、pt-query-digest），并结合EXPLAIN执行计划分析，能够系统性地解决数据库性能瓶颈。实际优化过程中，需根据业务场景灵活运用索引优化、SQL重写、读写分离等多种技术手段，持续监控并不断迭代优化方案，以实现最佳的性能效果。