讲师中心 微信公众号

工具推荐

视频效率加速
updream

一键生成高质量推广成片

剧云
剧云

中文短剧剧本制作平台

立刻MV
立刻MV

低成本歌曲 MV、视频生成

AniShort
AniShort

AI短剧协同制作平台

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

风静小哥_1259

风静小哥_1259

发布时间:2026-11-19 11:19:03

|

534人浏览过

|

来源于php中文网

原创

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

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

热门AI工具

更多
笔灵降AI

一款AI论文写作工具,主要用于论文降AI神器,适配知网及维普!一键降至安全线,100%保留原文格式;无口语化问题,文风更学术,降后字数控制最佳!,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

二狗PPT
二狗PPT Hot

一款AI演示文稿工具,主要用于专为中式职场打造的AI PPT生成工具,适合需要提升相关任务效率的用户。

小橡皮AI

一款AI工具,主要用于小橡皮一键去AI味,将机器味重的内容改得更有活人感,防止因为 AI 内容被平台打上 AI 标签,以及限流,适合需要提升相关任务效率的用户。

Seko
Seko Hot

一款AI视频创作工具,主要用于商汤科技推出的创编一体的AI短视频创作Agent,适合需要提升相关任务效率的用户。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

火山引擎

火山引擎是一款面向企业的云计算与AI服务平台。

相关专题

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

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

5103

2023.10.12

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

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

1031

2023.10.27

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

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

1349

2024.02.23

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

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

7201

2024.03.06

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

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

3823

2024.03.06

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

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

7180

2024.04.07

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

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

10101

2024.04.29

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

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

1370

2024.04.29

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

20

2026.11.17

热门下载

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

精品课程

更多
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn