Presto（Athena）范围交叉连接最优实现及事件日志查询优化

性能瓶颈根因 #

你当前的查询耗时高核心原因是使用了不等值范围连接（BETWEEN JOIN）：Athena（底层基于Presto）处理这类连接时，会先对两个表做笛卡尔积再过滤匹配条件，1.8万行标记表乘1000万行事件表会产生1.8e11行的中间计算量，耗时必然极高。另外单独创建临时marker_table会产生额外的S3读写开销，也会拉长耗时。

核心优化方案 #

1. 替换不等值连接为二分查找统计（性能提升最显著） #

利用Presto内置的数组二分查找能力，仅需1次排序即可完成所有统计，复杂度从O(M*N)降到O(N log N + M)，适配你的业务逻辑写法如下：

WITH 
-- 直接提取标记事件，避免创建临时表落盘
marker_events AS (
    SELECT event_id, event_type, start_time, end_time
    FROM event_log
    WHERE event_type = 'TypeM'
    -- 可补充时间范围过滤进一步裁剪数据
    -- AND end_time >= 'xxxx-xx-xx' AND end_time < 'xxxx-xx-xx'
),
-- 提取所有非标记事件的结束时间和ID，按结束时间排序
non_m_events AS (
    SELECT 
        array_agg(end_time ORDER BY end_time) AS sorted_end_times,
        array_agg(event_id ORDER BY end_time) AS sorted_event_ids
    FROM event_log
    WHERE event_type != 'TypeM'
    -- 和标记事件的时间范围对齐，裁剪多余数据
    AND end_time >= (SELECT min(start_time) FROM marker_events)
    AND end_time <= (SELECT max(end_time) FROM marker_events)
)
SELECT 
    m.*,
    -- 拼接成你期望的数量+ID格式
    concat(
        cast(
            (array_binary_search(nm.sorted_end_times, m.end_time) + 1) 
            - (array_binary_search(nm.sorted_end_times, m.start_time - 0.000001) + 1) 
            as varchar
        ),
        ' ',
        array_join(
            slice(
                nm.sorted_event_ids,
                (array_binary_search(nm.sorted_end_times, m.start_time - 0.000001) + 2),
                (array_binary_search(nm.sorted_end_times, m.end_time) + 1) 
                - (array_binary_search(nm.sorted_end_times, m.start_time - 0.000001) + 1)
            ),
            ', '
        )
    ) AS events_processed_count
FROM marker_events m
CROSS JOIN non_m_events nm

注：如果你使用的Athena版本不支持array_binary_search，可以替换为窗口函数排序后打行号的方式实现，逻辑一致。

2. 辅助优化项 #

• 投影裁剪：查询仅用到event_id、event_type、start_time、end_time四个字段，Parquet列存格式下仅读取需要的列，可减少70%以上的IO开销。
• 避免临时表落盘：用WITH子句替代CREATE TABLE创建临时表，中间数据直接在计算节点内存中流转，避免S3读写开销。
• 广播Join提示：如果因业务逻辑必须保留原有Join写法，可在查询开头加广播提示/*+ BROADCAST(mark) */，将仅1.8万行的小表广播到所有计算节点，避免大表重分区的开销。
• 数据类型优化：确保start_time、end_time为数值型时间戳，不要用字符串类型，排序和比较性能可提升数倍。

优化预期效果 #

上述优化落地后，原17分钟的查询可降到30秒以内完成。

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