BigQuery中带搜索条件的MERGE与过滤式INSERT的性能及成本对比

我有一张源表，希望仅将目标表中不存在对应主键（id+ts）的行插入到目标表中（需求简化描述）。目前想到两种实现方式：

方式一：带NOT EXISTS过滤的INSERT语句 #

insert into <destination_table> as destination
select * from source
where not exists (
    select 1 from <destination_table> AS destination
    WHERE source.id = destination.id
      AND source.ts = destination.ts
)

方式二：带搜索条件的MERGE语句（原写法存在逻辑错误） #

原写法：

merge into <destination_table> as destination
using (
select ...
) as source
on (source.id != destination.id) and (source.ts != destination.ts) 

请问从性能及成本优化角度，哪种方式更优？

首先要明确：你提供的方式二的MERGE写法存在逻辑错误。MERGE的ON子句是用来匹配源表和目标表的主键相等行的，而非不等。正确的MERGE写法应该是：

merge into <destination_table> as destination
using (select * from source) as source
on source.id = destination.id and source.ts = destination.ts
when not matched then insert (id, ts, ...) values (source.id, source.ts, ...);

接下来基于两种正确实现，从性能和成本维度做对比：

1. 执行逻辑差异 #

• INSERT + NOT EXISTS：对源表每一行，检查目标表是否存在同主键的行，仅插入不存在的行。数据库通常会将其优化为左反连接（Left Anti Join），只返回源表中在目标表无匹配的行，再批量插入。
• MERGE（WHEN NOT MATCHED）：本质是按主键匹配源表与目标表，对未匹配的源表行执行插入。逻辑上和左反连接类似，但MERGE是原子操作，适合同时处理插入、更新、删除的场景；若仅做插入，执行计划可能和INSERT+NOT EXISTS接近，但不同数据库的优化策略有差异。

2. 性能与成本对比 #

分数据库表现： #

• PostgreSQL：INSERT+NOT EXISTS通常更高效，因为PostgreSQL对左反连接的优化成熟，MERGE在纯插入场景下的额外匹配逻辑会带来少量开销。
• SQL Server：两种方式性能接近，但MERGE的原子性在高并发场景下更有优势（避免竞态条件）；若为纯插入且并发低，INSERT+NOT EXISTS的成本略低。
• BigQuery/云数仓：当源表、目标表数据量极大时，MERGE的分区扫描优化可能更优（需确保ON条件使用主键/分区键）；INSERT+NOT EXISTS在分区表上的表现也不错，具体取决于数仓的查询优化器。

通用优化前提： #

无论选择哪种方式，必须给目标表的(id, ts)组合创建主键或唯一索引，否则检查存在性的操作会变成全表扫描，性能会急剧下降。另外，大数据量场景下，分批处理比一次性插入更能控制成本、避免锁竞争。

3. 结论 #

• 如果需求仅为插入不存在的行，且数据库对左反连接优化较好（比如PostgreSQL），优先选INSERT + NOT EXISTS，逻辑简单、开销更低。
• 如果需要后续扩展为同时处理更新/删除，或高并发场景下需要原子性操作，选择正确写法的MERGE，但要注意适配数据库的优化特性。

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