AWS Athena使用其他表值作为Like匹配项时查询过慢如何优化

原有写法性能差的核心是：标量子查询返回的匹配值无法被Athena的查询优化器识别为全局固定常量，无法触发谓词下推、存储层预过滤等优化逻辑，部分场景下甚至会对table1的每一行都重复执行一次table2的查询，导致性能大幅下降。而写死常量时优化器可以直接把匹配规则下推到S3扫描阶段，仅返回符合条件的数据，因此速度极快。

方案1：拆分两次查询（性能最优，和写死常量性能完全一致） #

如果你的查询是通过调度脚本、应用程序调用Athena的方式执行，优先使用该方案：

• 第一步先执行查询取出动态匹配值：

select concat('%',t2.value,'%') as match_val from table2 t2 where t2.id =1 limit 1

• 第二步把第一步拿到的match_val作为常量拼入主查询的LIKE条件，和写死固定值的写法完全一致，性能没有任何损失。

方案2：单SQL内优化（适合必须用单个SQL完成的场景） #

如果无法拆分查询，用CTE提前提取匹配值再做交叉连接，此时优化器可以识别出匹配值是单值常量，触发相关优化，性能接近写死常量的水平：

WITH match_param AS (
    select concat('%',t2.value,'%') as val 
    from table2 t2 where t2.id =1 
    limit 1
)
SELECT * FROM table1 t1
CROSS JOIN match_param mp
WHERE t1.value LIKE mp.val

• 如果table1是分区表，尽量在WHERE里加上分区字段过滤条件，减少扫描的数据量
• 如果匹配规则的前缀固定，尽量把%xxx%改成xxx%，可以进一步提升过滤效率
• table2属于小表，Athena会自动做小表广播，不会产生额外的关联开销

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