脚本如何汇总分析慢SQL语句内容

wen 实用脚本 2026-06-25 32

脚本如何汇总分析慢SQL语句：从采集到优化的完整实战指南

目录导读

为什么需要脚本化慢SQL分析？
慢SQL日志采集脚本设计要点
多源汇总与格式化清洗脚本
核心分析脚本：执行频率、耗时与索引缺失
自动报告生成与告警联动脚本
常见问题问答（FAQ）

为什么需要脚本化慢SQL分析？

在生产环境，慢SQL是数据库性能的“头号杀手”，手动检查慢查询日志既耗时又容易遗漏，而通过脚本实现自动化汇总分析，可以快速定位高频耗时SQL、索引缺失和锁等待问题。

脚本如何汇总分析慢SQL语句内容

核心痛点：

单条慢SQL可能被多次执行，必须统计“总耗时 × 执行次数”
不同数据库（MySQL/PostgreSQL/Oracle）日志格式不同，需统一解析
分析结果需要与监控系统（如Prometheus、Zabbix）或工单系统联动

脚本目标：

从日志文件/系统表采集原始数据
按SQL指纹（Fingerprint）去重并聚合统计
输出TOP N慢SQL及其完整分析数据（执行计划、表扫描方式、索引建议）

慢SQL日志采集脚本设计要点

1 采集源选择

方式	适用场景	脚本实现示例
数据库内置慢日志文件（MySQL slow_query_log）	传统单机环境	`tail -f /var/log/mysql/slow.log \\| python3 parser.py`
系统表（`performance_schema`、`pg_stat_statements`）	云数据库或无法访问日志文件时	SQL查询：`SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE SUM_TIMER_WAIT > 1000000000`
代理层（如ProxySQL、MaxScale）	分布式数据库或中间件场景	解析ProxySQL的`stats_mysql_query_digest`表

2 脚本核心逻辑伪代码

# 1. 读取日志行
for line in log_file:
    if line contains "# Time:" or "# Query_time:":
        record = parse_line(line)
        # 提取SQL指纹：替换常量值为占位符
        fingerprint = normalize_sql(record['sql_text'])
        # 存入临时字典
        digest_map[fingerprint]['count'] += 1
        digest_map[fingerprint]['total_time'] += record['query_time']
        digest_map[fingerprint]['max_time'] = max(...)

多源汇总与格式化清洗脚本

1 解决日志格式不一致问题

不同数据库的慢日志字段名称不同,脚本应支持配置化解析：

MySQL原始行：
# Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 50000

PostgreSQL（通过pg_stat_statements）：
SELECT query, calls, total_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20

清洗脚本任务：

统一字段名为：sql_text, query_time, lock_time, rows_examined, rows_sent, timestamp
将非ID常量替换为（WHERE id = 123 变为 WHERE id = ?）
丢弃注释和前缀信息（如 use database_name;）

2 多文件/多实例合并

# 使用find + 合并脚本
find /var/log/mysql/ -name "slow-*.log" -mtime -7 | xargs python3 merge_slow_logs.py --output /tmp/merged_slow.csv

核心分析脚本：执行频率、耗时与索引缺失

1 关键分析维度

维度	计算公式	告警阈值建议
总耗时	`total_time = sum(query_time) over all executions`	> 1小时/天
平均耗时	`avg_time = total_time / count`	> 1秒
扫描行比	`rows_examined_avg / rows_sent_avg`	> 1000:1
锁等待占比	`lock_time / query_time`	> 20%

2 索引缺失检测脚本

基于rows_examined与rows_sent的比值,结合表结构分析：

-- 假设已获得慢SQL中的表名
SELECT 
    TABLE_NAME, 
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `size_mb`,
    ENGINE 
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';
-- 进一步检查是否有索引匹配WHERE条件
SHOW INDEX FROM orders;

脚本可根据上述信息，输出建议：
建议：在 orders.order_date 列添加索引，当前扫描50600行只返回5行

3 生成报告示例

{
  "fingerprint": "SELECT * FROM orders WHERE status = ? AND create_time > ?",
  "total_executions": 2340,
  "total_time_sec": 785.3,
  "avg_time_ms": 335,
  "rows_examined_avg": 150000,
  "rows_sent_avg": 80,
  "suggestion": "需添加复合索引 idx_status_create_time"
}

自动报告生成与告警联动脚本

1 每日/每周汇总邮件

python3 gen_slow_report.py \
  --input /tmp/merged_slow.csv \
  --top 10 \
  --output report.md \
  --email-to dba@company.com

2 实时告警脚本（配合crontab每分钟执行）

threshold_query_time = 3  # 单次超过3秒触发告警
if last_query_time > threshold_query_time:
    send_alert_to_webhook("慢SQL告警：耗时{last_query_time}秒，SQL：{sql_test}")

常见问题问答（FAQ）

Q1：脚本分析慢SQL时，为什么需要对SQL做“指纹归一化”？
A：避免相同逻辑的SQL被重复计数，例如SELECT * FROM users WHERE id = 1和SELECT * FROM users WHERE id = 2是同一类查询，应合并统计总执行次数和总耗时,否则会导致分析结果碎片化。

Q2：我的数据库是RDS，无法访问慢日志文件怎么办？
A：云数据库通常通过系统表暴露慢查询信息，例如AWS RDS MySQL可以查询performance_schema.events_statements_summary_by_digest表，阿里云可以通过information_schema.slow_log表访问，脚本只需改为SELECT ... FROM 系统表并解析结果集即可。

Q3：汇总分析时发现大量全表扫描SQL，但单次查询不慢，怎么处理？
A：重点关注“扫描行数多但返回行数少”的SQL（高rows_examined/rows_sent比值），这类SQL虽然单次不慢，但频繁执行会消耗大量IO，脚本应设置rows_examined_avg > 10000 AND ratio > 1000作为附加告警规则。

Q4：生成的报告中有很多重复的慢SQL，如何进一步去重？
A：除了指纹归一化，还应考虑参数化后的SQL是否完全相同，例如WHERE type IN (1,2,3)的IN列表也应归一化为WHERE type IN (?)，但注意保留LIMIT常量（LIMIT ?）以便分析分页性能。

Q5：脚本本身会不会对线上数据库造成压力？
A：会，建议：

采集源优先使用日志文件而非实时查询系统表
若必须查系统表，仅在从库或延迟低的节点执行
设置超时时间（例如statement_timeout = 5s）
分析脚本跑在独立服务器上，避免与业务数据库竞争资源

扩展阅读：

MySQL官方文档《Slow Query Log》部分
PostgreSQL pg_stat_statements 视图详解
开源工具pt-query-digest的解析脚本源码（Perl语言）可用于参考核心算法

（全文约1280字）