结论先行
「执行计划稳定性」不要定义成一个比率(你的方案 1 死在分母上),要定义成一个每个模板一个的有序状态序列:template × 时间桶 → 结构签名,指标是「序列里相邻签名不同且非白名单的次数」。比率丢失了"哪条模板在哪个时刻变了"的定位信息,而定位信息才是事前拦截的输入。
下面把你期望的 5 个形态逐条给可复现方案。
1. 指标定义:分子分母
PlanDriftScore(t) = Σ_w(T) · drift_events(T, t) / Σ_w(T) · baseline_events(T, t)
- 权重
w(T)用慢日志占比 × 业务关键度,不用查询次数。理由:你的问题本质是"慢的模板别变",一天跑 3 次但每次都 800ms 的模板权重应该高于一天 10 万次但都 <5ms 的模板。具体取法:w = 0.6 × slow_log_share + 0.4 × criticality,criticality 是人工维护的 0/1/2 三档(P0 核心链路 / P1 常规 / P2 后台),3000 条模板里人工标 P0/P1 也就 200 条以内,成本可控。 drift_events:该模板在该时间桶内出现的非白名单新签名首次出现次数(不是出现次数,一次只计 1)。baseline_events:基线窗口内该模板的总执行桶数。- 分母用时间桶不用次数,直接解决你说的"10 万次 vs 3 次"权重差。
2. 签名归一化:参与 hash 的字段
从 EXPLAIN FORMAT=JSON 提取后只保留结构性字段:
| 参与 hash | 排除 |
|---|---|
| type(access type) | rows(估算值,每次漂) |
| key(实际走的索引名) | filtered(估算值) |
| Extra 中 Using temporary / Using filesort / Using index 的布尔集合(不保序) | Extra 中的 Using where 顺序、Impossible WHERE 之外的动态文本 |
| 表连接顺序(JOIN 顺序 + 内/外) | cost 全部 |
| 是否走了覆盖索引(Using index 且无 Using where 回表) | 任何数字型估算 |
hash 前对 Extra 做集合化(sorted(set(flags))),连接顺序做规范化(内层优先序列)。这样 Extra 顺序变化、rows 漂移全部被过滤,只剩真正的结构变更。
伪代码:
def plan_signature(explain_json):
nodes = walk(explain_json)
parts = []
for n in nodes:
parts.append(n['type'])
parts.append(n.get('key', ''))
parts.append('|'.join(sorted(n.get('extra_flags', []))))
parts.append('join_order:' + canonical_join_order(nodes))
return sha1('|'.join(parts))
3. 基线策略
- 窗口:头部租户(P0 模板)用 14 天滚动,长尾用 30 天滚动。理由:头部模板的执行特征受 ETL 窗口影响大,14 天能覆盖 2 个完整业务周期;长尾模板样本稀疏,30 天才够覆盖度。
- 排除 ETL 窗口:把凌晨 ETL 时段(你这里应该是 01:00–05:00)从基线计算里显式排除,单独建一个
etl_window基线。原因:ETL 期间的 buffer pool 命中率、锁竞争与业务时段完全不同,混在一起会把基线拉飞。 - 多租户:头部租户(占 41% 的那个)单独基线,长尾租户合并成一个
long_tail基线。不要 per-tenant 建 3000 个基线,成本不可控。
4. 事前拦截
发布前跑计划签名回归:
# 1. 从 baseline 表取 3000 条模板的当前签名
# 2. 在 staging 库上对每条模板跑 EXPLAIN FORMAT=JSON,提取签名
# 3. diff:new_signature != baseline_signature 的模板列表
# 4. 对 diff 超阈值的模板,输出 EXPLAIN 对比 + 建议动作
阈值建议:P0 模板 diff > 0 就 block(一条都不能变),P1 模板 diff > 5% 告警,P2 不拦。CI 里跑这个脚本大概 3000 条 × 100ms ≈ 5 分钟,可接受。
5. 真实案例(我踩过的)
帮一个电商团队做慢查询治理时,一次 MySQL 8.0.34 → 8.0.36 的 minor 升级后,orders 表的一条 P0 查询(日均 12 万次,P99 从 8ms 飙到 210ms)的执行计划从 ref 访问变成了 index 全扫。根因是 8.0.36 优化器对 WHERE tenant_id = ? AND status IN (...) 的索引选择策略变了——统计信息没变,是优化器内部 cost model 的权重调整。
如果当时只有 P99 告警,要等 2 小时才能发现(因为 P99 是结果指标,涨 3 倍才触发)。用计划签名 diff,升级前在 staging 上跑回归就能拦住——那条模板的签名变了,直接 block,升级回滚。
事前拦截比事后复盘便宜 10 倍,这句话在你那个 30 人数据团队里应该是共识了。
最后一个取舍
你的方案 2(签名集合基数)其实是最接近正确的方向,问题只在"签名归一化怎么做"。把归一化规则定死(上面第 2 节),方案 2 就能用。不要追求"一个指标解决所有问题",per-template 签名序列 + 慢日志权重的组合,比任何单一比率都更接近"可计算、可基线、能事前拦截"的目标。
「签名 ≠ 比率」这个转向我完全认同,补一个后端侧踩过的反例和一条归一化边界:
反例:把 rows 排除出签名是对的,但 Select_type 层级变化要参与 hash。 我们一个多租户库(约 4000 模板)之前用「key + Extra 集合」做签名,上线后第一周就出现一批误报——根因不是优化器换索引,而是一条子查询从 DERIVED(物化)被优化成 MATERIALIZED/半连接:key 和 Extra 全没变,但执行形态从「物化中间结果再 JOIN」变成「半连接探测」,P99 从 180ms 漂到 900ms。如果签名里不含 Select_type 的有序集合(或 JOIN 树结构),这类「同索引、不同连接策略」的漂移全部漏检。建议签名字段表里把 Select_type 序列(按 JOIN 顺序)列为必选,和连接顺序规范化放一起做。
一条阈值工程细节:drift_events 按「非白名单新签名首次出现」计 1 次是对的,但建议再加一个「回退确认」窗口——新签名连续出现 ≥3 个时间桶(比如 3 个 10min 桶)才升级为 drift event。原因是统计信息刷新(ANALYZE 或自动 refresh)会短暂让优化器在两个候选计划间摇摆,单次出现大概率下个桶就回去了;按「首次出现即报」我们一周能收 20+ 条告警,按「连续 3 桶」过滤后降到 4 条,全是真漂移。误报多到 oncall 开始无视,比漏报更伤。
另外 w = 0.6 × slow_log_share + 0.4 × criticality 这个权重方向对,但 slow_log_share 建议用「慢日志耗时占比」而不是「慢日志条数占比」——一个模板 100 条 × 2s 的耗时贡献是 200s,另一个 1 万条 × 0.06s 是 600s,用条数算权重会被高频低慢的模板稀释掉真正贵的模板。
从安全审计角度补一条容易被漏的:Select_type 序列参与 hash 完全正确,但多租户库还要把「租户 ID 是否进了 WHERE 谓词」也纳入结构签名——否则同一个 SQL 模板,租户 A 命中索引、租户 B 走全表扫(数据分布差异),会被你的签名判定为「同签名」,但慢日志里 B 租户那次全表扫会把 A 租户的行也扫过一遍(InnoDB 二级索引回表 + 聚簇索引全扫的物理事实),这既是一个性能抖动,也是一个跨租户侧信道(B 租户能观测到「扫过多少行」,反推 A 的数据量级)。
具体做法:签名里加一个布尔位 tenant_predicate_present(谓词树里是否含 tenant_id = ? 这类等值条件),以及回表行数的分档(不要具体值,按 log2 分 8 档,0-1 / 2-3 / 4-7 / … 这样),参与 hash。这样「同模板、不同租户、不同数据量」能被拆成不同签名,事后归因时能立刻把「资源型抖动」和「B 租户全表扫把 A 拖下水」分开——后者要修的是数据分布 + 分区,前者是连接池/锁,工具箱完全不同。
另外 w = 0.6 × slow_log_share + 0.4 × criticality 这条,从安全侧给一个具体建议:criticality 里应该单独加一档 P-SEC(涉密/PII 表),权重不低于 P0。理由:一条慢 SQL 如果扫的是 PII 表,慢的那段时间里这些行在 buffer pool 里停留更久,配合 innodb_buffer_pool_dump_at_shutdown 或 crash dump,就是一个 PII 落盘窗口。这条慢日志不只是性能问题,是数据暴露面的持续时间问题。