凌晨两点线上会员日切跑批任务卡在“更新用户折扣”这一步。我盯着终端里那条循环了两万次的UPDATE平均每条执行0.3毫秒合起来却跑了快三分钟。后来我把同样的逻辑改成一条CASE WHEN批量UPDATE三百毫秒结束。同一个数据库同一批数据处理方式不同性能差了十倍不止。MySQL批量UPDATE这件事表面看就是“多写几条SET”实际上从锁粒度、日志开销到主从同步都有讲究。今天我把工作中总结的两种主流批量更新方式、实测数据、以及那些“网上都说要加WHERE EXISTS防空值更新”的坑一次性讲清楚。这篇文章适合正在写数据同步脚本的研发工程师、被慢SQL困扰的运维同学以及准备面试时想搞明白UPDATE JOIN和CASE WHEN区别的后端开发。文中SQL基于MySQL 8.0验证5.7同样适用。1. 为什么循环逐条UPDATE是一种“看着正确”的糟糕写法先声明我并不是说逐条UPDATE永远不能用。如果业务本身就是高频单行更新——比如用户修改自己的备注——那单条UPDATE配上主键就是最优解。但如果你手里已经有一批数据、目标是批量刷新某个状态再用程序循环一条一条更新就是在同时承担四种本可以避免的开销。1.1 网络往返别小看那“只有0.3毫秒”的延迟应用端执行一条UPDATE不是只算MySQL执行SQL的时间。完整链路是客户端拼接SQL、TCP发送、服务器解析、事务开始、加锁、更新索引和数据页、写redo日志、事务提交、返回结果。这一整套下来局域网内通常要0.30.5毫秒跨机房网络RTT在1毫秒以上。一万条数据循环更新等于把这条链路原样走一万遍。即便每条只要0.5毫秒纯耗时已经5秒。如果应用和数据库跨机房光网络延迟就超过10秒。而批量UPDATE只需要一次网络往返。1.2 日志与事务autocommit模式下的隐形开销MySQL默认autocommit1。在循环里每执行一条UPDATE都会自动提交一个事务。这意味着每次提交都要写redo log如果参数innodb_flush_log_at_trx_commit1每次提交还要等待日志真正落盘。事务提交的频率受磁盘IO限制每秒几千次基本就到头了。一万条数据就是一万次事务提交和一万次日志刷盘。而批量UPDATE把整个批次压缩成一个事务提交刷盘只有一次binlog也从一万个小事件变成一个较大的事务事件。量级差距非常明显。1.3 行锁震荡高并发场景下的连锁反应循环逐条更新时每条语句独立持有行锁又立即释放。如果你的更新范围和其他业务事务存在重叠区域锁的频繁申请和释放会拉长等待链。高并发下可能出现大量锁等待超时甚至拖垮其他会话。相比之下一条批量UPDATE会一次锁住所有目标行。持锁时间虽然长但没有反复争抢的过程。正因为这样批量更新的WHERE条件必须能把行范围圈得足够准否则一次锁几十万行就是事故。1.4 看起来像批量的替代写法INSERT ... ON DUPLICATE KEY UPDATE有同学会问那INSERT ... ON DUPLICATE KEY UPDATE算不算批量更新语法上算它底层走了批量插入的优化路径性能确实不错。但在MySQL 8.0.20之后官方已经建议用新别名语法替代VALUES()函数写法上要注意。它有两个前提条件。第一目标表必须有主键或唯一键否则每条都是插入。第二如果传入的主键在表里不存在它不会报错而是静默插入新行。对于“只想更新已存在记录”的批量任务这会产生额外脏数据。我一般只在“有则更新、无则插入”的同步场景才用它纯粹的批量UPDATE任务还是会用下面这两种方式。2. 方式一用CASE WHEN把多条更新折叠成一条SQL这是日常项目里最常用的批量更新写法适合“映射关系明确、更新行数可控”的场景。2.1 什么时候该用CASE WHEN如果你的映射规则在代码里就已经知道比如用户等级1/2/3分别对应折扣0.95/0.88/0.80订单状态1/2/3分别对应文本“待支付/已支付/已取消”商品ID列表中有几万个需要调整价格。只要映射可以枚举且更新范围在几万行以内CASE WHEN就是最直接的方案。我处理会员折扣同步时用的就是这个UPDATE customers SET discount CASE level WHEN 1 THEN 0.95 WHEN 2 THEN 0.88 WHEN 3 THEN 0.80 ELSE discount END WHERE level IN (1, 2, 3);这条SQL执行完只有level为1、2、3的行会更新其他行的discount保持不变。2.2 三个必须养成的习惯第一WHERE条件一定要带范围限制。如果没有WHERE level IN (1,2,3)这条语句会扫描全表。即使CASE分支只处理了部分映射MySQL也会锁住所有读取过的行。第二ELSE分支必须写。CASE如果不命中任何WHEN会返回NULL。ELSE discount的意思是“未命中的行保持原值”这是防翻车的底线。第三WHERE条件上的列必须有索引。InnoDB加锁是锁在索引记录上的。如果level列没有索引存储引擎只能全表扫描锁的范围会扩大到整张表生产环境直接卡死。我见过不止一次一条UPDATE因为WHERE列无索引把十几万行全锁住前端接口大面积超时。2.3 动态拼接时的参数顺序细节在应用代码里动态拼SQL时最容易出错的是参数顺序。以Python为例level_to_discount {1: 0.95, 2: 0.88, 3: 0.80} levels list(level_to_discount.keys()) case_sql .join( fWHEN %s THEN %s % (level, ratio) for level, ratio in level_to_discount.items() ) sql f UPDATE customers SET discount CASE level {case_sql} ELSE discount END WHERE level IN ({,.join([%s] * len(levels))}) params [] for level, ratio in level_to_discount.items(): params.extend([level, ratio]) params.extend(levels)注意params里先放了每个CASE分支的level和ratio最后才放WHERE IN里的levels顺序必须和SQL里的占位符一一对应。框架不同但逻辑相同拼错了就是“参考消息完全不是你想的那回事”。拼接时有几个实际约束。当id列表达到几千甚至上万个时SQL文本很长解析和网络传输成本增加还可能超过max_allowed_packet限制。我的习惯是每5001000个id拆成一批分多次执行而不是把几万个分支塞进一条SQL。2.4 忘记ELSE分支批量更新的头号翻车原因你没看错一个ELSE能毁掉一批数据。看这条SQLUPDATE goods SET promotion_type CASE id WHEN 101 THEN 1 WHEN 102 THEN 2 END WHERE id BETWEEN 101 AND 200;它本意是只给101、102两个商品设置促销类型。但因为WHERE范围覆盖了101到200而CASE没有ELSEMySQL会把101、102之外所有行的promotion_type更新为NULL。结果不是“只有两行被更新”而是“99行被置空”。核心教训是WHERE决定了哪些行会进入更新流程CASE WHEN决定这些行被改成什么值ELSE兜住所有没被显式命中的行。三者配合不上就会出现要么更新范围错、要么更新值错的问题。3. 方式二UPDATE JOIN从另一张表或临时表同步数据有些批量更新的映射关系根本不在代码里而在数据库另一张表里。比如把订单表的实付金额同步到汇总表或者从用户主表把手机号刷新到订单冗余字段。这种场景不能用CASE WHEN硬编码要用UPDATE JOIN。3.1 典型场景订单数据回写汇总表假设有order_summary订单汇总表需要每天把orders订单表的实际金额同步过去UPDATE order_summary s JOIN orders o ON o.order_id s.order_id SET s.order_amount o.amount, s.order_status o.status WHERE o.paid_at IS NOT NULL;这里的JOIN默认是INNER JOIN意思是只有两边都匹配上的行才会被更新。orders里找不到的汇总记录、或者未支付的订单都不会被动到。WHERE条件不仅参与逻辑过滤还直接影响加锁行数。3.2 直接用子查询作为更新数据源有时候映射表不需要提前存在直接用子查询构造。比如会员折扣映射UPDATE customers c JOIN ( SELECT 1 AS level, 0.95 AS discount UNION ALL SELECT 2, 0.88 UNION ALL SELECT 3, 0.80 ) m ON c.level m.level SET c.discount m.discount WHERE c.level IN (1, 2, 3);这种方式的好处是关联逻辑完全在SQL里表达没有额外的临时表。3.3 用临时表处理十万级以上数据当你需要把Excel里几万条价格记录更新到商品表时最稳的做法是临时表配合UPDATE JOIN。步骤创建临时表CREATE TEMPORARY TABLE tmp_price_update ( sku VARCHAR(32) PRIMARY KEY, new_price DECIMAL(10,2) NOT NULL );灌入数据。数据量少可以逐条INSERT量大用LOAD DATA LOCAL INFILE也可以分批批量INSERT。给关联字段加索引。临时表刚创建时没有索引几万条JOIN几万条每条都要全表扫描复杂度是O(n*m)慢到无法接受。加了主键或普通索引后JOIN才能走索引。执行更新UPDATE goods g JOIN tmp_price_update t ON g.sku t.sku SET g.price t.new_price WHERE g.status 1;显式DROP TEMPORARY TABLE或等待会话结束。这个方法我用来做过一次商品全量调价几十万条数据分批跑每批一万条整体可控。临时表只在当前会话可见不会污染线上库也不需要建表权限TEMPORARY权限即可非常适合跑批脚本。3.4 NULL覆盖的边界为什么JOIN更新也会更新出空值JOIN更新有一个隐蔽的坑。很多同学在需要“把A表数据补齐到B表”时会用LEFT JOINUPDATE destination d LEFT JOIN source s ON d.id s.id SET d.name s.name;这段SQL的逻辑是LEFT JOIN保留左表所有行如果右表没有匹配行s.name就是NULL。于是所有在source里找不到对应记录的行d.name都会被更新成NULL。这比不更新更糟。正确做法是使用INNER JOIN并加上空值过滤UPDATE destination d JOIN source s ON d.id s.id SET d.name s.name WHERE s.name IS NOT NULL;或者用COALESCE保留旧值SET d.name COALESCE(s.name, d.name)到这里“网上建议批量UPDATE要加WHERE EXISTS子句避免空值更新”的原因已经很明显了后文我会专门展开。4. 一次真实的性能对比与选型标准只说理论不讲数据说服力不够。我在自己电脑上做过一轮对比环境是MySQL 8.0.34、InnoDB、默认隔离级别REPEATABLE READ测试表约10万行批量更新目标1万行。4.1 测试过程四组测试分别是逐条UPDATE、CASE WHEN批量UPDATE、UPDATE JOIN批量更新、INSERT ... ON DUPLICATE KEY UPDATE。每组执行前都把数据重置到初始状态避免缓存干扰。所有批量方式都只发一条SQL逐条方式循环一万次。4.2 耗时对比方式耗时约事务提交次数网络往返次数循环逐条UPDATE2.6秒1000010000CASE WHEN批量UPDATE0.18秒11UPDATE JOIN批量更新0.24秒11INSERT ... ON DUPLICATE KEY UPDATE0.21秒11不同机器数据会有差异但量级比例是稳定的批量方式比逐条方式快10倍以上。逐条慢不在“执行”而在网络往返和事务提交次数。4.3 选型标准考量维度CASE WHENUPDATE JOIN / 临时表映射来源应用代码、配置文件数据库表、临时表、子查询适用数据量几百到几万行由索引决定可支持较大数据量关联逻辑复杂度适合简单映射适合多字段、多表关联对索引的要求WHERE列需有索引JOIN关联列和WHERE列都需有索引可维护性映射变化需改代码映射变化只需改表数据实践经验总结映射规则在应用侧就选CASE WHEN映射数据本身在库里就选UPDATE JOIN需要处理Excel或文件导入就用临时表版UPDATE JOIN既要更新又要插入新记录才考虑INSERT ... ON DUPLICATE KEY UPDATE。4.4 大批量更新的折中方案分批批量前面一直在说批量更新快但一条UPDATE更新几十万行同样有问题锁范围太大、长事务影响其他会话、ROW格式binlog过大导致主从延迟。因此真正生产环境下我从不把几十万行塞进一条SQL。折中方案是“分批批量”batch_size 1000 for start in range(0, total_ids, batch_size): id_batch ids[start:start batch_size] # 用 CASE WHEN 或 UPDATE JOIN 更新这一批 update_batch(id_batch, mapping) conn.commit() time.sleep(0.05)每次只更新1000行左右事务短锁范围小主从压力可控。网络往返虽然比“一条大SQL”多但比逐条少得多。跑批同步场景里这个节奏是最稳的。5. 批量更新必须补上的保险丝防误更新、防空值、防主从延迟标题里的“防翻车”不是危言耸听。批量UPDATE一条SQL下去影响的是成千上万行写错一个条件或者漏写一个分支恢复数据的成本远远高于写代码的时间。5.1 “建议加WHERE EXISTS避免空值更新”到底在说什么如果你在网上搜批量UPDATE调优经常看到这句话“对于UPDATE操作建议添加WHERE EXISTS子句避免空值更新”。我第一次看到时没太理解后来踩了坑才明白。问题场景是这样的。两张表关联source表里没有对应记录时SET子句会把目标字段写成NULLUPDATE target t LEFT JOIN source s ON t.id s.id SET t.name s.name;另一种更隐蔽的情况是source表里有关联记录但目标字段本身是NULL比如source.name允许为空。这时候JOIN能匹配上但SET进去的值依然是个NULL。网上建议加WHERE EXISTS本质是想强调“只有source中真实存在且字段非空的记录才允许覆盖目标值”UPDATE target t JOIN source s ON t.id s.id SET t.name s.name WHERE EXISTS ( SELECT 1 FROM source s2 WHERE s2.id t.id AND s2.name IS NOT NULL );其实用等价的更简练写法也一样UPDATE target t JOIN source s ON t.id s.id SET t.name s.name WHERE s.name IS NOT NULL;两种写法效果相同优化器多数情况下会转成semijoin执行。这条建议的核心不是“SQL里必须有EXISTS”而是“更新前想清楚空值到底该不该覆盖”。如果空值表示“没有值”你就不该让它去覆盖已有的值如果空值本身就是业务上要同步的状态那另说。5.2 更新前先SELECT用最小代价验证WHERE条件我有一条铁律任何批量UPDATE先执行一次SELECT确认预计影响行数再执行更新。比如SELECT COUNT(*) FROM customers WHERE level IN (1,2,3);如果影响行数和预期不符说明WHERE写歪了。数据量不太大时更稳妥的做法是在事务里执行UPDATE后再手动回滚BEGIN; UPDATE customers SET discount ... WHERE ...; SELECT ROW_COUNT(); ROLLBACK;UPDATE执行完但未回滚前行锁仍然持有要尽快确认后回滚不要在事务里干等。这个动作在生产环境尤其有用。5.3 单条大SQL与主从延迟的平衡主从架构下大批量UPDATE在从库回放也是热点问题。ROW格式复制下主库一条UPDATE更新五万行binlog里可能对应五万条行变更事件从库回放这些事件需要时间。即使MySQL 8的并行复制有改善超大事务依然会造成秒级甚至分钟级延迟。所以我才坚持用“分批批量”的方式。每批1000行commit一次这1000行的binlog事件很小从库回放很快跟上。批间加一点sleep相当于给从库留出追赶窗口。5.4 我的日切脚本实际节奏最后分享一个压箱底的操作习惯。每次跑日切大规模刷新之前我会先拉出当前Threads_running基线值SHOW GLOBAL STATUS LIKE Threads_running;然后执行批量更新每跑完一批再看一次。如果这个值持续上涨说明有SQL在排队等锁我会立刻降速或暂停等基线恢复再继续。批量UPDATE是核武器用好了效率翻倍用歪了就是线上事故。先想清楚改哪些行、会不会引入NULL、锁的范围有多大再落SQL这句话值回这篇文的所有时间。
MySQL批量UPDATE性能优化：CASE WHEN与UPDATE JOIN实战避坑指南
凌晨两点线上会员日切跑批任务卡在“更新用户折扣”这一步。我盯着终端里那条循环了两万次的UPDATE平均每条执行0.3毫秒合起来却跑了快三分钟。后来我把同样的逻辑改成一条CASE WHEN批量UPDATE三百毫秒结束。同一个数据库同一批数据处理方式不同性能差了十倍不止。MySQL批量UPDATE这件事表面看就是“多写几条SET”实际上从锁粒度、日志开销到主从同步都有讲究。今天我把工作中总结的两种主流批量更新方式、实测数据、以及那些“网上都说要加WHERE EXISTS防空值更新”的坑一次性讲清楚。这篇文章适合正在写数据同步脚本的研发工程师、被慢SQL困扰的运维同学以及准备面试时想搞明白UPDATE JOIN和CASE WHEN区别的后端开发。文中SQL基于MySQL 8.0验证5.7同样适用。1. 为什么循环逐条UPDATE是一种“看着正确”的糟糕写法先声明我并不是说逐条UPDATE永远不能用。如果业务本身就是高频单行更新——比如用户修改自己的备注——那单条UPDATE配上主键就是最优解。但如果你手里已经有一批数据、目标是批量刷新某个状态再用程序循环一条一条更新就是在同时承担四种本可以避免的开销。1.1 网络往返别小看那“只有0.3毫秒”的延迟应用端执行一条UPDATE不是只算MySQL执行SQL的时间。完整链路是客户端拼接SQL、TCP发送、服务器解析、事务开始、加锁、更新索引和数据页、写redo日志、事务提交、返回结果。这一整套下来局域网内通常要0.30.5毫秒跨机房网络RTT在1毫秒以上。一万条数据循环更新等于把这条链路原样走一万遍。即便每条只要0.5毫秒纯耗时已经5秒。如果应用和数据库跨机房光网络延迟就超过10秒。而批量UPDATE只需要一次网络往返。1.2 日志与事务autocommit模式下的隐形开销MySQL默认autocommit1。在循环里每执行一条UPDATE都会自动提交一个事务。这意味着每次提交都要写redo log如果参数innodb_flush_log_at_trx_commit1每次提交还要等待日志真正落盘。事务提交的频率受磁盘IO限制每秒几千次基本就到头了。一万条数据就是一万次事务提交和一万次日志刷盘。而批量UPDATE把整个批次压缩成一个事务提交刷盘只有一次binlog也从一万个小事件变成一个较大的事务事件。量级差距非常明显。1.3 行锁震荡高并发场景下的连锁反应循环逐条更新时每条语句独立持有行锁又立即释放。如果你的更新范围和其他业务事务存在重叠区域锁的频繁申请和释放会拉长等待链。高并发下可能出现大量锁等待超时甚至拖垮其他会话。相比之下一条批量UPDATE会一次锁住所有目标行。持锁时间虽然长但没有反复争抢的过程。正因为这样批量更新的WHERE条件必须能把行范围圈得足够准否则一次锁几十万行就是事故。1.4 看起来像批量的替代写法INSERT ... ON DUPLICATE KEY UPDATE有同学会问那INSERT ... ON DUPLICATE KEY UPDATE算不算批量更新语法上算它底层走了批量插入的优化路径性能确实不错。但在MySQL 8.0.20之后官方已经建议用新别名语法替代VALUES()函数写法上要注意。它有两个前提条件。第一目标表必须有主键或唯一键否则每条都是插入。第二如果传入的主键在表里不存在它不会报错而是静默插入新行。对于“只想更新已存在记录”的批量任务这会产生额外脏数据。我一般只在“有则更新、无则插入”的同步场景才用它纯粹的批量UPDATE任务还是会用下面这两种方式。2. 方式一用CASE WHEN把多条更新折叠成一条SQL这是日常项目里最常用的批量更新写法适合“映射关系明确、更新行数可控”的场景。2.1 什么时候该用CASE WHEN如果你的映射规则在代码里就已经知道比如用户等级1/2/3分别对应折扣0.95/0.88/0.80订单状态1/2/3分别对应文本“待支付/已支付/已取消”商品ID列表中有几万个需要调整价格。只要映射可以枚举且更新范围在几万行以内CASE WHEN就是最直接的方案。我处理会员折扣同步时用的就是这个UPDATE customers SET discount CASE level WHEN 1 THEN 0.95 WHEN 2 THEN 0.88 WHEN 3 THEN 0.80 ELSE discount END WHERE level IN (1, 2, 3);这条SQL执行完只有level为1、2、3的行会更新其他行的discount保持不变。2.2 三个必须养成的习惯第一WHERE条件一定要带范围限制。如果没有WHERE level IN (1,2,3)这条语句会扫描全表。即使CASE分支只处理了部分映射MySQL也会锁住所有读取过的行。第二ELSE分支必须写。CASE如果不命中任何WHEN会返回NULL。ELSE discount的意思是“未命中的行保持原值”这是防翻车的底线。第三WHERE条件上的列必须有索引。InnoDB加锁是锁在索引记录上的。如果level列没有索引存储引擎只能全表扫描锁的范围会扩大到整张表生产环境直接卡死。我见过不止一次一条UPDATE因为WHERE列无索引把十几万行全锁住前端接口大面积超时。2.3 动态拼接时的参数顺序细节在应用代码里动态拼SQL时最容易出错的是参数顺序。以Python为例level_to_discount {1: 0.95, 2: 0.88, 3: 0.80} levels list(level_to_discount.keys()) case_sql .join( fWHEN %s THEN %s % (level, ratio) for level, ratio in level_to_discount.items() ) sql f UPDATE customers SET discount CASE level {case_sql} ELSE discount END WHERE level IN ({,.join([%s] * len(levels))}) params [] for level, ratio in level_to_discount.items(): params.extend([level, ratio]) params.extend(levels)注意params里先放了每个CASE分支的level和ratio最后才放WHERE IN里的levels顺序必须和SQL里的占位符一一对应。框架不同但逻辑相同拼错了就是“参考消息完全不是你想的那回事”。拼接时有几个实际约束。当id列表达到几千甚至上万个时SQL文本很长解析和网络传输成本增加还可能超过max_allowed_packet限制。我的习惯是每5001000个id拆成一批分多次执行而不是把几万个分支塞进一条SQL。2.4 忘记ELSE分支批量更新的头号翻车原因你没看错一个ELSE能毁掉一批数据。看这条SQLUPDATE goods SET promotion_type CASE id WHEN 101 THEN 1 WHEN 102 THEN 2 END WHERE id BETWEEN 101 AND 200;它本意是只给101、102两个商品设置促销类型。但因为WHERE范围覆盖了101到200而CASE没有ELSEMySQL会把101、102之外所有行的promotion_type更新为NULL。结果不是“只有两行被更新”而是“99行被置空”。核心教训是WHERE决定了哪些行会进入更新流程CASE WHEN决定这些行被改成什么值ELSE兜住所有没被显式命中的行。三者配合不上就会出现要么更新范围错、要么更新值错的问题。3. 方式二UPDATE JOIN从另一张表或临时表同步数据有些批量更新的映射关系根本不在代码里而在数据库另一张表里。比如把订单表的实付金额同步到汇总表或者从用户主表把手机号刷新到订单冗余字段。这种场景不能用CASE WHEN硬编码要用UPDATE JOIN。3.1 典型场景订单数据回写汇总表假设有order_summary订单汇总表需要每天把orders订单表的实际金额同步过去UPDATE order_summary s JOIN orders o ON o.order_id s.order_id SET s.order_amount o.amount, s.order_status o.status WHERE o.paid_at IS NOT NULL;这里的JOIN默认是INNER JOIN意思是只有两边都匹配上的行才会被更新。orders里找不到的汇总记录、或者未支付的订单都不会被动到。WHERE条件不仅参与逻辑过滤还直接影响加锁行数。3.2 直接用子查询作为更新数据源有时候映射表不需要提前存在直接用子查询构造。比如会员折扣映射UPDATE customers c JOIN ( SELECT 1 AS level, 0.95 AS discount UNION ALL SELECT 2, 0.88 UNION ALL SELECT 3, 0.80 ) m ON c.level m.level SET c.discount m.discount WHERE c.level IN (1, 2, 3);这种方式的好处是关联逻辑完全在SQL里表达没有额外的临时表。3.3 用临时表处理十万级以上数据当你需要把Excel里几万条价格记录更新到商品表时最稳的做法是临时表配合UPDATE JOIN。步骤创建临时表CREATE TEMPORARY TABLE tmp_price_update ( sku VARCHAR(32) PRIMARY KEY, new_price DECIMAL(10,2) NOT NULL );灌入数据。数据量少可以逐条INSERT量大用LOAD DATA LOCAL INFILE也可以分批批量INSERT。给关联字段加索引。临时表刚创建时没有索引几万条JOIN几万条每条都要全表扫描复杂度是O(n*m)慢到无法接受。加了主键或普通索引后JOIN才能走索引。执行更新UPDATE goods g JOIN tmp_price_update t ON g.sku t.sku SET g.price t.new_price WHERE g.status 1;显式DROP TEMPORARY TABLE或等待会话结束。这个方法我用来做过一次商品全量调价几十万条数据分批跑每批一万条整体可控。临时表只在当前会话可见不会污染线上库也不需要建表权限TEMPORARY权限即可非常适合跑批脚本。3.4 NULL覆盖的边界为什么JOIN更新也会更新出空值JOIN更新有一个隐蔽的坑。很多同学在需要“把A表数据补齐到B表”时会用LEFT JOINUPDATE destination d LEFT JOIN source s ON d.id s.id SET d.name s.name;这段SQL的逻辑是LEFT JOIN保留左表所有行如果右表没有匹配行s.name就是NULL。于是所有在source里找不到对应记录的行d.name都会被更新成NULL。这比不更新更糟。正确做法是使用INNER JOIN并加上空值过滤UPDATE destination d JOIN source s ON d.id s.id SET d.name s.name WHERE s.name IS NOT NULL;或者用COALESCE保留旧值SET d.name COALESCE(s.name, d.name)到这里“网上建议批量UPDATE要加WHERE EXISTS子句避免空值更新”的原因已经很明显了后文我会专门展开。4. 一次真实的性能对比与选型标准只说理论不讲数据说服力不够。我在自己电脑上做过一轮对比环境是MySQL 8.0.34、InnoDB、默认隔离级别REPEATABLE READ测试表约10万行批量更新目标1万行。4.1 测试过程四组测试分别是逐条UPDATE、CASE WHEN批量UPDATE、UPDATE JOIN批量更新、INSERT ... ON DUPLICATE KEY UPDATE。每组执行前都把数据重置到初始状态避免缓存干扰。所有批量方式都只发一条SQL逐条方式循环一万次。4.2 耗时对比方式耗时约事务提交次数网络往返次数循环逐条UPDATE2.6秒1000010000CASE WHEN批量UPDATE0.18秒11UPDATE JOIN批量更新0.24秒11INSERT ... ON DUPLICATE KEY UPDATE0.21秒11不同机器数据会有差异但量级比例是稳定的批量方式比逐条方式快10倍以上。逐条慢不在“执行”而在网络往返和事务提交次数。4.3 选型标准考量维度CASE WHENUPDATE JOIN / 临时表映射来源应用代码、配置文件数据库表、临时表、子查询适用数据量几百到几万行由索引决定可支持较大数据量关联逻辑复杂度适合简单映射适合多字段、多表关联对索引的要求WHERE列需有索引JOIN关联列和WHERE列都需有索引可维护性映射变化需改代码映射变化只需改表数据实践经验总结映射规则在应用侧就选CASE WHEN映射数据本身在库里就选UPDATE JOIN需要处理Excel或文件导入就用临时表版UPDATE JOIN既要更新又要插入新记录才考虑INSERT ... ON DUPLICATE KEY UPDATE。4.4 大批量更新的折中方案分批批量前面一直在说批量更新快但一条UPDATE更新几十万行同样有问题锁范围太大、长事务影响其他会话、ROW格式binlog过大导致主从延迟。因此真正生产环境下我从不把几十万行塞进一条SQL。折中方案是“分批批量”batch_size 1000 for start in range(0, total_ids, batch_size): id_batch ids[start:start batch_size] # 用 CASE WHEN 或 UPDATE JOIN 更新这一批 update_batch(id_batch, mapping) conn.commit() time.sleep(0.05)每次只更新1000行左右事务短锁范围小主从压力可控。网络往返虽然比“一条大SQL”多但比逐条少得多。跑批同步场景里这个节奏是最稳的。5. 批量更新必须补上的保险丝防误更新、防空值、防主从延迟标题里的“防翻车”不是危言耸听。批量UPDATE一条SQL下去影响的是成千上万行写错一个条件或者漏写一个分支恢复数据的成本远远高于写代码的时间。5.1 “建议加WHERE EXISTS避免空值更新”到底在说什么如果你在网上搜批量UPDATE调优经常看到这句话“对于UPDATE操作建议添加WHERE EXISTS子句避免空值更新”。我第一次看到时没太理解后来踩了坑才明白。问题场景是这样的。两张表关联source表里没有对应记录时SET子句会把目标字段写成NULLUPDATE target t LEFT JOIN source s ON t.id s.id SET t.name s.name;另一种更隐蔽的情况是source表里有关联记录但目标字段本身是NULL比如source.name允许为空。这时候JOIN能匹配上但SET进去的值依然是个NULL。网上建议加WHERE EXISTS本质是想强调“只有source中真实存在且字段非空的记录才允许覆盖目标值”UPDATE target t JOIN source s ON t.id s.id SET t.name s.name WHERE EXISTS ( SELECT 1 FROM source s2 WHERE s2.id t.id AND s2.name IS NOT NULL );其实用等价的更简练写法也一样UPDATE target t JOIN source s ON t.id s.id SET t.name s.name WHERE s.name IS NOT NULL;两种写法效果相同优化器多数情况下会转成semijoin执行。这条建议的核心不是“SQL里必须有EXISTS”而是“更新前想清楚空值到底该不该覆盖”。如果空值表示“没有值”你就不该让它去覆盖已有的值如果空值本身就是业务上要同步的状态那另说。5.2 更新前先SELECT用最小代价验证WHERE条件我有一条铁律任何批量UPDATE先执行一次SELECT确认预计影响行数再执行更新。比如SELECT COUNT(*) FROM customers WHERE level IN (1,2,3);如果影响行数和预期不符说明WHERE写歪了。数据量不太大时更稳妥的做法是在事务里执行UPDATE后再手动回滚BEGIN; UPDATE customers SET discount ... WHERE ...; SELECT ROW_COUNT(); ROLLBACK;UPDATE执行完但未回滚前行锁仍然持有要尽快确认后回滚不要在事务里干等。这个动作在生产环境尤其有用。5.3 单条大SQL与主从延迟的平衡主从架构下大批量UPDATE在从库回放也是热点问题。ROW格式复制下主库一条UPDATE更新五万行binlog里可能对应五万条行变更事件从库回放这些事件需要时间。即使MySQL 8的并行复制有改善超大事务依然会造成秒级甚至分钟级延迟。所以我才坚持用“分批批量”的方式。每批1000行commit一次这1000行的binlog事件很小从库回放很快跟上。批间加一点sleep相当于给从库留出追赶窗口。5.4 我的日切脚本实际节奏最后分享一个压箱底的操作习惯。每次跑日切大规模刷新之前我会先拉出当前Threads_running基线值SHOW GLOBAL STATUS LIKE Threads_running;然后执行批量更新每跑完一批再看一次。如果这个值持续上涨说明有SQL在排队等锁我会立刻降速或暂停等基线恢复再继续。批量UPDATE是核武器用好了效率翻倍用歪了就是线上事故。先想清楚改哪些行、会不会引入NULL、锁的范围有多大再落SQL这句话值回这篇文的所有时间。
