Athena查询使用NOT IN性能低下，如何改写查询结构提升运行效率？

原查询性能低的原因 #

• 会触发两次table_b全表扫描：一次用于主查询关联，一次用于子查询的DISTINCT聚合，数据量较大时开销直接翻倍
• NOT IN在Athena（底层基于Presto/Trino引擎）中的优化优先级低，默认会走低效的广播过滤逻辑，同时还存在NULL值导致的隐式语义风险

优化方案1：最小改动替换为NOT EXISTS #

仅需修改过滤逻辑即可，执行引擎会自动将其优化为高效的anti-join，不需要提前做去重聚合，子查询匹配到任意符合条件的记录就会终止判断，执行效率提升明显：

SELECT *
FROM table_a
INNER JOIN table_b 
  ON table_b.external_id = table_a.external_id 
  AND table_b.client_name = table_a.client
WHERE NOT EXISTS (
  SELECT 1
  FROM table_b AS tb_filter
  WHERE tb_filter.id = table_b.id
    AND (tb_filter.decision IS NOT NULL OR tb_filter.submission_time IS NOT NULL)
)

优化方案2：预过滤table_b后再关联 #

如果table_b的数据量远大于table_a，可以先对table_b做一次全量过滤，剔除所有需要排除的id对应的行之后再做关联，减少join阶段处理的数据量：

WITH excluded_ids AS (
  -- 先筛选出所有需要排除的id
  SELECT DISTINCT id
  FROM table_b
  WHERE decision IS NOT NULL OR submission_time IS NOT NULL
),
filtered_table_b AS (
  -- 过滤掉所有需要排除的id对应的全量行
  SELECT tb.*
  FROM table_b tb
  LEFT JOIN excluded_ids ei ON tb.id = ei.id
  WHERE ei.id IS NULL
)
SELECT *
FROM table_a
INNER JOIN filtered_table_b ftb
  ON ftb.external_id = table_a.external_id 
  AND ftb.client_name = table_a.client

额外优化建议 #

• 可以给table_b的id、decision、submission_time字段配置分区规则或者投影索引，进一步降低扫描阶段的开销
• 避免使用SELECT *，仅查询需要的字段，减少数据传输和序列化的资源消耗

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