Hive SQL逻辑优化实战：谓词下推与Join重排解析

写这篇笔记之前，我刚在模拟项目X里把一个跑到天亮的Hive任务从“肉眼可见的慢”救回来。原因不是什么惊天动地的调优手段，就是SQL里一个in子查询被改写成join之后，shuffle数据量直接降了一个数量级。类似的场景在这套某高校软工实践课程的Hive系列里已经出现过很多次，所以这篇逻辑优化（2）主要想聊透一件事：优化器的规则是死的，但SQL写法的选择是活的，搞懂算子树的改造逻辑，比背一百条“调优建议”更有用。

上一篇我们盘过谓词下推、列剪枝这些最基础的逻辑优化手段，这篇把镜头拉到多表关联场景，重点拆一拆Join重排、子查询改写在真实任务里是怎么起作用的，以及为什么明明开了一堆优化开关，执行计划还是长得不理想。

1. 逻辑优化到底改了什么：先看一棵执行计划树

1.1 AST、逻辑计划与物理计划的关系

很多刚接触Hive的人会把优化理解成“写成什么SQL更聪明”，其实Hive执行一个查询要经历好几层：SQL先进解析器变成AST，再把AST转成逻辑计划，也就是一棵由TableScan（TS）、Filter（FIL）、Select（SEL）、GroupBy（GBY）、Join（JYV）等算子组成的树。逻辑优化就是在这棵树上做等价变换，比如把过滤条件往下压、把不必要的字段砍掉、把子查询变成另一种算子结构。之后才是物理优化，决定用什么Join算法、是不是走MapJoin、要不要排序。

所以判断一条SQL有没有被优化器“照顾”到，不要只看跑了多久，要直接看EXPLAIN输出里的Reduce Operator Tree长什么样。

1.2 用 Explain 快速定位有没有被优化

我习惯在写复杂SQL时先丢一个EXPLAIN EXTENDED（Hive 3.x里是EXPLAIN FORMATTED）。输出里重点看两类迹象：

• TableScan下面有没有紧跟着Filter/SEL算子，如果有，说明谓词下推和列剪枝生效了；
• Join算子前面有没有Map Join Operator标记，或者Reduce端Join的输入序列里，两边的outputColumnNames是不是被裁过。

示例：

EXPLAIN SELECT o.order_id, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.dt = '2024-01-01' AND u.vip_level > 2;

正常优化后的计划里，o.dt这个过滤会出现在orders这个表的TableScan阶段，而不是等两张表join完之后再过滤。如果执行计划里Filter被放到了Join的顶部，那基本可以断定这一版Hive的优化器没过这一关，SQL写得再“聪明”也白搭。

我在实践过程中给自己定了一条检查规则：任何超过五分钟的查询，第一步永远是看Explain里每个TableScan子树独立扫描了多少行、估算数据量是多少，而不是直接改SQL碰运气。定位“过滤发生在哪一层”这件事，比调一堆内存参数更接近问题本质。

2. 谓词下推的高级边角：from 子句里的“假乐观”

2.1 为什么部分过滤条件推不下 Join

谓词下推听起来很简单：把where里的条件往靠表的算子压。但实际执行计划里经常出现“看起来该下推却没下推”的情况。比如上面那个查询如果改成这样：

SELECT o.order_id, u.name FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.vip_level > 2 AND o.amount > 100;

左连接场景下，u.vip_level > 2这个条件放在where里，语义上就会把左表里有订单但用户为空的行过滤掉，这已经不再是单纯的“左连接保留左表全部记录”了。优化器为了保证结果语义不变，不会把这个条件直接压进users表的扫描器里，更安全的是在Join完成之后过滤。如果确实想让vip过滤尽早发生，得把它写进Join的on条件，但那样又会改变left join的行保留语义。这个坑不是Hive的问题，是所有SQL引擎的通用语义约束。

另一个典型情况是过滤条件里带非确定性函数，比如rand()：

SELECT ... FROM orders o JOIN users u ON ... WHERE rand() < 0.1;

rand()每次调用都返回不同值，优化器不能假设它在扫描和Join后的结果一致，所以不会做下推。还有UDF只要被标记为non-deterministic或stateful，同样会被优化器保守处理。实际调优时如果发现自定义函数里的常量过滤死活不下推，先检查函数定义里有没有声明确定性。

2.2 非确定性 UDF、外连接里 on/where 的语义区别

这里值得记一个判断句式：on条件决定的是“怎么连”，where条件决定的是“连完留哪些”。对外连接而言，把右表过滤条件写在on里，可以想象成先把右表缩小再连；写在where里，则是连接完再按结果过滤，此时右表为NULL的行会被误伤，语义和inner join趋同。所以需要的效果是“右表提前变薄但不能丢左表记录”，就写在on里；需要的是“整体结果只保留符合条件的人”，才写where。

这个边界在优化逻辑里也对应着不同的处理方式。Hive的优化规则里有一条叫ReduceSinkForOuterJoin之类的保护逻辑，会阻止会破坏外连接语义的谓词下推。如果你发现一个左连接里右表过滤很慢，最直接的验证方式是把右表过滤先写进一个子查询或CTE里再连，看看执行计划是不是多了一层提前过滤。

SELECT o.order_id, r.user_name FROM orders o LEFT JOIN (SELECT id, user_name FROM users WHERE vip_level > 2) r ON o.user_id = r.id;

这种改写虽然看起来只是“手动把谓词往子查询里移”，平面上的算子结构却完全不同：右表的vip过滤发生在索引/扫描最底层，整体IO能少读不少行。注意改写前后要自己确认语义等价，特别是外连接下子查询的去重需求。

3. Join 重排：CBO 的代价估算与左深树顺序

3.1 为什么小表不一定放在最左边

很多传统优化建议里有一条“join时把大表放最后”，这套经验来自Reduce端Join的实现方式：Hive早期版本会把最后一个表作为流式表（streaming），前面的表作为缓存表（buffered）。如果缓存表太大，内存压力会集中在Reducer上，所以大家习惯把大表写在最后。但这个经验在CBO开启之后不一定成立。

Hive从较新版本开始用CBO（基于代价的优化器，底层是Calcite的优化器）来决定join顺序。它会读每个表的统计信息，比如行数、平均字段长度、NDV（区分度）、文件大小，再估算每种join顺序下shuffle的字节量，挑一个成本最低的左深树。这时候“小表放最前”完全可能被重排成“小表放最后”。

举例：

SELECT f.order_id, d.dept_name, u.user_name FROM fact_order f JOIN dim_dept d ON f.dept_id = d.id JOIN dim_user u ON f.user_id = u.id;

如果fact_order是100亿行的流水表，dim_dept只有几十行，dim_user有1亿行，CBO大概率会把dim_dept和dim_user放前面，把fact_order放最后。因为先join两张维表后，中间结果还很小，最后再连事实表，相当于用两张维表交叉后的结果去探探测事实表，shuffle量最小。

3.2 实际任务中三表 Join 的调优经历

我自己在这个软工实践项目里遇到过反例。两张维表都不小，一张一亿行，一张五千万行，事实表一亿行，但事实表和其中一张维表之间有很强的时间分区相关性。CBO根据统计信息估算时，把两张维表先做了笛卡尔积式的关联路径，导致中间结果膨胀了十几倍。当时怎么定位的呢？看Explain里打印的估算SPLIT_COUNT和bytes，对照实际运行阶段的counter。

遇到这种情况，最直接的手段不是去调CBO参数，而是通过SQL结构限制join顺序。Hive里有个被低估的关键字：

SELECT /*+ STREAMTABLE(fact_order) */ ... FROM fact_order f JOIN dim_dept d ON f.dept_id = d.id JOIN dim_user u ON f.user_id = u.id;

STREAMTABLE提示告诉优化器哪张表作为流式表，能直接改变Join的左右位置。类似的还有MAPJOIN提示，强制某个输入走本地MapJoin。

不过要提醒一句：不要看到CBO选了奇怪的顺序就觉得是优化器笨。很多时候只是因为统计信息没更新，或者ANALYZE TABLE之后信息过期。我在每次大表结构变更、数据量翻倍之后，都会手动跑一下：

ANALYZE TABLE fact_order COMPUTE STATISTICS; ANALYZE TABLE fact_order COMPUTE STATISTICS FOR COLUMNS;

列级别的统计信息对CBO特别重要，尤其是join字段的NDV，没有它优化器只能瞎猜。

4. 子查询改写：从 IN/NOT IN 到 Semi Join 和去关联化

4.1 in 子查询的隐藏膨胀

in子查询是逻辑优化里最容易被误用的语法。看这条：

SELECT ... FROM orders WHERE user_id IN (SELECT id FROM users WHERE gender = 1);

绝大多数人在脑子里把它理解成“先查出所有符合条件user_id，再一条条判断orders”。实际执行时Hive会做两件事：先看子查询内部有没有关联条件，没有的话它就是一个独立集合，Hive会尝试把它转成semi join，也就是存在性连接。逻辑计划里会多出一个特殊的HashJoin/semiJoin模式，右表只参与建哈希集合，不输出右表字段。

但如果写成NOT IN，情况就敏感了。SQL的三值逻辑里，如果子查询结果中存在NULL，NOT IN的最终判断结果会是NULL，也就是可能整个查询返回空结果。为了防止语义出错，Hive的优化器会保守处理，这会导致额外的去重或者过滤成本。日常经验是：能用NOT EXISTS就别写NOT IN，配合上Hive的Rewriter可以把NOT EXISTS改造成anti join结构，执行起来更干净。

4.2 EXISTS 与外连接改写

EXISTS子查询在逻辑优化里一般会变成Semi Join，这个改写的核心收益是：不再需要把子查询的行数据都铺开，只要在哈希集合里命中一次就停。但很多同学在实际写的时候没有把它物化成一个集合，而是在子查询里用关联条件引用外层表，这种叫相关子查询（correlated subquery）。

Hive对相关子查询的支持历史上比较弱。如果遇到：

SELECT o.order_id FROM orders o WHERE EXISTS ( SELECT 1 FROM order_log l WHERE l.order_id = o.order_id AND l.status = 'success' );

优化器通常会把它去关联化（decorrelation），转成按order_id分组后的semi join。但去关联化成功也是有条件的：子查询里不能出现某些聚合、窗口函数或者limit。我在实测里发现一个很不直观的坑：子查询里一旦带LIMIT 1，某些Hive版本会放弃去关联化，退化成逐行调用，任务直接变蜗牛。

所以我在实践课程里给学生组定的规矩是：能用join表达的关联需求，尽量不用相关子查询。join让优化器有更多选择面，而子查询改写走的是非常狭窄的规则通道。举例来说，上面的exists改成：

SELECT DISTINCT o.order_id FROM orders o JOIN order_log l ON l.order_id = o.order_id WHERE l.status = 'success';

逻辑上等价，但它给优化器的提示更直接：两边都可以先过滤，再join，再distinct。当然，distinct本身也贵，所以更好的方式还是保留semi join的语义，即：

SELECT o.order_id FROM orders o LEFT SEMI JOIN order_log l ON l.order_id = o.order_id AND l.status = 'success';

LEFT SEMI JOIN是Hive专门为这种场景提供的语法，它不会输出右表列，也不会因为右表重复而膨胀左表行数。熟悉这个语法之后，大部分exists场景都可以直接替换掉。

5. 被低估的三类逻辑优化：列剪枝、常量折叠、Limit 下推

5.1 列剪枝在 ORC 上的 IO 收益

如果说谓词下推解决的是“少读行”，列剪枝解决的就是“少读列”。Hive的列剪枝发生在逻辑计划生成阶段，优化器会把Select算子里的引用字段向上回溯，把TableScan的输出列限定成实际需要的列。如果底层是ORC或Parquet这类列式存储，效果会放大很多倍，因为每个列在一个独立的stripe区域里，不读的列可以直接跳过IO和解析。比如一张200列的宽表，查询只用其中两列，列剪枝生效后scan阶段只读取这两列的stripe，快得不止一点。

很多老项目里会出现“查询结果只用了五列，但执行计划里TableScan outputColumns显示二十几列”的情况。原因通常是SELECT里用了*，或者某个子查询没有显式列出字段。我见过的最离谱案例是有人为了少打几个字，在子查询里写select *，外层只取三列，结果底层把全部字段都加载了一遍，任务IO翻了四五倍。排查方式依然是Explain找outputColumns的字段数。

5.2 常量折叠与 case when 的重写

常量折叠可能是我见过最不起眼但收益最稳定的优化。优化器会在逻辑计划阶段计算所有可由常量表达式确定的值，比如where dt = '2024-01-01' and 1=1会直接简化成where dt='2024-01-01'，substr('abcdef', 1, 3)直接变成常量。千万别小看这些小事——在SQL里拼接动态分区条件时，如果条件里带一层from_unixtime(unix_timestamp())这种函数，优化器是折叠不了的，每次扫描都会重复计算。

case when也有一个值得展开的重写场景。假设你要按省份统计订单，但省份字段维度取值有几十个，并且每个省份都有额外过滤条件，很多人会写：

SELECT CASE WHEN province = '广东' THEN '广东' WHEN province = '浙江' THEN '浙江' ELSE '其他' END AS p, count(*) FROM orders GROUP BY CASE WHEN province = '广东' THEN '广东' WHEN province = '浙江' THEN '浙江' ELSE '其他' END;

这个写法本身没问题，但如果每个case穿透的过滤条件不一样，比如统计“广东用户中VIP的下单数”和“浙江用户中新客的下单数”，强行凑进一个CASE里会让优化器很难做分区裁剪。我会把它拆成多个子查询再union all，让每个分支的过滤各自下推到TableScan阶段，执行计划里会看到每个分支独立做列剪枝和谓词下推。

5.3 全局 Limit 的排序消除

limit在逻辑优化里也有文章可做。假设你要取排序后前100条，写成：

SELECT * FROM ( SELECT order_id, amount, row_number() OVER (ORDER BY amount DESC) AS rn FROM orders ) t WHERE rn <= 100;

这个子查询里如果没有走对优化路径，会触发一次全量全局排序，代价极高。Hive逻辑优化里存在LimitOptimizer之类的规则，它会尝试把外层limit推到内层，让每个map/reduce只保留前N条，最终再合并取前N条。这就是为什么有些版本里执行计划可以看到ReduceSink后面跟着LIMIT 100的标记。

但这条规则依赖SQL形态。如果外层套了一个不必要的join或者聚合，limit就没法穿透。我通常建议：确实只需要前N条的场景，优先用Hive的LIMIT字面量直接写在最外层，让优化器有最大自由度。如果我上面这种row_number写法，尽量保持子查询里只有排序和limit，不要混入其他复杂算子。

6. 排查清单：一条慢 SQL 该怎么从逻辑层入手

6.1 优先盯住 TableScan 和 ReduceSink 的位置

我自己排查慢SQL有个固定流程，这里整理成可以直接照做的步骤：

1. 先跑EXPLAIN，看整个执行计划有几个MAP/REDUCE stage，stage越多，shuffle次数越多；
2. 检查每张表的TableScan子树里，Filter和SEL算子是否从一开始就压得够低；
3. 找到Reduce Operator Tree里的Join/GroupBy输入，看两边的字段列表是不是已经被列剪枝裁到最小；
4. 看每个stage的数据量估算，如果某个stage的中间结果行数比源表大一个量级，说明子查询或join顺序大概率有问题；
5. 再看有没有出现不合理的全局排序，比如只有一个Reducer的stage，通常意味着数据倾斜或者某个优化没兜住。

这些步骤看下来，其实已经跳过了“猜SQL写法”的层面，直接变成“读优化器的logical decision”。这也是我想强调的：优化不完全是玄学，优化器留下了一堆执行计划线索，只是大多数时候没人翻。

6.2 几个常用开关参数与验证顺序

Hive的优化开关不是开得越多越好，但有几项我个人每次都会确认：

验证逻辑顺序也很重要：不要一上来就开一堆参数，先开CBO和列剪枝，再看Explain；如果计划没变化，再查统计信息；如果统计信息没问题，再看是不是SQL结构挡住了优化规则。我见过太多人把mapreduce.reduce.memory.mb调来调去，结果执行计划根本就是全表扫描加超大shuffle，内存再大也拦不住。

最后回一下开头那个案例。我那次救回来的任务，原始SQL就是in子查询套了个大维表，改写成本地测试时计划里就少了一个join stage。所以整篇笔记最想留给大家的经验就一条：SQL写出来之后，花五分钟读一次它的逻辑执行计划，这比任何参数调优都更能决定任务的下限。如果你也在为某些“看起来不复杂却慢得离谱”的Hive查询发愁，不妨先把这篇里提到的几个检查点对着执行计划过一遍，大概率能省下好几个晚上的排查时间。