慢查询批量优化脚本提升数据库查询速度：慢查询批量优化脚本
慢查询批量优化脚本是什么？有什么作用？
慢查询批量优化脚本是一套自动化工具或流程，能扫描 MySQL 慢查询日志，识别执行时间过长的 SQL 语句，分析其执行计划，并批量生成索引添加或 SQL 改写建议。
核心目的是减少全表扫描、利用索引加速查询，从而提升数据库整体响应速度。
本文面向零基础运维站长，教你从零搭建自己的批量优化方案。
开始之前：确认环境与开启慢查询日志
在编写脚本前，需要确保 MySQL 已开启慢查询日志并配置合理阈值。
操作如下：
- 检查当前设置：登录 MySQL，执行
SHOW VARIABLES LIKE 'slow_query_log%';和SHOW VARIABLES LIKE 'long_query_time';。 - 开启慢查询日志（如果未开启）：
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 单位秒，建议先设为 1
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';若需永久生效，需修改 MySQL 配置文件 my.cnf 的 [mysqld] 部分：
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log- 等待一段时间：让系统业务运行几个小时以产生慢查询记录。
编写批量分析脚本：使用 pt-query-digest 自动提取高频慢 SQL
Percona Toolkit 中的 pt-query-digest 是最经典的慢查询分析工具。
安装方法：
- Ubuntu/Debian：
sudo apt install percona-toolkit - CentOS/RHEL：
sudo yum install percona-toolkit - 或从 Percona 官网 下载 RPM 包。
编写批量分析脚本 analyze_slow.sh：
#!/bin/bash
# 自动分析慢查询日志并输出 Top 10 慢查询及建议
LOG_FILE="/var/log/mysql/slow.log"
OUTPUT_DIR="/tmp/slow_analysis"
mkdir -p $OUTPUT_DIR
# 分析并生成报告
pt-query-digest $LOG_FILE --limit=10 --output=slowlog > $OUTPUT_DIR/report.txt
# 提取每个慢查询的样本 SQL
pt-query-digest $LOG_FILE --limit=10 --output=slowlog --sample=1 > $OUTPUT_DIR/sample_sql.txt
echo "分析完成，报告保存在 $OUTPUT_DIR/report.txt"赋予执行权限：chmod +x analyze_slow.sh。
运行 ./analyze_slow.sh 即可看到执行次数最多、总耗时最长的慢查询。
批量索引优化脚本：根据建议自动生成 ALTER TABLE
仅分析不够，还需批量修复。
下面脚本读取 pt-query-digest 的输出，提取包含“USE INDEX”或“ADD INDEX”的建议，并自动生成可执行的 SQL 文件。
#!/bin/bash
# 从报告提取索引建议并生成 ALTER 语句
REPORT_FILE="/tmp/slow_analysis/report.txt"
SQL_FILE="/tmp/slow_analysis/alter_statements.sql"
> $SQL_FILE
# 匹配形如 "ALTER TABLE `xxx` ADD INDEX ..." 的建议行
while IFS= read -r line; do
if [[ $line =~ ^.*ALTER\s+TABLE.*ADD\s+INDEX.* ]]; then
echo "$line;" >> $SQL_FILE
fi
done < "$REPORT_FILE"
echo "共提取 $(wc -l < $SQL_FILE) 条 ALTER 语句，已保存到 $SQL_FILE"执行前必须手动审核：每个 ALTER 语句建议先在测试库执行 EXPLAIN 确认索引有效，且评估在业务低峰期执行，避免锁表影响线上。
执行脚本的避坑指南
- 始终先备份：执行任何批量修改前，使用
mysqldump备份受影响表结构：mysqldump --no-data dbname > backup_struct.sql - 分表分批次执行：不要一次性执行几十条 ALTER，容易造成主从延迟或长时间锁表。建议每次只输出一条，确认后再继续。
- 关注冗余索引：添加新索引后，检查是否存在重复或前缀重叠的旧索引，可用
pt-duplicate-key-checker清理。 - 长期慢查询不一定全是索引问题：也可能是 SQL 写法、数据量过大或硬件瓶颈。脚本只建议索引，不代表万能。
验证优化效果
执行索引添加后，重新分析慢查询日志：
# 清空旧慢查询记录
sudo truncate -s 0 /var/log/mysql/slow.log
# 等待业务运行 24 小时
# 再次运行分析脚本
./analyze_slow.sh对比两次报告：慢查询总数量应明显下降，单条查询平均耗时缩短。
另外使用 SHOW PROCESSLIST 观察当前查询是否出现全表扫描。
常见问题（FAQ）
Q1：慢查询日志文件太大，pt-query-digest 分析很久怎么办？
A：可以先用 mysqldumpslow 初步过滤，或使用 pt-query-digest --filter 'exec_time >= 1' 只分析耗时超过 1 秒的查询。也可以按时间分割日志。
Q2：自动生成的 ALTER 索引建议一定正确吗？
A：不一定。pt-query-digest 会基于 MySQL 优化器的输出给出建议，但有时建议的索引并不适用于所有变体 SQL。建议先用 EXPLAIN 手动验证索引是否被扫描。
Q3：为什么添加了索引后查询反而变慢了？
A：可能是因为新增索引导致 INSERT/UPDATE 性能下降，或者优化器没有选择该索引（连接字段顺序不匹配）。可使用 FORCE INDEX 测试。
Q4：脚本只能在 Linux 上运行吗？
A：Percona Toolkit 有 Windows 版本（通过 WSL 或 Cygwin）但建议在 Linux 服务器上使用。如果你使用宝塔面板，可在“数据库”模块的“慢查询分析”功能直接查看优化建议，同样支持批量操作。
结语
通过慢查询批量优化脚本，你不再需要逐条手工分析慢 SQL，能快速定位瓶颈并完成优化。
关键是做好验证和备份，让自动化服务于人，而不是完全信任机器。
如果你正在处理线上数据库性能问题，建议先按本文步骤执行一遍，再根据业务特点调整脚本参数。
