慢查询批量优化脚本提升数据库速度

知识分享

2026-07-17 15:40

11 阅读

为什么需要慢查询批量优化脚本

数据库响应慢，大部分根因是慢查询在捣乱。
一个一个手动分析日志、加索引，效率低还容易漏。
用脚本批量扫描慢查询日志，自动提取高频问题并生成优化建议，能大幅提升调优效率。
本文基于 MySQL 5.7/8.0 常见配置，提供一个可直接运行的 Shell 脚本，零基础也能照做。

准备条件：开启慢查询日志

   SHOW VARIABLES LIKE 'slow_query_log';
   SHOW VARIABLES LIKE 'slow_query_log_file';
   SHOW VARIABLES LIKE 'long_query_time';

如果没开启，临时开启（重启失效）：

   SET GLOBAL slow_query_log = ON;
   SET GLOBAL long_query_time = 1;  -- 超过1秒记录

永久开启需修改 my.cnf（或 my.ini）：

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

然后重启 MySQL 或刷新配置。

确认日志文件路径后，记录下路径（例如 /var/log/mysql/slow.log），后续脚本会使用。

编写批量优化脚本

下面是一个基于 mysqldumpslow 和 awk 的脚本，
它会解析慢查询日志，
找出出现次数最多的 TOP 10 查询，
并生成对应的 EXPLAIN 和 SHOW CREATE TABLE 建议（需要手动执行分析，
不自动改表）。

#!/bin/bash
# 慢查询批量分析脚本 - 输出优化建议
# 用法: ./slow_analyzer.sh [慢查询日志路径]

SLOW_LOG="${1:-/var/log/mysql/slow.log}"
ANALYZE_DIR="/tmp/slow_analysis_$(date +%Y%m%d%H%M%S)"

mkdir -p $ANALYZE_DIR

# 1. 使用 mysqldumpslow 聚合查询
mysqldumpslow -s t -t 10 "$SLOW_LOG" > "$ANALYZE_DIR/top10.txt"

# 2. 提取每条查询的完整 SQL（去重）
grep -E '^SELECT|^UPDATE|^DELETE|^INSERT' "$SLOW_LOG" | sort -u | head -20 > "$ANALYZE_DIR/unique_queries.txt"

# 3. 生成建议报告
echo "=== 慢查询批量分析报告 ===" > "$ANALYZE_DIR/report.txt"
echo "分析时间: $(date)" >> "$ANALYZE_DIR/report.txt"
echo "日志文件: $SLOW_LOG" >> "$ANALYZE_DIR/report.txt"
echo "" >> "$ANALYZE_DIR/report.txt"
echo "--- 耗时最长的 TOP 10 查询 ---" >> "$ANALYZE_DIR/report.txt"
cat "$ANALYZE_DIR/top10.txt" >> "$ANALYZE_DIR/report.txt"

echo "" >> "$ANALYZE_DIR/report.txt"
echo "--- 优化建议 ---" >> "$ANALYZE_DIR/report.txt"
echo "请登录MySQL后，对以下表执行 SHOW CREATE TABLE 检查索引，" >> "$ANALYZE_DIR/report.txt"
echo "并针对慢查询使用 EXPLAIN 分析是否走索引。" >> "$ANALYZE_DIR/report.txt"
echo "" >> "$ANALYZE_DIR/report.txt"

# 提取涉及的表名
while IFS= read -r sql; do
    tables=$(echo "$sql" | grep -oP 'FROM\s+`?\w+`?' | awk '{print $2}' | tr -d '`' | sort -u)
    if [ -n "$tables" ]; then
        for tbl in $tables; do
            echo "表: $tbl" >> "$ANALYZE_DIR/report.txt"
        done
    fi
done < "$ANALYZE_DIR/unique_queries.txt"

echo ""
echo "报告已生成: $ANALYZE_DIR/report.txt"
echo "请查看报告并按建议手动检查索引。"

重要说明：这个脚本只做分析和建议，不会自动修改数据库。切勿在生产环境直接运行 DROP/ALTER 等自动操作。

使用步骤与结果解读

将上述脚本保存为 slow_analyzer.sh，赋予执行权限：

   chmod +x slow_analyzer.sh

运行脚本（默认日志路径或指定路径）：

   ./slow_analyzer.sh
   # 或 ./slow_analyzer.sh /var/log/mysql/mysql-slow.log

查看生成的报告：

   cat /tmp/slow_analysis_*/report.txt

   EXPLAIN SELECT * FROM users WHERE last_login < '2020-01-01'\G

如果 type 是 ALL（全表扫描），建议添加索引：

   ALTER TABLE users ADD INDEX idx_last_login (last_login);

添加索引前请确认业务无冲突，避开高峰期操作。

避坑指南

慢查询日志大小：生产环境建议设置 log_queries_not_using_indexes = 1 之后记得定期轮转日志（使用 logrotate 或手动清理），防止占满磁盘。
不要在生产环境直接执行自动添加索引的脚本：索引可能引发锁表或影响写入性能。
mysqldumpslow 可能未安装：在 CentOS 用 yum install mysql-server 会自带，Ubuntu 用 apt install mysql-server。如果缺失，可以用 percona-toolkit 中的 pt-query-digest 替代，功能更强。
脚本中的正则提取表名仅对简单查询有效，子查询或 JOIN 多的复杂 SQL 建议手动分析。

效果验证

执行优化（如添加索引）后，再次运行脚本，对比报告中的 TOP 10 查询耗时是否下降。
也可以直接用以下 SQL 查看当前慢查询数量：

SELECT * FROM mysql.slow_log WHERE start_time > NOW() - INTERVAL 1 HOUR;

如果优化后慢查询明显减少或消失，说明脚本的批量分析思路有效。

常见问题

Q：脚本报告里没有输出任何表名怎么办？
A：说明 SQL 语句没有匹配到 FROM 关键字，或者日志格式不标准。可以先用 head -20 /var/log/mysql/slow.log 手动查看日志内容，确认 SQL 语句完整。

Q：我可以直接用脚本来自动加索引吗？
A：强烈不建议。索引添加需要结合业务查询模式，自动加索引可能造成冗余或冲突。脚本只做分析，优化决策需要人工介入。

Q：有了这个脚本，还需要其他工具吗？
A：对于入门调优完全够用。大型项目建议配合 pt-query-digest 和 Performance Schema 做深度分析。

如果你正在处理慢查询批量优化脚本提升数据库速度，建议先按本文步骤完整执行，再根据自己的环境做微调；
遇到异常时优先回看避坑和高频问题部分。

AIOps智能故障自愈服务器自动处理配置教程

容器横向渗透防护安全加固配置：Docker容器横向渗透防护