MySQL事务中处理大批量更新技巧_分段更新以降低锁压力-mysql教程-PHP中文网

大批量update会卡表因扫描范围大时锁升级为间隙锁或表级锁，引发阻塞、主从延迟等；应按主键分段更新，每次用上一次最大id作为下一次起始条件，确保边界一致且避免函数干扰索引。

mysql事务中处理大批量更新技巧_分段更新以降低锁压力

MySQL 在执行 UPDATE 时默认加行级锁（InnoDB），但若扫描范围大、索引不走或 WHERE 条件没命中索引，实际会升级为间隙锁甚至表级锁扫描，导致其他事务长时间阻塞。更麻烦的是，事务越长，undo log 越大，主从延迟、binlog 写入压力、MVCC 快照膨胀全跟着来。

常见错误现象：Waiting for table metadata lock、Lock wait timeout exceeded、从库延迟突增几十秒到几分钟。

别用 UPDATE t SET x = y WHERE created_at 这种没索引字段的条件——哪怕有索引，如果 <code>created_at 区间太大，也会扫几百万行
单事务更新超过 5 万行，基本就该拆了；超过 10 万行，大概率引发锁等待和复制中断
避免在业务高峰期跑全表更新，尤其不要在从库上直接执行（即使只读，也可能触发临时锁）

核心思路：用主键（或带索引的唯一字段）做游标，每次只更新固定行数，提交后释放锁，再取下一段。不是靠时间/状态字段分页，因为那些字段可能重复、空值或没索引。

假设要更新 user_order 表中 status = 0 的订单，主键是 id：

UPDATE user_order 
SET status = 1, updated_at = NOW() 
WHERE id > 100000 AND status = 0 
ORDER BY id 
LIMIT 1000;

执行完后，记录本次最后更新的 id（比如 100999），下次 WHERE 改成 id > 100999 AND status = 0，循环直到影响行为 0。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

关键在「边界一致性」：确保每段查询的起始点严格大于上一段的结束点，且 WHERE 条件在每次执行时语义不变。

容易踩的坑：

用非单调字段（如 updated_at）做分段依据：并发更新可能导致同一行被两次匹配，或因写入延迟彻底漏掉
在循环中先 SELECT MAX(id) 再 UPDATE：中间若有新插入，就会漏掉这批数据；应该用原子性的 UPDATE ... LIMIT 自带的行锁定保证
WHERE 条件里混用函数，比如 DATE(created_at) ：无法走索引，退化为全表扫描
没加 AND status = 0 在每次 UPDATE 的 WHERE 里：分段过程中其他进程可能已改过部分行的状态，导致跳过