根本原因是MySQL 5.7执行UPDATE JOIN时被迫全表扫描+持有大量行锁+事务不提交,等效锁表;需确保索引命中、驱动表正确、WHERE不跨表、分批提交并改用单表IN更新。

多表 UPDATE JOIN 在生产中锁全表,根本不是语法写错了,而是 MySQL(尤其是 5.7)执行时被迫全表扫描 + 持有大量行锁 + 事务不提交,最终等效于锁表。只要没走索引、驱动表选错、或 WHERE 条件混用多表字段,就大概率触发。
WHERE 条件跨表或未命中索引 → 全表扫描加锁
MySQL 5.7 的 UPDATE JOIN 默认用嵌套循环(NLJ),优化器决定驱动表。一旦 WHERE 中出现 o.status = 'pending' AND c.country = 'CN' 这类跨表条件,优化器常放弃索引,退化为全表扫描——InnoDB 会对每条扫描到的行加 next-key lock,扫 100 万行就锁 100 万行。
- 用
EXPLAIN FORMAT=TREE看type字段:要是ALL或index,基本就是全表/全索引扫描 -
key为空,或显示的不是你建的索引名,说明没命中 - 复合索引顺序必须匹配:比如
(deleted, create_time)能用上deleted = 0,但(create_time, deleted)就不一定 - 隐式类型转换直接失效:
user_id是BIGINT,却传字符串'123',索引跳过
驱动表选错 → 大表先扫,锁住所有中间匹配行
错误写法:UPDATE orders o JOIN users u ON o.user_id = u.id SET o.status = 'done' WHERE u.reg_date > '2025-01-01'。orders 是大表,MySQL 先全扫它,再过滤,锁住几十万无关订单。
- 正确做法是让小表当驱动表:
UPDATE orders o JOIN (SELECT id FROM users WHERE reg_date > '2025-01-01') u ON o.user_id = u.id SET o.status = 'done' - 验证子查询:单独跑
EXPLAIN SELECT id FROM users WHERE reg_date > ?,确认type是range或ref,rows控制在千级以内 - JOIN 条件字段必须有索引,且类型严格一致;
ON o.user_id = u.id中一边是INT、一边是BIGINT,索引失效
事务未拆分 + 隐式长事务 → 锁升级成间隙锁甚至表锁
MySQL 5.7 默认隔离级别是 REPEATABLE READ,UPDATE JOIN 过程中不提交事务,锁就一直挂着。哪怕只更新几百行,若扫描范围大,InnoDB 会自动补上间隙锁,把相邻空隙也锁死——多个并发查同一个不存在的 order_no,全卡在同一个间隙上。
- 单条语句更新超 5000 行,极易触发锁升级(默认阈值 5000 行),建议批次控制在 1000–5000
- 绝对不要靠
LIMIT OFFSET分批:UPDATE ... LIMIT 5000不解决扫描问题,WHERE 仍可能全表扫,只是最后只改前 5000 行 - 真正安全的分片得用主键游标:
WHERE id > 100000 AND id ,下一批起点必须是上一批的 <code>MAX(id),不是靠COUNT算总页数 - 每批必须显式
COMMIT,锁才真正释放;ORM 中禁用事务内调 HTTP、发邮件等耗时操作
为什么改用单表 UPDATE + IN 更可控?
放弃 UPDATE JOIN,改用应用层收拢 ID 列表,能彻底消除跨表加锁顺序不确定性。MySQL 对 IN 子句的加锁行为明确:只锁列表中实际存在的主键行,不会因 JOIN 引入额外锁。
- 第一步:
SELECT id FROM users WHERE reg_date > '2025-01-01' ORDER BY id(确保顺序一致) - 第二步:
UPDATE orders SET status = 'done' WHERE id IN (101, 102, 105),ID 列表按升序拼接(强制统一加锁顺序) - 注意:
IN列表长度别超 1000,否则可能触发临时表或优化器误判;超量需分批,每批仍保持升序 - 若需动态计算值(如从
customers取region更新orders.status),改用标量子查询:UPDATE orders SET status = (SELECT region FROM customers WHERE customers.id = orders.customer_id LIMIT 1) WHERE customer_id IN (...)
最容易被忽略的一点:分批脚本上线前,必须在从库或备份库完整跑一遍,确认最终更新总数和业务逻辑完全对得上——因为 WHERE 条件里混了状态字段(比如 status = 'pending')时,中间若有新插入,会导致漏更或重复。

















