AWS Athena十亿级表求共同MD5的查询优化问题

核心问题分析 #

原查询通过UNION ALL合并两张十亿级表的所有md5后再聚合，会产生20亿条数据的shuffle与计算操作，远超Athena单查询的资源承载上限，最终导致超时失败。

优化方案 #

1. 使用半连接（SEMI JOIN）替代聚合 #

半连接仅返回左表中存在于右表的记录，无需全量合并数据，shuffle量大幅降低，是大数据量交集查询的最优选择：

-- EXISTS 写法（推荐，性能更稳定）
SELECT DISTINCT md5
FROM table1
WHERE EXISTS (
    SELECT 1 FROM table2 WHERE table2.md5 = table1.md5
)

-- IN 写法（逻辑等价，适合简单场景）
SELECT DISTINCT md5
FROM table1
WHERE md5 IN (SELECT md5 FROM table2)

注：若单表内md5无重复，可去掉DISTINCT进一步提升效率。

2. 先去重再执行JOIN #

若单表内存在大量重复md5，先对每张表去重可大幅减少后续JOIN的数据量：

WITH t1_unique AS (
    SELECT DISTINCT md5 FROM table1
),
t2_unique AS (
    SELECT DISTINCT md5 FROM table2
)
SELECT t1_unique.md5
FROM t1_unique
JOIN t2_unique ON t1_unique.md5 = t2_unique.md5

3. 直接用CTAS生成结果表 #

避免查询结果返回客户端的压力，直接将结果写入S3表，Athena的CTAS操作采用分布式执行，更适配大数据量场景：

CREATE TABLE common_md5
WITH (
    format = 'PARQUET',  -- 列式存储，压缩率高，后续查询效率更优
    write_compression = 'SNAPPY',
    external_location = 's3://your-bucket/path/to/common_md5/'  -- 指定自定义存储路径
) AS
SELECT DISTINCT md5
FROM table1
WHERE EXISTS (
    SELECT 1 FROM table2 WHERE table2.md5 = table1.md5
)

4. 辅助性能优化手段 #

• 转换为列式存储：若原表是CSV等行式格式，通过CTAS将表转换为Parquet/ORC格式，仅读取md5列时可大幅减少IO开销。
• 更新列统计信息：手动执行ANALYZE TABLE table1 COMPUTE STATISTICS FOR COLUMNS md5;，帮助Athena优化器生成更高效的执行计划。
• 调整查询超时：默认查询超时为30分钟，可通过AWS控制台提工单申请延长至2小时，适配超大数据量查询需求。

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