MySQL批量UPDATE会锁表，因InnoDB在非唯一索引或全表扫描时升级为间隙锁或临键锁，锁住大范围索引；应使用主键范围分批（如WHERE id BETWEEN ? AND ?），每批100–500行，取MAX(id)续查。
MySQL 批量 UPDATE 为什么会锁表
因为默认的 UPDATE 在非唯一索引或全表扫描场景下，InnoDB 可能升级为间隙锁（Gap Lock）或临键锁（Next-Key Lock），导致锁住一大片索引范围，不只是目标行。尤其当 WHERE 条件没走索引、或用 LIKE '%xxx'、或更新字段本身是索引列时，锁范围会意外扩大。
常见错误现象：Waiting for table metadata lock 或其他事务卡在 UPDATE / SELECT ... FOR UPDATE 上；SHOW ENGINE INNODB STATUS 里看到大量 lock_mode X locks gap before rec insert intention waiting。
关键点：锁粒度不由“语句是否批量”决定，而由“执行计划是否精准定位到索引记录”决定。
用 LIMIT + 自增 ID 分批 UPDATE 的实操要点
本质是把一个大事务拆成多个小事务，每个只操作几百行，降低单次锁持有时间与范围。前提是表有单调递增的主键（如 id）或时间戳字段（如 created_at）。
必须注意：
- WHERE 条件一定要包含主键范围，例如
WHERE id BETWEEN ? AND ?，不能只靠LIMIT控制数量（否则 MySQL 仍可能扫描全表再截断） - 每次提交后，下次查询的起始
id必须取上一批的MAX(id)，不能简单加固定步长（避免漏数据或重复） - 步长建议 100–500 行，太大仍可能触发锁升级；太小则网络和事务开销上升
示例（安全分批）：
UPDATE orders SET status = 'shipped' WHERE id >= 10000 AND id < 10500 AND status = 'pending';
WHERE 条件没索引时怎么避免全表锁
如果必须按非索引字段更新（比如 UPDATE users SET flag = 1 WHERE email LIKE '%@gmail.com'），InnoDB 会退化为聚簇索引全扫描，每行都加记录锁 —— 这等于事实上的“锁表”。
可行解法只有两个：
- 先建临时索引：
CREATE INDEX idx_email_like ON users(email);（注意：DDL 期间表可读不可写，但比锁表几小时好） - 改用应用层分页 + 主键驱动：先
SELECT id FROM users WHERE email LIKE '%@gmail.com' ORDER BY id LIMIT 500，拿到 ID 列表后再UPDATE users SET flag = 1 WHERE id IN (1,2,3...)（IN 列表别超 1000 项）
别信“加 FORCE INDEX 就能绕过全表扫”——没对应索引时，提示语法正确，但执行计划仍是 type: ALL。
事务提交频率与 binlog / 复制延迟的关系
分批提交不是越勤越好。每批都 COMMIT 会高频刷盘 binlog 和 redo log，主从复制压力陡增，尤其在 ROW 格式下，每条 UPDATE 都生成完整行镜像事件。
实际权衡点：
- 若从库 I/O 跟不上，宁可把每批大小提到 800 行，减少 COMMIT 次数，而不是压到 100 行+每秒 commit
- 确认
sync_binlog = 1和innodb_flush_log_at_trx_commit = 1是否开启——线上环境通常开，意味着每次 COMMIT 真实落盘，这是延迟主因 - 测试时用
pt-query-digest看慢日志里UPDATE的Lock_time和Rows_affected比值，比值高说明锁争抢严重，得调小批次
真正难处理的是“业务要求强一致性 + 必须按非索引字段批量更新 + 从库硬件弱”这三者叠加——这时候分批只是缓释，不是根治。
