★ FEATURED ARTICLE 写这篇笔记之前我刚在模拟项目X里把一个跑到天亮的Hive任务从“肉眼可见的慢”救回来。原因不是什么惊天动地的调优手段就是SQL里一个in子查询被改写成join之后shuffle数据量直接降了一个数量级。类似的场景在这套某高校软工实践课程的Hive系列里已经出现过很多次所以这篇逻辑优化2主要想聊透一件事优化器的规则是死的但SQL写法的选择是活的搞懂算子树的改造逻辑比背一百条“调优建议”更有用。上一篇我们盘过谓词下推、列剪枝这些最基础的逻辑优化手段这篇把镜头拉到多表关联场景重点拆一拆Join重排、子查询改写在真实任务里是怎么起作用的以及为什么明明开了一堆优化开关执行计划还是长得不理想。1. 逻辑优化到底改了什么先看一棵执行计划树1.1 AST、逻辑计划与物理计划的关系很多刚接触Hive的人会把优化理解成“写成什么SQL更聪明”其实Hive执行一个查询要经历好几层SQL先进解析器变成AST再把AST转成逻辑计划也就是一棵由TableScanTS、FilterFIL、SelectSEL、GroupByGBY、JoinJYV等算子组成的树。逻辑优化就是在这棵树上做等价变换比如把过滤条件往下压、把不必要的字段砍掉、把子查询变成另一种算子结构。之后才是物理优化决定用什么Join算法、是不是走MapJoin、要不要排序。所以判断一条SQL有没有被优化器“照顾”到不要只看跑了多久要直接看EXPLAIN输出里的Reduce Operator Tree长什么样。1.2 用 Explain 快速定位有没有被优化我习惯在写复杂SQL时先丢一个EXPLAIN EXTENDEDHive 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的三值逻辑里如果子查询结果中存在NULLNOT 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 11会直接简化成where dt2024-01-01substr(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有个固定流程这里整理成可以直接照做的步骤先跑EXPLAIN看整个执行计划有几个MAP/REDUCE stagestage越多shuffle次数越多检查每张表的TableScan子树里Filter和SEL算子是否从一开始就压得够低找到Reduce Operator Tree里的Join/GroupBy输入看两边的字段列表是不是已经被列剪枝裁到最小看每个stage的数据量估算如果某个stage的中间结果行数比源表大一个量级说明子查询或join顺序大概率有问题再看有没有出现不合理的全局排序比如只有一个Reducer的stage通常意味着数据倾斜或者某个优化没兜住。这些步骤看下来其实已经跳过了“猜SQL写法”的层面直接变成“读优化器的logical decision”。这也是我想强调的优化不完全是玄学优化器留下了一堆执行计划线索只是大多数时候没人翻。6.2 几个常用开关参数与验证顺序Hive的优化开关不是开得越多越好但有几项我个人每次都会确认参数作用建议值hive.cbo.enable是否开启CBOtrue新版本默认开hive.optimize.column.pruning是否开启列剪枝truehive.map.aggrMap端是否做局部聚合truehive.auto.convert.join是否自动转换MapJointruehive.fetch.task.conversion简单查询是否直接走fetchmorehive.exec.dynamic.partition允许动态分区true验证逻辑顺序也很重要不要一上来就开一堆参数先开CBO和列剪枝再看Explain如果计划没变化再查统计信息如果统计信息没问题再看是不是SQL结构挡住了优化规则。我见过太多人把mapreduce.reduce.memory.mb调来调去结果执行计划根本就是全表扫描加超大shuffle内存再大也拦不住。最后回一下开头那个案例。我那次救回来的任务原始SQL就是in子查询套了个大维表改写成本地测试时计划里就少了一个join stage。所以整篇笔记最想留给大家的经验就一条SQL写出来之后花五分钟读一次它的逻辑执行计划这比任何参数调优都更能决定任务的下限。如果你也在为某些“看起来不复杂却慢得离谱”的Hive查询发愁不妨先把这篇里提到的几个检查点对着执行计划过一遍大概率能省下好几个晚上的排查时间。
阅读完成 · 觉得有帮助？
