Exemple #1
0
 @Test
 public void test_两表join_单库单表_单表会优化为下推() {
   TableNode table = new TableNode("TABLE1");
   JoinNode join = table.join("TABLE8", "NAME", "NAME");
   join.query("TABLE1.ID = 0");
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
   Assert.assertTrue(qc instanceof IJoin);
   Assert.assertEquals(QUERY_CONCURRENCY.SEQUENTIAL, ((IJoin) qc).getQueryConcurrency()); // 串行
 }
Exemple #2
0
  // 单表主键查询
  // ID为主键,同时在ID上存在索引
  // 直接查询KV ID->data
  // keyFilter为ID=1
  @Test
  public void test_单表查询_主键条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("ID=1 AND ID<40");

    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IQuery);
    Assert.assertEquals("TABLE1.ID = 1", ((IQuery) qc).getKeyFilter().toString());
    Assert.assertEquals(null, ((IQuery) qc).getValueFilter());
  }
Exemple #3
0
 @Test
 public void test_两表join_单表_单库多表_生成JoinMergeJoin() {
   TableNode table = new TableNode("TABLE8");
   JoinNode join = table.join("TABLE1", "ID", "ID");
   join.query("TABLE1.ID IN (0,1)");
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertEquals(
       QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 串行
 }
Exemple #4
0
 // 两表Join查询,右表连接键为主键,右表为主键查询
 // 开启了join merge join
 // 右表主键索引的查询,Join列也索引列,应该选择IndexNestLoop
 // 会是一个(table1 join table2 index ) join table2 key 的多级join
 @Test
 public void test_两表Join_主键索引_存在主键索引条件() {
   TableNode table = new TableNode("TABLE1");
   JoinNode join = table.join("TABLE2", "ID", "ID");
   join.query("TABLE2.ID IN (1,2)");
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IJoin);
   IJoin subJoin = (IJoin) ((IMerge) qc).getSubNodes().get(0);
   Assert.assertEquals(JoinStrategy.INDEX_NEST_LOOP, subJoin.getJoinStrategy());
 }
Exemple #5
0
  // 单表非主键无索引查询
  // SCHOOL上不存在索引
  // 所以会执行全表扫描
  // 只会生成一个IQuery
  // SCHOOL=1作为valueFilter
  @Test
  public void test_单表查询_非任何索引条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("SCHOOL = 1");

    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(
        QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals("TABLE1.SCHOOL = 1", query.getValueFilter().toString());
  }
Exemple #6
0
 @Test
 public void test_两表join_orderby_groupby_limit条件() {
   TableNode table = new TableNode("TABLE1");
   JoinNode join = table.join("TABLE2", "ID", "ID");
   join.select(
       OptimizerUtils.createColumnFromString("TABLE1.ID AS JID"),
       OptimizerUtils.createColumnFromString("CONCAT(TABLE1.NAME,TABLE1.SCHOOL) AS JNAME"));
   join.orderBy("JID");
   join.groupBy("JNAME");
   join.having("COUNT(JID) > 0");
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 串行
 }
Exemple #7
0
 // 两表Join查询,右表连接键为主键,右表为二级索引查询
 // 开启了join merge join
 // 右表二级索引的查询,Join列也是二级索引索引,应该选择NestLoop
 // 会是一个(table1 index join table1 index ) join (table2 index join table2
 // key)的多级join
 @Test
 public void test_两表Join_二级索引_存在二级索引条件() {
   TableNode table = new TableNode("TABLE1");
   JoinNode join = table.join("TABLE2", "NAME", "NAME");
   join.query("TABLE2.NAME = 1");
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
   Assert.assertTrue(qc instanceof IJoin);
   Assert.assertTrue(((IJoin) qc).getLeftNode() instanceof IJoin);
   Assert.assertTrue(((IJoin) qc).getRightNode() instanceof IMerge);
   Assert.assertEquals(JoinStrategy.INDEX_NEST_LOOP, ((IJoin) qc).getJoinStrategy());
   IJoin subJoin = (IJoin) ((IJoin) qc).getLeftNode();
   Assert.assertTrue(((IJoin) subJoin).getLeftNode() instanceof IMerge);
   Assert.assertTrue(((IJoin) subJoin).getRightNode() instanceof IMerge);
   Assert.assertEquals(JoinStrategy.INDEX_NEST_LOOP, subJoin.getJoinStrategy());
 }
Exemple #8
0
 @Test
 public void test_两表join_单独limit条件_不做并行() {
   TableNode table = new TableNode("TABLE1");
   JoinNode join = table.join("TABLE2", "ID", "ID");
   join.select(
       OptimizerUtils.createColumnFromString("TABLE1.ID AS JID"),
       OptimizerUtils.createColumnFromString("CONCAT(TABLE1.NAME,TABLE1.SCHOOL) AS JNAME"));
   join.limit(10, 20);
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertEquals(QUERY_CONCURRENCY.SEQUENTIAL, ((IMerge) qc).getQueryConcurrency()); // 串行
   IJoin jn = (IJoin) ((IMerge) qc).getSubNodes().get(0);
   Assert.assertEquals("0", jn.getLimitFrom().toString());
   Assert.assertEquals("30", jn.getLimitTo().toString());
 }
Exemple #9
0
  // 单表非主键索引查询
  // NAME上存在索引
  // 会生成一个Join节点
  // 左边通过NAME索引找到满足条件的PK,keyFilter应该为NAME=1
  // 与pk->data Join
  // Join类型为IndexNestLoop
  @Test
  public void test_单表查询_value条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("NAME = 1");

    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(
        QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IJoin);
    IJoin join = (IJoin) dne;
    IQuery left = (IQuery) join.getLeftNode();
    Assert.assertEquals("TABLE1._NAME.NAME = 1", left.getKeyFilter().toString());
  }
Exemple #10
0
  // @Test
  public void test_单表merge_函数下推() {
    TableNode table = new TableNode("TABLE1");
    table.select("MAX(ID) AS ID");
    table.orderBy("COUNT(ID)");
    table.groupBy("SUBSTRING(NAME,0,10)");
    table.having("COUNT(ID) > 1");
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行

    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals("SUBSTRING(NAME, 0, 10)", query.getColumns().get(1).toString()); // 下推成功
    Assert.assertEquals("COUNT(ID)", query.getColumns().get(2).toString()); // 下推成功
  }
Exemple #11
0
  @Test
  public void testChooseIndex() throws QueryException {
    TableNode table = new TableNode("TABLE1");
    QueryTreeNode qn = table.query("ID=1");
    qn.build();

    IndexMeta index =
        IndexChooser.findBestIndex(
            table.getTableMeta(),
            emptyColumns,
            toDNFFilter(table.getWhereFilter()),
            table.getTableName(),
            extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE1");
  }
Exemple #12
0
 // 单表复杂查询条件
 // SCHOOL=1 AND (ID=4 OR ID=3)
 // 应该展开为
 // (SCHOOL=1 AND ID=4) OR (SCHOOL=1 AND ID=3)
 @Test
 public void test_单表查询_复杂条件展开() {
   TableNode table = new TableNode("TABLE1");
   table.query("SCHOOL=1 AND (ID=4 OR ID=3)");
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, true);
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, false);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertTrue(((IMerge) qc).isUnion()); // 是union查询
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IQuery);
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(1) instanceof IQuery);
   IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
   Assert.assertEquals("TABLE1.ID = 4", query1.getKeyFilter().toString());
   Assert.assertEquals("TABLE1.SCHOOL = 1", query1.getValueFilter().toString());
   IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);
   Assert.assertEquals("TABLE1.ID = 3", query2.getKeyFilter().toString());
   Assert.assertEquals("TABLE1.SCHOOL = 1", query2.getValueFilter().toString());
 }
Exemple #13
0
 // 单表or查询
 // 查询条件由or连接,
 // 由于NAME和ID上存在索引,所以会生成两个子查询
 // or的两边分别作为子查询的keyFilter
 // 由于NAME=2323的子查询为非主键索引查询
 // 所以此处会生成一个join节点
 // 最后一个merge节点用于合并子查询的结果
 @Test
 public void test_单表查询_OR条件_1() {
   TableNode table = new TableNode("TABLE1");
   table.query("NAME = 2323 OR ID=1");
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, true);
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, false);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertTrue(((IMerge) qc).isUnion()); // 是union查询
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IQuery);
   IQuery query = (IQuery) ((IMerge) qc).getSubNodes().get(0);
   Assert.assertEquals("TABLE1.ID = 1", query.getKeyFilter().toString());
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(1) instanceof IMerge);
   Assert.assertTrue(
       ((IMerge) ((IMerge) qc).getSubNodes().get(1)).getSubNodes().get(0) instanceof IJoin);
   IJoin join = (IJoin) ((IMerge) ((IMerge) qc).getSubNodes().get(1)).getSubNodes().get(0);
   Assert.assertEquals(
       "TABLE1._NAME.NAME = 2323", ((IQuery) join.getLeftNode()).getKeyFilter().toString());
 }
Exemple #14
0
  @Test
  public void test_单表查询_存在聚合函数_limit不下推() {
    TableNode table = new TableNode("TABLE1");
    table.limit(10, 20);
    table.select("count(distinct id)");
    QueryTreeNode qn = table;
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(qn, null, extraCmd);

    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(
        QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行
    Assert.assertEquals(10L, qc.getLimitFrom());
    Assert.assertEquals(20L, qc.getLimitTo());
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals(null, query.getLimitFrom());
    Assert.assertEquals(null, query.getLimitTo());
  }
Exemple #15
0
 @Test
 public void testChooseIndex手动指定索引() throws QueryException {
   TableNode table = new TableNode("TABLE9");
   table.build();
   table.useIndex(table.getTableMeta().getIndexs().get(0));
   table.build();
   System.out.println(table.toDataNodeExecutor());
 }
Exemple #16
0
 // @Test
 public void test_单表查询_函数下推() {
   TableNode table = new TableNode("TABLE1");
   table.select("ID");
   table.orderBy("COUNT(ID)");
   table.groupBy("NAME");
   table.having("COUNT(ID) > 1");
   table.query("ID = 1");
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
   Assert.assertTrue(qc instanceof IQuery);
   Assert.assertEquals(QUERY_CONCURRENCY.SEQUENTIAL, ((IQuery) qc).getQueryConcurrency()); // 并行
 }
Exemple #17
0
  /** C6,C7同时存在组合索引和倒排索引 同时有倒排和组合索引,并且选择度一样,优先选择组合 */
  @Test
  public void testChooseIndex选择度相同优先选组合() throws QueryException {
    TableNode table = new TableNode("TABLE9");
    QueryTreeNode qn = table.query("C6=10&&C7=3");
    qn.build();

    IndexMeta index =
        IndexChooser.findBestIndex(
            table.getTableMeta(),
            table.getColumnsRefered(),
            toDNFFilter(table.getWhereFilter()),
            table.getTableName(),
            extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE9._C6_C7");
  }
Exemple #18
0
  /** 虽然C1,C2上存在组合索引,但是由于范围查询的选择度不如等值查询 因此还是选择了单索引NAME=1 */
  @Test
  public void testChooseIndex单索引选择度好于组合索引() throws QueryException {
    TableNode table = new TableNode("TABLE9");
    QueryTreeNode qn = table.query("C1>10&&C2>3&&NAME=1");
    qn.build();

    IndexMeta index =
        IndexChooser.findBestIndex(
            table.getTableMeta(),
            table.getColumnsRefered(),
            toDNFFilter(table.getWhereFilter()),
            table.getTableName(),
            extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE9._NAME");
  }
  private static QueryTreeNode preProcess(QueryTreeNode qtn) throws QueryException {
    qtn.setOtherJoinOnFilter(processFilter(qtn.getOtherJoinOnFilter()));
    qtn.having(processFilter(qtn.getHavingFilter()));
    qtn.query(processFilter(qtn.getWhereFilter()));
    qtn.setKeyFilter(processFilter(qtn.getKeyFilter()));
    qtn.setResultFilter(processFilter(qtn.getResultFilter()));
    if (qtn instanceof TableNode) {
      ((TableNode) qtn)
          .setIndexQueryValueFilter(processFilter(((TableNode) qtn).getIndexQueryValueFilter()));
    }

    if (qtn instanceof JoinNode) {
      for (int i = 0; i < ((JoinNode) qtn).getJoinFilter().size(); i++) {
        processFilter(((JoinNode) qtn).getJoinFilter().get(i));
      }
    }

    for (ASTNode child : qtn.getChildren()) {
      preProcess((QueryTreeNode) child);
    }

    return qtn;
  }
Exemple #20
0
  /**
   * NAME=1的选择性显然比SCHOOL>1好,所以选择二级索引NAME
   *
   * @throws QueryException
   */
  @Test
  public void testChooseIndex列出现的顺序不影响索引选择() throws QueryException {
    TableNode table1 = new TableNode("TABLE1");
    QueryTreeNode qn1 = table1.query("SCHOOL>1&&NAME=1");
    qn1.build();

    IndexMeta index =
        IndexChooser.findBestIndex(
            table1.getTableMeta(),
            table1.getColumnsRefered(),
            toDNFFilter(table1.getWhereFilter()),
            table1.getTableName(),
            extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE1._NAME");

    TableNode table2 = new TableNode("TABLE1");
    QueryTreeNode qn2 = table2.query("NAME=1&&SCHOOL>1");
    qn2.build();

    index =
        IndexChooser.findBestIndex(
            table2.getTableMeta(),
            table2.getColumnsRefered(),
            toDNFFilter(table2.getWhereFilter()),
            table2.getTableName(),
            extraCmd);

    Assert.assertNotNull(index);
    Assert.assertEquals(index.getName(), "TABLE1._NAME");
  }