为何带CASE的单条SQL UPDATE性能不如两条带WHERE的UPDATE？

针对你描述的场景——根据column1是否在(1,2,3)范围内更新column3为'A'或'B'，两种方案的性能差异可以从以下几个核心维度解释：

1. 全表遍历 vs 定向更新 #

• 方案1的单条UPDATE无WHERE子句，数据库会扫描整张表的每一行：无论column3当前值是否已经符合目标结果，都会执行CASE判断并触发写入操作，包含大量无效更新（比如原本column3就是'A'的行，依然会被重复赋值）。
• 方案2的两条UPDATE都带精准WHERE条件，数据库可以直接定位到需要修改的行，只处理符合条件的记录，避免了全表遍历和无意义的写入。

2. 锁资源的持有效率 #

• 方案1需要持有锁的时间更长：因为要处理所有行，更新操作的执行周期被拉长，表锁/行锁的持有时间久，容易引发锁竞争，拖慢整体执行速度。
• 方案2的两次更新都是针对部分行，锁的持有时间更短，锁冲突概率更低，数据库能更快释放锁资源，并发性能更优。

3. 事务日志的IO开销 #

• 方案1中，即使行的column3值没有变化，数据库依然会生成事务日志（因为执行了SET操作），额外增加了日志写入的IO压力。
• 方案2仅对真正需要修改的行生成日志，无效日志的写入量大幅减少，IO消耗更低。

4. 执行计划的优化空间 #

• 方案1的全表UPDATE逻辑简单，优化器很难做精细化调整，只能按全表扫描的路径执行。
• 方案2的每条UPDATE都有明确的过滤条件，若column1上存在索引，优化器会直接选择索引扫描路径，大幅减少磁盘IO和CPU的消耗。

举个直观的例子：假设tableA有100万行，其中30万行符合column1 IN (1,2,3)，70万行符合NOT IN。方案1会强制处理全部100万行，而方案2两次分别处理30万和70万行，但因为是定向扫描+无无效更新，实际执行效率反而更高。

内容的提问来源于stack exchange，提问作者Hoa Tran