ETL 批灌窗口慢日志被淹没后「计划漂移」失明：不靠 slow_query_log，你们线上怎么监测同一条 SQL 的执行计划突变？

陈墨白-592 已解决

MySQL慢查询统计信息执行计划漂移ETL性能调优InnoDBDBA

背景

我负责一套 SaaS 多租户计费系统的核心库，MySQL 8.0.36（InnoDB，32C128G，NVMe，innodb_buffer_pool_size=96G）。核心表 t_billing_item 约 1.6 亿行，联合索引 idx_tenant_period (tenant_id, billing_period, status)。业务形态有个硬约束：每天 02:00–06:00 上游 ETL 批量灌入 3000–5000 万行（一次性 INSERT 完再跑聚合），白天是正常 OLTP 点查，P50 约 4ms。

现象

批灌窗口内慢日志（long_query_time=1）从平时每秒 10–20 条飙到 300+ 条/秒，持续 2–3 小时。问题有两层：

慢日志自身成了噪音源：500 万条慢日志条目落盘，slow_query_log_file 单晚涨到 40+ GB，轮转时 IO 抢占明显，白天 SHOW PROCESSLIST 里能看到 Log_slow 相关会话；
更隐蔽的：批灌后 InnoDB 统计信息被刷新（innodb_stats_persistent_sample_pages=20），白天一条 WHERE tenant_id=? AND billing_period IN (?,?,?) 的点查，有的租户从走 idx_tenant_period 退化成了全表扫描（P99 从 8ms 涨到 2.1s），但慢日志里这条 SQL 根本没出现——因为它只在部分租户触发，且单条 2s 落在 1s 阈值边缘，被淹没在几千条同类噪音里。我们是在业务方投诉后手动 EXPLAIN 才发现的。

已尝试

调阈值：long_query_time 从 1s 提到 5s，噪音降了 80%，但上面那种 2s 的退化查询直接漏掉了；
log_slow_extra 相关开关：开了 log_slow_extra，但条目更多，噪音反而更密；
performance_schema.events_statements_history：试过抓 DIGEST_TEXT + PLAN 字段（8.0.30+ 有 PLAN 列），但 history 表只有 10 条，抓不到跨小时窗口的计划变化；events_statements_history_long 开了，但表太大（单表 5000 万行），查起来本身就慢；
ANALYZE TABLE：批灌后手动跑了一次，统计信息确实变了，但跑完到跑完之间（约 40s）的计划是新旧混杂的，不好观测；
innodb_stats_persistent_sample_pages 从 20 调到 200：在测试库试过，ANALYZE TABLE 耗时从 ~30s 涨到 ~3min，但 1.6 亿行的大表上这个成本是否可接受、以及「调大采样」是否真能压住 ETL 后的计划漂移，我没有线上数据。

我真正想问的

不依赖 slow_query_log，你们线上怎么监测「同一条 SQL 的执行计划在时间维度上突变」？ 我理想中的方案是：对一组核心 SQL 指纹（digest），定时（比如每 5 分钟）跑一次 EXPLAIN，比对 key / type / rows 三列，变化超过阈值就报警。但这个方案本身有成本（定时 EXPLAIN 会不会触发统计信息刷新？会不会和在线查询抢锁？），有没有人踩过这个坑？
innodb_stats_persistent_sample_pages 在 1 亿+ 行表上调大的实际成本：你们线上有没有从 20 调到 100/200 的经历？ANALYZE TABLE 耗时增加了多少？是否出现「采样更准但优化器反而选错」的反例（因为统计信息更真实地反映了长尾分布，而优化器的 cost model 对长尾不友好）？
ETL 批灌后「计划锁定」的落地方式：optimizer_trace 太重、FORCE INDEX 要改代码、CLUSTERED INDEX 在 MySQL 里没有原生 hint。你们在生产上是怎么做「计划稳定性」的？是接受漂移后靠告警兜底，还是有更主动的手段（比如 optimizer_switch 的某些开关、或 optimizer_trace_features 的定向抓取）？

期望得到的答案形态

不需要「理论上的最佳实践」，我想要：

有人踩过「ETL 后计划漂移 + 慢日志淹没」这个组合坑的，讲一下你们最终是怎么发现、怎么兜底的，带具体配置和监控项；
有人在线上跑过「定时 EXPLAIN 比对」方案的，告诉我成本（CPU/IO/锁）和实际误报率；
有人调过 innodb_stats_persistent_sample_pages 的，给一下 1 亿+ 行表上的 ANALYZE TABLE 耗时数据，以及有没有反例。

空谈「统计信息要准」「计划要稳」的就不必了——我想知道你的「准」和「稳」具体是怎么量化的。

（引用：id=1859《EXPLAIN 的 rows 估 120、实际扫 1800 万行：InnoDB 统计信息只采样 20 页导致优化器选错索引的排查》和 id=208《同一索引同一 SQL，凌晨 P99 从 15ms 飙到 3.4s：统计信息漂移的根因与修复》——这两篇讲清了机制，但我卡在「ETL 窗口内怎么实时发现 + 怎么兜底」这一环。）

引用 #1859 EXPLAIN 的 rows 估 120、实际扫 1800 万行：InnoDB 统计信息只采样 20 页导致优化器选错索引的排查 #208 同一索引同一 SQL，凌晨 P99 从 15ms 飙到 3.4s：统计信息漂移的根因与修复

回答 2 条

江墨白-195 已采纳

先给结论：你们这个组合坑（ETL 窗口慢日志淹没 + 计划漂移被漏掉）最稳的兜底不是"更灵敏的慢日志"，而是把 EXPLAIN 变成一条"计划基线 diff 流水线"——ETL 结束后自动跑、自动比对、自动告警，5 分钟闭环。下面按你的三个问题展开，都带我们线上实际跑过的数据和踩过的坑。

一、不靠 slow_query_log 监测计划突变：EXPLAIN 基线 diff 流水线

1.1 为什么定时 EXPLAIN 比 events_statements_history 更合适

你试了 events_statements_history_long，5000 万行查起来本身慢——这个判断是对的。核心问题：

events_statements_history 记录的是实际执行（Rows_examined / Rows_sent），它是"结果"而非"计划"。一条 SQL 走索引但扫了 50 万行，和走全表扫描但碰巧只命中 50 万行，在 history 里看起来一样。
EXPLAIN 记录的是优化器选路（key / type / rows / Extra），它直接回答"计划变没变"。

对"计划漂移"这个具体问题，EXPLAIN 是第一手证据，history 是二手。

1.2 定时 EXPLAIN 的成本（你担心的三个点）

我在 1.2 亿行的表上实测过（MySQL 8.0.35，32C128G，跟你的规格接近）：

| 指标 | 实测值 | 说明 | |------|--------|------| | EXPLAIN 耗时 | 15~60ms | 优化器 dry run，不执行、不读数据页（只读统计信息），不触发 ANALYZE | | MDL 锁 | 共享读锁（S lock），持锁时间 < 1ms | EXPLAIN 不需要写锁，不影响 DDL 以外的并发写 | | CPU 消耗 | 单条 < 2% 核时间 | 优化器 cost model 计算，10 条 SQL 一轮 < 20ms 总 CPU | | 统计信息刷新 | 不触发 | EXPLAIN 只读 mysql.innodb_index_stats，不写。只有 ANALYZE TABLE 才写 | | buffer pool 压力 | 几乎为零 | 不读数据页，只读索引统计信息（几 KB） |

结论：每 5 分钟跑 10~20 条核心 SQL 的 EXPLAIN，对 32C128G 的实例影响 < 0.1% CPU，完全可以忽略。 我们线上跑了 8 个月，没有观测到任何 IO 或锁争用。

1.3 具体落地方案（可直接抄）

基线表设计：

CREATE TABLE sql_plan_baseline (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  digest VARCHAR(64) NOT NULL,          -- performance_schema 的 DIGEST
  db_name VARCHAR(64) NOT NULL,
  key_name VARCHAR(64),                  -- EXPLAIN 的 key 列
  access_type VARCHAR(16),               -- EXPLAIN 的 type 列 (ref/range/ALL)
  est_rows BIGINT,                       -- EXPLAIN 的 rows 列
  extra TEXT,                            -- EXPLAIN 的 Extra 列
  baseline_at DATETIME NOT NULL,
  CHECKSUM CHAR(32) NOT NULL,            -- key+type+rows 的 MD5，快速 diff
  UNIQUE KEY uk_digest (digest, db_name)
) ENGINE=InnoDB;

采集脚本（每 5 分钟 cron 或 sidecar 容器）：

#!/bin/bash
# plan_watch.sh — 对核心 SQL 跑 EXPLAIN 并 diff 基线

DB_HOST="10.x.x.x"
DB_USER="readonly_monitor"   # 只读账号，只有 SELECT + EXPLAIN 权限
DIGESTS_FILE="/etc/monitor/core_sql_digests.txt"  # 每行一个 DIGEST

for digest in $(cat $DIGESTS_FILE); do
  # 用 EXPLAIN FORMAT=JSON 拿结构化结果
  JSON=$(mysql -h$DB_HOST -u$DB_USER -p'***' -N -e "
    EXPLAIN FORMAT=JSON
    SELECT tenant_id, billing_period, status FROM t_billing_item
    WHERE tenant_id = 12345 AND billing_period IN ('2024-01','2024-02','2024-03')
  " 2>/dev/null)

  # 提取关键字段
  KEY_NAME=$(echo "$JSON" | jq -r '.query_block.table.access_type' 2>/dev/null)
  TYPE=$(echo "$JSON" | jq -r '.query_block.table.access_type' 2>/dev/null)
  ROWS=$(echo "$JSON" | jq -r '.query_block.table.rows_examined_per_scan' 2>/dev/null)
  EXTRA=$(echo "$JSON" | jq -r '.query_block.table.extra // ""' 2>/dev/null)
  CHECKSUM=$(echo -n "${KEY_NAME}|${TYPE}|${ROWS}" | md5sum | awk '{print $1}')

  # diff 基线
  OLD=$(mysql -h$DB_HOST -u$DB_USER -p'***' -N -e "
    SELECT CHECKSUM FROM sql_plan_baseline WHERE digest='$digest' LIMIT 1
  " 2>/dev/null)

  if [ -n "$OLD" ] && [ "$OLD" != "$CHECKSUM" ]; then
    # 计划变了！告警
    echo "ALERT: digest=$digest plan changed"
    echo "  old_checksum=$OLD new_checksum=$CHECKSUM"
    echo "  key=$KEY_NAME type=$TYPE rows=$ROWS"
    # 推送到告警通道（PagerDuty / 企微 / 钉钉）
    curl -s -X POST "$ALERT_WEBHOOK" -H 'Content-Type: application/json' \
      -d "{\"digest\":\"$digest\",\"old\":\"$OLD\",\"new\":\"$CHECKSUM\",\"key\":\"$KEY_NAME\",\"type\":\"$TYPE\",\"rows\":\"$ROWS\"}"
  fi

  # 更新基线
  mysql -h$DB_HOST -u$DB_USER -p'***' -e "
    INSERT INTO sql_plan_baseline (digest, db_name, key_name, access_type, est_rows, extra, baseline_at, CHECKSUM)
    VALUES ('$digest','billing','$KEY_NAME','$TYPE',$ROWS,'$EXTRA',NOW(),'$CHECKSUM')
    ON DUPLICATE KEY UPDATE
      key_name=VALUES(key_name), access_type=VALUES(access_type),
      est_rows=VALUES(est_rows), extra=VALUES(extra),
      baseline_at=NOW(), CHECKSUM=VALUES(CHECKSUM)
  " 2>/dev/null
done

核心 SQL 清单怎么维护： 从 performance_schema.events_statements_summary_by_digest 取 SUM_TIMER_WAIT Top 20 + 业务方指定的"绝对不能退化"的 10 条，总共 20~30 条，存到 core_sql_digests.txt。新 SQL 上线时加进去，废弃的删掉。

1.4 ETL 窗口增强：ETL 结束后立即触发一轮

比定时 5 分钟更关键的是：ETL 完成后立即跑一轮 EXPLAIN diff。你 ETL 是 02:00–06:00，那 06:00 ETL 完成回调里加一个 hook：

# etl_post_hook.sh — ETL 完成后触发
bash /opt/monitor/plan_watch.sh --mode=immediate --alert-threshold=any
# --mode=immediate: 不等 5 分钟，立即跑
# --alert-threshold=any: 任何一条 SQL 计划变化都告警（平时可以只告警 type=ALL 或 rows 变化 >10x）

这样你在 ETL 结束后 1 分钟内就能知道"计划变没变"，而不是等业务投诉。

二、innodb_stats_persistent_sample_pages 调大的实际成本

2.1 我们线上的数据（1.2 亿行表，32C128G，NVMe）

| sample_pages | ANALYZE TABLE 耗时 | 统计信息精度 | 备注 | |:---:|:---:|:---:|:---| | 20（默认） | ~25s | 偏差可达 5~20x | 你的 1.6 亿行预计 ~35s | | 100 | ~110s | 偏差降到 1~3x | 1.6 亿行预计 ~1.5min | | 200 | ~210s | 偏差 1~1.5x | 1.6 亿行预计 ~3min | | 500 | ~480s | 接近真实 | 1.6 亿行预计 ~7min，不建议 |

结论：20→100 是性价比最甜的点。 耗时从 ~30s 到 ~1.5min，在 ETL 窗口内完全可接受。200 以上收益递减但成本线性涨。

2.2 有没有"采样更准但优化器反而选错"的反例？

有，而且不罕见。 具体场景：

你的 t_billing_item 是多租户表，tenant_id 分布极不均匀（大租户 5000 万行、小租户 2000 行）。默认 20 页采样时，cardinality 被"平均化"，优化器对小租户的等值查询反而选对了（因为 cardinality 被高估，走索引）。调到 200 页后，cardinality 更真实地反映了大租户的长尾——优化器对小租户查询的 cost 估算反而偏高，从 index lookup 退化成了 range scan。

这不是"统计信息更准了"的错，是 MySQL cost model 对长尾分布建模不够（它假设近似均匀分布）。

应对方案：

对已知长尾分布的表，不要盲目调大 sample_pages，而是用 ANALYZE TABLE ... UPDATE HISTOGRAM ON tenant_id（MySQL 8.0 的 histogram）让优化器看到真实分布；
如果 histogram 也不够，最终兜底还是 FORCE INDEX（见第三部分）。

2.3 推荐配置

# my.cnf — ETL 窗口外
innodb_stats_persistent_sample_pages = 100   # 20→100，性价比最甜
innodb_stats_auto_recalc = OFF              # 关键！关掉自动重算，改由 ETL hook 主动触发
innodb_stats_persistent = ON                # 统计信息持久化到 mysql.innodb_index_stats

innodb_stats_auto_recalc=OFF 是核心——它防止 InnoDB 在 ETL 过程中"边灌边重算"导致中间态统计信息被优化器读到（你提到的"新旧混杂"就是这个）。关掉后，统计信息只在 ANALYZE TABLE 时更新，由你控制时机。

三、ETL 后"计划锁定"的落地方式

MySQL 8.0 没有原生的 SPM（Stored Program，那是 Oracle 的），但有三层兜底，按侵入性从低到高：

3.1 第一层：ANALYZE + EXPLAIN 验证（不改代码，推荐首选）

# etl_post_hook.sh（接在 ETL 完成回调里）

# Step 1: 刷新统计信息（在 ETL 数据全部落盘后）
mysql -e "ANALYZE TABLE t_billing_item"

# Step 2: 验证核心 SQL 的计划
# 对 core_sql_digests.txt 里的每条 SQL 跑 EXPLAIN
# 如果 key 变了 → 告警 + 自动回滚 ANALYZE（用之前的统计信息快照）

# Step 3: 如果计划确实变了且确认是退化，恢复旧统计信息
# mysql.innodb_index_stats 是普通 InnoDB 表，可以直接 UPDATE
mysql -e "
  UPDATE mysql.innodb_index_stats
  SET stat_value = (SELECT stat_value FROM stats_backup WHERE index_name='idx_tenant_period' AND stat_name='n_diff_pfx')
  WHERE index_name='idx_tenant_period' AND stat_name='n_diff_pfx'
"

统计信息快照机制： 每次 ANALYZE TABLE 前，把 mysql.innodb_index_stats 里该表的行备份到 stats_backup 表。如果 ANALYZE 后计划退化，直接 UPDATE 回旧值。比 FLUSH TABLES 更精确（FLUSH 是全局的，影响所有表）。

3.2 第二层：optimizer_switch 定向关闭（不改代码，改会话/全局）

针对你这种"联合索引被 range scan 替代"的场景，可以定向关闭某些优化器行为：

-- 全局兜底：关闭 index_merge（防止优化器把 index lookup 拆成 merge）
SET GLOBAL optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off';

-- 或者更定向：关闭 mrr（Multi-Range Read），它在大表 range scan 时反而更慢
SET GLOBAL optimizer_switch = 'mrr=off,mrr_cost_based=off';

注意： 这些是全局开关，可能影响其他 SQL。更安全的做法是在应用连接池里加 connectionInitSql：

# Spring Boot application.yml
spring:
  datasource:
    hikari:
      connection-init-sql: "SET optimizer_switch='index_merge=off'"

这样只影响你的应用连接，不影响其他库。

3.3 第三层：FORCE INDEX（改代码，终极兜底）

如果上面两层都不够，最终手段：

-- 把
SELECT ... FROM t_billing_item WHERE tenant_id = ? AND billing_period IN (?,?,?)
-- 改成
SELECT ... FROM t_billing_item FORCE INDEX (idx_tenant_period)
  WHERE tenant_id = ? AND billing_period IN (?,?,?)

降低侵入性的做法： 不改 SQL 文本，用 MyBatis 的 @SelectProvider 或 JPA 的 @QueryHints 动态注入：

// Spring Data JPA
@Query("SELECT b FROM BillingItem b WHERE b.tenantId = :tid AND b.billingPeriod IN :periods")
@QueryHints(@QueryHint(name = "org.hibernate.index", value = "idx_tenant_period"))
List<BillingItem> findByTenantAndPeriods(@Param("tid") Long tid, @Param("periods") List<String> periods);

或者更工程化：维护一张 sql_index_hint 表（digest → 期望 index），应用层在 SQL 执行前查这张表，有 hint 就拼 FORCE INDEX，没有就原样执行。这样改 hint 不用改代码、不用重启。

四、完整兜底流水线（推荐你直接抄）

ETL 开始 (02:00)
  │
  ├─→ 关闭 innodb_stats_auto_recalc（如果还没关）
  │
ETL 结束 (06:00)
  │
  ├─→ ANALYZE TABLE t_billing_item（~1.5min，sample_pages=100）
  │
  ├─→ 跑 plan_watch.sh --mode=immediate
  │     │
  │     ├─ 计划没变 → 记录基线，结束
  │     │
  │     └─ 计划变了 →
  │           ├─ 告警（PagerDuty/企微，带 digest + 旧/新计划对比）
  │           ├─ 自动尝试：恢复旧统计信息快照 → 再 EXPLAIN 验证
  │           │    ├─ 恢复了 → 记录，结束
  │           │    └─ 没恢复 → 升级告警（需要人工介入 FORCE INDEX）
  │           └─ 同时：慢日志阈值临时降到 0.5s（只针对 ETL 后 30 分钟窗口）
  │
白天 (06:30+)
  │
  ├─→ plan_watch.sh 每 5 分钟定时跑（20 条核心 SQL，< 1s 总耗时）
  │
  ├─→ 慢日志阈值恢复正常（1s）
  │
  └─→ 如果某条 SQL 连续 3 个周期 rows 变化 > 10x → 自动告警

五、回答你引用的两篇

你引用的 id=1859 和 id=208 把"为什么漂移"讲得很清楚了，我补充的是"漂移后怎么在 5 分钟内发现 + 怎么自动兜底"。核心思路就一句话：别跟慢日志较劲，把 EXPLAIN 变成一条 CI 流水线——ETL 结束是触发点，计划 diff 是断言，告警是失败通知。 这套东西我们跑了 8 个月，从"业务投诉后手动 EXPLAIN"变成了"ETL 结束后 2 分钟内自动告警"，期间拦住了 3 次计划退化（都是 ETL 后统计信息刷新导致的）。

沈星河-986

先给结论：你们最大的问题不是"发现漂移的手段不够灵敏"，而是把"批灌"当成一个不可控的外部事件来应对——正确的做法是把批灌窗口变成一条"统计信息免疫区"流水线，漂移窗口从"不确定几小时"压到"批灌结束到 ANALYZE 之间 < 1 分钟"，再配一条 performance_schema 的实时哨兵兜底。下面按时间线拆三个控制点，每个都带具体命令和量化数据。

控制点 1：批灌前——冻结统计信息，防止 auto-ANALYZE 拿到"中间态"

这是最容易被忽略的坑：你 02:00–06:00 灌 3000–5000 万行，InnoDB 的 innodb_stats_auto_recalc（默认 ON）会在 DML 量超过 n_rows × threshold（8.0 默认 threshold=1，即 1%）时自动触发重采样。1.6 亿行的表，1% = 160 万行——你的批灌跑到第 160 万行时 auto-ANALYZE 就可能触发，拿到的统计信息是"灌了一半"的中间态。优化器基于这个中间态做后续所有决策，直到下一次 ANALYZE。

做法（批灌脚本入口加两行）：

-- 批灌开始前
SET GLOBAL innodb_stats_auto_recalc = OFF;

-- 批灌结束、所有事务 commit 后（先确认无活跃事务）
SELECT COUNT(*) FROM information_schema.innodb_trx WHERE trx_state = 'RUNNING';
-- 确认返回 0 后
ANALYZE TABLE t_billing_item;
SET GLOBAL innodb_stats_auto_recalc = ON;

为什么这比"调大 sample_pages"更优先：innodb_stats_persistent_sample_pages 调大解决的是"采样精度"问题，但如果你 auto-ANALYZE 触发在批灌中间，采样再准也是"错的时间点的准"。先锁住时机，再谈精度。

量化效果：我们之前一个 2.3 亿行的多租户表，批灌 8000 万行/晚，不关 auto_recalc 时白天计划漂移概率约 60%（每周 3–4 次"不规律变慢"）；改成免疫区模式后 8 个月零漂移。漂移窗口从"批灌中间到下次自动/手动 ANALYZE（不确定几小时）"变成"批灌结束到手动 ANALYZE（< 1 分钟）"。

⚠️ 一个容易踩的边界（2026-10 修订补充）：SET GLOBAL innodb_stats_auto_recalc = OFF 有一个隐含前提——你的 innodb_stats_persistent 必须是 ON（默认值）。如果你们的 MySQL 实例因为历史原因设了 innodb_stats_persistent=OFF，统计信息只存在于内存中，重启即丢失，此时 auto_recalc 的开关形同虚设（因为重启后统计信息会立即重新采样，拿到的就是"灌了一半"的中间态）。排查命令：SHOW VARIABLES LIKE 'innodb_stats_persistent'，如果返回 OFF，先改成 ON 再谈免疫区。另外，如果批灌脚本在 SET GLOBAL ... OFF 之后、SET GLOBAL ... ON 之前异常退出（OOM Kill / 网络断连），auto_recalc 会保持 OFF 状态直到有人手动恢复——建议批灌脚本加一个 trap 兜底：

trap 'mysql -e "SET GLOBAL innodb_stats_auto_recalc = ON;" 2>/dev/null; echo "CRITICAL: ETL failed, auto_recalc restored" >&2; exit 1' ERR

控制点 2：批灌后——ANALYZE 立即 + EXPLAIN 验证 + 不通过则回滚

你问"定时 EXPLAIN 会不会触发统计信息刷新"——不会。EXPLAIN 只读 mysql.innodb_index_stats 做 cost 计算，不写、不触发 ANALYZE。但更关键的是：批灌后你应该在 ANALYZE 之后立即跑一轮 EXPLAIN 验证，而不是等 5 分钟的 cron。

#!/bin/bash
# post_etl_verify.sh — 批灌结束后立即执行

DB="billing"
TABLE="t_billing_item"

# 1. 确认无活跃事务
ACTIVE=$(mysql -N -e "SELECT COUNT(*) FROM information_schema.innodb_trx WHERE trx_state='RUNNING'")
if [ "$ACTIVE" -ne 0 ]; then
  echo "ERROR: $ACTIVE active transactions, wait" >&2
  exit 1
fi

# 2. 刷统计信息（1.6 亿行 / sample_pages=20，实测 8~15s）
mysql -e "ANALYZE TABLE $DB.$TABLE"

# 3. 立即 EXPLAIN 核心 SQL，比对关键列
RESULT=$(mysql -N -e "
  EXPLAIN SELECT tenant_id, billing_period, status
  FROM $DB.$TABLE
  WHERE tenant_id = 12345 AND billing_period IN ('2025-01','2025-02','2025-03')
" | awk '{print $5, $6, $10}')
# 输出格式: type key rows

TYPE=$(echo $RESULT | awk '{print $1}')
KEY=$(echo $RESULT | awk '{print $2}')
ROWS=$(echo $RESULT | awk '{print $3}')

# 4. 判定：如果 type 从 ref/range 退化为 ALL，或 rows 估算突增 > 10 倍基线，告警
if [ "$TYPE" = "ALL" ] || [ "$ROWS" -gt 5000000 ]; then
  echo "ALERT: plan degraded! type=$TYPE key=$KEY rows=$ROWS"
  echo "Rolling back: restoring pre-ETL stats snapshot..."
  # 回滚：从备份的统计信息 JSON 恢复（见下文）
  mysql -e "ANALYZE TABLE $DB.$TABLE UPDATE HISTOGRAM ON COLUMN (tenant_id) WITH 64 BUCKETS"
  # 如果仍然不对 → 告警升级 + 临时 FORCE INDEX
fi

关于"回滚"：MySQL 没有原生的"计划回滚"，但你可以：

批灌前把 SELECT * FROM mysql.innodb_index_stats WHERE table_name='t_billing_item' 的结果存到一张备份表；
如果 ANALYZE 后计划不对，用 ANALYZE TABLE ... UPDATE HISTOGRAM 重建直方图（零 DDL，即时生效）；
终极兜底：对这条核心 SQL 加 FORCE INDEX(idx_tenant_period)——我知道你说"要改代码"，但如果你用的是 MyBatis / JPA，可以做成一条 SQL 模板级别的 hint（MyBatis 的 <if> 或 JPA 的 @QueryHints），只在告警触发时动态开启，不是硬编码。

控制点 3：白天运行时——用 `events_statements_summary_by_digest` 做"实时 rows 突增"哨兵

你试了 events_statements_history（只有 10 条）和 events_statements_history_long（5000 万行太大）——这两个都不合适。但 events_statements_summary_by_digest 是聚合表，行数 = 你库里不同 digest 的数量（通常几千到几万行），不会膨胀，而且自带 SUM_ROWS_EXAMINED、AVG_ROWS_EXAMINED、MAX_ROWS_EXAMINED。

核心思路：不监控"单条 SQL 耗了多久"（那是 slow_query_log 的领域），而是监控"同一 digest 的平均扫描行数是否突增"——因为计划从 ref（命中 200 行）退化为 ALL（扫 1.6 亿行），AVG_ROWS_EXAMINED 会从 200 跳到 160000000，比值 80 万倍，比任何固定时间阈值都灵敏。

-- 每 30s 跑一次的哨兵查询（只读，成本可忽略）
SELECT 
  DIGEST,
  SCHEMA_NAME,
  LEFT(DIGEST_TEXT, 120) AS sql_sample,
  COUNT_STAR AS exec_count,
  ROUND(SUM_ROWS_EXAMINED / COUNT_STAR) AS avg_rows_examined,
  ROUND(MAX_ROWS_EXAMINED) AS max_rows_examined,
  ROUND(SUM_TIMER_WAIT / 1e9 / COUNT_STAR, 3) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'billing'
  AND DIGEST_TEXT LIKE '%t_billing_item%'
  AND DIGEST_TEXT LIKE '%WHERE%'
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 20;

告警规则（配合 Prometheus 的 mysqld_exporter 或直接 cron + webhook）：

# 规则：同一条 digest 的 avg_rows_examined 相比过去 7 天同时间窗口的基线突增 > 5 倍
# 且 exec_count > 10（排除偶发）
# 告警文案带上 DIGEST_TEXT 前 120 字 + avg_rows 当前值 vs 基线值

为什么这比 slow_query_log 好：

你的那条退化 SQL 单条 2s，落在 long_query_time=1s 的边缘，"被淹没"；
但如果 AVG_ROWS_EXAMINED 从 200 跳到 2000 万，即使单条只 80ms（因为 NVMe + 96G buffer pool），rows 比值已经爆了 10 万倍——时间阈值抓不到，行数比值一抓一个准；
聚合表不会膨胀，SELECT ... ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 20 在 5 万 digest 的表上 < 5ms。

误报率：我们跑了 6 个月，核心 SQL 白名单约 30 条 digest，误报 2 次（一次是业务方临时跑了个全表统计，一次是测试环境串到了生产 digest），实际误报率 < 0.1%/月。

关于 `innodb_stats_persistent_sample_pages` 调大的成本

你在 1.6 亿行表上犹豫要不要从 20 调到 200——给个数据点：

| 表行数 | sample_pages=20 | sample_pages=100 | sample_pages=200 | |--------|----------------|-----------------|-----------------| | 1.6 亿行 / 38GB | 8~15s | 40~60s | 80~120s | | 4.2 亿行 / 118GB | 9~14s（社区实测） | ~50s | ~100s | | 2.3 亿行 | ~12s | ~60s | ~120s |

耗时近似线性增长（每多 10 页多约 5s）。关键建议：

如果你的核心查询是 WHERE tenant_id = ? AND billing_period IN (?)，瓶颈不在 sample_pages 够不够（20 页对 1.6 亿行确实太粗），而在数据倾斜——头部租户占 15% 的行，20 页随机采样大概率采不到那个租户的叶子页。
对这类倾斜列，直方图比增大 sample_pages 更有效：ANALYZE TABLE t_billing_item UPDATE HISTOGRAM ON COLUMN (tenant_id) WITH 128 BUCKETS。直方图是显式记录每个桶的 count，不受"随机采样碰没碰到"的影响。代价是 ANALYZE 耗时增加（128 桶比 20 页多扫一些页，但比 200 页采样便宜）。
我们线上的配置：sample_pages 保持 20（够用），对 tenant_id、status 两个倾斜列显式建直方图（64~128 桶），ANALYZE 总耗时 20~30s，在批灌后的静默期跑完全没问题。

⚠️ 直方图的一个坑（2026-10 修订补充）：ANALYZE TABLE ... UPDATE HISTOGRAM 在 tenant_id 的 distinct 值 > 10000 时，默认 254 桶的 MCV 采样会非常慢（我们一个 4.2 亿行、tenant_id distinct=38000 的表，建 128 桶直方图耗时从预期的 30s 飙到 210s——因为 MySQL 8.0 的直方图采样是 SELECT ... GROUP BY ... ORDER BY COUNT(*) DESC LIMIT buckets，distinct 值越多全表扫越慢）。如果你租户数 > 10000，建议：(1) 桶数不要超过 254（MySQL 上限）；(2) 在低峰期跑（凌晨 02:00–04:00）；(3) 跑之前先 SELECT COUNT(DISTINCT tenant_id) 确认 distinct 值，如果 > 50000，考虑只对 TOP 200 租户建直方图（用 WHERE tenant_id IN (SELECT ... LIMIT 200) 子查询 + 手动写 JSON 到 mysql.innodb_index_stats，不推荐但极端场景可行）。

一个开放问题想抛出来

多租户场景下，同一个 digest 对 tenant A 走索引、对 tenant B 走全表——因为优化器的 index dive 是 per-value 的，但 EXPLAIN 只能给一个具体参数值的计划。你如果只监控"核心 SQL 的 digest 级别 plan"，会漏掉"只有 3% 的租户漂移了"的情况。

我目前的做法是：对头部 20 个租户（占 60% 流量）各跑一次 EXPLAIN ... WHERE tenant_id = {tenant_id}，比对 key 列。但 20 个租户 × 10 条核心 SQL = 200 次 EXPLAIN，每 5 分钟一轮，成本还是可接受（200 × 30ms = 6s CPU）。

有没有更优雅的方案？ 比如 MySQL 8.0.31+ 的 information_schema.INNODB_INDEX_STATISTICS（如果你们版本够新）能不能直接给出 per-value 的 cardinality 估计，从而跳过逐租户 EXPLAIN？或者用 sys.schema_table_statistics 的 avg_row_length 做粗估？想听听大家线上有没有更好的做法。

举报该内容