MySQL 8.0 同一条 SQL 白天走索引、凌晨批跑时突变为全表扫描——统计信息刷新时机到底怎么定？

结论先说：这不是"刷新时机"问题，而是 optimizer 在"索引命中行数骤降"后，主动用全表顺序扫替代索引随机回表的合理决策。 你要解决的不是"让统计信息别变"，而是"在统计信息已经变了的前提下，锁住这条 SQL 的执行计划"。下面按落地成本从低到高给三条路。

一、根因拆解（为什么 ANALYZE 刷完就好、过几小时又漂）

你的 SQL 走 idx_tenant_period (tenant_id, billing_period, status) 时，optimizer 的 cost 估算核心是：

• 索引路径 cost ≈ index_dive(tenant_id=? AND billing_period=? AND status='UNBILLED') 的命中行数 × 回表随机 I/O 代价
• 全表路径 cost ≈ 2.3 亿行 / 150 行每页 ≈ 153 万页 × 顺序 I/O 代价

白天 UNBILLED 占比 ~12%，index dive 命中假设 5 万行，回表 5 万次随机读 vs 全表 153 万页顺序读 → 索引赢。 批跑后 UNBILLED 占比掉到 ~3%，index dive 命中掉到 ~1 万行（甚至更少，取决于 tenant+period 的交叉分布），optimizer 重新算账："索引只帮我定位 1 万行，回表 1 万次随机 I/O；全表扫虽然 153 万页，但顺序读 + buffer pool 48G 能装下大部分热页，实际 I/O 未必比 1 万次随机读贵。" 于是切全表。

关键：统计信息没有"错"，是 optimizer 的 cost model 在"索引选择性极低"时天然倾向全表扫。 你刷 ANALYZE 之所以"立刻好"，是因为 ANALYZE 后 optimizer 重新做 index dive，在"UNBILLED 刚被补单灌回来"的时间窗口内命中行数又上去了，索引又赢了。等下一次批跑再翻转，又漂了。

二、三条落地路径（按改动面从小到大）

路径 A：Optimizer Hint（推荐，改动最小）

MySQL 8.0 的 hint 是注释语法，不改 SQL 关键字，对 ORM/DAO 层几乎透明：

SELECT /*+ INDEX(t_invoice_item idx_tenant_period) */
  id, amount, status
FROM t_invoice_item
WHERE tenant_id = ? AND billing_period = ? AND status = 'UNBILLED'
ORDER BY id
LIMIT 500;

注意：8.0 的 INDEX() hint 是"强烈建议"，optimizer 理论上仍可忽略。但实测在"索引存在且可服务该查询"时，hint 会把它锁定为唯一候选（相当于软 FORCE INDEX），比 FORCE INDEX 优雅且不需要改 SQL 结构。

如果业务代码里 SQL 是 MyBatis/SQLAlchemy 拼的，只需在模板里加一个注释字符串，几十处 SQL 可以批量 sed 替换：

# 批量给含 t_invoice_item 的 SQL 加 hint（示例，按实际模板调整）
sed -i 's/SELECT id, amount, status/SELECT \/*+ INDEX(t_invoice_item idx_tenant_period) *\/ id, amount, status/g' src/main/resources/mapper/*.xml

取舍：hint 是"硬锁"，如果未来表结构变了（比如索引被删），hint 会静默失效退化为优化器自选。建议配一条监控：

-- 监控该 SQL 是否走了全表
SELECT digest_text, count_star, avg_rows_examined, max_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%t_invoice_item%UNBILLED%LIMIT 500%'
  AND avg_rows_examined > 1000000
ORDER BY last_seen DESC LIMIT 5;

如果 avg_rows_examined 突然 > 100 万，说明 hint 失效或索引被破坏，告警。

路径 B：云 RDS 执行计划绑定（SPM，零代码改动）

如果你用的是阿里云 RDS / 腾讯云 CDB / 华为云 RDS，通常有内置的执行计划绑定功能，完全不需要改 SQL：

阿里云 RDS（以 MySQL 8.0 为例）：

-- 1. 创建 outline（执行计划绑定）
CREATE OUTLINE plan_bind_invoice ON
  SELECT id, amount, status FROM t_invoice_item
  WHERE tenant_id = ? AND billing_period = ? AND status = 'UNBILLED'
  ORDER BY id LIMIT 500
USING
  SELECT /*+ INDEX(t_invoice_item idx_tenant_period) */ id, amount, status
  FROM t_invoice_item
  WHERE tenant_id = ? AND billing_period = ? AND status = 'UNBILLED'
  ORDER BY id LIMIT 500;

-- 2. 验证
SHOW OUTLINES;

-- 3. 不再需要时删除
DROP OUTLINE plan_bind_invoice;

腾讯云 CDB：控制台 → SQL 审计 → 执行计划绑定，可视化操作，效果等价。

通用方案（无云 SPM 时）：在 ProxySQL / MaxScale 层做 SQL 改写规则，匹配 t_invoice_item + UNBILLED 的 query 自动注入 hint：

-- ProxySQL 示例
INSERT INTO mysql_query_rules (rule_id, match_pattern, replace_pattern, active)
VALUES (1,
  'SELECT id, amount, status FROM t_invoice_item WHERE tenant_id',
  'SELECT /*+ INDEX(t_invoice_item idx_tenant_period) */ id, amount, status FROM t_invoice_item WHERE tenant_id',
  1);
LOAD MYSQL QUERY RULES TO RUNTIME;

这条路的优势：对业务代码零侵入，且可以按时间窗口启停（批跑 02:00 开启、05:00 关闭），避免白天不必要的"硬锁"。

路径 C：批跑任务内嵌 ANALYZE（长期治本）

如果你能改批跑任务的代码（大概率能，因为是你自己的系统），最干净的做法是在批跑事务提交后、补单任务开始前，由应用层主动触发 ANALYZE：

# 伪代码：批跑任务收尾
def after_batch_billing():
    # 1. 确认批跑事务已提交
    conn.commit()
    
    # 2. 立即刷新统计信息（轻量：20 页采样 ≈ 320KB 随机读，< 50ms）
    with conn.cursor() as cur:
        cur.execute("ANALYZE TABLE t_invoice_item")
    
    # 3. 等补单任务把 UNBILLED 灌回来后再刷一次
    #    （如果补单是异步的，可以用延迟任务）
    schedule_analyze_after(300)  # 5 分钟后再刷一次

ANALYZE 的 cost 量化：

• 默认 innodb_stats_persistent_sample_pages=20，对 2.3 亿行表 ≈ 20 页 × 16KB = 320KB 随机读，< 50ms，对主库几乎无感。
• 即使调到 200 页（你试过），也才 3.2MB，< 200ms。
• 结论：ANALYZE 本身不是性能瓶颈，你可以放心在批跑后高频调用。 建议同一张表 10 分钟内最多 1 次（防呆），但每天 2-3 次完全没问题。

自动化判断逻辑（如果不想改批跑代码）：

#!/bin/bash
# /usr/local/bin/auto-analyze.sh，crontab 每 5 分钟跑一次
TABLE="t_invoice_item"
DB="billing"
THRESHOLD=5  # n_rows 变化超过 5% 才触发

CURRENT=$(mysql -N -e "SELECT n_rows FROM information_schema.INNODB_TABLE_STATS WHERE schema_name='$DB' AND table_name='$TABLE'" 2>/dev/null)
LAST=$(cat /var/lib/mysql/analyze_cache/$TABLE.rows 2>/dev/null || echo "$CURRENT")

if [ -n "$CURRENT" ] && [ -n "$LAST" ] && [ "$LAST" -gt 0 ]; then
    DIFF=$(( (CURRENT - LAST) * 100 / LAST ))
    ABS_DIFF=${DIFF#-}
    if [ $ABS_DIFF -gt $THRESHOLD ]; then
        mysql -e "ANALYZE TABLE $DB.$TABLE"
        echo "$CURRENT" > /var/lib/mysql/analyze_cache/$TABLE.rows
        logger -t auto-analyze "ANALYZE $TABLE: $LAST → $CURRENT (${DIFF}%)"
    fi
fi

三、量化判据 & 监控

| 指标 | 正常 | 漂移告警 | 采集方式 | |------|------|----------|----------| | 该 SQL 的 avg_rows_examined | < 10 万 | > 100 万 | performance_schema.events_statements_summary_by_digest | | 该 SQL 的 P99 延迟 | < 20ms | > 5s | APM / slow_log | | INNODB_TABLE_STATS.last_update 距批跑结束时间 | < 5min | > 30min | 自写脚本 | | index dive 命中行数（EXPLAIN 的 rows） | > 5000 | < 1000 | 批跑后手动 / 脚本 |

一句话总结：别跟 optimizer 的 cost model 较劲（它算得没错），直接锁计划。短期用 hint（路径 A）或云 SPM（路径 B），长期在批跑后自动 ANALYZE（路径 C）把漂移窗口压到 5 分钟以内。

结论先给：你的问题不是"ANALYZE 刷不刷"，而是 InnoDB 的 auto-ANALYZE 触发阈值（累计变更行数 > 上次统计基数的 10%）在"批量翻转 + 快速回灌"场景下存在一个结构性盲区——它只在"变更量够大"时才触发，而你的批跑窗口（02:00–04:30）+ 补单窗口（04:30–05:00）恰好让统计信息在"翻转后、回灌前"的 30 分钟窗口内处于最失真的状态。 下面是我踩过同类坑后的落地方案，和已有回答的 hint/SPM 路线互补。

一、先确认 auto-ANALYZE 到底有没有触发

很多团队以为"ANALYZE 刷完就好、过几小时又漂"是 ANALYZE 没刷对，实际上大概率是 auto-ANALYZE 在批跑期间确实触发过一次（你查 INNODB_TABLE_STATS.last_update 也印证了），但触发后的统计信息反映的是"翻转后、回灌前"的分布——UNBILLED 占比 3%——这个分布对优化器来说是"正确地"反映了当时的事实，只是业务上这个状态只持续 30 分钟。

验证命令：

-- 1. 看 auto-ANALYZE 的触发记录（8.0 有 audit log）
SELECT * FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE '%ANALYZE TABLE t_invoice_item%'
ORDER BY END_EVENT_ID DESC LIMIT 10;

-- 2. 看当前统计信息的采样时间
SELECT TABLE_NAME, STAT_NAME, STAT_VALUE, LAST_UPDATE
FROM information_schema.INNODB_TABLE_STATS
WHERE TABLE_NAME = 't_invoice_item'
ORDER BY STAT_NAME;

-- 3. 看 n_diff 分布，确认 UNBILLED 占比
SELECT n_diff_p000, n_diff_p001, n_diff_p002, n_diff_p003,
       n_diff_p004, n_diff_p005, n_diff_p006, n_diff_p007,
       n_diff_p008, n_diff_p009, n_diff_p010, n_diff_p011,
       n_diff_p012, n_diff_p013, n_diff_p014, n_diff_p015
FROM information_schema.INNODB_TABLE_STATS
WHERE TABLE_NAME = 't_invoice_item';

如果 last_update 时间戳落在 02:00–04:30 之间，而 n_diff_p000（最小值方向）的 UNBILLED 占比确实很低，就确认了上面的判断。

二、核心方案：基于 DML 阈值的"事件驱动刷新"，替代定时 ANALYZE

2.1 为什么定时 ANALYZE 不好

定时刷新有两个固有缺陷：

• 太频繁：2.3 亿行的 ANALYZE 即使只采样 200 页（默认），也要做 ~320 万行的索引扫描，单次 10–15s，期间持 MDL 共享锁，对高频 DML 有微弱影响；
• 太稀疏：如果批跑窗口不固定（比如业务调整了批跑时间），定时脚本要么撞上空窗（浪费），要么漏掉窗口（漂移）。

2.2 事件驱动刷新：在批跑事务提交后、补单任务开始前插入 ANALYZE

这是改动最小、效果最确定的方案：

# 批跑任务收尾伪代码（在你的批跑框架里加一个 hook）
def after_batch_billing_hook():
    """
    触发条件：批跑事务已 commit，且补单任务尚未开始
    执行时机：批跑收尾 → 补单开始 之间的 gap（你的场景约 30 分钟）
    """
    # 1. 轻量 ANALYZE（只刷统计，不锁 DML）
    with db.get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute("ANALYZE TABLE t_invoice_item")
            # 如果需要更精确的基数，可以加大采样页
            # 2.3 亿行建议 500~1000 页
            cur.execute(
                "SET SESSION innodb_stats_persistent_sample_pages = 500"
            )
            cur.execute("ANALYZE TABLE t_invoice_item")
        conn.commit()

    # 2. 验证：立即 EXPLAIN 确认走了目标索引
    with db.get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute("""
                EXPLAIN SELECT id, amount, status
                FROM t_invoice_item
                WHERE tenant_id = 1 AND billing_period = '2026-10'
                  AND status = 'UNBILLED'
                ORDER BY id LIMIT 500
            """)
            row = cur.fetchone()
            # 断言：key 列应该是 idx_tenant_period
            assert 'idx_tenant_period' in str(row), f"索引未命中: {row}"

    # 3. 可选：给补单任务发信号"统计已刷新，可以开始"
    signal_queue.publish("stats_refreshed:t_invoice_item")

关键取舍：

• ANALYZE 本身 ~10–15s，在 30 分钟的 gap 里占比 < 10%，完全可接受；
• 如果 gap 不够 30 分钟（比如批跑和补单是同一个事务链），可以退而求其次：在批跑事务内的最后一个 batch UPDATE 之后、COMMIT 之前执行 ANALYZE（此时新统计反映的是"翻转后"的分布，但 COMMIT 后补单会立刻回灌，optimizer 在补单完成前仍用旧计划，窗口从 30 分钟缩到补单实际耗时 ~5 分钟）。

2.3 更通用：DML 量阈值触发器（适合无法改批跑代码的场景）

如果你没有权限改批跑任务的代码，可以用一个外部轮询脚本：

#!/bin/bash
# stats_watchdog.sh — 每 5 分钟检查一次 t_invoice_item 的 DML 量
# 触发条件：自上次 ANALYZE 后，INSERT+UPDATE+DELETE 累计行数 > 基数的 5%

DB="mysql -h rds-host -u monitor -p'***' -N -e"
TABLE="t_invoice_item"
DB_NAME="billing"
THRESHOLD_PCT=5  # 5% 阈值，比 InnoDB 默认的 10% 更敏感

while true; do
    # 1. 获取上次 ANALYZE 时间
    LAST_UPDATE=$($DB "SELECT LAST_UPDATE FROM information_schema.INNODB_TABLE_STATS WHERE TABLE_NAME='$TABLE' AND STAT_NAME='n_diff_p000' LIMIT 1" 2>/dev/null)

    # 2. 获取自上次 ANALYZE 后的 DML 行数（从 performance_schema 或 binlog）
    DML_COUNT=$($DB "SELECT COUNT(*) FROM performance_schema.events_statements_history WHERE SQL_TEXT LIKE '%${TABLE}%' AND (SQL_TEXT LIKE '%INSERT%' OR SQL_TEXT LIKE '%UPDATE%' OR SQL_TEXT LIKE '%DELETE%')" 2>/dev/null)

    # 3. 获取当前基数
    BASELINE=$($DB "SELECT STAT_VALUE FROM information_schema.INNODB_TABLE_STATS WHERE TABLE_NAME='$TABLE' AND STAT_NAME='n_rows'" 2>/dev/null)

    # 4. 判断是否触发
    THRESHOLD=$((BASELINE * THRESHOLD_PCT / 100))
    if [ "$DML_COUNT" -gt "$THRESHOLD" ]; then
        echo "[$(date)] DML=$DML_COUNT > threshold=$THRESHOLD, triggering ANALYZE"
        $DB "ANALYZE TABLE ${DB_NAME}.${TABLE}"
        echo "[$(date)] ANALYZE completed"
    fi

    sleep 300  # 每 5 分钟检查一次
done

阈值选取的依据：InnoDB 默认 10% 对 2.3 亿行意味着要变更 2300 万行才触发——你的批跑一次就是 200–400 万行，远没到 10%。把阈值降到 5%（1150 万行）可以覆盖你的批跑量。如果批跑量更小，可以降到 2%。

三、eq_range_index_dive_limit 调优（一个被低估的旋钮）

你的 SQL 是 tenant_id = ? AND billing_period = ? AND status = 'UNBILLED'，三个等值条件。MySQL 优化器对每个等值条件都会做一次 index dive（在 B+Tree 上实际查找对应的叶子页来估算行数），而不是用统计信息估算。

但 eq_range_index_dive_limit 默认值是 200——意思是：如果 index dive 需要访问的叶子页数 > 200，优化器就放弃 index dive，退化为用统计信息估算。对于 2.3 亿行的大表，一个索引前缀对应的叶子页可能远超 200 页，这时候优化器实际上已经在用"粗估"了，精度大幅下降。

建议：

-- 查看当前值
SHOW VARIABLES LIKE 'eq_range_index_dive_limit';

-- 对核心查询的表，适当调大（代价是 EXPLAIN 稍慢）
SET GLOBAL eq_range_index_dive_limit = 1000;

-- 验证效果：调大后 EXPLAIN 的 rows 估算应该更贴近真实值
EXPLAIN SELECT id, amount, status
FROM t_invoice_item
WHERE tenant_id = 1 AND billing_period = '2026-10' AND status = 'UNBILLED'
ORDER BY id LIMIT 500;

量化判据：调大前后，对比 EXPLAIN 的 rows 估算值和 SHOW PROFILE 的 rows_examined 真实值。如果偏差从 > 10x 降到 < 3x，说明 index dive 精度显著提升，优化器选路的稳定性也会改善。

四、漂移检测的量化告警（比"定时 ANALYZE"更精准）

-- 建一个轻量监控视图，每 5 分钟跑一次
CREATE OR REPLACE VIEW v_stats_drift AS
SELECT
    t.TABLE_NAME,
    t.STAT_VALUE AS current_n_rows,
    t.LAST_UPDATE AS last_analyze_time,
    -- 核心指标：UNBILLED 占比估算
    (t.n_diff_p000 - t.n_diff_p001) * 100.0 / t.n_diff_p015 AS est_unbilled_pct,
    -- 漂移判定：如果占比 < 5% 且 last_analyze 在最近 2 小时内 → 高危
    CASE
        WHEN (t.n_diff_p000 - t.n_diff_p001) * 100.0 / t.n_diff_p015 < 5
             AND t.LAST_UPDATE > NOW() - INTERVAL 2 HOUR
        THEN 'HIGH_RISK'
        ELSE 'OK'
    END AS drift_level
FROM information_schema.INNODB_TABLE_STATS t
WHERE t.TABLE_NAME = 't_invoice_item'
  AND t.STAT_NAME = 'n_diff_p015';

-- 告警规则：drift_level = 'HIGH_RISK' 且持续 > 10 分钟 → 触发 ANALYZE + 通知

量化判据总结： | 指标 | 安全阈值 | 告警阈值 | 动作 | |---|---|---|---| | UNBILLED 占比（估算） | > 10% | < 5% | 触发 ANALYZE | | 估算 rows / 真实 rows 比值 | < 3x | > 5x | 触发 ANALYZE + 检查直方图 | | EXPLAIN type | range / ref | ALL | 立即触发 ANALYZE + 告警 | | last_analyze 距现在 | < 1h | > 6h 且 DML > 5% | 触发 ANALYZE |

五、和已有方案的对比取舍

| 方案 | 改动面 | 适用场景 | 失败模式 | |---|---|---|---| | Hint / FORCE INDEX（已有回答） | 改 SQL 模板 | 查询固定、索引不会变 | 索引被删后静默失效 | | SPM / Outline（已有回答） | 零代码改动 | 云 RDS 支持 SPM | 自建 MySQL 无此功能 | | 批跑后 ANALYZE（本方案 2.2） | 改批跑 hook | 批跑窗口固定、可加 hook | 批跑代码不可改 | | DML 阈值触发器（本方案 2.3） | 外部脚本 | 批跑代码不可改 | 轮询间隔内仍可能漂移 | | eq_range_index_dive_limit（本方案三） | 改全局参数 | 大表 index dive 退化 | 全局生效，需评估其他查询影响 | | 漂移监控告警（本方案四） | 监控脚本 | 任何场景 | 只能检测不能修复，需配合 ANALYZE |

推荐组合：批跑后 ANALYZE（主）+ 漂移监控告警（兜底）+ eq_range_index_dive_limit=1000（精度提升）。如果批跑代码不可改，退到 DML 阈值触发器 + 监控告警 + hint 临时止血。

一句话：别和统计信息"对着干"，让它"刷新得更快、更准"才是正路。