SQL执行计划漂移测试：测试环境断言用例与CI自动化监控清单

现象或背景

我做了10年测试开发，在2.3亿行级别的账务表上踩过一模一样的坑——凌晨批跑后白天的接口回归突然从5ms飙到12s，CI绿了但性能回归没人拦。下面从测试侧给你三个可落地的动作，和前面回答的"运维侧修"互补。

原理或根因分析

执行计划漂移的根本原因在于统计信息（cardinality）的失真，尤其是在数据批量写入后。优化器基于错误的统计信息选择索引或访问路径，导致查询性能急剧下降。这类漂移比索引没建好更隐蔽，因为它不报错、不锁表，只在EXPLAIN的rows估算里悄悄骗你。

具体做法

一、把EXPLAIN当断言：计划漂移检测（Plan Stability Test）

核心思路：在你现有的300条接口回归里，给这条核心SQL加一条"计划断言用例"，每次回归都跑。不是跑一遍业务逻辑看200，而是跑一遍EXPLAIN看执行计划没变。

# tests/test_plan_stability.py
import pytest
import pymysql

EXPECTED_PLAN = {
    "table": "t_invoice_item",
    "type": "ref",          # 索引访问类型
    "key": "idx_tenant_period",
    "rows_max": 50000,      # 预估命中行数上限（留余量）
}

@pytest.mark.plan_stability
def test_invoice_query_plan_stable(db_conn):
    """断言核心SQL的执行计划未漂移"""
    sql = (
        "EXPLAIN SELECT id, amount, status FROM t_invoice_item "
        "WHERE tenant_id = %s AND billing_period = %s AND status = 'UNBILLED' "
        "ORDER BY id LIMIT 500"
    )
    with db_conn.cursor(pymysql.cursors.DictCursor) as cur:
        cur.execute(sql, (999, '2026-10'))
        row = cur.fetchone()

    # 核心断言：必须走索引
    assert row["key"] == EXPECTED_PLAN["key"], \
        f"计划漂移! 期望 key={EXPECTED_PLAN['key']}, 实际 key={row['key']}, type={row['type']}"
    assert row["type"] in ("ref", "range"), \
        f"访问类型退化: {row['type']} (期望 ref/range)"
    # 行数断言：如果 rows 飙到2亿，说明全表扫了
    assert row["rows"] <= EXPECTED_PLAN["rows_max"], \
        f"预估行数异常: {row['rows']} > {EXPECTED_PLAN['rows_max']}, 疑似全表扫描"

为什么这比运维侧修更重要：运维侧（hint/SPM/定时ANALYZE）解决的是"生产别炸"，但你的测试环境凌晨也会跑批（或者造数脚本批量UPDATE status），测试环境同样会触发计划漂移——你白天跑回归是绿的，凌晨跑就挂了，然后报"环境不稳定"。计划断言用例把这个问题前置到每次CI里，不用等到生产才发现。

二、测试环境里的"批跑后漂移窗口"怎么治

你的测试环境大概率也有批量造数/清数据脚本（比如每轮回归前把status全刷成UNBILLED、或模拟批跑把一批翻成BILLED）。这些批量DML同样触发InnoDB自动ANALYZE，同样导致计划漂移。

推荐做法：在造数脚本的DML结束后、业务用例开始前，插入一条ANALYZE TABLE：

# 造数脚本末尾（伪代码）
mysql -e "UPDATE t_invoice_item SET status='BILLED' WHERE batch_id=..."
mysql -e "ANALYZE TABLE t_invoice_item"   # ← 加这一行，< 100ms
# 然后再跑业务用例
pytest -m business -v

代价：ANALYZE TABLE在2.3亿行表上大约50~200ms（InnoDB默认采样20页=320KB随机读），对CI时长几乎无影响。收益：消灭了"造数后30分钟内用例偶发超时"的假失败。

三、监控侧：把rows_examined纳入性能回归告警

即使你加了计划断言，也建议在performance_schema上做一个持续监控，不需要额外部署：

-- 定时查（cron每5分钟跑一次，或接入Prometheus MySQL exporter）
SELECT
    digest_text,
    count_star,
    ROUND(avg_rows_examined / 1000, 1) AS avg_rows_k,
    ROUND(max_rows_examined / 1000, 1) AS max_rows_k,
    ROUND(avg_latency / 1000000, 2) AS avg_ms,
    last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'billing'
  AND digest_text LIKE '%t_invoice_item%UNBILLED%'
ORDER BY last_seen DESC
LIMIT 5;

告警规则：avg_rows_examined从日常的~5万突然飙到>1000万→触发P1告警（说明计划漂移了，还没到全表但已经在退化）。

为什么用rows_examined而不是latency：latency受buffer pool命中率、CPU负载等干扰大；rows_examined是optimizer自己的估算，一旦计划从索引切到全表，这个数字会瞬间从5万跳到2.3亿，是最早、最确定的漂移信号。

踩过的坑或反直觉之处

一个我踩过的具体坑（改库改时间戳触发计划漂移）

去年我负责一条对账链路，测试环境造数脚本里有一步UPDATE t_recon SET biz_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) WHERE ...，单条更新800万行。跑完之后，下游查询WHERE biz_date = ? AND status = 'PENDING'突然从走索引变成全表扫——原因和楼主一模一样：PENDING占比从15%掉到2%，optimizer认为全表更便宜。

我们的解法（三步）：

造数脚本改为INSERT（造新数据带正确biz_date），不再UPDATE已有行→消灭了DML触发ANALYZE的源头。
核心查询加/*+ INDEX(t_recon idx_biz_status) */hint→兜底。
CI里加test_plan_stability用例→防回归。

三步加起来改动量：造数脚本改10行、SQL模板加1个注释、新写1条pytest用例。总耗时2小时，从此再没出过"凌晨回归挂了、白天又好了"的假故障。

结论与可复用清单

□ 核心SQL有EXPLAIN断言用例（key / type / rows三项），每次CI必跑
□ 造数/清数据的批量DML结束后，立即ANALYZE TABLE（<200ms，无副作用）
□ 造数优先INSERT正确值，避免UPDATE大批量行触发InnoDB自动统计刷新
□ performance_schema监控avg_rows_examined，阈值告警（5万→1000万 = P1）
□ 如果用了hint/SPM，配一条"hint失效检测"：EXPLAIN结果key != 期望值→告警

以上方案不依赖改表结构、不改业务SQL关键字（hint是注释，对ORM透明），落地成本2~4小时。如果你需要我展开某一步的完整代码（比如conftest的db_conn fixture、或Prometheus的MySQL exporter配置），可以追问。

0赞 0收藏 0评论 2浏览