BigQuery中关联分区列的DELETE成本过高的原因及优化方案

问题1：为何JOIN/EXISTS关联时不自动触发分区裁剪？ #

BigQuery的分区裁剪逻辑依赖优化阶段即可确定的常量过滤条件。当你通过JOIN、EXISTS或子查询关联分区列时，查询优化器无法提前将small_table中的分区值解析为固定的过滤规则——它会默认先扫描big_table的所有分区，再和small_table做关联匹配，最终导致不必要的全表扫描。哪怕small_table数据量极小，优化器也不会主动先提取其中的分区值来限制扫描范围。

问题2：能否让BigQuery识别这类过滤条件？ #

目前没有完全自动的方式，但可以通过显式暴露分区过滤条件引导优化器触发裁剪。核心思路是把small_table中的分区值转化为优化器在查询规划阶段就能识别的常量集合，比如你提到的预收集分区到数组的方法，本质就是将动态的子查询结果转化为固定值，让优化器明确知道只需要扫描哪些分区。

问题3：少数分区删除特定行的最优策略 #

根据受影响分区的数量和删除行的比例，推荐以下几种成本最低的方案：

方案1：预收集受影响分区（你提到的方法） #

这是通用且高效的方案，通过提前提取small_table中的分区值，将其作为常量过滤条件传入DELETE语句，强制触发分区裁剪：

-- 收集受影响分区
DECLARE affected_partitions ARRAY<DATE>;
SET affected_partitions = (
  SELECT ARRAY_AGG(DISTINCT partition_date)
  FROM `project.dataset.small_table`
);

-- 精准删除指定分区的目标行
DELETE FROM `project.dataset.big_table`
WHERE partition_date IN UNNEST(affected_partitions)
  AND (partition_date, product_id) IN (
    SELECT partition_date, product_id
    FROM `project.dataset.small_table`
  );

优势：只需少量预处理代码，就能让BigQuery只扫描目标分区，避免全表扫描的高额成本。

方案2：针对单个分区单独执行DELETE #

如果受影响的分区只有2-3个，直接针对每个分区写单独的DELETE语句更简单：

-- 删除第一个分区的目标行
DELETE FROM `project.dataset.big_table`
WHERE partition_date = '2023-01-01'
  AND (partition_date, product_id) IN (
    SELECT partition_date, product_id
    FROM `project.dataset.small_table`
    WHERE partition_date = '2023-01-01'
  );

-- 删除第二个分区的目标行
DELETE FROM `project.dataset.big_table`
WHERE partition_date = '2023-01-02'
  AND (partition_date, product_id) IN (
    SELECT partition_date, product_id
    FROM `project.dataset.small_table`
    WHERE partition_date = '2023-01-02'
  );

优势：无需额外变量声明，逻辑更直观，适合分区数量极少的场景。

方案3：重建分区（当删除行占分区多数时） #

如果某个分区中需要删除的行占比很高（比如超过50%），直接重建分区比DELETE更高效：

1. 将分区中需要保留的数据导出到临时表：

CREATE OR REPLACE TABLE `project.dataset.temp_partition_data`
AS SELECT * FROM `project.dataset.big_table`
WHERE partition_date = '2023-01-01'
  AND (partition_date, product_id) NOT IN (
    SELECT partition_date, product_id
    FROM `project.dataset.small_table`
    WHERE partition_date = '2023-01-01'
  );

2. 删除原分区（元数据操作，成本极低）：

ALTER TABLE `project.dataset.big_table`
DROP PARTITION DATE('2023-01-01');

3. 将临时表的数据写回原分区：

INSERT INTO `project.dataset.big_table`
SELECT * FROM `project.dataset.temp_partition_data`;

优势：避免了DELETE操作对分区内大量行的改写，利用BigQuery的分区删除和批量写入特性，大幅降低成本和执行时间。

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