Amazon Athena下基于另一表过滤SQL时IN与JOIN性能对比

结论 #

在你使用的Amazon Athena（底层基于Presto查询引擎）场景下，第二种JOIN写法的执行速度普遍更快，如果需要完全匹配IN查询的逻辑，还可以做进一步优化。

原因分析 #

• IN子查询的性能瓶颈：你写的IN查询自带DISTINCT去重逻辑，Athena执行时会首先全量扫描tableA生成去重后的账号列表，再将这个列表下发到各个计算节点逐行匹配tableB的数据。当tableA数据量很大时，去重计算、中间结果传输的开销会显著拉高整体查询耗时，极端情况下还可能触发内存溢出问题。
• JOIN的优化优势：Athena对JOIN逻辑有专门的执行优化，会根据两个表的数据量自动选择更高效的关联策略：如果tableA是小表，会用广播Join把tableA全量推送到所有计算节点本地匹配；如果两个表都是大表，会用分区Join按关联键分片并行匹配，省去了IN子查询单独生成中间结果集的额外开销，执行效率更高。

注意事项 #

你当前写的普通JOIN写法和IN查询的逻辑不完全等价：如果tableA中同一个accnt_numA存在多条重复记录，JOIN后tableB对应的行也会被重复返回。如果需要完全实现和IN一致的「只要存在匹配就只返回一次tableB对应行」的逻辑，更推荐使用专门的半连接写法，性能比上述两种都好：

SELECT * FROM tableB b
LEFT SEMI JOIN tableA a 
ON b.accnt_numB = a.accnt_numA;

额外优化建议 #

如果这类关联查询是高频需求，可以将两个表的accnt_num字段设为分区键或者分桶键，能进一步降低查询扫描的数据量，提升执行速度。

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