SQL 慢查询治理：慢日志不是收集完就算完事了

SQL 慢查询治理：慢日志不是收集完就算完事了

有些团队的做法是：MySQL 开一个slow_query_log，往 ELK 里一灌，然后在 Grafana 上挂一张"慢查询数量趋势图"，就算治理完了。结果真正吃资源的慢查询一个没解决，只是每天在日会上感叹一句"今天的慢查询又多了"。慢日志的真正价值不在收集，在分析、归因、闭环处理。

一、慢日志堆在那，就像没拆封的体检报告

先看一个典型场景。数据库的slow_query_log每天产生 2GB 的文本，你配置了 Filebeat 把它灌进 Elasticsearch，Kibana 里能看到慢查询列表。然后呢？

你看到了这些信息：

• 有一个查询跑了 12 秒
• SQL 文本是：SELECT * FROM orders WHERE ...

但你看不到：

• 这个查询昨天跑了 0.5 秒，今天为什么突然 12 秒？
• 同样的业务逻辑，为什么有时走索引有时全表扫描？
• 这 12 秒是锁等待还是真在扫数据？
• 这个慢查询每小时出现多少次？是不是在某个时间扎堆？

原始慢日志只告诉你"这是一个慢查询"，但没告诉你"为什么慢"。就像体检报告上写着"血压偏高"，但没告诉你是因为昨晚没睡好还是血管真有问题。治理的第一步是把原始日志变成可诊断的结构化信息。

为什么慢查询治理的最大瓶颈不是"找不到慢查询"而是"不知道哪些值得修"？一个生产数据库每天可能产生 2000+ 条慢查询，但其中 1500 条是同一个报表 SQL 在凌晨 3 点触发的——它确实慢了，但这个时间段没人在意。另外 300 条是某个运营同事写了个超大分页查询翻到第 1001 页导致的，改成分页方案就消失了。只有 200 条是真正需要你花精力去修的核心路径查询。如果不对慢日志做指纹归类和频次排序，你会被 2000 条 log 淹没，最后修了 10 个"看起来好修"的查询，却漏掉了那个每小时出现 500 次、每次都 block 其他事务 2 秒的"锁等待杀手"。

二、慢查询分析的四个维度

拿到慢日志后，应该从四个维度做结构化分析：

graph TD A[原始慢日志] --> B1[维度1: 时间模式] A --> B2[维度2: SQL 指纹] A --> B3[维度3: 执行计划] A --> B4[维度4: 执行画像] B1 --> B1a[按小时统计慢查询数量] B1 --> B1b[识别异常时间窗口] B1 --> B1c[发现定时任务触发] B2 --> B2a[SQL 归一化: 去掉具体值] B2 --> B2b[按指纹归类统计] B2 --> B2c[识别 Top-N 高频慢查询] B3 --> B3a[提取 EXPLAIN 输出] B3 --> B3b[检查索引使用情况] B3 --> B3c[分析扫描行数 vs 返回行数] B4 --> B4a[锁等待时间 vs 执行时间] B4 --> B4b[Rows_sent vs Rows_examined] B4 --> B4c[临时表/文件排序标记] style B2 fill:#f96,stroke:#333,stroke-width:2px style B3 fill:#69f,stroke:#333,stroke-width:2px

时间模式分析：慢查询在一天里是怎么分布的？如果每天凌晨 2 点爆发，八成是定时任务导致；如果随机出现，大概率是索引设计问题。

SQL 指纹归一化：把WHERE id=1和WHERE id=999归为同一条 SQL。用pt-fingerprint或者自己写正则把值替换为?：

import re from collections import Counter def fingerprint_sql(sql: str) -> str: """ 将 SQL 归一化为"指纹"，去掉字面量值只保留结构 设计意图：SELECT * FROM t WHERE id=100 和 id=200 本质是同一条 SQL 的不同参数。只有去参数化后才能 统计出"到底是什么查询模式在拖慢数据库"。 """ # 替换字符串字面量 sql = re.sub(r"'[^']*'", "?", sql) # 替换数字常量（但保留 LIMIT 后面的数字） sql = re.sub(r"(?<!\bLIMIT\s)(?<!\bLIMIT\s{2})\b\d+\b", "?", sql) # 标准化空白字符 sql = re.sub(r"\s+", " ", sql).strip() # 转小写比较 return sql.lower() def analyze_slow_query_patterns(slow_logs: list[str]) -> dict: """对慢查询做指纹归类，找出高频问题 SQL 模式""" fingerprints = [fingerprint_sql(log) for log in slow_logs] return Counter(fingerprints).most_common(20) # Top 20

这一步做完后，你会发现：200 条不同类型的慢查询里，80% 其实都是那么 3~5 个 SQL 模板导致的。治理优先级一目了然。

为什么 SQL 指纹归一化不能只用简单的正则替换？文章里的re.sub(r"\b\d+\b", "?", sql)会把LIMIT 100中的 100 也替换成?，导致LIMIT 10和LIMIT 1000被归为同一条 SQL——但查询优化器对LIMIT 10和LIMIT 1000可能选择完全不同的执行计划（前者用小索引扫描，后者用全表扫描）。另外，IN (1, 2, 3)被替换成IN (?, ?, ?)，而IN (1)被替换成IN (?)，两条结构完全相同的 SQL 因为参数数量不同变成了不同指纹。更严谨的做法是用 SQL parser（如 sqlparse 或 sqlglot）对 AST 做归一化，而不是正则——后者对复杂 SQL 的边界情况处理很脆弱。

三、从 EXPLAIN 到根因判定

知道了 Top 问题 SQL 后，别急着加索引。先看 EXPLAIN，判断慢在哪一层：

-- 用 EXPLAIN FORMAT=JSON 获取详细执行计划 EXPLAIN FORMAT=JSON SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.create_time BETWEEN '2026-06-01' AND '2026-06-30' ORDER BY o.amount DESC LIMIT 100;

重点看三个指标：

def diagnose_slow_query(explain_output: dict) -> str: """ 根据 EXPLAIN 输出诊断慢查询根因 设计意图：EXPLAIN 字段很多，但不是每个都需要关注。 抽取出最关键的 5 个信号并发出一句话诊断报告， 让运维或开发一眼看出问题在"没索引"还是"排序太大"。 """ issues = [] query_block = explain_output.get("query_block", {}) # 检查是否走索引 if query_block.get("table", {}).get("access_type") == "ALL": issues.append("全表扫描，缺少合适的索引") # 检查扫描行数与返回行数比 rows_examined = query_block.get("cost_info", {}).get("rows_examined", 0) rows_sent = query_block.get("select_id", 0) # 需要从慢日志里取 if rows_sent and rows_examined / max(rows_sent, 1) > 100: issues.append(f"扫描{rows_examined}行只返回{rows_sent}行，索引选择性差") # 检查排序是否使用文件排序 ordering = query_block.get("ordering_operation", {}) if ordering.get("using_filesort"): issues.append("ORDER BY 列未使用索引，触发文件排序") return "; ".join(issues) if issues else "未发现明显性能问题"

四、闭环治理：从收集到解决的完整流程

慢查询治理的闭环分五步，每步都有明确产出：

sequenceDiagram participant DB as 数据库 participant Collector as 慢日志采集 participant Analyzer as 指纹分析引擎 participant Tracker as 治理工单 participant Monitor as 回验看板 DB->>Collector: slow_query_log 定时采集 Collector->>Analyzer: 原始日志结构化 Analyzer->>Analyzer: 指纹归类 + EXPLAIN 诊断 Note over Analyzer: Top-N 问题 SQL<br/>附带诊断报告 Analyzer->>Tracker: 为每条问题 SQL<br/>自动创建治理工单 Tracker->>Tracker: 责任人 + 优化建议<br/>（加索引 / 改写SQL / 缓存） DB->>Monitor: 优化后重新采集慢日志 Monitor->>Monitor: 对比优化前后<br/>耗时/频率/扫描行数 Note over Monitor: 指标改善 → 关单<br/>未改善 → 重新诊断

这里的关键设计是工单闭环和回验机制。不加回验的治理等于没治 —- 你给orders表加了个(user_id, create_time)联合索引，看着 EXPLAIN 里 type 从ALL变成了range，满意走人。一周后发现DELETE FROM orders WHERE id<1000因为新索引的维护开销反而变慢了。回验看板能从全局维度告诉你，加了这个索引后，表的整体慢查询是多了还是少了。

def verify_optimization( sql_fingerprint: str, before_metrics: dict, after_metrics: dict ) -> dict: """ 对比优化前后的关键指标 设计意图：加索引不是目的，变快才是目的。 如果加了索引后扫描行数没减少、耗时没降低， 说明优化方向错了，需要重新诊断。 """ avg_time_before = before_metrics["avg_query_time"] avg_time_after = after_metrics["avg_query_time"] freq_before = before_metrics["occurrences_per_hour"] freq_after = after_metrics["occurrences_per_hour"] time_improved = avg_time_after < avg_time_before * 0.8 # 耗时降低 20%+ return { "sql": sql_fingerprint, "time_before_ms": avg_time_before, "time_after_ms": avg_time_after, "improved": time_improved, "freq_change": freq_after - freq_before, "verdict": "优化有效" if time_improved else "需重新诊断" }

五、总结

🚨 踩坑提醒

1. 重复 EXPLAIN 不会展示实际数据量下的执行计划：MySQL 的EXPLAIN在分析优化器选择时用的是统计信息而不是真实数据分布。如果你的表刚做完大批量删除但还没ANALYZE TABLE，统计信息可能是过期的，EXPLAIN显示"走索引"，实际执行时优化器发现索引选择性超差、临时切回全表扫描。要想看真实的执行计划，用EXPLAIN ANALYZE（MySQL 8.0.18+），它能输出每个步骤的实际耗时和行数。

2. rows_examined >> rows_sent不一定是"无效扫描"：如果你的查询条件是WHERE status = 'pending' AND create_time > '2026-01-01'，返回 100 行但扫描 100 万行，看起来是索引问题。但实际情况可能是：status 的 pending 在整个表里只占 0.01%，而时间范围覆盖了半年的数据。索引只能选(status, create_time)或(create_time, status)，两者扫描量都大。这种情况下不是索引没走对，而是数据分布决定了无法用一个索引同时高效过滤两个条件，该考虑的是物化视图或分区表。

3. 回验只对比了同一条 SQL 的耗时，没考虑它对新引入的全局影响：你给 A 查询加了索引，A 确实快了。但这个新索引让 INSERT/UPDATE 变慢了一倍——因为每次写入都要更新这个索引的 B+ 树。如果你回验时只看 A 的耗时降了，没看写入瓶颈升了，等于拆东墙补西墙。完整的回验应该包含：该 SQL 自身的耗时变化 + 该表整体的 INSERT/UPDATE/DELETE 耗时变化 + 索引大小增量。

慢日志治理的正确姿势是：收集 → 指纹归类 → EXPLAIN 诊断 → 建工单 → 回验。光收集不看等于白做，光看不改等于白看，改了不回验等于白改。

落地时记住一个数字：80% 的慢查询由 20% 的 SQL 模板产生。指纹归类做了，优先级就出来了。优先修那 20%，四两拨千斤。然后配上回验看板，让每次优化都有数据背书，而不是拍脑袋说"应该加个索引"。