AWS Athena关联查询性能优化及RIGHT JOIN异常问题排查

问题背景 #

在AWS Athena中关联两张表，要求第二张表的日期处于第一张表的两个日期区间内，但查询执行耗时过长。调换表的关联顺序后性能有所提升，但使用RIGHT JOIN时性能表现存在明显差异。

核心问题 #

1. 还有哪些方法可以加快该关联查询的速度？对全量数据使用CROSS JOIN是否合理？
2. 该如何解释这种性能差异？原本认为A LEFT JOIN B等价于B RIGHT JOIN A，但执行时间却截然不同。

数据规模 #

• 全量数据：car_locations共307184行，app_opening共3024250行
• 测试子集：car_locations有16062行，app_opening有147747行

不同查询的耗时表现 #

查询1：app_opening LEFT JOIN car_locations（平均耗时1分钟） #

SELECT
    sk_app_openingevent,
    eventdatetime_local,
    device_id,
    latitude,
    longitude,
    COUNT(sk_app_openingevent) AS available_fleet_size,
    MIN(GREAT_CIRCLE_DISTANCE(latitude, longitude, gps_latitude, gps_longitude))AS closest_car_km
FROM app_opening
LEFT JOIN car_locations ON eventdatetime_local BETWEEN from_datetime_local AND to_datetime_local
GROUP BY 1,2,3,4,5

将LEFT改为RIGHT后，耗时降至45秒。

查询2：car_locations LEFT JOIN app_opening（耗时20-25秒） #

SELECT
    sk_app_openingevent,
    eventdatetime_local,
    device_id,
    latitude,
    longitude,
    COUNT(sk_app_openingevent) AS available_fleet_size,
    MIN(GREAT_CIRCLE_DISTANCE(latitude, longitude, gps_latitude, gps_longitude)) AS closest_car_km
FROM car_locations
LEFT JOIN app_opening ON eventdatetime_local BETWEEN from_datetime_local AND to_datetime_local
GROUP BY 1,2,3,4,5

将LEFT改为RIGHT后，耗时反而升至6分钟。

查询3：CROSS JOIN + WHERE（耗时20-22秒） #

SELECT
    sk_app_openingevent,
    eventdatetime_local,
    device_id,
    latitude,
    longitude,
    COUNT(sk_app_openingevent) AS available_fleet_size,
    MIN(GREAT_CIRCLE_DISTANCE(latitude, longitude, gps_latitude, gps_longitude)) AS closest_car_km
FROM car_locations
CROSS JOIN app_opening 
WHERE eventdatetime_local BETWEEN from_datetime_local AND to_datetime_local
GROUP BY 1,2,3,4,5

补充：多次执行耗时统计 #

问题解答 #

1. 加速关联查询的方法及CROSS JOIN的合理性 #

优化方法

• 分区与分桶：按日期字段（如eventdatetime_local、from_datetime_local）对两张表做分区，Athena会仅扫描符合条件的分区数据，大幅减少处理量；针对高频关联字段（如日期、设备ID）做分桶，可提升关联匹配效率。
• 添加索引：给app_opening的eventdatetime_local、car_locations的from_datetime_local和to_datetime_local添加分区索引或布隆过滤器，帮助快速定位符合区间条件的数据。
• 预过滤数据：关联前通过WHERE子句限制时间范围或过滤无效数据，减少参与关联的行数。
• 重构查询逻辑：考虑用窗口函数替代关联，比如先对car_locations按时间范围预处理，再与app_opening做高效匹配。
• 使用列式存储：将表转换为Parquet或ORC格式，这类格式比CSV更适配Athena，能降低IO开销。

CROSS JOIN的合理性

测试子集下CROSS JOIN + WHERE性能尚可，但全量数据绝对不能用。全量数据中car_locations约30万行、app_opening约300万行，CROSS JOIN会生成9万亿条中间数据，即使有WHERE过滤，中间数据量也会爆炸，导致查询超时或成本飙升。

2. JOIN类型与顺序的性能差异解释 #

虽然A LEFT JOIN B和B RIGHT JOIN A逻辑结果等价，但Athena基于Presto的查询优化器对两种写法的执行计划可能完全不同：

• 驱动表选择：LEFT JOIN以左表为驱动表，RIGHT JOIN以右表为驱动表。car_locations数据量远小于app_opening，以小表为驱动表时，需要匹配的次数更少，性能自然更优。
• 优化器局限性：Athena优化器不会自动将RIGHT JOIN重写为等价的LEFT JOIN来选择更优驱动表。比如car_locations RIGHT JOIN app_opening，优化器可能仍将大表app_opening作为驱动表，扫描300万行去匹配30万行，而非自动切换为小表驱动的执行计划。
• 关联条件过滤效率：关联条件eventdatetime_local BETWEEN from_datetime_local AND to_datetime_local，小表作为驱动表时，可提前预处理时间区间，再去大表匹配；大表作为驱动表时，每条数据都要遍历小表的所有时间区间，过滤效率极低。

从耗时统计看，cl right app（car_locations RIGHT JOIN app_opening）耗时极长，正是因为优化器选择了大表作为驱动表，匹配计算量陡增；而cl left app以小表为驱动，匹配次数少，性能自然更好。

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