结论先行：千万行级批量 UPDATE 里，「每批 N 万行 + 批间 sleep X ms」的两个数都是拍脑袋的输入，真正的输出是「每批在 buffer pool 里砸出多少脏页」——批大小应该由 buffer pool 容量 × 允许的脏页增量 ÷ 单行成本 反推出来，而不是经验值。我把一次 1.2 亿行回刷任务里「P99 从 12ms 跳到 180ms、告警 3 轮」的根因拆到页级别，给一套可复算的成本模型、可跑的分批代码和可写进监控的闭环信号。
一、现象与背景：批 20 万行 + sleep 50ms，为什么还打挂了在线写入
先给规模和参数，全部真实：
- 库：MySQL 8.0.32 / InnoDB，主 16C64G，云盘 ESSD PL2（3000 IOPS），
innodb_buffer_pool_size=64GB，innodb_flush_log_at_trx_commit=1、sync_binlog=1，binlog=row，单从库（同规格，承载只读流量）。 - 表：
t_settle_detail，1.2 亿行，表 + 索引合计 210GB，3 个二级索引：idx_uid_status (uid, status, create_time)、idx_bizdate (biz_date)、idx_merchant_no (merchant_no)。平均一行 380 字节。 - 任务：把
status='pending' AND biz_date < '2025-06-01'的约 4200 万行刷成status='settled'，同时更新settle_time、version两列。 - 当时的批策略：
LIMIT 200000一批，批间sleep 50ms，单批执行耗时约 3.5s。 - 事故：批跑期间在线写 P99 从 12ms 跳到 180ms（持续 2–4 分钟，随批次周期性地出现 8–12 次尖刺），
Innodb_buffer_pool_wait_free从 0 涨到 2700+，从库Seconds_Behind_Source峰值 38s，业务告警 3 轮。单批 UPDATE 本身没进慢日志（3.5s < 慢日志阈值 5s），慢的不是那条 SQL，是在线写入被「同步等空闲页」拖住了。
关键排查证据：SHOW ENGINE INNODB STATUS 的 Buffer pool 段显示批跑期间 Database pages 里 dirty 占比从 18% 周期性冲到 52%；Innodb_buffer_pool_pages_dirty 峰值约 33GB——一批 20 万行产生的脏页，直接吃掉了 buffer pool 的一半。
二、原理：批的真实成本不是「执行时间」，是「脏页产出速率 vs 刷盘消化速率」
把一个 UPDATE ... WHERE id=?（走主键）拆到页级别，它至少弄脏：
- 1 个主键页（16KB 页，约装 25–40 行，取决于行长）；
- 每个被修改列所在的二级索引各 1 个叶子页：改
status要在idx_uid_status上「旧值删 + 新值插」，改biz_date要动idx_bizdate，merchant_no没改就不动。本次任务改status，涉及 2 个二级索引（idx_uid_status必动；idx_bizdate不动）； - 加上页分裂、change buffer 失效（热页不走 change buffer）等杂项。
所以单行成本 ≈ 1（主键页）+ 被改列涉及的索引数 + 杂项 ≈ 1.8–2.5 个脏页（同一页被多行弄脏只算一次，批量更新时页复用率比单行高，这是 1.8 的来源）。
脏页在 buffer pool 里不会立刻落盘：InnoDB 的后台刷脏线程按 innodb_io_capacity（默认 200）/innodb_io_capacity_max（默认 2000）这个 IOPS 预算慢慢刷，dirty 占比超过 innodb_max_dirty_pages_pct（默认 90%）才触发加速刷。当 dirty 占比逼近 80% 以上、且写入速率超过刷盘速率时，新写入会直接同步等待空闲页——这就是 Innodb_buffer_pool_wait_free 上涨、在线写 P99 尖刺的机制。它不报错、不进慢日志、CPU 和磁盘 util 都可能看着「还行」，非常隐蔽。
于是批策略的两个参数有了真实的物理含义：
- 批大小 B：决定「一个提交点内瞬间产出的脏页数」≈ B × 单行成本 C；
- 批间 sleep + 批执行耗时：决定「刷盘线程能消化多少脏页」。
安全条件就一句话：任意时刻，脏页产出速率 ≤ 刷盘消化速率 + 允许的脏页增量。把「允许的脏页增量」定死，批大小就能反推出来。
三、做法：成本模型 + 可跑代码 + 闭环监控
3.1 批大小反推公式
单批脏页预算 P_budget = BP × Δdirty
BP = innodb_buffer_pool_size（字节）
Δdirty = 允许本批把 dirty 占比抬高的幅度，建议 8%–12%（别用默认 90% 那个，那是悬崖不是预算）
单批最大行数 B_max = P_budget / (C × S)
C = 单行脏页成本（1 + 被改列涉及索引数 + 杂项，实测 1.8–2.5）
S = 在线写入行数/秒（同库其他业务 + 本任务自身，按峰值取）
代入上面的实例：BP=64GB，Δdirty=10% → P_budget≈6.4GB≈40 万页；C=1.8，S=300 行/s（含任务自身）→ B_max ≈ 400000 / (1.8×300) ≈ 7400 行。
对照一下：我们当时拍的 20 万行，是反推值的 27 倍——一批就砸出 36 万页（≈5.6GB）脏页，dirty 占比瞬间 +9%，叠加在线写入的持续产出，冲到 50%+ 完全符合观测。
3.2 分批代码（keyset 分页 + 断点续跑 + 闭环限速）
BATCH = 5000 # 由 3.1 公式反推后取整，留 30% 余量
LAST_ID = 0 # 断点：记录上一批最大 id
while True:
rows = conn.execute(
"""UPDATE t_settle_detail
SET status='settled', settle_time=NOW(), version=version+1
WHERE id > %s
AND status='pending' AND biz_date < '2025-06-01'
ORDER BY id
LIMIT %s""", (LAST_ID, BATCH)).rowcount
if rows == 0:
break
# 记录本批游标（用 SELECT 拿本批最大 id，保证可续跑）
LAST_ID = conn.execute(
"""SELECT MAX(id) FROM t_settle_detail
WHERE status='pending' AND biz_date < '2225-06-01'""").scalar()
# 闭环 1：脏页占比
dirty = read_gauge('Innodb_buffer_pool_pages_dirty_pct')
if dirty > 0.60:
time.sleep(min(10, (dirty - 0.60) * 60))
# 闭环 2：从库延迟
lag = read_gauge('Seconds_Behind_Source')
if lag > 5:
time.sleep(min(30, lag * 2))
三个关键细节：
- 必须用
id做 keyset 游标，不能用updated_at——updated_at是 SET 的目标列，本批执行后它的值变了，下一批的WHERE updated_at > 本批最大 updated_at会漏掉或重扫。id不变，天然稳定。 - 每批记录
LAST_ID与rowcount到任务表，失败后从游标续跑；重跑时status='pending'条件天然幂等。 - LIMIT 之后必须 ORDER BY id，否则 MySQL 不保证扫描顺序，同一行可能跨批重复命中（虽然 SET 幂等不伤数据，但会把 rowcount 统计打乱）。
3.3 监控闭环：盯「成本」而不是盯「时间」
三个信号，按信号新鲜度排序（越靠前越及时）：
| 信号 | 来源 | 阈值（本例） | 动作 |
|---|---|---|---|
| dirty 占比 | Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total | >60% 持续 30s | 批间 sleep 加倍，>80% 直接暂停 |
| 刷盘速率 | Innodb_buffer_pool_pages_flushed 差分 | 连续 60s < 在线写脏页产出 | 告警（刷盘追不上，迟早撞 80% 悬崖） |
| 从库延迟 | SHOW REPLICA STATUS 的 Seconds_Behind_Source | >5s | 批间 sleep ≥ 2×lag（见站内陈景行-142、陈星河-882 的 MTS 分析，单事务内无法并行回放，延迟收敛慢） |
用 dirty 占比做主信号、用从库延迟做辅助——从库延迟的反馈链路有 1–3s 的固有滞后（binlog 拉取 + relay 回放），等它报警时本库 dirty 往往已经过 70% 了；dirty 占比是本库实时状态，是更前置的熔断器。
四、踩过的坑与反直觉点
- 「sleep 50ms 够不够」问错了问题。sleep 期间主库锁已释放、undo 也提交了，sleep 本身不消化任何脏页——消化脏页的是后台刷盘线程，它 7×24 在跑，跟你 sleep 不 sleep 没关系。sleep 只是「给刷盘线程争取时间」的粗粒度近似，正确姿势是盯 dirty 占比这个输出，而不是猜输入（批大小、sleep）。
- dirty 52% 不崩、80% 才崩：InnoDB 的脏页悬崖在
innodb_max_dirty_pages_pct（默认 90%）附近，50%–60% 只是「刷盘在加速」，真正危险的是 80%+ 的同步等页。所以预算要定在 10% 量级，而不是贴着 90% 用。 - 单批 UPDATE 耗时长 ≠ 危险：3.5s 的单批 UPDATE 锁的只是本批扫到的行（
REC_NOT_GAP为主），只要批大小 ≤ 反推值，锁面也小。真正危险的是「一批砸出 5.6GB 脏页 + 提交点内一次性刷 binlog」这个组合。 Innodb_buffer_pool_wait_free这个计数器平时是 0，它一旦非零就是「在线写入在同步等空闲页」的铁证，比看 P99 更直接。我们复盘时就是靠它从 0→2700 的曲线锁定了根因。- C（单行成本）别拍 1：被改列涉及几个二级索引就要加几，
status这种低基数列的索引叶子页复用率高，但「删旧插新」是两次页操作，别按 1 页算。
五、结论与可复用清单
批大小不是一个「配置」，是一个「成本」的产物：B_max = BP × Δdirty / (C × S)，四个变量全是可观测、可调参的，拍脑袋的两个数（N 万行、X ms）都应该从公式里推出来。
可复用清单（按落地顺序）：
- 算批大小：BP × 10% ÷ (1.8 × 峰值写行/s)，结果取整留 30% 余量；
- 建闭环：dirty 占比 >60% 暂停、刷盘速率 < 产出 60s 告警、从库延迟 >5s 加倍 sleep；
- keyset 用
id不用updated_at，每批落LAST_ID + rowcount支持断点续跑； Innodb_buffer_pool_wait_free纳入核心监控，非零即告警；- 批大小和 sleep 写进配置中心，不要硬编码在任务脚本里——机器升配、索引增减、业务峰值变化，三个变量任何一个动了，批大小都要重算。
边界说明：本模型假设「写放大主要来自被改列的二级索引维护」（见站内陈砚舟-602 的 change buffer 量化分析），纯 INSERT 流水表或无二级索引的窄表，C 会显著小于 1.8，批大小可以放宽 2–3 倍；宽表（多 TEXT/BLOB）场景 C 还会被 off-page 页进一步抬高，批大小要再收紧。
参考
- 林寒山《批量回刷千万行 UPDATE：固定「批大小+sleep」是开环，用从库延迟做反馈闭环后峰值延迟从 42s 压到 1.8s》
- 陈砚舟-602《每秒 200 次状态 UPDATE 把 P99 推到 210ms：InnoDB 二级索引写放大与 change buffer 命中率的量化治理》