如何在ID存在于列表列时实现两个SQL大表的低开销关联查询

普通非等值关联（直接使用array_contains作为关联条件）会触发笛卡尔积计算，数据量较大时计算成本极高，可根据场景选择以下低成本优化方案：

方案1：数组展开后做等值关联 #

• 实现逻辑：先将存储ID列表的大表的id_lst列执行展开操作，把每个数组元素拆分为独立行，再与目标ID表通过ID字段做等值关联，最后按业务需求去重即可。等值关联的计算效率远高于非等值关联，是最通用的优化方案。
• 参考SQL（适配Spark SQL/Hive）：

-- 展开大表的id_lst数组为单行单ID
WITH exploded_large_table AS (
    SELECT 
        t.*,
        id_item
    FROM large_table t
    LATERAL VIEW EXPLODE(id_lst) tmp AS id_item
)
-- 等值关联后去重获取结果
SELECT DISTINCT e.* 
FROM exploded_large_table e
INNER JOIN target_id_table t ON e.id_item = t.id

• 适用场景：id_lst平均长度较低（单个数组元素个数<100），展开后数据量膨胀可控的场景。

方案2：Bloom Filter预过滤 #

• 实现逻辑：先基于量级更小的目标ID表构建Bloom Filter，将过滤规则下推到大表的扫描阶段，提前过滤掉id_lst完全不包含目标ID的无效行，再执行后续关联逻辑，可大幅减少参与计算的数据量。
• 适用场景：目标ID表量级远小于大表，且大表中大部分行的id_lst都不包含目标ID的场景，过滤效果好的情况下可降低90%以上的计算成本。

方案3：预构建倒排索引（高频查询场景适用） #

• 实现逻辑：如果该关联逻辑为高频复用的查询，且大表数据更新频率较低，可以提前预处理大表生成ID倒排索引表，存储格式为(单个ID, 原表行唯一标识/全量字段)，按ID字段分区存储。后续查询时直接通过倒排索引做等值关联即可，无需每次都展开全量数组。
• 适用场景：需要重复执行该关联逻辑的离线业务场景，查询效率可提升数十倍。

额外优化点 #

如果目标ID表的量级极小（比如小于10万条），可以将目标ID列表作为广播变量分发到大表的所有计算节点，在大表侧直接本地判断id_lst是否包含目标ID，完全避免shuffle操作，资源消耗最低。

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