Java JoinNode类代码示例

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Override
public void visit(StraightJoin node) {// inner join
    TableReference ltable = node.getLeftTableRef();
    TableReference rtable = node.getRightTableRef();
    JoinNode joinNode = commonJoin(ltable, rtable);
    Expression cond = node.getOnCond();
    if (cond != null) {
        MySqlExprVisitor ev = new MySqlExprVisitor(parent);
        cond.accept(ev);
        IFilter ifilter = ev.getFilter();
        joinNode.setInnerJoin();
        addJoinOnColumns(ifilter, joinNode);
    }

    this.tableNode = joinNode;
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
/**
 * 会遍历所有节点将right join的左右节点进行调换，转换成left join.
 * 
 * <pre>
 * 比如 A right join B on A.id = B.id
 * 转化为 B left join B on A.id = B.id
 * </pre>
 */
private static QueryTreeNode findAndChangeRightJoinToLeftJoin(QueryTreeNode qtn) {
    for (ASTNode child : qtn.getChildren()) {
        findAndChangeRightJoinToLeftJoin((QueryTreeNode) child);
    }

    if (qtn instanceof JoinNode && ((JoinNode) qtn).isRightOuterJoin()) {
        /**
         * 如果带有其他非column=column条件，不能做这种转换，否则语义改变
         */
        if (qtn.getOtherJoinOnFilter() != null) {
            return qtn;
        }

        JoinNode jn = (JoinNode) qtn;
        jn.exchangeLeftAndRight();
        jn.build();
    }

    return qtn;
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
/**
 * <pre>
 * 由于优化过程中需要将QueryTree转换为执行计划树，而外部查询转换为执行计划树是依赖于子查询的
 * 所以需要先对子查询进行优化，再对外层查询进行优化，回溯完成
 * </pre>
 */
private static void optimizeSubQuery(QueryTreeNode qtn, Map<String, Object> extraCmd) {
    if (qtn instanceof QueryNode) {
        QueryNode qn = (QueryNode) qtn;
        if (qn.getChild() != null) {
            optimizeSubQuery(qn.getChild(), extraCmd);
            qn.setChild(optimizeJoin(qn.getChild(), extraCmd));
        }

        // 复制当前queryNode的where条件
        qn.setResultFilter(qn.getWhereFilter());
        qn.query((IFilter) null);
    } else if (qtn instanceof JoinNode) {
        JoinNode jn = (JoinNode) qtn;
        optimizeSubQuery(jn.getLeftNode(), extraCmd);
        optimizeSubQuery(jn.getRightNode(), extraCmd);
    } else {
        // 不会出现mergeNode
        // 不单独处理tableNode
    }

}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
/**
 * 判断是否需要调整join顺序
 * 
 * <pre>
 * 比如mysql: select xx from a,b,c where a.id = c.id and b.name = c.name
 * 这时的结构树为 (a join b) join c ， a join b上不存在join条件，需要调整join顺序为 (a join c) join b 或者 (b join c) join a
 * </pre>
 */
private static boolean needReChooseJoinOrder(QueryTreeNode qtn) {
    if (qtn instanceof JoinNode) {
        if (((JoinNode) qtn).getJoinFilter() == null || ((JoinNode) qtn).getJoinFilter().isEmpty()) {
            return true;
        }
    }

    for (ASTNode node : qtn.getChildren()) {
        if (!(node instanceof QueryTreeNode)) {
            return false;
        }

        if (needReChooseJoinOrder((QueryTreeNode) node)) {
            return true;
        }
    }

    return false;
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void test_join条件下推_子表_case3_不下推() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    table1.alias("A");
    QueryNode query = new QueryNode(table1);
    query.orderBy("ID");
    query.orderBy("NAME");
    query.limit(0, 10);

    table2.alias("B");

    JoinNode join = query.join(table2);
    join.setJoinStrategy(JoinStrategy.INDEX_NEST_LOOP);
    join.orderBy("A.NAME");
    join.orderBy("A.SCHOOL");
    join.build();

    OrderByPusher.optimize(join);

    Assert.assertEquals(2, query.getOrderBys().size());
    Assert.assertEquals("A.ID", query.getOrderBys().get(0).getColumn().toString());
    Assert.assertEquals("A.NAME", query.getOrderBys().get(1).getColumn().toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
/**
 * 找到join条件完全是分区键的filter，返回null代表没找到，否则返回join条件
 */
private static PartitionJoinResult isJoinOnPartition(JoinNode join) {
    QueryTreeNode left = join.getLeftNode();
    QueryTreeNode right = join.getRightNode();

    PartitionJoinResult leftResult = isJoinOnPartitionOneSide(join.getLeftKeys(), left);
    PartitionJoinResult rightResult = isJoinOnPartitionOneSide(join.getRightKeys(), right);
    // 允许一张单表和一张广播表的组合
    if (!leftResult.flag && !rightResult.broadcast) {
        return leftResult;
    }

    if (!rightResult.flag && !leftResult.broadcast) {
        return rightResult;
    }

    PartitionJoinResult result = new PartitionJoinResult();
    result.broadcast = leftResult.broadcast || rightResult.broadcast;
    result.flag = StringUtils.equalsIgnoreCase(leftResult.joinGroup, rightResult.joinGroup) || result.broadcast;
    result.joinGroup = leftResult.joinGroup;
    result.joinFilters = join.getJoinFilter();
    return result;
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void test_join查询_不生成JoinMergeJoin_左边是不是Merge_右边是子查询_开启优化参数() {
    KVIndexNode table1 = new KVIndexNode("TABLE8");// 这是一个单表，不分库
    table1.keyQuery("TABLE8.ID IN (1,2,3)");
    KVIndexNode table2 = new KVIndexNode("TABLE2");// 分库表
    table2.keyQuery("TABLE2.ID IN (1,2,3)");
    QueryNode query = new QueryNode(table2);
    query.setSubQuery(true); // 是个子查询，会进行BLOCK_NEST_LOOP处理，就不需要未决节点进行mget处理

    JoinNode join = table1.join(query, "ID", "ID");
    join.build();
    QueryTreeNode qtn = shard(join, false, true);

    Assert.assertTrue(qtn instanceof JoinNode);
    Assert.assertTrue(((JoinNode) qtn).getLeftNode() instanceof KVIndexNode);
    Assert.assertTrue(((JoinNode) qtn).getRightNode() instanceof MergeNode);
    Assert.assertTrue(((JoinNode) qtn).getRightNode().getChild() instanceof QueryNode);
    Assert.assertEquals(3, ((JoinNode) qtn).getRightNode().getChildren().size());// 是个子查询的Merge
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void testQuery_join_子查询_多级组合() throws SqlParserException {
    String joinSql = "SELECT TABLE1.ID,TABLE1.NAME FROM TABLE1 JOIN TABLE2 ON TABLE1.ID=TABLE1.ID WHERE TABLE1.NAME=1";
    String subsql = "SELECT * FROM (" + joinSql + " ) S WHERE S.NAME = 1";
    String sql = "SELECT * FROM (" + subsql + ") B , (" + subsql + ") C WHERE B.NAME = 6 AND B.ID = C.ID";
    QueryTreeNode qn = query(sql);
    qn.build();

    // System.out.println(qn);
    // 第一级是JoinNode
    // 第二级是QuerNode / QueryNode
    // 第三级是JoinNode / JoinNode
    Assert.assertTrue(qn instanceof JoinNode);
    Assert.assertTrue(((JoinNode) qn).getLeftNode() instanceof QueryNode);
    Assert.assertTrue(((JoinNode) qn).getRightNode() instanceof QueryNode);
    Assert.assertTrue(((QueryNode) ((JoinNode) qn).getLeftNode()).getChild() instanceof JoinNode);
    Assert.assertTrue(((QueryNode) ((JoinNode) qn).getRightNode()).getChild() instanceof JoinNode);
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void test_join查询_不生成merge() {
    KVIndexNode table1 = new KVIndexNode("TABLE1");
    table1.keyQuery("ID = 1");
    KVIndexNode table2 = new KVIndexNode("TABLE2");
    table2.keyQuery("ID = 1");

    JoinNode join = table1.join(table2, "ID", "ID");
    join.build();
    QueryTreeNode qtn = shard(join, false, false);

    Assert.assertTrue(qtn instanceof JoinNode);
    Assert.assertEquals("andor_group_0", ((JoinNode) qtn).getLeftNode().getDataNode());
    Assert.assertEquals("andor_group_0", ((JoinNode) qtn).getRightNode().getDataNode());
    Assert.assertEquals("table1_01", ((KVIndexNode) ((JoinNode) qtn).getLeftNode()).getActualTableName());
    Assert.assertEquals("table2_01", ((KVIndexNode) ((JoinNode) qtn).getRightNode()).getActualTableName());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void testQuery_join_子查询_多级组合() throws SqlParserException, QueryException {
    String joinSql = "SELECT TABLE1.ID,TABLE1.NAME FROM TABLE1 JOIN TABLE2 ON TABLE1.ID=TABLE1.ID WHERE TABLE1.NAME=1";
    String subsql = "SELECT * FROM (" + joinSql + " ) S WHERE S.NAME = 1";
    String sql = "SELECT * FROM (" + subsql + ") B , (" + subsql + ") C WHERE B.NAME = 6 AND B.ID = C.ID";
    QueryTreeNode qn = query(sql);
    qn.build();

    // System.out.println(qn);
    // 第一级是JoinNode
    // 第二级是QuerNode / QueryNode
    // 第三级是JoinNode / JoinNode
    Assert.assertTrue(qn instanceof JoinNode);
    Assert.assertTrue(((JoinNode) qn).getLeftNode() instanceof QueryNode);
    Assert.assertTrue(((JoinNode) qn).getRightNode() instanceof QueryNode);
    Assert.assertTrue(((QueryNode) ((JoinNode) qn).getLeftNode()).getChild() instanceof JoinNode);
    Assert.assertTrue(((QueryNode) ((JoinNode) qn).getRightNode()).getChild() instanceof JoinNode);
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void test_where条件下推_多级join() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    TableNode table3 = new TableNode("TABLE3");

    JoinNode join = table1.join(table2);
    join.select("TABLE1.ID AS ID , TABLE1.NAME AS NAME");
    join.query("TABLE1.ID>5 AND TABLE2.ID<10 AND TABLE1.NAME = TABLE2.NAME");
    join.build();

    JoinNode nextJoin = join.join(table3);
    nextJoin.query("TABLE1.NAME = 6 AND TABLE1.ID = TABLE3.ID");
    nextJoin.build();
    FilterPusher.optimize(nextJoin);

    Assert.assertEquals("(TABLE1.NAME = 6 AND TABLE1.ID > 5)", ((JoinNode) nextJoin.getLeftNode()).getLeftNode()
        .getWhereFilter()
        .toString());
    Assert.assertEquals("(TABLE2.ID < 10 AND TABLE2.NAME = 6)", ((JoinNode) nextJoin.getLeftNode()).getRightNode()
        .getWhereFilter()
        .toString());
    Assert.assertEquals("TABLE1.ID = TABLE3.ID", nextJoin.getJoinFilter().get(0).toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Override
public void visit(StraightJoin node) {// inner join
    TableReference ltable = node.getLeftTableRef();
    TableReference rtable = node.getRightTableRef();
    JoinNode joinNode = commonJoin(ltable, rtable);
    Expression cond = node.getOnCond();
    if (cond != null) {
        MySqlExprVisitor ev = new MySqlExprVisitor();
        cond.accept(ev);
        IFilter ifilter = ev.getFilter();
        joinNode.setInnerJoin();
        addJoinOnColumns(ifilter, joinNode);
    }

    this.tableNode = joinNode;
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void testJoin() {
    KVIndexNode studentID = new KVIndexNode("TABLE1");
    studentID.alias("TABLE1");
    studentID.select("ID,NAME,SCHOOL");

    KVIndexNode studentName = new KVIndexNode("TABLE1._NAME");
    studentName.alias("TABLE1._NAME");
    studentName.select("ID,NAME");
    studentName.keyQuery("NAME=1");

    studentID.valueQuery("SCHOOL=1");

    JoinNode join = studentName.join(studentID).addJoinKeys("ID", "ID");
    // build之前的操作順序可以任意
    join.build();

    System.out.println(join);
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void test_TableNode转化为index到kv_复杂条件查询() {
    TableNode table = new TableNode("TABLE1");
    table.select("(ID + NAME) AS NEWNAME"); // 设置为函数
    table.query("NAME = 1 AND ID > 3 AND SCHOOL = 1");
    table.orderBy("SCHOOL", false);// 增加一个隐藏列
    table.groupBy("NEWNAME");
    build(table);

    QueryTreeNode qn = table.convertToJoinIfNeed();

    Assert.assertTrue(qn instanceof JoinNode);
    Assert.assertEquals("TABLE1._NAME.NAME = 1", ((JoinNode) qn).getLeftNode().getKeyFilter().toString());
    Assert.assertEquals("TABLE1._NAME.ID > 3", ((JoinNode) qn).getLeftNode().getResultFilter().toString());
    Assert.assertEquals("TABLE1.SCHOOL = 1", ((JoinNode) qn).getRightNode().getResultFilter().toString());
    Assert.assertEquals("TABLE1._NAME.ID = TABLE1.ID", ((JoinNode) qn).getJoinFilter().get(0).toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入依赖的package包/类
@Test
public void test_join条件下推_子表_case1_下推NAME() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    table1.alias("A");
    table1.orderBy("ID");
    table2.alias("B");

    JoinNode join = table1.join(table2);
    join.setJoinStrategy(JoinStrategy.INDEX_NEST_LOOP);
    join.orderBy("A.ID");
    join.orderBy("A.NAME");
    join.build();

    OrderByPusher.optimize(join);

    Assert.assertEquals(2, table1.getOrderBys().size());
    Assert.assertEquals("TABLE1.ID", table1.getOrderBys().get(0).getColumn().toString());
    Assert.assertEquals("TABLE1.NAME", table1.getOrderBys().get(1).getColumn().toString());
}