2021-03-29 spark sql not in 造成的执行缓慢

背景：同事写了很长一段sql，执行时间特别长，让我帮他优化一下

执行引擎：
spark 一个小时都跑不出结果，卡在mapshuffle阶段

impala 不到一分钟出结果

过程：

多次调试：

怀疑是GC引起的，尝试修改driver，executor内存，无效

set spark.executor.memory = 4g;
set spark.driver.memory = 10g;

set spark.sql.adaptive.shuffle.targetPostShuffleInputSize=128000000;
set spark.sql.adaptive.enabled=true;
set spark.sql.crossJoin.enabled = true; 
set hive.exec.dynamic.partition.mode = nonstrict;

set spark.executor.extraJavaOptions='-XX:SurvivorRatio=3 -XX:NewRatio=3 -XX:+PrintGCDetails -XX:+PrintGCTimeStamps';

最终定位到是由not in subquery 引起的

多次拆解sql，explain查看执行计划，最终定位到 not in subquery 生成的BroadcastNestedLoopJoin

修改not in

使用left join + is null 替代 not in
最终结果 1分钟完成

参考：

Spark SQL中Not in Subquery为何低效以及如何规避