author：彭程

介绍

访问器（Visitor）能够对语法分析器（Parser）生成的抽象语法树进行遍历，获取需要的信息。本文主要介绍复杂SQL的Visitor构造过程

SQL语句

以如下SQL语句为例介绍dble的Visitor构造过程

  1. select * from tb_enum_sharding a left join tb_hash_sharding b on a.id = b.id;

Visitor访问过程

上一篇中说到dble会在NonBlockingSession#executeMultiSelect方法中构建Visitor，并调用Visitor的MySQLPlanNodeVisitor#visit方法访问druid生成的语法树SQLSelectStatement(ast),并会调用其重载方法visit(SQLSelectQuery node)

  1. public boolean visit(SQLSelectStatement node) {
  2.         TraceManager.TraceObject traceObject = TraceManager.threadTrace("visit-for-sql-structure");
  3.         try {
  4.             SQLSelectQuery sqlSelect = node.getSelect().getQuery();
  5.             MySQLPlanNodeVisitor mtv = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  6.             mtv.visit(sqlSelect); //调用其重载方法
  7.             this.tableNode = mtv.getTableNode();
  8.             this.containSchema = mtv.isContainSchema();
  9.             MySQLItemVisitor.clearCache();
  10.             return true;
  11.         } finally {
  12.             TraceManager.finishSpan(traceObject);
  13.         }
  14.     }

visit函数会先判断SQL中是否包含union，示例SQL没有union，所以进入visit(MySqlSelectQueryBlock node)方法

  1.     public void visit(SQLSelectQuery node) {
  2.         if (node instanceof MySqlSelectQueryBlock) { //如果是查询语句
  3.             visit((MySqlSelectQueryBlock) node);
  4.         } else if (node instanceof SQLUnionQuery) {  //如果是union查询语句
  5.             visit((SQLUnionQuery) node);
  6.         }
  7.     }

visit(MySqlSelectQueryBlock node)方法访问到from后的SQL，此时需要判断是否有SQL嵌套，如select code from (select * from tb_enum_sharding where id > 1) as a;本例没有，则进入from的访问。

  1.     public boolean visit(MySqlSelectQueryBlock sqlSelectQuery) {
  2.         SQLTableSource from = sqlSelectQuery.getFrom();  // 获取from后的SQL
  3.         if (from != null) {
  4.                 //判断from后是否还有select，如select code from (select * from tb_enum_sharding where id > 1) as a;
  5.             String innerFuncSelectSQL = createInnerFuncSelectSQL(sqlSelectQuery.getSelectList());
  6.             if (innerFuncSelectSQL != null) {
  7.                 MySQLPlanNodeVisitor mtv = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  8.                 mtv.visit(from);
  9.                 NoNameNode innerNode = new NoNameNode(currentDb, innerFuncSelectSQL);
  10.                 innerNode.setFakeNode(true);
  11.                 List<Item> selectItems = handleSelectItems(selectInnerFuncList(sqlSelectQuery.getSelectList()));
  12.                 if (selectItems != null) {
  13.                     innerNode.select(selectItems);
  14.                 }
  15.                 this.tableNode = new JoinInnerNode(innerNode, mtv.getTableNode());
  16.                 this.containSchema = mtv.isContainSchema();
  17.             } else {
  18.                 visit(from); //进入from的访问
  19.             }
  20.             if (this.tableNode instanceof NoNameNode) {
  21.                 this.tableNode.setSql(SQLUtils.toMySqlString(sqlSelectQuery));
  22.             }
  23.         } else {
  24.             this.tableNode = new NoNameNode(currentDb, SQLUtils.toMySqlString(sqlSelectQuery));
  25.             String innerFuncSelectSQL = createInnerFuncSelectSQL(sqlSelectQuery.getSelectList());
  26.             if (innerFuncSelectSQL != null) {
  27.                 ((NoNameNode) tableNode).setFakeNode(true);
  28.             }
  29.         }
  30.                 ...
  31.         return true;
  32.     }

访问from会根据from后面的语句类型进行区分，不同的类型又会进行不同的访问，如本例中from是left join，因此会进入visit(SQLJoinTableSource)方法

  1.     public void visit(SQLTableSource tables) {
  2.         if (tables instanceof SQLExprTableSource) { //from后面接简单的表名
  3.             SQLExprTableSource table = (SQLExprTableSource) tables;
  4.             MySQLPlanNodeVisitor mtv = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  5.             mtv.visit(table);
  6.             this.tableNode = mtv.getTableNode();
  7.             this.containSchema = mtv.isContainSchema();
  8.         } else if (tables instanceof SQLJoinTableSource) { //from后面的表使用了连接操作
  9.             SQLJoinTableSource joinTables = (SQLJoinTableSource) tables; //将from转为SQLJoinTableSource对象
  10.             MySQLPlanNodeVisitor mtv = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  11.             mtv.visit(joinTables);
  12.             this.tableNode = mtv.getTableNode();
  13.             this.containSchema = mtv.isContainSchema();
  14.         } else if (tables instanceof SQLUnionQueryTableSource) { ////from后面内部持有一个SQLUnionQuery对象
  15.             if (tables.getAlias() == null) {
  16.                 throw new MySQLOutPutException(ErrorCode.ER_DERIVED_MUST_HAVE_ALIAS, "", "Every derived table must have its own alias");
  17.             }
  18.             SQLUnionQueryTableSource unionTables = (SQLUnionQueryTableSource) tables;
  19.             MySQLPlanNodeVisitor mtv = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  20.             mtv.visit(unionTables);
  21.             this.tableNode = new QueryNode(mtv.getTableNode());
  22.             this.tableNode.setContainsSubQuery(mtv.getTableNode().isContainsSubQuery());
  23.             this.containSchema = mtv.isContainSchema();
  24.         } else if (tables instanceof SQLSubqueryTableSource) { //子查询表
  25.             if (tables.getAlias() == null) {
  26.                 throw new MySQLOutPutException(ErrorCode.ER_DERIVED_MUST_HAVE_ALIAS, "", "Every derived table must have its own alias");
  27.             }
  28.             SQLSubqueryTableSource subQueryTables = (SQLSubqueryTableSource) tables;
  29.             MySQLPlanNodeVisitor mtv = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  30.             mtv.visit(subQueryTables);
  31.             this.tableNode = new QueryNode(mtv.getTableNode());
  32.             this.tableNode.setContainsSubQuery(mtv.getTableNode().isContainsSubQuery());
  33.             this.containSchema = mtv.isContainSchema();
  34.         }
  35.         if (tables.getAlias() != null) {
  36.             this.tableNode.setAlias(tables.getAlias());
  37.         }
  38.     }

这里进行进一步进行访问时，dble会将from强制转化为一个SQLJoinTableSource对象，该对象有left、right分别代表左、右表，是一种二叉树结构，除此之外joinType属性表示连接的类型，condition属性表示连接条件image.png
visit(SQLJoinTableSource)中会对left和right继续调用上文的visit(SQLTableSource)方法，直到left和right中没有子树，之后会根据JoinType的类型，本例是left join，因此设置node.LeftOuterJoin=true

  1.     public boolean visit(SQLJoinTableSource joinTables) {
  2.         SQLTableSource left = joinTables.getLeft();
  3.         MySQLPlanNodeVisitor mtvLeft = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  4.         mtvLeft.visit(left);
  5.         SQLTableSource right = joinTables.getRight();
  6.         MySQLPlanNodeVisitor mtvRight = new MySQLPlanNodeVisitor(this.currentDb, this.charsetIndex, this.metaManager, this.isSubQuery, this.usrVariables);
  7.         mtvRight.visit(right);
  8.         JoinNode joinNode = new JoinNode(mtvLeft.getTableNode(), mtvRight.getTableNode(), this.charsetIndex);
  9.         joinNode.setContainsSubQuery(mtvLeft.getTableNode().isContainsSubQuery() || mtvRight.getTableNode().isContainsSubQuery());
  10.         switch (joinTables.getJoinType()) {
  11.             case JOIN:
  12.             case CROSS_JOIN:
  13.             case INNER_JOIN:
  14.             case STRAIGHT_JOIN:
  15.                 joinNode.setInnerJoin();
  16.                 break;
  17.             case LEFT_OUTER_JOIN:
  18.                 if ((joinTables.getCondition() == null) && (joinTables.getUsing() == null || joinTables.getUsing().size() == 0) && !joinTables.isNatural()) {
  19.                     throw new MySQLOutPutException(ErrorCode.ER_PARSE_ERROR, "42000", "left join without join_condition!");
  20.                 }
  21.                 joinNode.setLeftOuterJoin();
  22.                 break;
  23.             case RIGHT_OUTER_JOIN:
  24.                 if ((joinTables.getCondition() == null) && (joinTables.getUsing() == null || joinTables.getUsing().size() == 0) && !joinTables.isNatural()) {
  25.                     throw new MySQLOutPutException(ErrorCode.ER_PARSE_ERROR, "42000", "right join without join_condition!");
  26.                 }
  27.                 joinNode.setRightOuterJoin();
  28.                 break;
  29.             case NATURAL_JOIN:// never happen
  30.                 break;
  31.             default:
  32.                 break;
  33.         }
  34.                ...
  35.         this.tableNode = joinNode;
  36.         this.containSchema = mtvLeft.isContainSchema() || mtvRight.isContainSchema();
  37.         return true;
  38.     }

访问器访问到底后返回到visit(MySqlSelectQueryBlock node)方法，之后会继续判断是否有orderby、groupby等，并在各自的处理函数中对node进行更新。

  1.     public boolean visit(MySqlSelectQueryBlock sqlSelectQuery) {
  2.         SQLTableSource from = sqlSelectQuery.getFrom();  // 获取from后的SQL
  3.         if (from != null) {
  4.            ...
  5.             } else {
  6.                 visit(from);
  7.             }
  8.             if (this.tableNode instanceof NoNameNode) {
  9.                 this.tableNode.setSql(SQLUtils.toMySqlString(sqlSelectQuery));
  10.             }
  11.         } else {
  12.             this.tableNode = new NoNameNode(currentDb, SQLUtils.toMySqlString(sqlSelectQuery));
  13.             String innerFuncSelectSQL = createInnerFuncSelectSQL(sqlSelectQuery.getSelectList());
  14.             if (innerFuncSelectSQL != null) {
  15.                 ((NoNameNode) tableNode).setFakeNode(true);
  16.             }
  17.         }
  19.         if (tableNode != null && (sqlSelectQuery.getDistionOption() == SQLSetQuantifier.DISTINCT || sqlSelectQuery.getDistionOption() == SQLSetQuantifier.DISTINCTROW)) {
  20.             this.tableNode.setDistinct(true);
  21.         }
  23.         List<SQLSelectItem> items = sqlSelectQuery.getSelectList();
  24.         if (items != null) {
  25.             List<Item> selectItems = handleSelectItems(items);
  26.             if (selectItems != null) {
  27.                 this.tableNode.select(selectItems);
  28.             }
  29.         }
  31.         SQLExpr whereExpr = sqlSelectQuery.getWhere();
  32.         if (whereExpr != null) {
  33.             handleWhereCondition(whereExpr);
  34.         }
  36.         SQLOrderBy orderBy = sqlSelectQuery.getOrderBy();
  37.         if (orderBy != null) {
  38.             handleOrderBy(orderBy);
  39.         }
  41.         SQLSelectGroupByClause groupBy = sqlSelectQuery.getGroupBy();
  42.         if (groupBy != null) {
  43.             handleGroupBy(groupBy);
  44.         }
  46.         SQLLimit limit = sqlSelectQuery.getLimit();
  47.         if (limit != null) {
  48.             handleLimit(limit);
  49.         }
  50.         return true;
  51.     }

访问器执行结束后就能看到visitor.PlanNode的值如下

  1. Join
  2.     joinStrategy: SORTMERGE
  3.     type: left outter join
  4.     joinFilter: a.id = b.id
  5.     otherJoinOnFilter: null
  6.     leftJoinOnOrder:  
  7.     rightJoinOnOrder:  
  8.     isDistinct: false
  9.     columns: *
  10.     where: null
  11.     having: null
  12.     groupBy:  
  13.     orderBy:  
  14.     sql: null
  15.     left:
  16.         Query from tb_enum_sharding as a
  17.             isDistinct: false
  18.             columns:  
  19.             where: null
  20.             having: null
  21.             groupBy:  
  22.             orderBy:  
  23.             sql: null
  24.     right:
  25.         Query from tb_hash_sharding as b
  26.             isDistinct: false
  27.             columns:  
  28.             where: null
  29.             having: null
  30.             groupBy:  
  31.             orderBy:  
  32.             sql: null