如何高效批量更新表中外键列，解决30万条记录更新超时问题？

原语句性能问题核心原因 #

你当前使用的逐行关联子查询写法，每更新EMA_SUMO_CM_MEMBER表的1行数据，就需要对EMA_SUMO_SUBSCRIPTION_MIRROR表执行1次查询匹配，30万行数据就要触发30万次查表操作，没有索引支撑的情况下会产生大量全表扫描开销，自然会出现执行超时的问题。

前置通用优化（所有方案都建议先做） #

给关联列添加索引，大幅降低匹配查询的开销，如果SUBSCRIPTION_NUMBER列值唯一，建议创建唯一索引：

CREATE INDEX idx_ema_sumo_subs_num ON D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR(SUBSCRIPTION_NUMBER);

可选优化方案 #

方案1：关联批量更新（适合无业务锁冲突的场景，效率最高） #

不同数据库的语法略有区别，一次性完成全量匹配更新，比子查询写法效率高3~10倍：

• Oracle数据库用MERGE语法：

  MERGE INTO D00EMA01.EMA_SUMO_CM_MEMBER sm
  USING D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR sb
  ON (sm.MEMBER_NUMBER = sb.SUBSCRIPTION_NUMBER)
  WHEN MATCHED THEN
    UPDATE SET sm.SUBSCRIPTION_ID = sb.SUBSCRIPTION_ID;
  

• MySQL数据库用多表关联UPDATE语法：

  UPDATE D00EMA01.EMA_SUMO_CM_MEMBER sm
  INNER JOIN D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR sb
  ON sm.MEMBER_NUMBER = sb.SUBSCRIPTION_NUMBER
  SET sm.SUBSCRIPTION_ID = sb.SUBSCRIPTION_ID;
  

方案2：分批更新（适合生产环境有持续读写的业务表，避免长事务锁表） #

如果担心一次性更新30万行导致锁表时间过长、事务日志暴增，可以按固定批次拆分更新，每批更新完成后提交事务：

• Oracle分批写法：

  DECLARE
    v_batch_size NUMBER := 5000; -- 每批更新5000条，可根据实际调整大小
    v_updated_count NUMBER := 0;
  BEGIN
    LOOP
      UPDATE D00EMA01.EMA_SUMO_CM_MEMBER sm
      SET sm.SUBSCRIPTION_ID = (SELECT sb.SUBSCRIPTION_ID FROM D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR sb WHERE sb.SUBSCRIPTION_NUMBER = sm.MEMBER_NUMBER)
      WHERE sm.SUBSCRIPTION_ID IS NULL
      AND ROWNUM <= v_batch_size;
      
      v_updated_count := SQL%ROWCOUNT;
      COMMIT;
      EXIT WHEN v_updated_count = 0;
    END LOOP;
  END;
  /
  

• MySQL分批写法：

  WHILE ROW_COUNT() > 0 DO
    UPDATE D00EMA01.EMA_SUMO_CM_MEMBER sm
    INNER JOIN D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR sb
    ON sm.MEMBER_NUMBER = sb.SUBSCRIPTION_NUMBER
    SET sm.SUBSCRIPTION_ID = sb.SUBSCRIPTION_ID
    WHERE sm.SUBSCRIPTION_ID IS NULL
    LIMIT 5000;
  END WHILE;
  

额外提速技巧 #

• 更新前可以暂时关闭目标表的非必要索引、触发器，更新完成后再重建开启，能再提升30%~50%的更新速度
• 尽量选择业务低峰期执行更新操作，避免行锁冲突导致执行变慢

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