GaussDB 慢 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 |
根因链条至此闭合:
merchant_type='HEAD'实际只有 2 个商户,但其流水占全量的 31%;- 优化器假设
merchant_type与merchant_id相互独立,于是估算:
E-rows = (HEAD 选择率 2/380000) × (batch_date 分区行数 2300万) ≈ 121 行; - 而真实值是
2 个商户 × 平均流水 141 万 = 283 万行——偏差 6732 倍; - 基于 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,每次仍需回表取列)。
加索引的决策清单(缺一不可):
- 看当前计划:
EXPLAIN (ANALYZE, BUFFERS),确认瓶颈算子是Seq Scan、Index Scan还是NestLoop内侧; - 看估算偏差:若
E-rows与A-rows差 100 倍以上,先修统计信息,不要加索引——索引救不了错误的估算; - 看回表放大:索引列若不覆盖查询所需列,NestLoop 内侧会成为"索引扫 + 随机回表",此时应优先考虑覆盖索引(
INCLUDE)或改写为 HashJoin; - 加完后重新 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 价值总结
-
慢 SQL 治理的核心不是"调 SQL",而是"修估算":本次事故表面上是一条 SQL 变慢,实质是优化器的输入(统计信息)失真了 6732 倍。建立"先看
E-rowsvsA-rows"的排查习惯,能让 80% 的慢 SQL 问题在 10 分钟内锁定方向,而不是盲目加索引、重启实例。 -
多列相关性是 CBO 的系统性盲区,必须用扩展统计显式建模:任何满足"某一列取值决定了另一列分布"的场景(类型/状态与 ID、地区与编码、租户与用户),单列统计在数学上就无法表达,只能靠
CREATE STATISTICS ... (dependencies)。这应当成为表设计评审的必查项。 -
计划固化是必要成本,不是过度设计:金融核心系统的 SQL 一旦验证过好计划,就应该像代码一样被"版本管理"起来。SQL Patch + 计划基线 diff 卡点,把"计划变更"从不可见的运行时行为,变成了发布流程中可评审、可回滚的显式动作。
-
运维脚本的正确性需要被验证:
ANALYZE 父表这种"看起来对、实际无效"的脚本在分区表场景极其常见。建议所有数据维护脚本上线前,都用"故意造一条劣化计划 → 看脚本能否修复"的方式做反向验证,而不是只看脚本有没有报错。
- 点赞
- 收藏
- 关注作者
评论(0)