关于小标join大表，操作不当会导致笛卡尔积,数据倾斜

以前总是说笛卡尔积，笛卡尔积，没碰到过，今天在跑流程调度时，就碰到笛卡尔积了，本来，就是查询几个编码的信息，然后由于使用的是with tmp as，没使用where in ,所以跑的很慢

现象，  4条编码，与一张数据量在亿万级别的表进行join,  占用内存在30多g，执行10分钟，20分钟以上，依然半分钟进度只增加1%，到了50% - 60%结果就卡住跑不动了。此时应该就是发生了数据倾斜，笛卡尔积。

后续，同事反馈内存占用较大，需要停止执行任务，然后就停止了，  然后同事和领导跑过来看执行的语句，后被另一个领导批评了一点，然后观察我的sql

WITH tmp.test1  AS 
(    
SELECT '111111111' AS c1_num, '222222222' AS c2_num     
UNION ALL SELECT '4444444444', '333333333'      
UNION ALL SELECT '555555555', '666666666666'     
UNION ALL SELECT '8888888888', '77777777777' 
) select tn.c1_num,tn.c2_numfrom  tmp.test1 tninner join H_IS.day t ON tn.c1_num = t.c1_num AND tn.c2_num = t.c2_numwhere t.dt >= '20240301'  and  t.dt <= '20240630' 

发现我这样写，会导致全表扫描，让我改sql,我寻思不就是占用了30多个g吗，有人还占用上千g内存呢，心想不至于吧。      起初还以为是分区的问题，dt限制的分区天数太多导致跑的满，后面改完sql以后，发现不是day表分区问题，就是小表Join达标出现了数据倾斜， 后学，我按照经理的建议，将sql的关联条件改到where里面执行，首先创建一个表存编码，然后再从where里面过滤，

select tn.c1_num,tn.c2_numfrom  tmp.test1 tninner join H_IS.day t ON tn.c1_num = t.c1_num AND tn.c2_num = t.c2_numwhere t.dt >= '20240301'  and  t.dt <= '20240630'  and t.c3_num in (select c1_num from   tmp.test1)

发现改完后的代码，执行速度直接飞起，从原来的执行10-20分钟没有执行完，到1分钟，30秒就马上就出结果了，于是我意识到，这里，with tmp as生成的虚拟表与大表join导致数据倾斜，

所以执行的很慢还占用内存，才where提前分区过滤裁剪，提高了执行速度，避免了数据倾斜。

这里补充gpt的解释：

        

问题分析

1. WITH 子句（CTE）中的数据量小但未被优化处理：

   • 你的 WITH tmp.test1 创建的是一个小型的虚拟数据集，它在 SQL 查询优化器中往往被视为一个笛卡尔积生成表（即将它的内容分布到所有可能的分区中）。
   • 如果后续 JOIN 操作涉及的大表（例如 H_IS.day）有大量分区，查询引擎需要扫描所有分区的数据并将临时数据广播到每个分区，从而导致数据倾斜和内存高占用。

2. INNER JOIN 中的广播问题：

   • 在大数据场景中，Hive 或类似系统会默认对小表广播（broadcast join）。当 WITH 中的数据不是一个实际表，而是一个临时计算结果时，可能没有被优化为广播操作，导致全表扫描和网络数据传输。

3. WHERE IN 的优化：

   • 在第二个查询中，当你将 WITH 的数据写入一个物理表并使用 WHERE IN 筛选时，查询引擎可以识别出表中字段的索引（如果有）和分区，直接过滤数据。
   • 而且 WHERE IN 的条件可以被优化器提前下推到 JOIN 的早期阶段，减少需要计算和传输的数据量。

4. 分区过滤的问题：

   • 如果 t.dt是分区字段，使用 WITH 定义的子查询可能无法触发分区裁剪，而使用实际表和 WHERE IN 则可以利用分区裁剪优化性能。