40万行记录场景下带关联子查询的SQL UPDATE语句运行过慢优化咨询

1. 新增关联字段索引 #

这是优先级最高的优化手段，你的语句关联条件是barcode字段，首先给itemOri表的barcode加索引，若barcode为唯一值建议加唯一索引，查询效率更高：

-- 给itemOri的关联字段加索引，唯一场景用UNIQUE INDEX
CREATE UNIQUE INDEX idx_itemori_barcode ON itemOri(barcode);
-- 可选：给items表的barcode也加索引
CREATE INDEX idx_items_barcode ON items(barcode);

2. 替换子查询为关联更新写法 #

原写法是关联子查询，相当于每扫描一条items记录就要执行一次子查询，40万条数据就要执行40万次查询，效率极低，改成JOIN更新的写法可以大幅降低执行开销，不同数据库写法略有差异：

MySQL版本 #

UPDATE items i
INNER JOIN itemOri o ON i.barcode = o.barcode
SET i.itemId = o.id;

PostgreSQL版本 #

UPDATE items i
SET itemId = o.id
FROM itemOri o
WHERE i.barcode = o.barcode;

3. 分批更新（生产环境优先选择） #

40万条数据一次性更新会触发长事务，锁表时间过长影响其他业务操作，同时会产生大量事务日志，建议按批次拆分更新，示例（基于items表有自增主键id的场景）：

-- 每次处理1000条，可根据实际情况调整批次大小
SET @min_id = (SELECT MIN(id) FROM items);
SET @max_id = (SELECT MAX(id) FROM items);
SET @batch_size = 1000;

WHILE @min_id <= @max_id DO
  UPDATE items i
  INNER JOIN itemOri o ON i.barcode = o.barcode
  SET i.itemId = o.id
  WHERE i.id BETWEEN @min_id AND @min_id + @batch_size - 1
  -- 可选：过滤已经更新过的记录，减少重复操作
  AND i.itemId IS NULL;
  
  SET @min_id = @min_id + @batch_size;
  -- 可选：加短休眠，降低对业务的影响
  DO SLEEP(0.1);
END WHILE;

4. 额外优化建议 #

• 提前过滤无效更新：如果部分记录已经有正确的itemId，可以在更新条件中加i.itemId IS NULL或者i.itemId <> o.id，减少需要处理的行数
• 临时关闭非必要索引：如果items表的itemId字段本身建有索引，更新前可以先删除该索引，全量更新完成后再重建，避免更新过程中频繁维护索引带来的额外开销
• 操作前提前备份数据，避免更新逻辑出错导致数据丢失

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