企业官网建设流程全解析

凌晨两点，线上会员日切跑批任务卡在“更新用户折扣”这一步。我盯着终端里那条循环了两万次的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.3～0.5毫秒，跨机房网络RTT在1毫秒以上。

一万条数据循环更新，等于把这条链路原样走一万遍。即便每条只要0.5毫秒，纯耗时已经5秒。如果应用和数据库跨机房，光网络延迟就超过10秒。而批量UPDATE只需要一次网络往返。

1.2 日志与事务：autocommit模式下的隐形开销

MySQL默认autocommit=1。在循环里每执行一条UPDATE，都会自动提交一个事务。这意味着每次提交都要写redo log；如果参数innodb_flush_log_at_trx_commit=1，每次提交还要等待日志真正落盘。事务提交的频率受磁盘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( f"WHEN %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限制。我的习惯是每500～1000个id拆成一批，分多次执行，而不是把几万个分支塞进一条SQL。

2.4 忘记ELSE分支：批量更新的头号翻车原因

你没看错，一个ELSE能毁掉一批数据。看这条SQL：

UPDATE 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没有ELSE，MySQL会把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。步骤：

1. 创建临时表：

CREATE TEMPORARY TABLE tmp_price_update ( sku VARCHAR(32) PRIMARY KEY, new_price DECIMAL(10,2) NOT NULL );

2. 灌入数据。数据量少可以逐条INSERT，量大用LOAD DATA LOCAL INFILE，也可以分批批量INSERT。
3. 给关联字段加索引。临时表刚创建时没有索引，几万条JOIN几万条，每条都要全表扫描，复杂度是O(n*m)，慢到无法接受。加了主键或普通索引后，JOIN才能走索引。
4. 执行更新：

UPDATE goods g JOIN tmp_price_update t ON g.sku = t.sku SET g.price = t.new_price WHERE g.status = 1;

5. 显式DROP TEMPORARY TABLE或等待会话结束。

这个方法我用来做过一次商品全量调价，几十万条数据分批跑，每批一万条，整体可控。临时表只在当前会话可见，不会污染线上库，也不需要建表权限（TEMPORARY权限即可），非常适合跑批脚本。

3.4 NULL覆盖的边界：为什么JOIN更新也会更新出空值

JOIN更新有一个隐蔽的坑。很多同学在需要“把A表数据补齐到B表”时，会用LEFT JOIN：

UPDATE 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 耗时对比

不同机器数据会有差异，但量级比例是稳定的：批量方式比逐条方式快10倍以上。逐条慢不在“执行”，而在网络往返和事务提交次数。

4.3 选型标准

实践经验总结：映射规则在应用侧就选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子句会把目标字段写成NULL：

UPDATE 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，这句话值回这篇文的所有时间。