慢查询批量优化脚本解决数据库卡顿问题

知识分享

2026-07-19 20:40

22 阅读

慢查询是数据库卡顿的元凶

很多网站卡顿、接口超时，根因往往是数据库里堆积了大量慢查询。
慢查询是指执行时间超过指定阈值的 SQL 语句（MySQL 默认阈值 10 秒）。
你不需要成为 DBA，只要掌握一个简单的批量优化脚本，就能自动定位问题 SQL 并给出优化建议，彻底解决数据库卡顿问题。
本文从零开始，带你一步步搭建这个脚本。

准备阶段：先打开慢查询日志

脚本依赖慢查询日志，先确保 MySQL 已经开启了慢查询记录。
登录数据库执行以下命令检查：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

如果 slow_query_log 值为 OFF，执行：

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

将 long_query_time 设为 2 秒，低于行业推荐的 5 秒，更适合快速定位。
注意这个设置在重启后会失效，建议直接修改 MySQL 配置文件 /etc/my.cnf：

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

编写批量优化脚本：自动提取并生成优化建议

脚本核心思路：解析慢查询日志，找出执行次数最高的前 10 条 SQL，然后自动添加索引建议或改写 SQL。
这里提供一个可直接部署的 Bash 脚本 batch_optimize_slow.sh：

#!/bin/bash
# 慢查询批量优化脚本 v1.0
SLOW_LOG="/var/log/mysql/mysql-slow.log"
OUTPUT_DIR="/tmp/slow_optimize"
mkdir -p $OUTPUT_DIR

# 1. 若安装了 pt-query-digest 则用它分析，否则用 mysqldumpslow
if command -v pt-query-digest &> /dev/null; then
    echo "使用 pt-query-digest 分析慢查询..."
    pt-query-digest --limit=10 $SLOW_LOG > $OUTPUT_DIR/report.txt
else
    echo "使用 mysqldumpslow 分析慢查询..."
    # mysqldumpslow 默认排序按平均查询时间，-t 10 取前10
    mysqldumpslow -s at -t 10 $SLOW_LOG > $OUTPUT_DIR/report.txt
fi

# 2. 从报告中提取 SQL 指纹并生成建议（以 mysqldumpslow 输出为例）
if [ -s $OUTPUT_DIR/report.txt ]; then
    echo "========================================"
    echo "慢查询 TOP10 报告已生成：$OUTPUT_DIR/report.txt"
    echo "========================================"
    # 提取查询语句并尝试分析（简单示例：输出到建议文件）
    grep -E "^SELECT|^UPDATE|^DELETE|^INSERT" $OUTPUT_DIR/report.txt | head -10 > $OUTPUT_DIR/sql_list.txt
    echo "已提取前10条高频慢查询，请打开 sql_list.txt 查看具体 SQL。"
    echo "建议操作："
    echo "3.1 对频繁出现的 SELECT 语句，用 EXPLAIN 分析是否缺少索引；"
    echo "3.2 考虑在 WHERE 和 JOIN 列上添加复合索引；"
    echo "3.3 若 UPDATE/INSERT 频繁，检查是否有锁等待。"
else
    echo "慢查询日志为空或分析出错，请检查日志文件。"
fi

将脚本保存后赋予执行权限：

chmod +x batch_optimize_slow.sh

运行脚本：

sudo ./batch_optimize_slow.sh

脚本会自动生成 /tmp/slow_optimize/report.txt 和 /tmp/slow_optimize/sql_list.txt。
打开这两个文件，你就知道到底是哪几条 SQL 在拖垮数据库。

避坑指南：这些错误千万不要犯

不要在业务高峰期运行分析脚本：解析大日志文件会占用 CPU 和磁盘 IO，建议凌晨低谷执行。
千万别脚本自动加索引：索引虽能提速，但建错索引反而拖慢写入。建议生成建议后人工审核再执行。
慢查询日志会越滚越大：务必配置日志轮转（如 logrotate），否则可能撑爆磁盘。
mysqldumpslow 输出可能不涵盖参数化后的 SQL：需要结合 pt-query-digest 获得更准确的指纹分组。
谨慎对待 long_query_time=0：不要设置为 0，否则全量记录会把磁盘写爆。

效果验证：怎么知道数据库卡顿已经解决

优化后，执行以下验证：

再次运行脚本对比前后慢查询数量：

    wc -l /var/log/mysql/mysql-slow.log

如果日志增长速度明显下降，说明优化有效。

使用 SHOW GLOBAL STATUS LIKE 'Slow_queries'; 查看累计慢查询数，重启计数器后再观察。
用 top 或 htop 观察 MySQL 进程 CPU 和内存占用是否回落。
配合监控工具（如宝塔面板的数据库监控）查看查询时间曲线是否下降。

常见问题 FAQ

Q：我连 pt-query-digest 都没装怎么办？
A：用脚本中的 mysqldumpslow 方案，MySQL 自带这个工具。如果也没有，先安装：yum install -y percona-toolkit 或 apt install percona-toolkit。

Q：脚本运行提示 Permission denied？
A：慢查询日志通常属于 mysql 用户，脚本需要 sudo 权限或把当前用户加入 mysql 组。

Q：生成的建议看不懂怎么手动改？
A：比如报告显示一条慢查询是 SELECT * FROM orders WHERE status = 1，你可以执行 EXPLAIN SELECT * FROM orders WHERE status = 1; 看到 type 为 ALL 代表全表扫描，然后执行 ALTER TABLE orders ADD INDEX idx_status (status); 即可。

Q：脚本分析出来的慢查询很多，都需要优化吗？
A：优先优化执行次数高、平均时间长的；单次耗时大的也要关注。建议按 TOP10 逐个优化。

如果你正在处理慢查询批量优化脚本解决数据库卡顿问题，建议先按本文步骤开启慢查询日志、部署脚本、分析报告，再人工审核执行优化。
每优化一条 SQL 就验证一次，别一次性全改。
遇到问题回顾避坑部分，多数卡点都能自己解决。

CPU占用100%恶意进程一键定位查杀脚本

磁盘IO过高存储阵列扩容优化站点响应