为何IN子句用子查询触发全表扫描，用数组却不会？（BigQuery）

子查询引发全表扫描的原因 #

BigQuery对未分区表的IN子查询优化逻辑是：当IN右侧是动态子查询时，优化器无法提前计算出子查询返回的日期集合，也就没办法基于这个集合做精准的行过滤。它只能先全表扫描表B的所有69GB数据，再逐行校验cdate是否存在于子查询结果中，这就导致了全量数据扫描。

而直接写常量日期数组时，优化器可以直接把这些常量作为过滤条件，只读取表B中cdate匹配的行，自然大幅减少了数据处理量。

替代方案 #

1. 脚本自动生成常量日期列表

用脚本先提取表A的唯一日期，再拼接成DELETE语句，避免手动构造数组：

# 用bq命令行获取表A的去重日期，格式化为适合IN子句的字符串
DATE_STR=$(bq query --nouse_legacy_sql --format=csv "SELECT STRING_AGG(DISTINCT FORMAT_DATE('%Y-%m-%d', cdate), '\",\"') FROM `your-project.dataset.table_A`")

# 执行DELETE语句
bq query --nouse_legacy_sql "DELETE FROM `your-project.dataset.table_B` WHERE cdate IN (\"$DATE_STR\")"

你也可以用Python、Shell等其他脚本语言实现相同逻辑，核心是先预计算日期集合再传入DELETE语句。

2. 使用EXISTS或JOIN改写查询

尝试用EXISTS子查询或JOIN替代IN，让优化器有机会先计算日期集合再过滤表B：

-- EXISTS写法
DELETE FROM `table_B` b
WHERE EXISTS (
  SELECT 1 FROM `table_A` a
  WHERE a.cdate = b.cdate
)

-- JOIN写法
DELETE b
FROM `table_B` b
INNER JOIN (SELECT DISTINCT cdate FROM `table_A`) a
ON b.cdate = a.cdate

实际测试中，这种写法可能让优化器先处理小表A生成日期集合，再用这个集合去匹配表B的行，避免全表扫描。

3. 给表B按日期分区（长期最优解）

如果表B的查询/删除操作经常按cdate过滤，最好给它按日期分区：

-- 创建分区表并迁移数据
CREATE OR REPLACE TABLE `your-project.dataset.table_B_partitioned`
PARTITION BY DATE(cdate)
AS SELECT * FROM `your-project.dataset.table_B`;

-- 之后的DELETE会自动触发分区裁剪
DELETE FROM `your-project.dataset.table_B_partitioned`
WHERE cdate IN (SELECT DISTINCT cdate FROM `table_A`);

分区表天生支持基于日期的裁剪，不管用子查询还是常量，都会只扫描匹配的分区，彻底解决全表扫描问题。

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