基于唯一item_id在AWS Athena中高效关联多表指定列的技术问询

背景信息 #

现有三个表，结构及数据如下：

表item_attributes #

表item_cost #

表item_names（主表） #

目标是基于item_id+store联合键（避免同item_id不同store的冲突），将item_cost的price列和item_attributes的brand列关联到主表item_names中。用户尝试的SQL如下：

SELECT 

att.item_id, 
att.store,
att.brand,

cost.item_id,
cost.store,
cost.price, 

main.item_id, 
main.store, 
main.name, 

FROM (item_name main LEFT OUTER JOIN item_attributes att) LEFT OUTER JOIN item_cost cost
ON main.item_id=att.item_id AND main.item_id=cost.item_id
WHERE main.store=cost.store AND main.store=att.store

问题解答 #

1. 是否存在更简洁且不增加时间复杂度（Big-O）的查询语句？ #

有。原SQL存在两个问题：一是WHERE子句中的store匹配条件会把LEFT JOIN强制转换成INNER JOIN（过滤掉主表中无匹配store的记录），违背左连接初衷；二是重复选择多份item_id和store列，冗余无意义。

优化后的简洁SQL如下，时间复杂度与原查询一致（均为O(n)级，基于联合键的高效关联）：

SELECT
  main.item_id,
  main.store,
  main.name,
  att.brand,
  cost.price
FROM item_names main
LEFT JOIN item_attributes att
  ON main.item_id = att.item_id AND main.store = att.store
LEFT JOIN item_cost cost
  ON main.item_id = cost.item_id AND main.store = cost.store

该写法将关联条件放在ON子句中，保留左连接语义，同时只选择必要字段，结构更清晰。

2. 先嵌套关联main与att再关联cost，是否比链式LEFT OUTER JOIN计算成本更低？还是两者成本相同？ #

两者计算成本基本相同。Athena底层基于Presto/Trino查询引擎，其优化器会自动重写逻辑等价的关联语句，最终生成的执行计划完全一致。不管是嵌套写法还是链式写法，优化器都会根据数据分布、统计信息选择最优关联顺序，性能上无差异。

3. 先执行main UNION att UNION cost再按item_id分组，是否计算成本更低？ #

这种方法成本反而更高，原因如下：

• UNION需要扫描三个表的全部数据并合并，数据处理量远大于定向JOIN；
• 后续GROUP BY需要对合并后的数据做跨节点shuffle和分组聚合，额外增加计算开销；
• 直接JOIN可借助item_id+store联合键做定向匹配，甚至利用表的分区、排序信息减少数据移动，效率远高于UNION+GROUP BY的方式。

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