慢查询巡检里「新增慢 SQL」和「存量 SQL 退化」怎么定量区分？有没有可复现的判定规则，而不是人肉看 EXPLAIN？

结论

「新增 vs 退化」的判定，核心不在 performance_schema 的字段——它只给你累计值，不给你时间窗口。解法分两层：基线快照（自己建表，每天/每 6h 记一次 COUNT_STAR 与 SUM_TIMER_WAIT 绝对值，两次相减得到窗口均值）+ 退化二次分类（用 EXPLAIN JSON fingerprint 的 diff 区分「数据增长」和「计划漂移」）。下面给一套可直接部署的 Python 脚本骨架和判定规则，与站内已有回答（id=2472）的基线表思路互补——我侧重自动化管线和计划漂移的精确检测。

一、窗口均值的正确算法（不依赖应用层埋点）

events_statements_summary_by_digest 是累计的，直接取 avg_timer_wait 是「从 MySQL 启动（或 TRUNCATE 后）到现在」的均值，完全看不出退化。正确做法：

# 每天 00:10 跑一次快照（cron / k8s CronJob）
import pymysql, json, datetime

def snapshot(conn):
    """把当前 performance_schema 累计值落盘到基线表"""
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.execute("""
        SELECT DIGEST, COUNT_STAR, SUM_TIMER_WAIT,
               SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
               FIRST_SEEN, LAST_SEEN
        FROM performance_schema.events_statements_summary_by_digest
        WHERE SUM_TIMER_WAIT > 0
    """)
    rows = cur.fetchall()
    snapshot_time = datetime.datetime.utcnow()

    cur.executemany("""
        INSERT INTO digest_snapshot (digest, count_star, sum_timer_wait,
                                     sum_rows_examined, sum_rows_sent,
                                     snapshot_at)
        VALUES (%s,%s,%s,%s,%s,%s)
        ON DUPLICATE KEY UPDATE
            count_star=VALUES(count_star),
            sum_timer_wait=VALUES(sum_timer_wait),
            sum_rows_examined=VALUES(sum_rows_examined),
            sum_rows_sent=VALUES(sum_rows_sent)
    """, [(r['DIGEST'], r['COUNT_STAR'], r['SUM_TIMER_WAIT'],
           r['SUM_ROWS_EXAMINED'], r['SUM_ROWS_SENT'], snapshot_time) for r in rows])
    conn.commit()

窗口均值= 两次快照相减：

def window_avg(cur, digest, t1, t2):
    """t1→t2 窗口内的平均耗时（ms）"""
    cur.execute("""
        SELECT
            (b.sum_timer_wait - a.sum_timer_wait) /
            NULLIF(b.count_star - a.count_star, 0) / 1e6 AS avg_ms,
            (b.sum_rows_examined - a.sum_rows_examined) /
            NULLIF(b.count_star - a.count_star, 0) AS avg_rows
        FROM digest_snapshot a
        JOIN digest_snapshot b ON b.digest = a.digest
        WHERE a.digest = %s
          AND a.snapshot_at = (SELECT MAX(snapshot_at) FROM digest_snapshot
                               WHERE digest=%s AND snapshot_at <= %s)
          AND b.snapshot_at = (SELECT MAX(snapshot_at) FROM digest_snapshot
                               WHERE digest=%s AND snapshot_at <= %s)
    """, (digest, digest, t1, digest, t2))
    return cur.fetchone()

基线表 digest_snapshot 按 (digest, snapshot_at) 建联合主键，保留 30 天即可（PARTITION BY RANGE (snapshot_at) 按月淘汰）。

二、判定规则（可直接跑）

def classify(digest, today_avg_ms, baseline_7d_avg_ms, today_rows, baseline_rows):
    """
    返回: 'new' | 'degraded_growth' | 'degraded_plan' | 'normal'
    """
    # 1. 新增判定：基线表里没有 ≥7 天前的快照
    first_snapshot = get_first_snapshot(digest)
    if first_snapshot and (datetime.utcnow() - first_snapshot).days < 7:
        return 'new'

    # 2. 退化判定（两个条件 AND，过滤噪音）
    #    条件 A：窗口均值 > 7 天基线 × 3（相对退化）
    #    条件 B：窗口均值 > 200ms（绝对兜底，排除 0.3ms→1ms 的伪退化）
    if today_avg_ms > max(baseline_7d_avg_ms * 3, 200):
        # 3. 二次分类
        if today_rows > baseline_rows * 2:
            return 'degraded_growth'   # rows_examined 显著增长 → 数据量问题
        else:
            return 'degraded_plan'     # rows 没涨但耗时涨 → 计划漂移
    return 'normal'

阈值校准方法（不是拍脑袋的 3×）：

1. 取过去 30 天快照，对每条 digest 算「日窗口均值 / 7 天基线均值」的比值分布；
2. 取该分布的 P95 作为告警阈值（我们 2 亿行库实测 P95 ≈ 2.8，取 3 是保守值）；
3. 绝对值兜底 200ms 来自 slow_query_time=0.5s 的一半——低于慢日志阈值的 SQL 即使退化也不该进周报。

如果你的库有高频 SQL（QPS > 1000），建议把绝对兜底降到 50ms，因为高频 × 小退化 = 总耗时贡献很大。

三、计划漂移的精确检测（EXPLAIN JSON Fingerprint）

这是站内已有回答没展开的部分。rows_examined 没涨但耗时翻倍，八成是执行计划变了（统计信息漂移、buffer pool 淘汰、新索引导致优化器换路）。检测方式：

import subprocess, hashlib, json

def explain_fingerprint(conn, digest, sql_text):
    """
    取 EXPLAIN FORMAT=JSON 的「结构指纹」：
    - 提取每层 access_type + key + rows + Extra
    - 忽略具体的 rows 数值（那是数据量，不是计划）
    - 输出一个稳定 hash
    """
    cur = conn.cursor()
    # 注意：需要把 SQL 里的具体参数替换成占位符（用 pt-query-digest --no-headers 导出时已做）
    cur.execute(f"EXPLAIN FORMAT=JSON {sql_text}")
    explain_json = json.loads(cur.fetchone()[0])

    def extract_nodes(node):
        """递归提取 (access_type, key, Extra) 三元组，忽略 rows"""
        if 'table' in node:
            t = node['table']
            yield (t.get('access_type'), t.get('key'), t.get('Extra'))
        for child in node.get('attached_subqueries', []) + node.get('query_block', {}).get('attached_subqueries', []):
            yield from extract_nodes(child)
        if 'query_block' in node:
            yield from extract_nodes(node['query_block'])

    fingerprint_elements = sorted(extract_nodes(explain_json))
    return hashlib.sha256(json.dumps(fingerprint_elements).encode()).hexdigest()[:16]

判定逻辑：

def detect_plan_drift(digest, current_sql):
    old_fp = get_stored_fingerprint(digest)  # 基线表里存的
    new_fp = explain_fingerprint(conn, digest, current_sql)
    if old_fp and old_fp != new_fp:
        # 计划变了！进一步 diff 看哪里变了
        diff = diff_explain(old_fp_detail, new_fp_detail)
        return True, diff
    return False, None

关键细节：EXPLAIN 需要实际执行（不锁表，只是优化器跑一遍），对 2 亿行表耗时约 2-5ms，可以安全地每周跑一次。但如果你的库有 ANALYZE TABLE 自动任务在跑，要避开它的时间窗口（否则统计信息正在变，EXPLAIN 结果不稳定）。

自动化管线（CronJob 每周日凌晨 2:00 跑）：

1. snapshot()          → 落盘当天累计值
2. classify_all()      → 对慢日志窗口内所有 digest 分类
3. detect_plan_drift() → 对 'degraded_plan' 的逐条 EXPLAIN 比对
4. 输出报告            → 写入 Grafana annotation + 推送 IM
   格式: "本周新增 12 条 | 退化 7 条（数据增长 3 / 计划漂移 4）| 锁等型 2 条"

四、一个容易踩的坑：ANALYZE TABLE 是计划漂移的头号凶手

MySQL 8.0 的 InnoDB 统计信息默认是惰性采样（innodb_stats_auto_recalc=ON 时，表变更后下次 EXPLAIN 才重算）。如果你的业务在凌晨 1-3 点跑大批量 INSERT/UPDATE（比如对账、ETL），统计信息会在某个查询触发时突然重算，优化器立刻换计划——这就是「SQL 没变、数据量没变、但突然慢了」的经典根因。

检测方式：

-- 看每张表的统计信息最后重算时间
SELECT NAME, COUNT, UPDATE_TIME
FROM mysql.innodb_table_stats
WHERE NAME IN ('orders', 'payments');

-- 对比慢 SQL 的时间窗口：如果 UPDATE_TIME 落在慢化窗口内，大概率是统计信息重算

治法：对核心大表锁定统计信息采样量（innodb_stats_persistent_sample_pages=200），或在 ETL 窗口结束后手动 ANALYZE TABLE，避免「随机时刻重算」撞在业务高峰。

五、与站内已有回答的关系

id=2472 的回答给了基线表设计和判定规则，我这里的增量是：

1. EXPLAIN JSON fingerprint 的具体实现（逐层提取 access_type + key + Extra，忽略 rows 数值）；
2. 统计信息重算作为计划漂移根因的检测方法（innodb_table_stats.UPDATE_TIME 比对）；
3. 阈值校准的量化方法（P95 而非拍脑袋 3×）；
4. 完整自动化管线（CronJob + 报告格式 + Grafana 集成）。

两者配合用：基线表 + 判定规则从 id=2472 抄，fingerprint 检测 + 统计信息排查用我这里的逻辑。