author：彭程

介绍

dble优化是在生成的语法抽象树的基础上，基于一些规则改写其逻辑关系表达式，并将一些能下推的关系表达式进行下推。

代码分析

优化过程主要在MyOptimizer#optimize方法中，代码结构如下，下面具体分析各个优化步骤

  1.    public static PlanNode optimize(PlanNode node) {
  2.         TraceManager.TraceObject traceObject = TraceManager.threadTrace("optimize-for-sql");
  3.         TraceManager.log(ImmutableMap.of("plan-node", node), traceObject);
  4.         try {
  5.             // PreProcessor SubQuery ,transform in sub query to join预处理子查询
  6.             if (SystemConfig.getInstance().isInSubQueryTransformToJoin()) { //判断子查询是否转换为join
  7.                 node = SubQueryPreProcessor.optimize(node);
  8.             } else {
  9.                 node = SubQueryPreNoTransformProcessor.optimize(node);
  10.         }
  11.             updateReferredTableNodes(node);
  12.             int existGlobal = checkGlobalTable(node, new HashSet<>());
  13.             if (node.type() == PlanNode.PlanNodeType.QUERY || node.isExistView() || existGlobal != 1 || node.isWithSubQuery() || node.isContainsSubQuery() || !PlanUtil.hasNoFakeNode(node)) {
  14.                 // optimizer sub query [Derived Tables (Subqueries in the FROM Clause)] 
  15.                 //子查询优化器[派生表(FROM子句中的子查询)]
  16.                 // transform right join to left join 将right join转为left join
  17.                 node = JoinPreProcessor.optimize(node);
  18.                 //优化总是为真或总是为假的过滤条件
  19.                 //  filter expr which is always true/false
  20.                 node = FilterPreProcessor.optimize(node);
  21.                 //  push down the filter which may contains ER KEY
  22.                 node = FilterJoinColumnPusher.optimize(node);
  24.                 node = JoinERProcessor.optimize(node);
  26.                 if (existGlobal >= 0) {
  27.                     node = GlobalTableProcessor.optimize(node); //存在全局表优化
  28.                 }
  29.                 //  push down filter
  30.                 //将过滤条件下推
  31.                 node = FilterPusher.optimize(node);
  32.                 //将orderby下推
  33.                 node = OrderByPusher.optimize(node);
  34.                 //将limit下推，如存在union情况
  35.                 node = LimitPusher.optimize(node);
  37.                 node = SelectedProcessor.optimize(node);
  39.                 boolean useJoinStrategy = SystemConfig.getInstance().isUseJoinStrategy();
  40.                 if (useJoinStrategy) {
  41.                     node = JoinStrategyProcessor.optimize(node);
  42.                 }
  43.             }
  44.             return node;
  45.         } catch (MySQLOutPutException e) {
  46.             LoggerFactory.getLogger(MyOptimizer.class).error(node.toString(), e);
  47.             throw e;
  48.         } finally {
  49.             TraceManager.finishSpan(traceObject);
  50.         }
  51.     }

预处理子查询SubQueryPreProcessor#optimize和SubQueryPreNoTransformProcessor#optimize

代码如下

  1. // PreProcessor SubQuery ,transform in sub query to join预处理子查询
  2. if (SystemConfig.getInstance().isInSubQueryTransformToJoin()) { //判断子查询是否转换为join
  3.     node = SubQueryPreProcessor.optimize(node);
  4. } else {
  5.     node = SubQueryPreNoTransformProcessor.optimize(node);
  6.         }

dble根据SystemConfig.isInSubQueryTransformToJoin参数进行判断，从SystemConfig文件可以看到inSubQueryTransformToJoin参数为只读参数，代表是否将insubquery转换为join，默认为false

  1. readOnlyParams.add(new ParamInfo("inSubQueryTransformToJoin", 
  2. sysConfig.isInSubQueryTransformToJoin() + "",
  3. "The inSubQuery is transformed into the join ,the default value is false"));

判断为false会进入SubQueryPreNoTransformProcessor#optimize方法

  1.    public static PlanNode optimize(PlanNode qtn) {
  2.         TraceManager.TraceObject traceObject = TraceManager.threadTrace("for-subquery");
  3.         try {
  4.             MergeHavingFilter.optimize(qtn); //对having进行优化，将非聚合函数条件转为where条件
  5.             qtn = handlerComparisonsSubQuery(qtn);
  6.             return qtn;
  7.         } finally {
  8.             TraceManager.log(ImmutableMap.of("plan-node", qtn), traceObject);
  9.             TraceManager.finishSpan(traceObject);
  10.         }
  11.     }

首先执行MergeHavingFilter#optimize方法，对having条件进行优化，主要是判断having条件是否可以转为where条件，判断依据为条件中是否有聚合函数，代码分析如下

  1.     public static void optimize(PlanNode qtn) {
  2.         if (qtn.getHavingFilter() != null) {
  3.             List<Item> subFilters = FilterUtils.splitFilter(qtn.getHavingFilter()); //将每一个having条件放入列表
  4.             List<Item> canMergeSubs = new ArrayList<>(); //可以转换的条件列表
  5.             for (Item subFilter : subFilters) {
  6.                 if (!subFilter.isWithSumFunc()) { //判断是否为聚合条件
  7.                     canMergeSubs.add(subFilter);
  8.                 }
  9.             }
  10.             subFilters.removeAll(canMergeSubs);
  11.             qtn.having(FilterUtils.and(subFilters)); //将不满足条件的having条件放回having条件
  12.             qtn.setWhereFilter(FilterUtils.and(qtn.getWhereFilter(), FilterUtils.and(canMergeSubs))); //将满足条件的having条件转为where条件
  13.         }
  14.         for (PlanNode child : qtn.getChildren()) //如果有子查询递归子查询
  15.             optimize(child);
  16.     }
  17. }

如以下SQL语句，having条件有两个：id>1和max(id)>1，前一个条件就能优化为where条件，执行优化后结果如下：

  1. select content,max(id) from tb_enum_sharding group by content having id>=1 and max(id)>1;

image.png
优化前 优化后

JoinPreProcessor#optimize

JoinPreProcessor#optimize会将SQL的right join都转为left join，如A right join B on A.id = B.id将转为B left join B on A.id = B.id，JoinPreProcessor#optimize是调用JoinPreProcessor#findAndChangeRightJoinToLeftJoin方法

  1.  // transform right join to left join 将right join转为left join
  2. node = JoinPreProcessor.optimize(node);
  1.     public static PlanNode optimize(PlanNode qtn) {
  2.         TraceManager.TraceObject traceObject = TraceManager.threadTrace("optimize-for-join-order");
  3.         try {
  4.             qtn = findAndChangeRightJoinToLeftJoin(qtn);
  5.             return qtn;
  6.         } finally {
  7.             TraceManager.log(ImmutableMap.of("plan-node", qtn), traceObject);
  8.             TraceManager.finishSpan(traceObject);
  9.         }
  10.     }

JoinPreProcessor#findAndChangeRightJoinToLeftJoin会便利所有的子句，对每一个子句都递归调用该方法，如果不存在子句了会继续调用JoinNode#exchangeLeftAndRight方法

  1.     private static PlanNode findAndChangeRightJoinToLeftJoin(PlanNode qtn) {
  2.         for (PlanNode child : qtn.getChildren()) {
  3.             findAndChangeRightJoinToLeftJoin(child); //对每一个子句递归调用优化函数
  4.         }
  6.         if (qtn instanceof JoinNode && ((JoinNode) qtn).isRightOuterJoin()) {
  7.             JoinNode jn = (JoinNode) qtn;
  8.             jn.exchangeLeftAndRight(); //交换左右连接
  9.         }
  11.         return qtn;
  12.     }

JoinNode#exchangeLeftAndRight方法中实现了左右node交换

  1.     public void exchangeLeftAndRight() { //交换左右连接
  3.         PlanNode tmp = this.getLeftNode();
  4.         this.setLeftNode(this.getRightNode());
  5.         this.setRightNode(tmp);
  7.         boolean tmpOuter = this.leftOuter;
  8.         this.leftOuter = this.rightOuter;
  9.         this.rightOuter = tmpOuter;
  11.         this.buildJoinColumns(true);
  13.     }

示例SQL如下，执行该优化后可以看到所有的左连接转化为了右连接

  1. select * from tb_enum_sharding a right join tb_hash_sharding b on a.id = b.id where a.id >= 0；

image.png
优化前 优化后

LimitPusher#optimize

LimitPusher#optimize可以对limit进行下推，如存在union情况，可以将limit下推到每一个子句之中，LimitPusher#optimize是调用LimitPusher#findChild方法实现的

  1. //将limit下推，如存在union情况
  2. node = LimitPusher.optimize(node);
  1.     public static PlanNode optimize(PlanNode qtn) {
  2.         TraceManager.TraceObject traceObject = TraceManager.threadTrace("optimize-for-limit");
  3.         try {
  4.             qtn = findChild(qtn);
  5.             return qtn;
  6.         } finally {
  7.             TraceManager.log(ImmutableMap.of("plan-node", qtn), traceObject);
  8.             TraceManager.finishSpan(traceObject);
  9.         }
  10.     }

LimitPusher#findChild方法记录了有效的limit数据，并调用LimitPusher#pushLimit方法实现下推

  1.     private static PlanNode findChild(PlanNode qtn) { //limit优化器，对union进行优化
  2.         if (qtn instanceof MergeNode) {
  3.             // optimizer limit
  4.             // union: push down limit to children
  5.             // union all:push down limit to children and add distinct
  6.             MergeNode node = (MergeNode) qtn;
  7.             long limitFrom = node.getLimitFrom(); //记录limit from
  8.             long limitTo = node.getLimitTo(); //记录limit to
  9.             if (limitFrom != -1 && limitTo != -1) { //如果存在有效的limit
  10.                 for (PlanNode child : node.getChildren()) { //下推到每一个子句
  11.                     pushLimit(child, limitFrom, limitTo, node.isUnion());
  12.                 }
  13.             }
  15.         } else if ((qtn instanceof JoinNode) || (qtn instanceof QueryNode)) { 
  16.             for (PlanNode child : qtn.getChildren()) {
  17.                 findChild(child);
  18.             }
  19.         }
  20.         return qtn;
  21.     }

LimitPusher#pushLimit方法对每一个子句下推了limit

  1.     private static void pushLimit(PlanNode node, long limitFrom, long limitTo, boolean isUnion) {
  2.         if (isUnion) {
  3.             node.setDistinct(true);
  4.         }
  5.         if (node.getLimitFrom() == -1 && node.getLimitTo() == -1) {
  6.             node.setLimitFrom(0);
  7.             node.setLimitTo(limitFrom + limitTo);
  8.         }
  9.     }

示例SQL如下，经过limit优化之后可以看到limit已经下推到了每个子句中

  1. select * from tb_enum_sharding a union select * from tb_hash_sharding b limit 2;

image.png
优化前 优化后