MySQL 慢查询拖垮业务？我用一行脚本实现秒级定位与自动化分析

MySQL 慢查询拖垮业务我用一行脚本实现秒级定位与自动化分析上周凌晨两点我被一阵报警短信炸醒。核心业务库的CPU飙到95%应用响应时间从50ms涨到8秒。业务方在群里疯狂我说支付流程卡死了。我爬起来连上VPN第一反应就是肯定是慢查询在搞事情。慢查询的杀伤力比你想的更狠很多人以为慢查询就是查得慢一点等等就好了。但在高并发场景下一条慢SQL就能引发雪崩慢查询占用连接不释放 → 连接池耗尽 → 新请求进不来大量数据扫描 → CPU和IO被打满 → 正常查询也被拖慢InnoDB锁等待超时 → 事务回滚 → 数据不一致我那次故障就是因为一条没走索引的统计查询把整个集群拖垮了。常规排查太慢了事发后我复盘了整个排查过程。说实话太憋屈了。当时我的排查步骤登录MySQLmysql -u root -p查看慢查询日志路径show variables like slow_query_log_file;退出MySQL用vim打开日志文件肉眼筛选找执行时间1秒的SQL一条条复制出来用EXPLAIN分析执行计划这一套下来15分钟没了。凌晨两点业务挂了15分钟你知道是什么概念吗我写的自动化脚本痛定思痛我搞了一套脚本把排查时间从15分钟压缩到30秒。脚本1实时慢查询监控#!/bin/bash# slow_query_monitor.sh - 实时监控慢查询MYSQL_USERrootMYSQL_PASSyour_passwordSLOW_LOG/var/log/mysql/slow.logTHRESHOLD1.0# 秒echo MySQL 慢查询实时监控 echo时间:$(date)echo阈值:${THRESHOLD}秒echo# 检查慢查询日志是否开启SLOW_LOG_ENABLED$(mysql -u$MYSQL_USER -p$MYSQL_PASS-eSHOW VARIABLES LIKE slow_query_log;|grepON)if[-z$SLOW_LOG_ENABLED];thenecho⚠️ 警告慢查询日志未开启echo 执行以下命令开启echo SET GLOBAL slow_query_log ON;echo SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;echo SET GLOBAL long_query_time 1;exit1fi# 使用pt-query-digest分析推荐ifcommand-vpt-query-digest/dev/null;thenecho✓ 使用 pt-query-digest 分析中...pt-query-digest --order-by Query_time:sum--limit10$SLOW_LOGelse# 备用方案awk分析echo⚠️ pt-query-digest 未安装使用 awk 分析...grep-A1Query_time:$SLOW_LOG|\awk/Query_time:/{t$3} /SELECT|INSERT|UPDATE|DELETE/{print t, $0}|\sort-rn|head-20fiechoecho 当前正在执行的慢查询 mysql -u$MYSQL_USER-p$MYSQL_PASS-e SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 100) as SQL_PREVIEW FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME $THRESHOLDAND COMMAND ! Sleep ORDER BY TIME DESC; 这个脚本做了三件事环境检查自动检测慢查询日志是否开启没开启就给出开启命令日志分析优先用pt-query-digestPercona Toolkit的神器没有就用awk兜底实时进程查当前正在跑的慢查询方便kill掉救急脚本2一键执行计划分析找到慢SQL之后还要分析执行计划。我又写了个脚本#!/bin/bash# explain_analyzer.sh - 自动分析SQL执行计划MYSQL_USERrootMYSQL_PASSyour_passwordDB_NAME$1SQL$2if[-z$DB_NAME]||[-z$SQL];thenecho用法: ./explain_analyzer.sh 数据库名\SQL语句\echo示例: ./explain_analyzer.sh orders\SELECT * FROM orders WHERE user_id 12345\exit1fiecho SQL执行计划分析 echo数据库:$DB_NAMEechoSQL:$SQLecho# 执行EXPLAIN并格式化输出mysql -u$MYSQL_USER-p$MYSQL_PASS$DB_NAME-eEXPLAIN FORMATJSON$SQL;|\python3-mjson.tool2/dev/null||mysql -u$MYSQL_USER-p$MYSQL_PASS$DB_NAME-eEXPLAIN$SQL;echoecho 索引使用分析 mysql -u$MYSQL_USER-p$MYSQL_PASS$DB_NAME-e EXPLAIN$SQL; |awk NR1 {print} NR1 { if ($0 ~ /ALL/) print ⚠️ 全表扫描: $0 else if ($0 ~ /index/) print 索引扫描: $0 else if ($0 ~ /range/) print 范围查询: $0 else if ($0 ~ /ref/) print ✓ 索引命中: $0 else if ($0 ~ /eq_ref/) print ⭐ 唯一索引: $0 else print $0 }echoecho 优化建议 mysql -u$MYSQL_USER-p$MYSQL_PASS$DB_NAME-e EXPLAIN$SQL; |grep-qALLecho❌ 检测到全表扫描建议添加索引||echo✓ 已使用索引这个脚本能自动识别执行计划中的问题ALL 全表扫描必须加索引index 索引扫描效率一般range 范围查询可以接受ref/eq_ref 索引命中性能最好脚本3慢查询日报生成除了应急排查我还做了个定时任务每天早上一封慢查询报告邮件#!/bin/bash# slow_query_report.sh - 生成慢查询日报REPORT_DATE$(date%Y-%m-%d)SLOW_LOG/var/log/mysql/slow.logREPORT_FILE/tmp/slow_query_report_${REPORT_DATE}.txtechoMySQL 慢查询日报 -${REPORT_DATE}$REPORT_FILEecho$REPORT_FILEecho$REPORT_FILE# 统计今日慢查询数量SLOW_COUNT$(grep-cQuery_time:$SLOW_LOG2/dev/null||echo0)echo今日慢查询总数:$SLOW_COUNT$REPORT_FILEecho$REPORT_FILE# Top 10 最慢SQLifcommand-vpt-query-digest/dev/null;thenpt-query-digest --order-by Query_time:sum--limit10--report-formatjson$SLOW_LOG2/dev/null|\python3-c import json, sys data json.load(sys.stdin) print(Top 10 慢查询按累计执行时间排序) print(- * 50) for i, q in enumerate(data.get(classes, [])[:10], 1): print(f\{i}. 执行次数: {q[attribute][ts_cnt][value]}\) print(f\平均耗时: {q[attribute][Query_time][avg]:.2f}s\) print(f\SQL指纹: {q[fingerprint][:80]}...\) print() $REPORT_FILEfi# 发送邮件需要配置sendmail或mail命令mail-sMySQL慢查询日报${REPORT_DATE}dbacompany.com$REPORT_FILEecho✓ 日报已发送至 dbacompany.com实际效果现在当业务出现性能问题时我的排查流程变成跑slow_query_monitor.sh30秒定位慢SQL跑explain_analyzer.sh1分钟确认是否缺索引加索引或者kill掉异常查询5分钟内恢复上周同样的问题从发现到解决总共用了4分钟。业务方还没反应过来我已经处理完了。几个避坑建议用了这套脚本之后我也踩过一些坑分享给你坑1慢查询日志文件太大默认慢查询日志不切割跑几个月能占几十G。建议用logrotate定期切割或者直接用pt-query-digest的–since参数只分析最近数据。坑2long_query_time设置不合理有人设置成0秒结果日志爆炸。建议生产环境设1秒测试环境设0.1秒。要平衡排查需求和磁盘IO。坑3只看慢查询不看执行频率一条SQL执行1次耗时5秒不急。另一条执行10000次每次0.5秒这才是大坑。pt-query-digest默认按累计时间排序就是这个原因。写在最后MySQL慢查询排查说难不难说简单也不简单。核心就两点把慢查询找出来- 靠脚本自动化别肉眼翻日志把索引加上去- 用EXPLAIN验证别凭感觉这套脚本我已经用了半年处理了十几次线上性能问题。代码我放GitHub了有需要自取。如果你也有MySQL性能优化的踩坑经历欢迎在评论区交流。P.S. 下次遇到数据库CPU飙升先别慌跑一下脚本30秒就知道是不是慢查询在搞鬼了。