MySQL慢查询监控与优化实战指南-平芜编程栈

1. 慢查询SQL的识别价值与核心原理

当数据库开始出现性能瓶颈时，慢查询往往是首要怀疑对象。我经历过一个电商系统在促销期间崩溃的惨痛教训——事后分析发现，一条未被及时发现的商品分类查询SQL在流量激增时拖垮了整个数据库集群。这个经历让我深刻认识到，慢查询监控不是可选项，而是数据库运维的生命线。

MySQL的慢查询识别机制本质上是个"执行时间过滤器"。通过设定阈值（默认10秒），系统会自动记录所有执行时间超过该阈值的SQL语句。但这里有个关键细节：这个时间计算的是实际执行时间（execution time），而非响应时间（response time），这意味着网络延迟等因素不会被计入。在MySQL 5.7及以上版本中，时间精度可以达到微秒级，这对于高性能应用尤为重要。

慢查询日志的核心参数包括：

slow_query_log：开关（1开启/0关闭）
slow_query_log_file：日志文件路径
long_query_time：阈值（秒）
log_queries_not_using_indexes：记录未使用索引的查询
log_throttle_queries_not_using_indexes：限制每分钟记录的未使用索引查询数量

关键提示：生产环境建议将long_query_time设置为1-3秒，电商等高并发场景可能需要0.5秒。但要注意设置过低会导致日志量暴增。

2. 慢查询日志的配置与启用实战

2.1 动态参数设置与持久化

最快的方式是通过SET GLOBAL命令立即生效：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

但这种方式重启后会失效。我建议同时修改配置文件（my.cnf或my.ini）：

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

配置后需要重启MySQL服务或执行FLUSH LOGS命令。有个容易忽略的细节：确保日志目录的权限设置正确：

chown mysql:mysql /var/log/mysql/ chmod 755 /var/log/mysql/

2.2 日志轮转与维护策略

慢查询日志会持续增长，需要定期维护。我推荐以下方案：

使用Linux的logrotate工具创建/etc/logrotate.d/mysql-slow配置文件：

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

对于大型系统，考虑将日志写入单独的分区，避免占满系统空间
高负载环境下，可以启用log_throttle_queries_not_using_indexes防止日志爆炸

3. 慢查询日志的分析方法与工具链

3.1 原生分析工具mysqldumpslow

MySQL自带的mysqldumpslow工具能快速统计慢查询模式：

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

常用参数组合：

-s t按总时间排序
-s l按锁定时间排序
-s at按平均时间排序
-t 10只显示前10条

输出示例：

Count: 5 Time=12.34s (61s) Lock=0.00s (0s) Rows=1000.0 (5000), user1[user1]@[10.0.0.1] SELECT * FROM orders WHERE create_time > 'N'

这个输出告诉我们：这个查询执行了5次，平均耗时12.34秒，每次返回1000行。其中的'N'表示这是个变化的参数值。

3.2 可视化分析工具pt-query-digest

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

pt-query-digest /var/log/mysql/mysql-slow.log --output slow_report.txt

它生成的报告包含：

总体统计：总查询量、唯一查询模式、时间分布
查询排名：按影响排序（执行时间×次数）
每个查询的详细分析：
- 执行计划（EXPLAIN）
- 时间分布直方图
- 表扫描统计

我特别推荐使用--filter参数聚焦关键问题：

pt-query-digest --filter '$event->{arg} =~ /WHERE.*id=\d+/' slow.log

3.3 实时监控与报警方案

对于关键业务系统，建议建立实时监控：

使用Prometheus + mysqld_exporter采集slow_queries指标
配置Grafana仪表盘监控慢查询趋势
设置Alertmanager规则，当慢查询突增时触发报警

示例PromQL查询：

rate(mysql_global_status_slow_queries[5m]) > 5

4. 慢查询SQL的优化实战指南

4.1 索引缺失型慢查询

典型特征：

执行计划显示"ALL"类型扫描
rows_examined远大于rows_sent
日志中显示"Using where; Using filesort"

优化案例：

-- 原查询（耗时3.8秒） SELECT user_name FROM orders WHERE create_time > '2023-01-01'; -- 优化方案 ALTER TABLE orders ADD INDEX idx_create_time (create_time);

注意点：

避免过度索引，每个索引会增加写操作开销
多列索引要遵循最左前缀原则
使用EXPLAIN FORMAT=JSON获取更详细的执行计划

4.2 复杂连接查询优化

典型问题场景：

SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending' ORDER BY o.create_time DESC LIMIT 100;

优化策略：

确保连接字段有索引（o.user_id和u.id）
对于排序字段添加索引（o.create_time）
考虑使用覆盖索引：

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

4.3 分页查询深度优化

深度分页是常见性能杀手：

-- 低效写法（偏移量越大越慢） SELECT * FROM products ORDER BY id LIMIT 100000, 20; -- 优化方案1：基于游标的分页 SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20; -- 优化方案2：延迟关联 SELECT p.* FROM products p JOIN (SELECT id FROM products ORDER BY id LIMIT 100000, 20) AS tmp ON p.id = tmp.id;

4.4 临时表与文件排序处理

当看到"Using temporary; Using filesort"警告时：

检查GROUP BY和ORDER BY子句是否使用相同字段
增大sort_buffer_size参数（建议2-4MB）
考虑使用SQL_BIG_RESULT提示

5. 生产环境慢查询治理体系

5.1 分级处理机制

根据影响程度建立四级响应：

紧急（单次>10s）：立即处理，可能kill查询
严重（平均>3s）：当天优化
一般（平均>1s）：本周优化
观察（偶尔>阈值）：记录跟踪

5.2 预防性检查清单

每次上线前检查：

[ ] 所有WHERE条件字段是否有索引
[ ] 分页查询是否使用游标方式
[ ] JOIN操作是否使用小表驱动大表
[ ] 事务是否保持简短
[ ] 是否避免使用SELECT *

5.3 性能测试验证方案

优化后必须验证：

sysbench oltp_read_write \ --db-driver=mysql \ --mysql-host=127.0.0.1 \ --mysql-port=3306 \ --mysql-user=test \ --mysql-password=test \ --mysql-db=sbtest \ --tables=10 \ --table-size=100000 \ --threads=32 \ --time=300 \ --report-interval=10 \ run

监控指标：