【GreatSQL优化器-07】mm tree - IT评测·应用市场-qidao123.com

greatsql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT,date1 DATETIME);
greatsql> INSERT INTO t1 VALUES (1,10,'2021-03-25 16:44:00.123456'),(2,1,'2022-03-26 16:44:00.123456'),(3,4,'2023-03-27 16:44:00.123456'),(5,5,'2024-03-25 16:44:00.123456'),(7,null,'2020-03-25 16:44:00.123456'),(8,10,'2020-10-25 16:44:00.123456'),(11,16,'2023-03-25 16:44:00.123456');
greatsql> CREATE TABLE t2 (cc1 INT PRIMARY KEY, cc2 INT);
greatsql> INSERT INTO t2 VALUES (1,3),(2,1),(3,2),(4,3),(5,15);
greatsql> CREATE TABLE t3 (ccc1 INT, ccc2 varchar(100));
greatsql> INSERT INTO t3 VALUES (1,'aa1'),(2,'bb1'),(3,'cc1'),(4,'dd1'),(null,'ee');
greatsql> CREATE INDEX idx1 ON t1(c2);
greatsql> CREATE INDEX idx2 ON t1(c2,date1);
greatsql> CREATE INDEX idx2_1 ON t2(cc2);
greatsql> CREATE INDEX idx3_1 ON t3(ccc1);
greatsql> EXPLAIN SELECT * FROM t1 WHERE (c1=1 AND c2<10) OR (c2<6 AND date1 < '2023-03-27 16:44:00.123456') OR (c2>10 and c2<15 AND c1>0);
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx1",
"ranges": [ # 这里面就是一个mm tree二叉树结果
"NULL < c2 < 6",
"6 <= c2 < 10",
"10 < c2 < 15"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 5,
"cost": 8.51,
"chosen": false,
"cause": "cost"
},
{
"index": "idx2",
"ranges": [ # 这里面就是一个mm tree二叉树结果
"NULL < c2 < 6",
"6 <= c2 < 10",
"10 < c2 < 15"
],
"index_dives_for_eq_ranges": true, # 用范围扫描来估计cost，这个涉及到index dives，下一期讲
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"in_memory": 1,
"rows": 5, # 这里的意思是在 c2<15 范围内有5条记录
"cost": 0.761828,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx2",
"rows": 5,
"ranges": [
"NULL < c2 < 6",
"6 <= c2 < 10",
"10 < c2 < 15"
]
},
"rows_for_plan": 5,
"cost_for_plan": 0.761828,
"chosen": true
}
}
}
]
},

复制代码

# 代码流程：make_join_plan --> estimate_rowcount --> get_quick_record_count --> test_quick_select
int test_quick_select() {
RANGE_OPT_PARAM param;
# 找出所有sql条件涉及的索引
if (setup_range_optimizer_param(thd, return_mem_root, temp_mem_root,
keys_to_use, table, query_block, &param)) {
return 0;
}
# 有condition条件的话，执行以下函数
get_mm_tree();
if(tree) {
# 用下面的函数来计算索引和范围对应的cost，这个下一期讲
get_key_scans_params();
}
}
SEL_TREE *get_mm_tree(THD *thd, RANGE_OPT_PARAM *param, table_map prev_tables,
table_map read_tables, table_map current_table,
bool remove_jump_scans, Item *cond) {
# 1、如果是Item::COND_ITEM的话，遍历所有参数
for (Item &item : *down_cast<Item_cond *>(cond)->argument_list()) {
get_mm_tree();
如果是and条件，tree = tree_and()
如果是OR条件，tree = tree_or()
}
# 2、如果非条件Item，见下表一
switch (cond_func->functype()) {
case Item_func::BETWEEN:
case Item_func::IN_FUNC:
case Item_func::MULT_EQUAL_FUNC:
default:
}
}

复制代码

parent 黑
/ \
当前SEL_ARG 当前SEL_ARG 红
/ \ / \
left right left right 黑

复制代码