直接用 WHERE id BETWEEN x AND y 分段 UPDATE 易导致边界重叠/遗漏、主键空洞引发全表扫描,且数据库不保证按主键顺序扫描;应改用基于主键值的锚点分段,先查边界 ID 再执行范围 UPDATE。

为什么不能直接用 WHERE id BETWEEN x AND y 分段 UPDATE?
看似简单,但实际执行时容易踩两个坑:一是 BETWEEN 包含边界,若分段重叠或遗漏,会导致部分行被跳过或重复更新;二是主键不连续(比如有 DELETE 过的记录),BETWEEN 会扫描大量空洞区间,性能骤降。更关键的是,MySQL/PostgreSQL 在 UPDATE ... WHERE 中无法保证按主键顺序物理扫描,优化器可能走全表扫描或错误索引。
推荐做法:用主键值而非行号做分段锚点
核心思路是每次取一段「已知最小 ID」和「下一个分段起始 ID」,避免依赖连续性。需要先查出所有分段边界点,再逐段执行。以 MySQL 为例:
SELECT id FROM t ORDER BY id LIMIT 1 OFFSET 0; -- 第一段起点 SELECT id FROM t ORDER BY id LIMIT 1 OFFSET 9999; -- 第二段起点(1w 行一段) SELECT id FROM t ORDER BY id LIMIT 1 OFFSET 19999;
拿到这些锚点后,生成实际 UPDATE:
UPDATE t SET status = 'done' WHERE id >= 1001 AND id = 10002 AND id
-
OFFSET值需提前算好,建议用脚本生成(Python/Shell 都行),别手写 - 每段上限用
而非,确保无重叠 - 首段下界用
>=,末段上界可设为WHERE id >= last_start_id收尾 - PostgreSQL 可用
SELECT id FROM t ORDER BY id LIMIT 1 OFFSET N,行为一致
并发执行时必须加锁或控制事务隔离级别
多个进程同时跑不同分段 UPDATE,若没处理好,可能因 MVCC 或锁竞争导致更新丢失或死锁。尤其在 UPDATE 涉及二级索引更新时,InnoDB 会锁住索引范围。
- MySQL 下建议显式加
SELECT ... FOR UPDATE预占范围(但会阻塞其他分段) - 更稳妥的是应用层加分布式锁(如 Redis 锁住分段 ID 区间),或用单线程调度器串行派发任务
- PostgreSQL 中可配合
SKIP LOCKED实现安全分片消费,但 UPDATE 本身不支持,需改用 CTE +RETURNING提前捞出 ID 列表再更新 - 务必关闭自动提交,每个分段一个事务,失败时能单独回滚
UPDATE 分段大小怎么定?
不是越大越好。10 万行一段在多数 OLTP 场景下已偏大:事务日志暴涨、锁持有时间过长、OOM 风险上升。实测建议从 5000 行起步,观察慢查询日志和 innodb_row_lock_time_avg。
- MySQL 5.7+ 可用
EXPLAIN UPDATE ...(8.0.19+ 支持)看是否命中主键索引 - 如果表有高频写入,分段太小(如 100 行)会导致调度开销反超收益
- 注意 binlog 格式:
ROW模式下每行变更都记日志,分段大则单事务 binlog 文件激增 - 云数据库(如 RDS、Aurora)对长事务有限制,超时会 kill,需设置合理
wait_timeout和重试逻辑
主键分段 UPDATE 的本质是把「不可控的全表扫描」转为「可控的索引范围扫描」,但分段逻辑、锁策略和大小必须匹配你的数据分布与负载特征——离散度高的主键,比连续主键更需要预查锚点,而不是硬切数字区间。










