百万级大表与小表关联UPDATE查询的优化方案咨询

场景说明 #

我有一张仅数千行数据的table2，以及一张包含数百万行数据的table1（table2是table1的子集），需要依据匹配的记录标识id，用table2的数据更新table1。

初始数据快照 #

table1（百万级数据）：

id  data  moredata
-------------------
1   abc   def
2   ghi   jkl

table2（数千行数据）：

id  data  moredata
-------------------
1   abc   defg

期望更新结果 #

id  data  moredata
 ------------------
 1   abc   defg
 2   ghi   jkl

现有方案的问题 #

常规的UPDATE结合INNER JOIN方案会产生近乎m*n量级的比较操作（m为table1行数，n为table2行数），对百万级的table1来说效率极低，现有语句如下：

UPDATE table1
SET table1.moredata = table2.moredata 
FROM table1 
INNER JOIN table2 ON table1.id = table2.id;

优化方案 #

核心思路是避免全表扫描table1，仅定位到table2中存在的id对应的table1记录进行更新，以下是两种高效写法：

写法1：基于小表驱动的关联更新（推荐） #

UPDATE table1
SET moredata = table2.moredata
FROM table2
WHERE table1.id = table2.id;

这种写法会优先遍历小表table2，然后通过table1.id的索引快速定位目标记录，避免了对百万级table1的全表扫描，关联操作的量级仅为table2的行数（数千次）。

写法2：用EXISTS过滤更新范围 #

UPDATE table1
SET moredata = (SELECT moredata FROM table2 WHERE table2.id = table1.id)
WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id);

通过WHERE EXISTS先过滤出table1中需要更新的记录（仅数千条），再执行更新操作，同样大幅减少了不必要的遍历。

关键优化前提 #

• 必须确保table1的id字段是主键或唯一索引，这样通过id定位记录的效率接近O(1)。
• 给table2的id字段建立索引，能进一步提升关联时的匹配速度。

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