基于calcite做傻瓜式的sql优化(三)

上一篇说到的是Hive是如何对sql进行解析，生成ASTNode

那么Hive拿到ASTNode之后，就会触发：BaseSemanticAnalyzer.analyze这个方法；

这个方法非常的重要，从AST到task的生成这一系列的操作，都会在这个调用栈下进行的；

如下图：

按照：基于calcite做傻瓜式的sql优化给出的sql示例，我们提前看下，经过hive各阶段优化后，会改变什么样子

sql：

select 
  * 
from 
  (
    select 
      Sname, 
      Sex, 
      Sage, 
      Sdept, 
      count(1) as num 
    from 
      student_ext 
    group by 
      Sname, 
      Sex, 
      Sage, 
      Sdept
  ) t1 
  left join student_ext t2 on t1.Sname = t2.Sname 
where 
  t1.Sage > 10 
  and t2.Sdept = 'MA';

##########################Gen Calcite Plan##############################################
HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

##########################applyPreJoinOrderingTransforms-0##############################################
HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

##########################Push Down Semi Joins##############################################
HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

##########################JOIN Add not null filters##############################################
HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

##########################Constant propagation, common filter extraction, and PPD##############################################
HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

##########################basePlan##############################################
HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

##########################Projection Pruning##############################################
HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
  HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
    HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
      HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[1])
        HiveProject(sname=[$1], sex=[$2], sage=[$3], sdept=[$4])
          HiveTableScan(table=[[default.student_ext]])
    HiveProject(sno=[$0], sname=[$1], sex=[$2], sage=[$3], sdept=[$4])
      HiveTableScan(table=[[default.student_ext]])

##########################Apply Pre Join Order optimizations##############################################
HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
  HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
    HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
      HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[1])
        HiveProject(sname=[$1], sex=[$2], sage=[$3], sdept=[$4])
          HiveTableScan(table=[[default.student_ext]])
    HiveProject(sno=[$0], sname=[$1], sex=[$2], sage=[$3], sdept=[$4])
      HiveTableScan(table=[[default.student_ext]])

##########################优化后的执行计划##############################################
HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
  HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none], cost=[not available])
    HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
      HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[1])
        HiveProject(sname=[$1], sex=[$2], sage=[$3], sdept=[$4])
          HiveTableScan(table=[[default.student_ext]])
    HiveProject(sno=[$0], sname=[$1], sex=[$2], sage=[$3], sdept=[$4])
      HiveTableScan(table=[[default.student_ext]])

View Code

上面就是在各阶段优化，产生的执行计划

1. 生成Calcite的执行计划：RelNode

持续跟进，最终会调用到一个抽象方法：

public abstract void analyzeInternal(ASTNode ast) throws SemanticException;

上文提到过，在Hive中，使用Calcite来进行核心优化，它将AST Node转换成QB，又将QB转换成Calcite的RelNode，在Calcite优化完成后，又会将RelNode转换成Operator Tree，说起来很简单，但这又是一条很长的调用链。

简答来说：Hive是基于antlr做的词法和语法解析后生成的语法树，然后基于Calcite对语法树做深度优先遍历，在遍历过程中通过匹配规则来剪掉部分Operator或者合并Operattor等，这样就大大减小了shuffle数据量（其实就是RBO和CBO）；

因此程序走到这个抽象方法后，就会跳到hive的优化实现类：CalcitePlanner类上

这样程序就进入：CalcitePlanner.analyzeInternal; 然后判断，是否需要进行CBO优化；

当然不管执行的是CBO还是RBO，其实最终走的都是：analyzeInternal(ASTNode ast, PlannerContext plannerCtx),如下图：

接下来就会走到非常重要的代码：

//TODO  2. Gen OP Tree from resolved Parse Tree rbo优化的地方
Operator sinkOp = genOPTree(ast, plannerCtx);

上面这段代码，就会基于Calcite对ast进行各种规则的优化，然后返回Operator

所以跟进genOPTree方法：

2. 对Join操作进行规则优化

直接跳到他的实现方法上：CalcitePlanner.genOPTree(ASTNode ast, PlannerContext plannerCtx)

上面这段代码意思非常明确，是否需要进行CBO优化，如果不需要的话，会直接执行最下面的代码，返回未经优化的Operator

 if (skipCalcitePlan) {
      sinkOp = super.genOPTree(ast, plannerCtx);
    }

如果需要进行CBO优化，代码既执行else内部的逻辑

其中优化核心代码是：

ASTNode newAST = getOptimizedAST();

这段代码产生的hive优化流程：

1. 生成Calcite的执行计划：RelNode
2. 对Join操作进行规则优化
  2.1、聚合去重
  2.2、Semi Joins的下推
  2.3、Add not null filters
  2.4、Join的谓词下推，投影提取、常量合并等工作
  2.5、谓词推送到下游并进行分区修剪
  2.6、投影修剪
  2.7、列剪枝       
3. Appy Join Order Optimizations using Hep Planner (MST Algorithm)

那么接下来看下，hive是如何完成上述操作的：

Calcite优化的主要类是CalcitePlanner，更加细节点，是在CalcitePlannerAction.apply()这个方法,如下图：

进入CalcitePlannerAction这个内部类，优化的重点就在CalcitePlannerAction.apply()这个方法：

这个apply方法由三个重要的局部变量：

//calcite基于QB生成一个初始化的RelNode
RelNode calciteGenPlan = null;
//执行CBO优化后生成的RelNode
RelNode calcitePreCboPlan = null;
//经过一些列规则优化之后，返回的结果RelNode
RelNode calciteOptimizedPlan = null;

1、calciteGenPlan

hive会根据事先生成好的QB，来转化为初始化的RelNode，而calcite对sql优化，其实就是针对RelNode进行优化

其中代码：

calciteGenPlan = genLogicalPlan(getQB(), true);

就是QB中获取成员变量的值，然后将这些值重组成RelNode（类似）

这样当代码拿到基于QB重组成的RelNode之后（calciteGenPlan），然后就开始进行CBO的规则优化

2.1、聚合去重

程序进入applyPreJoinOrderingTransforms方法后：首先做一个计算引擎的判断：

如果当前支持tez计算引擎，并且支持优化去重重写操作，那么Hive会进行一次HiveExpandDistinctAggregatesRule的优化：

HiveExpandDistinctAggregatesRule

那么问题来了，HiveExpandDistinctAggregatesRule是什么优化？

这种优化简单来说就是将：count dintict进行扩展为聚合的优化方式

听起来很别扭是吧？

举个栗子：

count(distinct colA)就是将colA中所有出现过的不同值取出来，相信只要接触过数据库的同学都能明白什么意思！

count(distinct colA)的操作也可以用group by的方式完成，具体代码如下：

select count(distinct colA) from table1;
select count(1) from (select colA from table1 group by colA)alias_1;

这两者最后得出的结果是一致的! , 但是具体的实现方式，有什么不同呢？

上面两种方式本质就是时间与空间的权衡。
distinct需要将colA中的所有内容都加载到内存中，大致可以理解为一个hash结构，key自然就是colA的所有值。因为是hash结构，那运算速度自然就快。最后计算hash中有多少key就是最终的结果。
那么问题来了，在现在的海量数据环境下，需要将所有不同的值都存起来，这个内存消耗，是可想而知的。所以如果数据量特别大，可能会out of memory。。。

group by的实现方式是先将colA排序。排序大家都不陌生，拿最见得快排来说，时间复杂度为O(nlogn),而空间复杂度只有O(1)。这样一来，即使数据量再大一些，group by基本也能hold住。但是因为需要做一次O(nlogn) 的排序，时间自然会稍微慢点

虽然时间慢了，但是在海量数据，比如：10T大小的表情况下，相比count dintict肯定优先选择group by

2.2、Semi Joins的下推

接下来就会走到常规的第一次优化，代码如下：

// 1. Push Down Semi Joins
      basePlan = hepPlan(basePlan, true, mdProvider, SemiJoinJoinTransposeRule.INSTANCE,
      　　SemiJoinFilterTransposeRule.INSTANCE, SemiJoinProjectTransposeRule.INSTANCE);

这里面涉及到的优化规则是：

SemiJoinJoinTransposeRule
SemiJoinFilterTransposeRule
SemiJoinProjectTransposeRule

这三个规则都是关于SemiJoin的优化

简单介绍下semi join的作用：

常规联接中，结果可能会出现重复值，而子查询可以获得无重复的结果。

比如需要找出有人口大于 2000万的城市的国家，如果用普通联接，则可能出现重复结果：

select country.* from country join city on country.code=city.country_code \
and population>20000000;
+---------+----------+
| code    | name     |
+---------+----------+
|    1    | china    | 
|    1    | china    |
+---------+----------+
2 rows in set (0.00 sec)

出现这种情况，一般会使用子查询来解决，比如：

select * from country where code in (select country_code from city where population>20000000);
+------+---------+
| code | name    |
+------+---------+
|  1   | china   |
+------+---------+
1 row in set (0.00 sec)

但是,仔细观察sql会发现，这种子查询的性能很糟糕，因为where后面的子查询每扫描一条数据，Where子查询都会被重新执行一遍，这样效率就会很低如果父表数据很多带来什么问题？那么就有了将子查询的结果提升到FROM中，不需要再父表中每个符合条件的数据都要去把子查询执行一轮了;所以为了完成同样目标，我们可以选择semi join来做优化。

比如：

select country.* from country semi join city on country.code = city.country_code where population > 20000000;

现在我们在拿这个例子semi join来做优化，经过：SemiJoinJoinTransposeRule,SemiJoinFilterTransposeRule,SemiJoinProjectTransposeRule这些规则处理后

最后生成的sql就是：

select country.* from counttry semi join (select country_code from city where population > 20000000) a on country.code = a.country_code

经过Push Down Semi Joins流程后，代码接下来执行到如图所示的地方：

2.3、Add not null filters

这里面涉及到一个规则：HiveJoinAddNotNullRule

此优化规则Rule主要功能是将SQL语句中Inner Join关联时，出现在关联条件中的字段存在为null可能的字段，都加上相应字段 is not null条件限制（因为hive在做关联的时候，并不会对null = null这样的条件进行关联）

贴一个连接，将HiveJoinAddNotNullRule讲的非常透彻，建议深入看一下：连接

比如执行这样一个sql：

select * from (select Sname , Sex , Sage , Sdept , count(1) as num from student_ext group by Sname , Sex , Sage , Sdept) t1  inner join student_ext t2 on t1.Sname = t2.Sname where t1.Sage>10 and t2.Sdept = 'MA';

在HiveJoinAddNotNullRule规则优化前后对比的执行计划如下：

在经过HiveJoinAddNotNullRule优化前：

HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available])
      HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
        HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
          HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
            HiveTableScan(table=[[default.student_ext]])
      HiveTableScan(table=[[default.student_ext]])

在经过HiveJoinAddNotNullRule优化后：

HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4], sno=[$5], sname1=[$6], sex1=[$7], sage1=[$8], sdept1=[$9])
  HiveFilter(condition=[AND(>($2, 10), =($9, 'MA'))])
    HiveJoin(condition=[=($0, $6)], joinType=[inner], algorithm=[none], cost=[not available])
      HiveFilter(condition=[isnotnull($0)])
        HiveProject(sname=[$0], sex=[$1], sage=[$2], sdept=[$3], num=[$4])
          HiveAggregate(group=[{0, 1, 2, 3}], agg#0=[count($4)])
            HiveProject($f0=[$1], $f1=[$2], $f2=[$3], $f3=[$4], $f4=[1])
              HiveTableScan(table=[[default.student_ext]])
      HiveFilter(condition=[isnotnull($1)])
        HiveTableScan(table=[[default.student_ext]])

仔细观察，多了 HiveFilter(condition=[isnotnull($1)]) ，这个就是HiveJoinAddNotNullRule规则的作用，出现在关联条件中的字段存在为null可能的字段，都加上相应字段 is not null条件限制

2.4、Constant propagation, common filter extraction, and PPD

继续跟进debug，程序走到 3. Constant propagation, common filter extraction, and PPD

第一个规则：ReduceExpressionsRule.PROJECT_INSTANCE 叫做常量折叠，比如我们写这样一个sql:

select  1+2 , a.name , a.age ,b.money from a left join b on a.id=b.id where a.name='张三' and b.department='it'

在没有进行常量折叠优化之前，如果不进行常量折叠，那么每行数据都需要进行计算，显然会增大sql的CPU使用情况

然后是下面的三个规则要放在一起：

ReduceExpressionsRule.FILTER_INSTANCE,
ReduceExpressionsRule.JOIN_INSTANCE
HivePreFilteringRule.INSTANCE

就是在join的过程中帮我们进行谓词下推操作;

那么Constant propagation, common filter extraction, and PPD这个优化规则组合起来，用一张图来说明一下，依然是sql：

select  1+2 , a.name , a.age ,b.money from a left join b on a.id=b.id where a.name='张三' and b.department='it'

图：

从上图的优化前后对比可以看到，当需要查询的表数据量很大是后续，这种优化能极大优化:

1、join过程中的数据量
2、CPU计算次数

未完待续.............

posted @ 2020-11-23 23:34 niutao 阅读(3744) 评论(8) 编辑收藏举报

刷新页面返回顶部