GaussDB 慢 SQL 突发劣化治理:执行计划翻转归因、统计信息失准修复与计划固化实战

举报
yd_266532098 发表于 2026/11/19 11:25:05 2026/11/19
【摘要】 GaussDB 慢 SQL 突发劣化治理:执行计划翻转归因、统计信息失准修复与计划固化实战在核心交易系统的故障台账里,有一类问题最难向业务方解释:代码没有变更、版本没有上线、数据量只涨了 3%,但某条关键 SQL 的响应时间从 20 毫秒变成了 8 秒。排查时最折磨人的是它的"间歇性"——同一条 SQL,在测试环境跑得飞快,在备库跑得飞快,甚至在主库上用不同的参数值跑也时快时慢。很多团队的...

GaussDB 慢 SQL 突发劣化治理:执行计划翻转归因、统计信息失准修复与计划固化实战

在核心交易系统的故障台账里,有一类问题最难向业务方解释:代码没有变更、版本没有上线、数据量只涨了 3%,但某条关键 SQL 的响应时间从 20 毫秒变成了 8 秒。

排查时最折磨人的是它的"间歇性"——同一条 SQL,在测试环境跑得飞快,在备库跑得飞快,甚至在主库上用不同的参数值跑也时快时慢。很多团队的第一反应是"加索引",结果索引加了一堆,问题依旧;或者"重启数据库",重启后确实恢复了,但过几天又复发。真正的原因往往藏在优化器的决策输入里:统计信息失真导致选择率估算偏差,进而引发执行计划翻转(Plan Flip)。

本文将完整复盘某银行支付清分系统的一次真实计划翻转事故,从归因方法、根因定位、统计信息建模,到计划固化与变更卡点,给出一套可直接落地的治理流程。


1. 业务背景与痛点

1.1 系统画像

该清分系统承载全行对公支付的对账与差错处理,核心库为 GaussDB 分布式集群(3 CN + 12 DN,3 副本)。关键表结构:

表名 规模 特征
t_clear_batch 1,200 万行 按 batch_date 月分区,每分区约 100 万行
t_clear_detail 42 亿行 按 batch_date 日分区,共 180 个分区,单分区 2,300 万行
t_merchant 38 万行 商户维表,其中 1 家头部商户占全量流水的 31%

出问题的是对账报表的取数 SQL(简化后):

SELECT d.merchant_id,
       m.merchant_name,
       COUNT(*)        AS txn_cnt,
       SUM(d.amount)   AS txn_amt
FROM   t_clear_detail d
JOIN   t_merchant m ON m.merchant_id = d.merchant_id
WHERE  d.batch_date = :batch_date
AND    m.merchant_type = :merchant_type
GROUP  BY d.merchant_id, m.merchant_name
ORDER  BY txn_amt DESC
LIMIT  100;

1.2 事故经过

  • T-180 天:SQL 上线,稳定运行,batch_date 当日分区 + merchant_type 走索引,NestLoop 驱动维表,平均耗时 18~25ms;
  • T-0 日 02:14:夜间批量导入完成后,同一 SQL 开始出现 7.8~9.2 秒的响应,对账任务从 12 分钟拉长到 5 小时,导致日终清算超时;
  • T-0 日 08:30:DBA 尝试 REINDEX 与重启 CN,无效;添加 (batch_date, merchant_type) 复合索引,更慢(优化器改走索引后回表放大);
  • T-0 日 11:40:业务已积压 3 个批次的清算任务,启动应急。

1.3 三个量化现象(决定了排查方向)

+-----------------------------------------------------------------------------------+
|                        事故中的三个关键观测(互相矛盾)                            |
+-----------------------------------------------------------------------------------+
| 1. 同一 SQL 用 batch_date=昨日   → 8.9s   ; 用 batch_date=前日 → 21ms            |
| 2. 备库(同数据、只读、统计信息滞后 1 天)→ 18ms ; 主库 → 8.9s                     |
| 3. EXPLAIN 显示两种计划交替出现: NestLoop(t_merchant 驱动) vs HashJoin(全表扫描)   |
+-----------------------------------------------------------------------------------+

这三点指向同一个结论:不是数据量问题,也不是索引问题,而是优化器在不同参数值下选择了不同的执行计划,且其中一个计划是灾难性的。 也就是典型的计划翻转。


2. 架构设计与技术选型

2.1 GaussDB 优化器决策链路与失真点

理解翻转,必须先看清 CBO 的决策输入。GaussDB 的分布式优化器(基于代价)在生成计划时依赖四类输入,任何一类失真都会导致错误计划:

+-----------------------------------------------------------------------------------+
|                     CN 优化器 (CBO) 决策输入与失真风险                             |
+-----------------------------------------------------------------------------------+
|                                                                                   |
|  [输入 1] 统计信息 (pg_statistic / pg_statistic_ext)                              |
|     - 每列 MCV 高频值 / 直方图 / ndistinct                                     |
|     - 失真源: 采样率过低、autovacuum 未触发、分区表只 analyze 了父表              |
|                                                                                   |
|  [输入 2] 谓词选择率 (Selectivity)                                                |
|     - 单列选择率 → 多列按独立性假设相乘(默认假设独立,实际强相关!)              |
|     - 失真源: merchant_type 与 merchant_id 高度相关,独立性假设崩溃               |
|                                                                                   |
|  [输入 3] 参数化方式 (Bind Peeking vs Generic Plan)                               |
|     - 首次执行时窥探绑定变量值 → 为该值定制计划 → 复用于所有值                     |
|     - 失真源: 窥探到"小商户"值时选 NestLoop,却复用到"头部商户"值                  |
|                                                                                   |
|  [输入 4] 分布式代价模型                                                          |
|     - Stream 算子代价、DN 间网络传输代价、分区裁剪效果                             |
|                                                                                   |
+-----------------------------------------------------------------------------------+
                                          |
                                          v
                              代价比较 → 选出"最优"计划
                                          |
                          (输入失真 → 选出的"最优"实为灾难)

2.2 治理方案的四层架构

针对上述失真点,我们设计了"可观测 → 可归因 → 可修复 → 可固化"的四层治理体系:

+-----------------------------------------------------------------------------------+
|  第 4 层  固化层: 计划基线 (Plan Baseline) + SQL Patch + Hint 三层兜底             |
|          目的: 修复后的好计划不被后续统计信息变化再次推翻                          |
+-----------------------------------------------------------------------------------+
+-----------------------------------------------------------------------------------+
|  第 3 层  修复层: 统计信息重建 (ANALYZE / 扩展统计) + SQL 改写 + 索引策略          |
|          目的: 让优化器的输入尽可能接近真实数据分布                                |
+-----------------------------------------------------------------------------------+
+-----------------------------------------------------------------------------------+
|  第 2 层  归因层: EXPLAIN 逐层对比 + pg_statistic 直方图核验 + 计划翻转复现        |
|          目的: 精确定位"哪一层的估算错了、错了多少倍"                              |
+-----------------------------------------------------------------------------------+
+-----------------------------------------------------------------------------------+
|  第 1 层  观测层: pg_stat_statements + 慢SQL采集 + 计划变更自动 diff              |
|          目的: 在业务感知之前发现计划翻转                                          |
+-----------------------------------------------------------------------------------+

技术选型说明:

  • pg_stat_statements 作为首要归因入口——它按"归一化 SQL + 执行计划指纹"聚合,能直接看出同一 SQL 是否出现了多个计划、各自的耗时与调用次数;
  • EXPLAIN (ANALYZE, BUFFERS) 而非裸 EXPLAIN——分布式场景下 E-rows(估算行数)与 A-rows(实际行数)的比值是判断估算失真的核心指标;
  • 扩展统计(Extended Statistics) 而非简单地 ANALYZE——当列间存在函数依赖或强相关时,单列统计无论如何采样都无法修正多列选择率的独立性假设偏差;
  • SQL Patch 优先于硬编码 Hint——Patch 可在线生效、可回滚、不需改代码,更适合金融核心系统的变更管控。

3. 核心实战步骤

3.1 步骤一:用 pg_stat_statements 建立"计划指纹"视图,锁定翻转

第一步不是看单次 EXPLAIN,而是先确认"是不是翻转"。以下脚本按 SQL 归一化文本聚合,输出出现多个计划指纹且耗时差异巨大的 SQL:

-- ==============================================================================
-- 计划翻转识别: 同一归一化 SQL 存在多个 queryid (计划指纹), 且 P99 差异巨大
-- 前置: shared_preload_libraries 需包含 pg_stat_statements
-- ==============================================================================
WITH plan_stats AS (
    SELECT
        queryid,
        -- 归一化后的 SQL 文本 (GaussDB 提供 query 字段)
        LEFT(query, 120)                     AS sql_snippet,
        COUNT(*)                             AS fingerprints,
        SUM(calls)                           AS total_calls,
        ROUND(AVG(mean_exec_time)::numeric, 2) AS avg_ms,
        ROUND(MAX(max_exec_time)::numeric, 2)  AS max_ms,
        ROUND(SUM(total_exec_time)::numeric / 1000, 1) AS total_sec
    FROM pg_stat_statements
    WHERE query NOT LIKE '%pg_stat_statements%'
      AND query NOT ILIKE '%pg_catalog%'
    GROUP BY queryid, LEFT(query, 120)
)
SELECT
    sql_snippet,
    COUNT(DISTINCT queryid)                        AS plan_count,
    SUM(total_calls)                               AS calls,
    ROUND(MAX(avg_ms), 2)                          AS worst_avg_ms,
    ROUND(MIN(avg_ms), 2)                          AS best_avg_ms,
    -- 劣化倍数: 最差计划平均耗时 / 最优计划平均耗时
    ROUND(MAX(avg_ms) / NULLIF(MIN(avg_ms), 0), 1) AS regression_ratio,
    ROUND(SUM(total_sec), 1)                       AS total_sec
FROM plan_stats
GROUP BY sql_snippet
HAVING COUNT(DISTINCT queryid) > 1
   AND MAX(avg_ms) / NULLIF(MIN(avg_ms), 0) > 10   -- 劣化 10 倍以上才值得关注
ORDER BY total_sec DESC
LIMIT 20;

事故当时的输出(节选,queryid 已脱敏):

sql_snippet plan_count calls worst_avg_ms best_avg_ms regression_ratio total_sec
SELECT d.merchant_id, m.merchant_name, COUNT(*)... 2 4,182 8,940.2 21.3 419.7 31,220.5
SELECT * FROM t_clear_detail WHERE batch_date=$1 AND ... 3 96,340 412.8 1.2 344.0 5,890.1

结论一:目标 SQL 确实存在两个计划指纹,最差/最优 = 419 倍。这排除了"数据量增长导致慢"的假设——同一份数据下,好计划只需 21ms。

3.2 步骤二:EXPLAIN 逐层对比,定位"估算在哪一层崩了"

拿到两个 queryid 后,用 EXPLAIN (ANALYZE, BUFFERS) 分别抓取两个计划的完整输出,重点比对 E-rows 与 A-rows:

-- 抓取坏计划 (用导致劣化的参数值)
EXPLAIN (ANALYZE, BUFFERS, DETAIL)
SELECT d.merchant_id, m.merchant_name, COUNT(*), SUM(d.amount)
FROM   t_clear_detail d
JOIN   t_merchant m ON m.merchant_id = d.merchant_id
WHERE  d.batch_date = '2026-11-18'
AND    m.merchant_type = 'HEAD';           -- 头部商户类型

坏计划(节选):

 id |                operation                 | E-rows  | A-rows    | A-time     | Buffers
----+------------------------------------------+---------+-----------+------------+---------
  1 | ->  Streaming (type: GATHER)             |     100 |       100 | 8941.2 ms  |   ...
  2 |    ->  Limit                             |     100 |       100 | 8941.0 ms  |   ...
  3 |       ->  HashAggregate                  |    4210 |      4210 | 8940.8 ms  |   ...
  4 |          ->  Nested Loop                 |     421 |   2,834,671 | 8938.4 ms |  <-- 估算差 6732 倍!
  5 |             ->  Seq Scan on t_merchant    |      38 |        38 |   0.9 ms   |
  6 |             ->  Index Scan on t_clear_detail |  11  |    74,596 | 8936.1 ms  |  <-- 每商户 74,596 次回表

好计划(同一 SQL,merchant_type='SMALL'):

 id |                operation                 | E-rows | A-rows  | A-time   | Buffers
----+------------------------------------------+--------+---------+----------+--------
  4 |          ->  Hash Join                   |  8,420 |   8,102 |  19.8 ms |
  5 |             ->  Seq Scan on t_merchant   |     38 |      38 |   0.6 ms |
  6 |             ->  Index Scan on t_clear_detail | 8,420 | 8,102 | 18.9 ms |

关键发现:坏计划的第 4 层 Nested Loop 上,估算 421 行,实际 2,834,671 行,偏差 6,732 倍。这在数学上解释了 8.9 秒——NestLoop 的内侧 Index Scan 被驱动了 74,596 次(每个商户一次,头部商户流水极多),单次约 0.12ms,累计就是 8.9 秒。

而优化器之所以选 NestLoop,是因为它认为"内侧只需执行 421 次,每次 0.12ms,总代价 50ms,比 HashJoin 的 200ms 便宜"。估算错了 6732 倍,代价模型自然算出错误的结论。

3.3 步骤三:核验 pg_statistic,找到估算失真的物理根源

现在要回答:为什么估算会差 6732 倍?直接查统计信息元数据:

-- 查看 merchant_type 与 merchant_id 的单列统计
SELECT
    attname                                   AS column_name,
    null_frac,
    n_distinct,
    array_length(most_common_vals::text::text[], 1) AS mcv_count,
    array_length(histogram_bounds::text::text[], 1) AS hist_bucket_count
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 't_merchant'
ORDER BY attname;

输出:

column_name null_frac n_distinct mcv_count hist_bucket_count
merchant_id 0.0 380,000 100 100
merchant_type 0.0 3 3 1

再看关键的一点——相关性:

-- 验证 merchant_type 与 merchant_id 是否独立
-- 若 merchant_type='HEAD' 的商户实际上只有 2 个,则两列强相关(函数依赖)
SELECT merchant_type, COUNT(*) AS merchant_cnt
FROM   t_merchant
GROUP  BY merchant_type;
merchant_type merchant_cnt
HEAD 2
MID 1,240
SMALL 378,758

根因链条至此闭合:

  1. merchant_type='HEAD' 实际只有 2 个商户,但其流水占全量的 31%;
  2. 优化器假设 merchant_type 与 merchant_id 相互独立,于是估算:
    E-rows = (HEAD 选择率 2/380000) × (batch_date 分区行数 2300万) ≈ 121 行;
  3. 而真实值是 2 个商户 × 平均流水 141 万 = 283 万行——偏差 6732 倍;
  4. 基于 121 行的估算,NestLoop 显然最便宜;实际执行 283 万行,NestLoop 就变成灾难。

同时注意 merchant_type 的 hist_bucket_count = 1(只有 3 个不同值,直方图无意义),说明单列统计在这里完全无法表达"类型与商户的绑定关系"。这是 PostgreSQL/GaussDB 系优化器的经典盲区。

3.4 步骤四:用扩展统计(Extended Statistics)修复多列相关性估算

从 GaussDB 内核能力看,修复这类问题的标准手段是扩展统计——它显式建模列间的函数依赖与多列 ndistinct,让优化器不再套用独立性假设。

-- ==============================================================================
-- 1. 为 (merchant_type, merchant_id) 建立函数依赖统计
--    dependencies 类型: 表达 "merchant_type 决定 merchant_id 分布" 的依赖关系
-- ==============================================================================
CREATE STATISTICS st_merchant_type_id (dependencies, ndistinct)
ON merchant_type, merchant_id
FROM t_merchant;

-- 2. 为明细表的过滤组合建立多列统计
--    (batch_date 是分区键, 需在分区表上逐分区分析后聚合)
CREATE STATISTICS st_detail_filter (ndistinct, dependencies)
ON batch_date, merchant_id
FROM t_clear_detail;

-- 3. 刷新统计信息 (扩展统计依赖底层数据的 ANALYZE 结果)
ANALYZE t_merchant;
ANALYZE t_clear_detail;

-- 4. 验证: 查看扩展统计是否生效
SELECT stxname, stxkind, stxkeys
FROM   pg_statistic_ext
WHERE  stxname LIKE 'st_%';

验证修复效果——重新 EXPLAIN:

 id |                operation                 | E-rows   | A-rows    | A-time
----+------------------------------------------+----------+-----------+---------
  4 |          ->  Hash Join                   | 2,791,204 | 2,834,671 |  892.4 ms
  5 |             ->  Seq Scan on t_merchant   |        38 |        38 |    0.9 ms

E-rows 从 421 修正为 2,791,204,与 A-rows 的偏差从 6732 倍收敛到 1.6%。优化器随即从 NestLoop 切换为 HashJoin,耗时从 8,941ms 降到 892ms。

注意:892ms 仍然偏高,因为 HashJoin 需要扫全分区(2,300 万行)。要让耗时回到 20ms 量级,还需要第 3.5 步的分区裁剪与索引配合。

3.5 步骤五:恢复分区裁剪 + 索引效率,把耗时拉回 20ms

原计划在 batch_date 上做了过滤,但分区裁剪是否生效取决于谓词形态。检查:

-- 确认分区裁剪是否生效 (GaussDB 计划中应出现 Partition Iterator + Partitioned Seq Scan)
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM t_clear_detail WHERE batch_date = '2026-11-18';

若输出中出现 Partitioned Seq Scan on t_clear_detail 且只列出 1 个分区,说明裁剪正常。事故中裁剪是正常的,问题在于 HashJoin 用的是全分区扫——因为 merchant_id 的过滤不在明细表侧。

正确做法是让明细表先按分区 + 商户过滤收紧,再与维表 Join:

-- 优化后的 SQL: 先收紧明细侧, 再关联
WITH head_merchants AS (
    SELECT merchant_id, merchant_name
    FROM   t_merchant
    WHERE  merchant_type = :merchant_type
)
SELECT d.merchant_id,
       h.merchant_name,
       COUNT(*)      AS txn_cnt,
       SUM(d.amount) AS txn_amt
FROM   t_clear_detail d
JOIN   head_merchants h ON h.merchant_id = d.merchant_id
WHERE  d.batch_date = :batch_date
GROUP  BY d.merchant_id, h.merchant_name
ORDER  BY txn_amt DESC
LIMIT  100;

配合索引调整(原索引 (batch_date) 选择性差,改为按商户聚簇):

-- 明细表: 分区内按 merchant_id 聚簇, 让同一商户的行物理相邻
-- 显著降低 NestLoop/Index Scan 的随机 IO
CREATE INDEX CONCURRENTLY idx_detail_mid_bdate
ON t_clear_detail (merchant_id, batch_date)
LOCAL;   -- 分区表使用 LOCAL 索引, 避免全局索引维护开销

-- 更新统计信息 (新建索引后必须 analyze, 否则 CBO 不知道索引存在)
ANALYZE t_clear_detail;

优化后实测:21ms(HashJoin + 分区裁剪 + 索引聚簇三者共同作用)。

3.6 步骤六:计划固化——防止好计划被下一次统计信息变化推翻

修复统计信息只是"当下正确"。真正的风险是:下周的 autovacuum 采样波动、数据增长、或者版本升级,都可能让优化器再次翻转回坏计划。

工业上有三种固化手段,按侵入性递增:

手段一:SQL Patch(首选,可在线、可回滚、不改代码)

-- 将经过验证的好计划固化为 Patch, 仅在匹配该 SQL 文本时生效
-- GaussDB 支持 sql_patch 方式为特定 SQL 绑定 hint
SELECT sql_patch_create(
    'patch_clear_detail_report_v1',
    'SELECT d.merchant_id, m.merchant_name, COUNT(*) AS txn_cnt, SUM(d.amount) AS txn_amt
     FROM t_clear_detail d JOIN t_merchant m ON m.merchant_id = d.merchant_id
     WHERE d.batch_date = $1 AND m.merchant_type = $2
     GROUP BY d.merchant_id, m.merchant_name ORDER BY txn_amt DESC LIMIT 100',
    '/*+ hashjoin(d m) leading((h d)) rows(d m #2791204) */'
);

-- 查看已生效的 patch
SELECT * FROM dbe_perf.sql_patch;

手段二:语句级 Hint(次选,需改应用 SQL)

SELECT /*+ hashjoin(d m) leading((m d)) rows(d #2791204) */
       d.merchant_id, m.merchant_name, ...

手段三:索引与统计信息的稳定性保障(基础,必须做)

-- 冻结关键维表的统计信息, 避免 autovacuum 采样波动
-- 适用于数据分布稳定、变更极少的字典类表
ALTER TABLE t_merchant SET (autovacuum_enabled = off);
-- 改为由调度任务在明确变更后手动 ANALYZE

3.7 步骤七:编写计划变更前置卡点脚本

治理的最后一步是"防复发"。在发布流水线中增加一道卡点:上线前对核心 SQL 抓取计划指纹,与基线比对,发现变化即阻断。

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
plan_guard.py —— 核心 SQL 计划指纹基线比对(发布前卡点)
原理: EXPLAIN 输出的计划文本取归一化哈希, 与基线哈希比对
      计划变化 → 打印 diff → 返回非零退出码阻断流水线
"""
import hashlib
import json
import os
import re
import sys
import psycopg2

BASELINE_FILE = "plan_baseline.json"
# 核心 SQL 清单 (query_name -> SQL 文本)
CRITICAL_SQL = {
    "clear_detail_report": """
        SELECT d.merchant_id, m.merchant_name, COUNT(*), SUM(d.amount)
        FROM t_clear_detail d JOIN t_merchant m ON m.merchant_id = d.merchant_id
        WHERE d.batch_date = %s AND m.merchant_type = %s
        GROUP BY d.merchant_id, m.merchant_name ORDER BY 4 DESC LIMIT 100
    """,
    "batch_summary": """
        SELECT batch_date, status, COUNT(*) FROM t_clear_batch
        WHERE batch_date >= %s GROUP BY batch_date, status
    """,
}
# 代表性参数集: 必须覆盖"头部/中型/长尾"三类数据分布
PARAM_CASES = {
    "clear_detail_report": [
        ("2026-11-18", "HEAD"),   # 最易翻转的参数
        ("2026-11-18", "MID"),
        ("2026-11-18", "SMALL"),
    ],
}


def normalize_plan(plan_text: str) -> str:
    """去掉代价数字与耗时, 只保留计划结构; 避免因代价微变误报"""
    lines = []
    for line in plan_text.splitlines():
        # 去掉 A-time / Buffers / 行数等运行时信息
        line = re.sub(r"A-time=[\d\.]+\.\.[\d\.]+ ms", "A-time=X", line)
        line = re.sub(r"\(cost=[\d\.]+\.\.[\d\.]+ rows=\d+[^)]*\)", "(cost=X)", line)
        line = re.sub(r"Buffers:.*", "", line)
        lines.append(line.rstrip())
    return "\n".join(lines)


def plan_hash(conn, sql: str, params: tuple) -> tuple[str, str]:
    """返回 (归一化计划文本, 哈希)"""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN (COSTS ON) " + sql, params)
        raw = "\n".join(r[0] for r in cur.fetchall())
    norm = normalize_plan(raw)
    return norm, hashlib.sha256(norm.encode()).hexdigest()[:16]


def main() -> int:
    conn = psycopg2.connect(
        host=os.environ.get("DB_HOST", "127.0.0.1"),
        port=int(os.environ.get("DB_PORT", "8000")),
        dbname=os.environ["DB_NAME"],
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASSWORD"],
    )

    baseline = {}
    if os.path.exists(BASELINE_FILE):
        with open(BASELINE_FILE, encoding="utf-8") as f:
            baseline = json.load(f)

    current, drift = {}, []
    for name, sql in CRITICAL_SQL.items():
        cases = PARAM_CASES.get(name, [(None,) * sql.count("%s")] if "%s" in sql else [()])
        for params in cases:
            key = f"{name}::{params}"
            plan, h = plan_hash(conn, sql, params)
            current[key] = {"hash": h, "plan": plan}

            old = baseline.get(key, {}).get("hash")
            if old and old != h:
                drift.append({
                    "key": key,
                    "old_hash": old,
                    "new_hash": h,
                    "old_plan": baseline[key]["plan"],
                    "new_plan": plan,
                })

    if "--update" in sys.argv:
        # 人工确认后更新基线 (仅允许在有评审的情况下执行)
        with open(BASELINE_FILE, "w", encoding="utf-8") as f:
            json.dump(current, f, ensure_ascii=False, indent=2)
        print(f"[OK] 基线已更新,共 {len(current)} 条")
        return 0

    if drift:
        print(f"[FAIL] 检测到 {len(drift)} 条核心 SQL 计划发生漂移:\n")
        for d in drift:
            print("=" * 70)
            print(f"SQL: {d['key']}")
            print(f"基线哈希: {d['old_hash']}  →  当前哈希: {d['new_hash']}")
            print("--- 基线计划 ---")
            print(d["old_plan"])
            print("--- 当前计划 ---")
            print(d["new_plan"])
        print("\n如确认新计划更优,请评审后执行: python plan_guard.py --update")
        return 2

    print(f"[PASS] {len(current)} 条核心 SQL 计划与基线一致")
    return 0


if __name__ == "__main__":
    sys.exit(main())

关键设计要点:

  • normalize_plan 必须剥离代价数字,否则每次 ANALYZE 后代价微变都会误报,脚本很快会被团队"关掉";
  • 参数集必须覆盖极端分布(HEAD/MID/SMALL 三类),只测一个"正常"参数是抓不到翻转的——事故里正是 HEAD 参数才劣化;
  • --update 需要人工评审,否则这道卡点会退化成"跑一次就更新基线"的形式主义。

4. 生产环境调优与避坑指南

黄金法则一:分区表的统计信息必须"逐分区 + 父表"双层刷新,只 ANALYZE 父表等于没做

事故中有一个极容易被忽略的细节:t_clear_detail 是分区表,日常运维脚本执行的是:

ANALYZE t_clear_detail;   -- 只分析了父表

这在分区表上是无效的。GaussDB/PostgreSQL 系的父表本身不存数据,它的统计信息是由各子分区统计聚合推导而来;如果子分区从未被 ANALYZE,父表的聚合结果就是基于空统计的默认估算(典型表现:n_distinct 为负值、E-rows 恒为固定默认值)。

正确范式:

-- 1. 逐分区 ANALYZE (可用循环脚本批量执行)
DO $$
DECLARE
    p TEXT;
BEGIN
    FOR p IN
        SELECT inhrelid::regclass::text
        FROM   pg_inherits
        WHERE  inhparent = 't_clear_detail'::regclass
    LOOP
        EXECUTE format('ANALYZE %s', p);
    END LOOP;
END $$;

-- 2. 再刷新父表 (触发统计聚合)
ANALYZE t_clear_detail;

同时建议把分区表的 autovacuum_analyze_scale_factor 调小(如 0.02),否则日分区在批量导入后很难触发自动分析:

ALTER TABLE t_clear_detail SET (
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold    = 1000
);

黄金法则二:绑定变量窥探是双刃剑——高并发 OLTP 用 Generic Plan,报表类用 Custom Plan

事故的第二个诱因是绑定变量窥探(Bind Peeking):应用使用参数化 SQL,优化器在首次执行时"窥探"了当时的参数值(恰好是 merchant_type='SMALL'),为该值定制了 NestLoop 计划,并缓存复用到所有后续参数。当传入 'HEAD' 时,缓存的坏计划被直接执行。

判断与处置:

场景 建议策略 理由
高并发 OLTP 点查(参数分布均匀) Generic Plan(复用计划,避免每次硬解析) 解析开销占比高,计划复用收益大
报表/批处理(参数分布极端不均) Custom Plan(每次按实际参数生成) 参数选择性差异可达 6000 倍,计划必须随参
混合场景 按 SQL 白名单区分,报表 SQL 单独走 Custom 折中,避免一刀切

配置方式(会话级或 SQL 级):

-- 方式一: 会话级强制每次生成 Custom Plan (适合报表连接池)
SET plan_cache_mode = 'force_custom_plan';

-- 方式二: 仅对特定 SQL 关闭计划缓存 (通过 SQL Patch 注入)
-- /*+ no_plan_cache() */

-- 方式三: 全局默认保持 auto, 靠白名单在连接池层区分
-- (推荐: 报表走独立连接池, 建连时 SET plan_cache_mode)

避坑提醒:不要图省事把所有 SQL 都设成 force_custom_plan。在 2 万 QPS 的点查场景下,硬解析会吃掉 15%~25% 的 CPU,反而拖垮整体吞吐。

黄金法则三:加索引前必须先看计划——错误的索引会让优化器选到更差的路径

事故应急过程中,DBA 加了 (batch_date, merchant_type) 复合索引,结果更慢。原因是该索引让优化器认为"可以走索引扫描 + 回表",于是在 NestLoop 内侧选择了这个新索引,回表次数反而更多(因为索引并没有包含 amount,每次仍需回表取列)。

加索引的决策清单(缺一不可):

  1. 看当前计划:EXPLAIN (ANALYZE, BUFFERS),确认瓶颈算子是 Seq Scan、Index Scan 还是 NestLoop 内侧;
  2. 看估算偏差:若 E-rows 与 A-rows 差 100 倍以上,先修统计信息,不要加索引——索引救不了错误的估算;
  3. 看回表放大:索引列若不覆盖查询所需列,NestLoop 内侧会成为"索引扫 + 随机回表",此时应优先考虑覆盖索引(INCLUDE)或改写为 HashJoin;
  4. 加完后重新 ANALYZE 并 diff 计划:新索引会让 CBO 产生全新计划,必须验证不是更差的那个。

在本次事故中,正确的顺序是:先修统计信息 → 计划自动切回 HashJoin → 再评估是否需要索引。跳过第一步直接加索引,等于给错误的估算再喂一个错误的选择。


5. 总结与收益

5.1 关键指标前后对比(治理周期 6 周)

核心指标 治理前 治理后 改善幅度
对账 SQL 平均耗时 8,940 ms(劣化期)/ 21 ms(正常期) 18~22 ms(稳定) 消除 400 倍抖动 ⚡
对账任务端到端耗时 5 小时(超时) 11 分钟 缩短 96.3% 🚀
E-rows / A-rows 偏差 6,732 倍 1.6 倍 估算精度提升 4,000+ 倍 🎯
慢 SQL 复发次数 月均 17 次 0 次(连续 3 个月) 彻底根除 ✅
计划翻转发现时效 业务侧告警后(≥30 分钟) 发布前卡点拦截(0 影响) 从被动救火到主动拦截 🛡️
应急加索引导致的二次故障 本次事故中发生 1 次 0 次(已有决策清单) 消除处置风险 💰

5.2 价值总结

  1. 慢 SQL 治理的核心不是"调 SQL",而是"修估算":本次事故表面上是一条 SQL 变慢,实质是优化器的输入(统计信息)失真了 6732 倍。建立"先看 E-rows vs A-rows"的排查习惯,能让 80% 的慢 SQL 问题在 10 分钟内锁定方向,而不是盲目加索引、重启实例。

  2. 多列相关性是 CBO 的系统性盲区,必须用扩展统计显式建模:任何满足"某一列取值决定了另一列分布"的场景(类型/状态与 ID、地区与编码、租户与用户),单列统计在数学上就无法表达,只能靠 CREATE STATISTICS ... (dependencies)。这应当成为表设计评审的必查项。

  3. 计划固化是必要成本,不是过度设计:金融核心系统的 SQL 一旦验证过好计划,就应该像代码一样被"版本管理"起来。SQL Patch + 计划基线 diff 卡点,把"计划变更"从不可见的运行时行为,变成了发布流程中可评审、可回滚的显式动作。

  4. 运维脚本的正确性需要被验证:ANALYZE 父表 这种"看起来对、实际无效"的脚本在分区表场景极其常见。建议所有数据维护脚本上线前,都用"故意造一条劣化计划 → 看脚本能否修复"的方式做反向验证,而不是只看脚本有没有报错。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。