为何BigQuery中IN子句硬编码与子查询性能差异巨大？

BigQuery的查询优化器对硬编码值列表和子查询的处理逻辑完全不同：

• 第一个查询用硬编码ID列表时，优化器能直接获取到具体的ID值，结合src_id是分区键的特性，会触发分区修剪——只扫描这3个ID对应的分区，所以仅处理254KB数据。
• 另外两个用子查询的场景，默认情况下优化器没有提前计算子查询的结果并将其应用到分区过滤中，而是选择先扫描整个demo_table的所有分区（对应750MB数据），再和子查询返回的ID列表做匹配，导致性能骤降。

要让第二个查询达到第一个的性能，核心是让优化器提前获取子查询的ID列表，触发分区修剪。这里有几种可行的方法：

方法1：使用WITH子句+materialized修饰 #

强制BigQuery先物化子查询的结果，让优化器能拿到具体ID再做分区过滤：

WITH id_list AS MATERIALIZED (
    SELECT src_id FROM `myproject.mydataset.id_table`
)
SELECT *
FROM `myproject.mydataset.demo_table`
WHERE src_id IN (SELECT src_id FROM id_list);

方法2：用JOIN代替IN子查询 #

通过显式JOIN让优化器优先处理小表（id_table），再关联大表的对应分区：

SELECT dt.*
FROM `myproject.mydataset.demo_table` dt
JOIN `myproject.mydataset.id_table` it
ON dt.src_id = it.src_id;

方法3：添加查询提示强制MERGE JOIN #

提示优化器使用MERGE JOIN，会先处理小表获取ID列表，再扫描对应分区：

SELECT *
FROM `myproject.mydataset.demo_table`
WHERE src_id IN (SELECT src_id FROM `myproject.mydataset.id_table`)
OPTION(join_type=MERGE);

方法4：用变量+UNNEST提前获取ID列表 #

如果id_table的记录数固定很少，可以先把ID存入变量，再用UNNEST做过滤：

DECLARE ids ARRAY<INT64> DEFAULT (
    SELECT ARRAY_AGG(src_id) FROM `myproject.mydataset.id_table`
);

SELECT *
FROM `myproject.mydataset.demo_table`
WHERE src_id IN UNNEST(ids);

这些方法的核心都是让BigQuery先拿到具体的ID列表，再触发分区修剪，避免全表扫描。

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