MySQL执行大量update锁表_将大批量更新改为小批量循环

MySQL UPDATE卡表主因是WHERE未走索引导致锁全表，或大范围更新长期持锁；应确保索引命中、分批提交、加sleep限流、避开高峰，并优先用pt-archiver替代手写脚本。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

UPDATE 为什么会让整个表卡住

MySQL的UPDATE操作，默认确实是行级锁，但这有个重要前提：WHERE条件必须能用上索引。一旦条件字段没索引，InnoDB引擎就不得不“升级”锁的级别——更准确地说，它会锁住所有聚簇索引记录，效果上跟锁住整个表没什么两样。另一种情况是，即使走了索引，如果更新的范围过大，比如涉及几百万行，事务长时间持有这些锁，同样会阻塞后续的读写操作，让整个表“卡”住。

典型的错误现象是什么呢？在SHOW PROCESSLIST里，你会看到大量连接处于Waiting for table metadata lock或Updating状态；业务层面，接口开始超时，数据库的主从延迟也会直线飙升。

• 检查索引命中情况：可以用EXPLAIN UPDATE ...来预览执行计划（MySQL 8.0.19及以上版本支持直接在UPDATE前加EXPLAIN关键字）。
• 警惕无索引的时间范围更新：像UPDATE ... WHERE created_at < ...这类操作，如果created_at字段没索引，在大表上就是灾难。
• 注意隐式类型转换：比如条件是WHERE user_id = '123'，但user_id字段是INT类型，这个隐式转换也会导致索引失效。

怎么切分小批量 UPDATE（带安全边界）

核心思路很明确：利用主键或唯一有序字段作为游标进行分页处理，每次只更新几千行，严格控制事务的体积和锁持有的时间。这里有个关键点：千万别用LIMIT OFFSET，因为在数据动态变化的过程中，偏移量很容易错位，导致重复更新或漏更新。

推荐的做法是按主键递增范围进行扫描：

• 首先，查出起始ID：SELECT MIN(id) FROM t WHERE status = 0
• 然后进入循环：UPDATE t SET status = 1 WHERE id BETWEEN ? AND ? AND status = 0
• 每批处理的行数，建议在1000到5000行之间，具体取决于单行数据大小和当前的数据库QPS压力。
• 务必带上原有的业务条件（比如status = 0），这是防止重复更新的重要安全边界。

一个清晰的伪代码逻辑示例：

last_id = 0
while True:
    rows = SELECT id FROM t WHERE id > last_id AND status = 0 ORDER BY id LIMIT 5000
    if not rows: break
    UPDATE t SET status = 1 WHERE id IN (/* rows */) AND status = 0
    last_id = rows[-1].id

事务提交频率和 sleep 控制节奏

每批UPDATE执行后，必须立刻COMMIT，否则锁资源会一直被占用，无法释放。但反过来，提交得太频繁也不行——高频的提交会给binlog和磁盘I/O带来巨大压力，在主从复制架构下，尤其容易拖慢从库的复制线程。

实际操作中，可以遵循这几个建议：

• 每批必须独立提交：坚决禁止将所有批次包裹在一个大事务里。
• 批次间引入短暂休眠：在每批之间加上SLEEP(0.1)（即100毫秒），给数据库和其他查询一点“喘息”的机会。线上环境可以根据实际情况，在0.05到0.2秒之间调整，以平衡执行速度和系统干扰。
• 选择合适的时间窗口：尽量避开业务高峰期执行。同时，可以通过监控SELECT @@innodb_row_lock_time_a vg来观察行锁平均等待时间的趋势，及时发现潜在问题。
• 慎用存储过程封装：不建议用存储过程来写这种循环逻辑，一旦出错难以中断，日志追踪麻烦，DBA也不方便介入处理。

用 pt-archiver 替代手写脚本的适用场景

当更新逻辑相对简单（比如单表、单一条件、没有复杂的JOIN操作），并且你不想自己维护调度和重试机制时，pt-archiver工具是一个更稳健的选择。它内置了游标分片、失败重试、限流控制和进度打印等功能，相比手写Python或Shell脚本，出错的概率要小得多。

一个典型的命令示例如下：

pt-archiver \
  --source h=localhost,D=app,t=user \
  --where "status = 0 AND created_at < '2022-01-01'" \
  --limit 1000 \
  --commit-each \
  --sleep 0.1 \
  --no-delete \
  --bulk-insert \
  --statistics \
  --dry-run

使用时有几个关键点需要注意：

• --no-delete参数至关重要，因为工具默认行为是归档（即迁移并删除源数据），加上这个参数才能确保只更新不删除。
• --bulk-insert可以加速写入，但前提是目标表的列允许NULL值或有默认值。
• 首次执行前，务必加上--dry-run参数，预览工具生成的SQL是否符合业务预期。
• 这个工具不支持跨数据库的JOIN更新，如果业务逻辑复杂，还是得自己动手写脚本。

话说回来，技术层面的分片更新其实并不算最棘手的问题。真正的挑战在于，如何确保每一批更新都满足业务的幂等性要求。比如涉及状态机流转、金额累加或者触发外部服务回调的场景，仅仅靠WHERE status = 0这样的条件可能无法完全兜底。这时候，往往需要在应用层补充额外的校验逻辑，或者引入分布式锁来保证一致性。