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

慢查询批量优化脚本是一类自动采集慢日志、提取高频慢SQL、批量生成优化建议（如添加索引或改写查询）的脚本。
本文从零搭建该脚本，通过简单配置即可定期扫描慢查询池，自动输出待优化SQL，大幅降低数据库响应延迟。
适合MySQL 5.7及以上版本，无需专业DBA也能落地。

你的库为什么跑得慢

数据库查询卡顿通常来自全表扫描、缺少索引、数据量过大或SQL写法不合理。
慢查询日志记录了执行时间超过阈值的SQL，通过批量分析这些日志，可以快速找到病根。
本文的脚本将自动完成“采集—解析—建议”闭环。

动手前需要准备这些

• MySQL 5.7+ 环境（如云服务器、本地虚拟机均可）
• 开启慢查询日志：在MySQL命令行执行以下语句（临时生效，重启后失效，正式环境建议写入配置文件）

  SET GLOBAL slow_query_log = ON;
  SET GLOBAL long_query_time = 2;  -- 单位秒，超过2秒记录
  SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

• 确认日志已生成：执行 SHOW VARIABLES LIKE 'slow_query_log%'; 查看状态为ON且文件路径正确。

编写批量优化脚本（以Shell为例）

以下脚本基于系统自带的 mysqldumpslow 工具，无需额外安装。
新建文件 optimize_slow.sh：

#!/bin/bash
SLOW_LOG="/var/log/mysql/slow.log"
OUTPUT="./slow_report_$(date +%Y%m%d).txt"
# 自动分析慢查询，按查询次数排序输出
mysqldumpslow -s c -t 10 "$SLOW_LOG" > "$OUTPUT"
echo "查询次数最多的10条慢SQL已写入 $OUTPUT"
# 针对每条慢SQL，通过EXPLAIN提取表名和扫描行数（示例）
mysqldumpslow -s t -t 5 "$SLOW_LOG" | grep -oP 'FROM \w+' | sort -u > temp_tables.txt
echo "涉及的高频表名："
cat temp_tables.txt

执行脚本：bash optimize_slow.sh，会生成报告并列出可能缺索引的表。

更进阶的做法是结合 pt-query-digest（Percona Toolkit），它能生成更详细的分析报告和优化建议。
安装方法（以CentOS为例）：

yum install -y percona-toolkit
pt-query-digest "$SLOW_LOG" > ./report.html

然后根据报告中的“索引建议”字段，逐一执行 ALTER TABLE … ADD INDEX …。

避坑：新手最容易犯的错误

1. 未开启慢查询日志：检查 SHOW VARIABLES LIKE 'slow_query_log%'; 确认值为ON。如果MySQL重启后失效，需在 my.cnf 中添加固定配置。
2. 日志文件权限问题：确保 mysqldumpslow 或脚本有读权限，通常 chmod 644 slow.log。
3. 索引不是越多越好：批量添加索引前，先分析每条慢SQL的真实执行计划（EXPLAIN），避免重复索引或冗余索引。
4. 忽略测试环境：先在测试库运行脚本，确认不锁表、不影响业务再上生产。

如何验证优化效果

执行脚本并添加索引后，重启一段时间（如1小时），再次检查慢查询日志：

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

如果记录数明显减少，且业务侧反馈查询响应变快，则优化成功。
也可通过 SHOW GLOBAL STATUS LIKE 'Questions'; 观察QPS变化。

常见问题解答

Q：脚本里mysqldumpslow命令找不到？
A：确认MySQL安装路径是否在PATH中，或直接使用全路径 /usr/bin/mysqldumpslow。如果未安装，则需安装 percona-toolkit 或使用 pt-query-digest。

Q：pt-query-digest生成报告后，如何知道具体加什么索引？
A：报告末尾有“Index Analysis”部分，会列出建议的索引列。对照表结构，考虑索引覆盖性、选择性，使用 ALTER TABLE … ADD INDEX … 添加。

Q：批量优化脚本可以定时跑吗？
A：可以，将脚本加入crontab，例如每天凌晨3点执行：0 3 * * * /root/optimize_slow.sh，结合邮件告警或日志监控。

Q：使用云服务器（如泽御云等正规服务商）时，慢日志文件路径需要修改吗？
A：是的，云服务器上MySQL数据目录可能不同，先用 SHOW VARIABLES LIKE 'datadir'; 查看，再拼出慢日志路径。