批量UPDATE锁太久主因是未走索引导致全表扫描加锁，或大范围更新长期持锁；应分批执行、确保索引命中、显式COMMIT释放锁，并用ORDER BY+LIMIT控制扫描顺序与范围。
为什么批量UPDATE会锁太久？
MySQL 的 UPDATE 默认走行级锁，但若没走索引、或 WHERE 条件匹配大量行，InnoDB 会升级为间隙锁（Gap Lock）甚至临键锁（Next-Key Lock），导致锁范围远超预期。更关键的是：事务越长，锁持有时间越长；而一条含 1000 行的 UPDATE ... WHERE id IN (1,2,...,1000) 会在事务内一次性加锁、更新、写 redo/undo，全程阻塞其他对这些行的读写。
用 LIMIT 分批次 + 显式事务控制锁粒度
核心思路是把大更新拆成小事务，每批只锁几十到几百行，快速提交释放锁。必须配合 ORDER BY 防止幻读和重复处理：
START TRANSACTION; UPDATE orders SET status = 'shipped' WHERE id BETWEEN 10001 AND 10100 AND status = 'pending' ORDER BY id LIMIT 100; COMMIT;
说明：
-
ORDER BY id确保每次扫描顺序一致，避免因并发 INSERT 导致漏更新 -
LIMIT 100不仅控量，还让优化器倾向使用索引扫描而非全表扫描 - 不要依赖
IN列表做分批——列表过长会触发 SQL 长度限制、参数绑定开销高、且无法利用范围索引 - 每次事务后加
SLEEP(0.01)（应用层控制）可缓解主库压力，但别在 SQL 里用SLEEP()函数，它会延长事务时间
WHERE 条件必须命中索引，否则锁全表
这是最常踩的坑：哪怕你写了 LIMIT 100，如果 WHERE status = 'pending' 没在 status 上建索引，MySQL 就得全表扫描，所有扫过的行都会被加锁——锁住几万行太常见。
检查方式：
MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
- 执行
EXPLAIN UPDATE ...（注意：MySQL 8.0.19+ 支持，低版本用EXPLAIN SELECT ...类比） - 确认
key列非NULL，rows值接近你要更新的行数，而不是表总行数 - 复合索引优先考虑
(status, id)——既加速 WHERE 过滤，又支撑ORDER BY id无排序
考虑 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE？
仅当业务允许“先删后插”语义时才适用。它们本质是写操作，但锁行为不同：INSERT ... ON DUPLICATE KEY UPDATE 只对冲突的唯一键值加锁，不锁整个扫描范围。例如：
INSERT INTO user_stats (user_id, login_count) VALUES (123, 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1;
优势：
- 只锁
user_id = 123对应的那条记录（假设user_id是主键或唯一索引） - 天然幂等，适合计数类更新
- 但不适用于需要基于旧值计算的复杂逻辑（比如
SET balance = balance - amount WHERE balance >= amount）
真正难处理的是带条件判断的更新，比如“余额充足才扣款”，这种必须用带 SELECT ... FOR UPDATE 的显式事务，而锁优化的关键就落在“查多少、锁多窄”上——别忘了给 WHERE 条件字段加索引，这是所有优化的前提。
