百万行 UPDATE 踩坑：从 undo 膨胀到拆批提交

问题背景：一条 UPDATE 为什么拖垮整条链路

业务上常见这样的需求：给历史订单补字段、批量刷新用户状态、修复脏数据。开发同学最容易写出的语句是：

-- 看似简单直接，实际可能命中百万行
UPDATE orders
SET settle_status = 'DONE',
    updated_at = NOW()
WHERE settle_status = 'INIT'
  AND created_at < '2024-01-01';

如果筛选条件命中 100 万行，这条 SQL 在默认 autocommit=1 下仍然是一个独立事务。它会一次性持有大量行锁、生成海量 undo log、写入大量 binlog，直到全部修改完成才提交。线上典型表现是：业务接口出现锁等待，从库复制延迟持续拉大，数据库服务器 IO 飙升，回滚段空间快速增长，最终可能触发慢查询告警甚至实例磁盘告警。

这类问题的本质不是“UPDATE 慢”这么简单，而是一个大事务同时冲击了 InnoDB 存储引擎、MySQL 复制拓扑和在线业务访问。

方案对比：一次性提交、存储过程与外部任务拆批

方案一：一次性大事务更新

优点是实现简单，语句数量少；缺点也最致命：事务持续时间不可控，undo log 不能及时 purge，行锁持有时间长，binlog 一次性灌入从库，失败后回滚成本极高。该方案只适合小表或低峰期可控的数据修复。

方案二：存储过程内部循环提交

在数据库内部使用 WHILE 循环，每批修改固定行数并主动 COMMIT。它不依赖外部服务，适合 DBA 临时运维，但要注意存储过程中的异常处理、超时设置和进度记录，否则会话断开后难以判断执行位置。

方案三：应用程序或任务平台拆批

由 Java、Go、Python 或数据运维平台按主键范围分批读取 ID，再按小事务更新。这是生产环境最推荐的方式，便于限流、暂停、重试、记录断点，也更容易结合监控观察主从延迟。

根因分析：undo 膨胀与主从延迟是如何发生的

InnoDB 修改数据时会生成 undo log，用于事务回滚和 MVCC 旧版本读取。一个事务未提交前，它修改过的数据页都需要保留可访问的旧版本。百万行 UPDATE 若横跨多个数据页，purge 线程无法及时清理这些 undo 记录，就会出现回滚段膨胀，表现为 history list length 持续升高。

同时，InnoDB 会对扫描到的索引记录加锁。如果过滤字段没有合适索引，MySQL 可能扫描大量不必要的行，锁范围进一步扩大。即使最后只更新部分记录，扫描过程中的锁等待也会影响正常交易。

复制方面，主库提交后 binlog 才发送到从库。一个超大事务在从库单线程或较少并行 worker 下回放，会造成明显延迟。虽然 MySQL 5.7 及 8.0 提供了基于 LOGICAL_CLOCK 的并行复制，但单个事务内部无法并行，超大事务仍然是复制延迟的常见元凶。

可以使用以下语句观察长事务：

-- 查看执行时间较长的事务，重点关注 trx_started、trx_rows_modified
SELECT trx_id,
       trx_state,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
       trx_rows_modified,
       trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC
LIMIT 10;

实操步骤：建表、建索引并按主键拆批提交

下面构造一张可复现实验的订单表。生产环境不要在业务高峰直接执行，应先在备库或测试库验证执行计划和批次大小。

-- 1. 建表：主键使用自增 BIGINT，便于按范围拆批
CREATE TABLE orders_batch_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  settle_status VARCHAR(16) NOT NULL DEFAULT 'INIT',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_status_created (settle_status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 造数：递归 CTE 生成 10000 行测试数据
INSERT INTO orders_batch_demo (order_no, settle_status, created_at, updated_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT CONCAT('ORD', LPAD(n, 8, '0')),
       'INIT',
       DATE_SUB(NOW(), INTERVAL n HOUR),
       DATE_SUB(NOW(), INTERVAL n HOUR)
FROM seq;

先确认筛选条件的执行计划，避免全表扫描：

-- 3. 确认是否使用复合索引
EXPLAIN
SELECT id
FROM orders_batch_demo
WHERE settle_status = 'INIT'
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 1000;

数据库内部可以使用存储过程按主键推进。关键点是每批都通过 id > last_id 缩小扫描范围，并在更新后立即提交：

-- 4. 存储过程拆批：按主键游标推进，每批 1000 行
DELIMITER $$

CREATE PROCEDURE sp_update_orders_batch(
  IN p_batch_size INT,
  IN p_sleep_seconds DECIMAL(5,2)
)
BEGIN
  DECLARE v_last_id BIGINT DEFAULT 0;
  DECLARE v_affected INT DEFAULT 1;

  WHILE v_affected > 0 DO
    -- 先取本批最大主键，避免 UPDATE 中 LIMIT 跨版本兼容性问题
    SELECT COALESCE(MAX(t.id), 0) INTO v_last_id
    FROM (
      SELECT id
      FROM orders_batch_demo
      WHERE id > v_last_id
        AND settle_status = 'INIT'
        AND created_at < '2024-01-01'
      ORDER BY id
      LIMIT 1000
    ) t;

    IF v_last_id = 0 THEN
      SET v_affected = 0;
    ELSE
      UPDATE orders_batch_demo
      SET settle_status = 'DONE',
          updated_at = NOW()
      WHERE id <= v_last_id
        AND id > 0
        AND settle_status = 'INIT'
        AND created_at < '2024-01-01';

      SET v_affected = ROW_COUNT();
      COMMIT;

      -- 让主从复制和 purge 线程有缓冲时间
      DO SLEEP(p_sleep_seconds);
    END IF;
  END WHILE;
END$$

DELIMITER ;

-- 5. 执行：每批 1000 行，每批之间暂停 0.2 秒
CALL sp_update_orders_batch(1000, 0.20);

更推荐的应用侧伪代码如下，核心是每批独立事务，而不是外层开启一个大事务包住所有循环：

-- 应用侧每轮先查 ID 范围
SELECT MIN(id) AS min_id, MAX(id) AS max_id
FROM (
  SELECT id
  FROM orders_batch_demo
  WHERE id > ?
    AND settle_status = 'INIT'
    AND created_at < ?
  ORDER BY id
  LIMIT 1000
) t;

-- 再按闭区间更新，条件保留业务过滤，防止重复执行误改
UPDATE orders_batch_demo
SET settle_status = 'DONE',
    updated_at = NOW()
WHERE id BETWEEN ? AND ?
  AND settle_status = 'INIT'
  AND created_at < ?;

批次大小不要机械固定为 1000。单行较宽、索引较多、从库配置较弱时，可从 500 开始；单行很窄且低峰执行，可尝试 5000。判断标准是单批执行时间通常控制在 1 秒内，主从延迟不持续抬升，锁等待数量不明显增加。

执行期间如何监控锁等待和复制延迟

-- 查看当前锁等待：谁阻塞了谁
SELECT r.trx_id AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_seconds,
       b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b
  ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r
  ON r.trx_id = w.requesting_trx_id;

在从库上执行：

-- MySQL 8.0 查看复制状态，重点关注 Seconds_Behind_Source 与回放线程状态
SHOW REPLICA STATUS\G

-- 如果仍使用旧版本命令，可执行：
-- SHOW SLAVE STATUS\G

如果发现 Seconds_Behind_Source 持续上升，应立即降低批次大小或增加批次间隔；如果已经积压严重，应暂停任务，等从库追平后再继续，而不是把所有批次尽快跑完。

避坑总结：至少记住这 5 条实践规则

1. 不要用无索引字段驱动百万行 UPDATE。 常见错误现象是执行计划出现 type=ALL，业务线程大量卡在 updating 或锁等待。修正方法是为过滤字段和排序字段建立合适复合索引，例如 (settle_status, created_at)，拆批时再结合主键范围访问。

2. 不要在外层大事务中循环更新。 有些代码先 BEGIN，循环 1000 次后再统一 COMMIT，以为“分批 SQL”就安全，实际上 undo、锁和 binlog 仍然属于一个大事务。正确做法是每个批次独立提交，提交后短暂停顿，给 purge 和复制回放留出窗口。

3. 不要只按 LIMIT 1000 更新而不记录进度。 会话中断后容易不知道执行到哪里，重复执行还可能因为数据状态变化而改到新数据。应按主键或时间游标推进，并在更新条件中保留原始状态判断，必要时建立任务进度表记录最后处理位置。

4. 不要忽视从库延迟告警。 主库上几秒跑完的任务，可能让只读库延迟数分钟，影响报表、风控和用户查询。执行期间必须监控复制延迟，支持随时暂停；跨地域、弱配置从库尤其要降低批次速率。

5. 不要在业务高峰直接全量刷新。 大批量数据修复应先在测试库评估行数、索引、单批耗时和 binlog 量，再选择低峰窗口灰度执行。对于超高并发核心表，还可以结合消息队列、任务调度平台做限速，并准备停止脚本与幂等校验 SQL。

验收方法：确认更新正确且没有残留

-- 统计应更新与已更新数量，确认任务结果
SELECT settle_status, COUNT(*) AS cnt
FROM orders_batch_demo
WHERE created_at < '2024-01-01'
GROUP BY settle_status;

-- 检查是否仍有遗漏
SELECT COUNT(*) AS remaining_rows
FROM orders_batch_demo
WHERE settle_status = 'INIT'
  AND created_at < '2024-01-01';

百万行数据修复的关键，不是把 SQL 写得多么短，而是把事务切得足够小、把节奏控制得足够稳。拆批提交本质上是在吞吐和安全之间做限流：主库短事务及时释放锁，undo log 可以被 purge，binlog 以更平滑的方式发送到从库，最终才能既完成数据修复，又不伤害在线业务。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。