为什么SQL多表JOIN更新UPDATE语句在生产中容易锁全表?

风静小哥_1259

风静小哥_1259

2026-11-19

534人浏览

原创

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

为什么sql多表join更新update语句在生产中容易锁全表?

多表 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 ,下一批起点必须是上一批的 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')时,中间若有新插入,会导致漏更或重复。

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5103

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1349

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7201

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3823

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7180

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

10101

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1370

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1192

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
phpStudy极速入门视频教程
phpStudy极速入门视频教程

共6课时 | 54.7万人学习

独孤九贱(4)_PHP视频教程
独孤九贱(4)_PHP视频教程

共89课时 | 134.3万人学习