MySQL慢查询日志：原理、配置与性能优化实战-开发者社区

1. MySQL慢查询日志：数据库性能优化的第一道防线

作为数据库管理员，我每天上班第一件事就是检查MySQL慢查询日志。这就像医生查看病人的体检报告一样，通过分析这些"异常指标"，我们能快速定位数据库的性能瓶颈。慢查询日志记录了所有执行时间超过阈值的SQL语句，是优化数据库性能最直接的突破口。

在电商大促期间，我们曾通过慢查询日志发现一个毫不起眼的商品查询SQL，在高峰期竟拖慢了整个数据库集群。经过优化后，系统吞吐量直接提升了40%。这就是为什么我认为慢查询日志是DBA工具箱里最实用的工具之一。

2. 慢查询日志核心原理与配置

2.1 慢查询日志的工作原理

MySQL服务器在执行SQL语句时，会默默计时。当语句执行时间超过long_query_time参数设定的阈值（默认10秒），就会将这条SQL的详细信息记录到慢查询日志中。记录的内容包括：

执行时间戳
查询耗时
锁定时间
返回行数
扫描行数
完整的SQL语句

注意：即使SQL执行结果为空，只要执行时间超过阈值也会被记录。这是很多开发者容易忽略的点。

2.2 关键配置参数详解

在my.cnf配置文件中，这些参数控制着慢查询日志的行为：

slow_query_log = 1 # 启用慢查询日志 slow_query_log_file = /var/log/mysql/mysql-slow.log # 日志文件路径 long_query_time = 2 # 阈值设为2秒 log_queries_not_using_indexes = 1 # 记录未使用索引的查询 min_examined_row_limit = 100 # 仅记录扫描超过100行的查询

建议生产环境将long_query_time设置为1-3秒。对于高频查询接口，甚至可以考虑0.5秒的阈值。

3. 慢查询日志实战分析

3.1 日志格式解析

一条典型的慢查询日志记录如下：

# Time: 2023-08-20T14:23:45.123456Z # User@Host: user[user] @ [192.168.1.100] # Query_time: 5.123456 Lock_time: 0.001234 Rows_sent: 1 Rows_examined: 100000 SET timestamp=1692548625; SELECT * FROM orders WHERE user_id=123 AND status='pending';

关键字段解读：

Query_time：实际执行时间（秒）
Lock_time：等待表锁的时间
Rows_sent：返回给客户端的行数
Rows_examined：扫描的行数

3.2 使用mysqldumpslow工具分析

MySQL自带的mysqldumpslow工具可以汇总慢查询日志：

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

常用参数：

-s t按总时间排序
-s l按锁定时间排序
-s r按返回行数排序
-t 10只显示前10条结果

输出示例：

Count: 5 Time=3.12s (15s) Lock=0.00s (0s) Rows=1.0 (5), user[user]@[192.168.1.100] SELECT * FROM products WHERE category='electronics' AND price<N

这表示该查询被执行了5次，平均耗时3.12秒，总耗时15秒。

4. 慢查询优化实战技巧

4.1 索引优化案例

发现慢查询：

SELECT * FROM orders WHERE create_time > '2023-01-01' AND status = 'shipped';

优化步骤：

使用EXPLAIN分析执行计划
发现没有使用到status字段的索引

添加复合索引：

ALTER TABLE orders ADD INDEX idx_status_createtime (status, create_time);

4.2 SQL重写技巧

原始慢查询：

SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total_amount > 1000

优化方案：

SELECT u.* FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 1000 )

优化原理：使用EXISTS替代DISTINCT+JOIN，减少临时表生成。

5. 生产环境最佳实践

5.1 日志轮转策略

为防止日志文件过大，建议配置日志轮转：

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

5.2 监控与告警方案

使用Prometheus+Grafana监控慢查询趋势：

配置mysqld_exporter收集慢查询指标
Grafana面板设置告警规则：
- 每分钟慢查询数 > 10
- 相同SQL模板重复出现
对接企业微信/钉钉告警

6. 高级应用场景

6.1 使用pt-query-digest深度分析

Percona Toolkit中的pt-query-digest提供更强大的分析能力：

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

输出包括：

查询响应时间分布
执行频率统计
表访问分析
索引建议

6.2 与Performance Schema结合

MySQL 5.7+版本可以将慢查询日志与performance_schema结合：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

这样可以在不开启慢查询日志的情况下，通过SQL直接查询慢查询统计信息。

7. 常见问题排查指南

7.1 日志没有记录预期查询

可能原因：

查询实际执行时间低于阈值
使用了缓存（query_cache或应用层缓存）
日志文件权限问题

检查步骤：

SHOW VARIABLES LIKE 'slow_query%'; SHOW STATUS LIKE 'Qcache%';

7.2 日志文件增长过快

解决方案：

调整long_query_time阈值
设置log_queries_not_using_indexes=0
增加min_examined_row_limit值
使用log_slow_filter过滤特定查询

8. 性能优化checklist

每次分析慢查询日志时，按照这个清单逐一检查：

[ ] 是否缺少合适的索引？
[ ] SQL是否有优化空间（如避免SELECT *）？
[ ] 是否存在不必要的全表扫描？
[ ] 是否可以添加查询缓存？
[ ] 是否需要调整表结构（如分区）？
[ ] 是否可以考虑读写分离？

经过多年实践，我发现80%的数据库性能问题都能通过慢查询日志定位。关键在于建立定期检查机制，将性能优化变成日常运维的一部分，而不是等到系统崩溃才临时救火。

MySQL慢查询日志：原理、配置与性能优化实战