@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()); // 串行
}
// 单表主键查询
// 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());
}
@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()); // 串行
}
// 两表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());
}
// 单表非主键无索引查询
// 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());
}
@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()); // 串行
}
// 两表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());
}
@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());
}
// 单表非主键索引查询
// 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());
}
// @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()); // 下推成功
}
@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");
}
// 单表复杂查询条件
// 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());
}
// 单表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());
}
@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());
}
@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());
}
// @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()); // 并行
}
/** 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");
}
/** 虽然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;
}
/**
* 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");
}