高效编写SQL：BILL_ADJ_MSG表消息码批量更新规则实现

需求说明 #

• 现有BILL_ADJ_MSG表由复杂存储过程填充，不修改原有填充逻辑，仅在数据填充完成后执行UPDATE操作
• 处理规则：
  • 若某条记录中存在除161、162之外的其他有效消息码，则将该记录中的161、162替换为空格
  • 若记录中仅包含161、162（或其中一个），则保留这些值

原表示例数据 #

B_NO    MSG_1   MSG_2   MSG_3   MSG_4
----------------------------------------
100     UA1     16       15      162
200     161     UA161           
300     162
400     161     162

期望更新结果 #

B_NO    MSG_1       MSG_2       MSG_3       MSG_4
-------------------------------------------------------------------------
100     UA1         16              15      
200                 UA161                       
300     162     
400     161         162

当前尝试的SQL #

UPDATE BILL_ADJ_MSG
SET MSG_1 = 
CASE
WHEN MSG_1 IN ('161','162')
THEN ' '
ELSE MSG_1
END,
---SAME FOR MSG_2, MSG_3, MSG_4 --
WHERE 
TRIM(MSG_1)||TRIM(MSG_2)||TRIM(MSG_3)||TRIM(MSG_4) NOT IN ('161162','162161', '161','162');

优化实现方案 #

针对200万条记录的大表场景，从逻辑准确性、性能两方面优化：

1. 修正WHERE条件逻辑 #

原语句的字符串拼接方式存在逻辑漏洞（例如不同消息码拼接后可能误匹配排除列表），且无法利用索引，改用EXISTS子查询直接判断是否存在非目标消息码：

UPDATE BILL_ADJ_MSG
SET 
    MSG_1 = CASE WHEN MSG_1 IN ('161', '162') THEN ' ' ELSE MSG_1 END,
    MSG_2 = CASE WHEN MSG_2 IN ('161', '162') THEN ' ' ELSE MSG_2 END,
    MSG_3 = CASE WHEN MSG_3 IN ('161', '162') THEN ' ' ELSE MSG_3 END,
    MSG_4 = CASE WHEN MSG_4 IN ('161', '162') THEN ' ' ELSE MSG_4 END
WHERE EXISTS (
    SELECT 1
    FROM (VALUES
        (TRIM(MSG_1)), (TRIM(MSG_2)), (TRIM(MSG_3)), (TRIM(MSG_4))
    ) AS t(val)
    WHERE val IS NOT NULL 
      AND val <> '' 
      AND val NOT IN ('161', '162')
);

2. 大表分批更新 #

一次性更新大量记录易导致锁表、事务日志膨胀，按主键B_NO分批处理：

DECLARE @BatchSize INT = 10000;
DECLARE @MaxB_NO INT = (SELECT MAX(B_NO) FROM BILL_ADJ_MSG);
DECLARE @CurrentB_NO INT = (SELECT MIN(B_NO) FROM BILL_ADJ_MSG);

WHILE @CurrentB_NO <= @MaxB_NO
BEGIN
    UPDATE BILL_ADJ_MSG
    SET 
        MSG_1 = CASE WHEN MSG_1 IN ('161', '162') THEN ' ' ELSE MSG_1 END,
        MSG_2 = CASE WHEN MSG_2 IN ('161', '162') THEN ' ' ELSE MSG_2 END,
        MSG_3 = CASE WHEN MSG_3 IN ('161', '162') THEN ' ' ELSE MSG_3 END,
        MSG_4 = CASE WHEN MSG_4 IN ('161', '162') THEN ' ' ELSE MSG_4 END
    WHERE B_NO BETWEEN @CurrentB_NO AND @CurrentB_NO + @BatchSize - 1
      AND EXISTS (
          SELECT 1
          FROM (VALUES
              (TRIM(MSG_1)), (TRIM(MSG_2)), (TRIM(MSG_3)), (TRIM(MSG_4))
          ) AS t(val)
          WHERE val IS NOT NULL 
            AND val <> '' 
            AND val NOT IN ('161', '162')
      );
    
    SET @CurrentB_NO = @CurrentB_NO + @BatchSize;
    WAITFOR DELAY '00:00:01'; -- 可选，降低资源占用峰值
END

3. 额外性能建议 #

• 若B_NO无索引，临时创建索引可加速分批定位，更新完成后可按需删除
• 针对SQL Server：更新前将数据库切换为批量日志恢复模式，减少事务日志膨胀；更新后切回原模式
• 针对MySQL：调整innodb_log_file_size参数，提升大更新场景下的日志写入效率

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