SQL 慢查询治理慢日志不是收集完就算完事了有些团队的做法是MySQL 开一个slow_query_log往 ELK 里一灌然后在 Grafana 上挂一张慢查询数量趋势图就算治理完了。结果真正吃资源的慢查询一个没解决只是每天在日会上感叹一句今天的慢查询又多了。慢日志的真正价值不在收集在分析、归因、闭环处理。一、慢日志堆在那就像没拆封的体检报告先看一个典型场景。数据库的slow_query_log每天产生 2GB 的文本你配置了 Filebeat 把它灌进 ElasticsearchKibana 里能看到慢查询列表。然后呢你看到了这些信息有一个查询跑了 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 id1和WHERE id999归为同一条 SQL。用pt-fingerprint或者自己写正则把值替换为?import re from collections import Counter def fingerprint_sql(sql: str) - str: 将 SQL 归一化为指纹去掉字面量值只保留结构 设计意图SELECT * FROM t WHERE id100 和 id200 本质是同一条 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 FORMATJSON 获取详细执行计划 EXPLAIN FORMATJSON 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;重点看三个指标EXPLAIN 指标正常需警惕原因rows_examined≈rows_sentrows_sent大量无效扫描Extra: Using filesort偶尔每次排序列无索引Extra: Using temporary无有内存临时表 → 磁盘type: ALL从不出现全表扫描key字段有值走了索引NULL没用索引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 问题 SQLbr/附带诊断报告 Analyzer-Tracker: 为每条问题 SQLbr/自动创建治理工单 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 id1000因为新索引的维护开销反而变慢了。回验看板能从全局维度告诉你加了这个索引后表的整体慢查询是多了还是少了。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 需重新诊断 }五、总结 踩坑提醒重复 EXPLAIN 不会展示实际数据量下的执行计划MySQL 的EXPLAIN在分析优化器选择时用的是统计信息而不是真实数据分布。如果你的表刚做完大批量删除但还没ANALYZE TABLE统计信息可能是过期的EXPLAIN显示走索引实际执行时优化器发现索引选择性超差、临时切回全表扫描。要想看真实的执行计划用EXPLAIN ANALYZEMySQL 8.0.18它能输出每个步骤的实际耗时和行数。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)两者扫描量都大。这种情况下不是索引没走对而是数据分布决定了无法用一个索引同时高效过滤两个条件该考虑的是物化视图或分区表。回验只对比了同一条 SQL 的耗时没考虑它对新引入的全局影响你给 A 查询加了索引A 确实快了。但这个新索引让 INSERT/UPDATE 变慢了一倍——因为每次写入都要更新这个索引的 B 树。如果你回验时只看 A 的耗时降了没看写入瓶颈升了等于拆东墙补西墙。完整的回验应该包含该 SQL 自身的耗时变化 该表整体的 INSERT/UPDATE/DELETE 耗时变化 索引大小增量。慢日志治理的正确姿势是收集 → 指纹归类 → EXPLAIN 诊断 → 建工单 → 回验。光收集不看等于白做光看不改等于白看改了不回验等于白改。落地时记住一个数字80% 的慢查询由 20% 的 SQL 模板产生。指纹归类做了优先级就出来了。优先修那 20%四两拨千斤。然后配上回验看板让每次优化都有数据背书而不是拍脑袋说应该加个索引。