背景与挑战

在IDC运维与数据库管理中，MySQL慢查询是性能瓶颈的主要诱因之一。实时监控虽能捕获单次慢查询，但缺乏对高频低效SQL的持续筛选能力，导致大量资源被重复性低效语句占用。本文介绍一套基于慢查询日志与性能视图的持续监控方案，聚焦高频低效SQL的自动识别与优化。

监控体系搭建

1. 启用慢查询日志

修改MySQL配置文件my.cnf：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

建议根据业务调整long_query_time（如0.5秒），并开启log_queries_not_using_indexes捕获全表扫描语句。

2. 实时采集与存储

使用pt-query-digest（Percona Toolkit）定期分析慢日志，将结果存入MySQL或时序数据库：

pt-query-digest /var/log/mysql/slow.log --output=json > slow_digest.json

配合脚本每5分钟执行一次，实现近实时汇总。

高频低效SQL筛选策略

按执行频率与总耗时排序

从performance_schema获取语句统计：

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_sec,
AVG_TIMER_WAIT/1000000000 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT NOT LIKE '%information_schema%'
ORDER BY COUNT_STAR DESC, total_sec DESC
LIMIT 20;

重点关注执行次数>1000次且平均耗时>0.1秒的SQL。

结合索引使用情况

联合查询sys.schema_unused_indexes与sys.schema_index_statistics，识别因缺少索引导致的高频慢查询：

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql','sys','performance_schema');

同时通过SHOW INDEX FROM table检查已存在索引的重复度。

自动化告警与优化闭环

将筛选结果推送到Zabbix或Prometheus，设定阈值（如单SQL日均执行5000次且平均耗时>1秒）触发告警。优化流程包括：

• 添加覆盖索引：针对SELECT字段创建INCLUDE索引。
• 改写SQL：避免SELECT *、用EXISTS替代IN子查询。
• 分区表/缓存：对定期扫描的大表使用RANGE分区，或引入Redis二级缓存。

每次优化后重新采集7天数据对比，确保执行次数与总耗时下降30%以上。

总结

通过慢查询日志与performance_schema的持续监控，结合执行频率与总耗时双重指标，运维团队可精准定位高频低效SQL，避免“一刀切”优化。该方案已在多个IDC托管实例中验证，将数据库QPS提升15%-25%。