【GreatSQL优化器-12】make_tmp_tables_info
【GreatSQL优化器-12】make_tmp_tables_info
一、make_tmp_tables_info介绍
GreatSQL的优化器对于聚合函数和窗口函数需要创建内部临时表来进行计算并输出最后结果,这个内部临时表又需要原始表来作为数据输入源,具体的代码处理在make_tmp_tables_info
函数实现。
下面用一个简单的例子来说明make_tmp_tables_info
是做什么的。
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> select to_char(t1.c1),t1.c2+1 from t1 join t2 on t1.c1=t2.cc1 group by t1.c1,t1.c2;
{
"optimizing_distinct_group_by_order_by": {
"simplifying_group_by": {
"original_clause": "`t1`.`c1`,`t1`.`c2`",
"items": [
{
"item": "`t1`.`c1`"
},
{
"item": "`t1`.`c2`"
}
],
"resulting_clause_is_simple": false,
"resulting_clause": "`t1`.`c1`,`t1`.`c2`"
}
}
},
{
"finalizing_table_conditions": [
{
"table": "`t1`",
"original_table_condition": "(`t1`.`c1` = `t2`.`cc1`)",
"final_table_condition ": null
}
]
},
{
"refine_plan": [
{
"table": "`t2`"
},
{
"table": "`t1`"
}
]
},
{
"considering_tmp_tables": [ 这里创建临时表
{
"adding_tmp_table_in_plan_at_position": 2, 临时表总是添加在主表之后
"write_method": "write_all_rows"
}
]
}
]
}
},
{
{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "<temporary>",
"in_plan_at_position": 2,
"columns": 4, 这里有4个列,说明见下面
"row_length": 64,
"key_length": 9,
"unique_constraint": false,
"makes_grouped_rows": true,
"cannot_insert_duplicates": false,
"location": "TempTable"
}
}
},
{
"materialize": {
"select#": 1,
"steps": [
]
}
}
]
}
}
二、make_tmp_tables_info代码解释
内部临时表在优化器的顺序都是排在最后面,因此在优化器最后阶段才创建临时表。但是所需临时表的个数在这之前的make_join_plan()
就计算出来了。这里面涉及到一个很重要的概念:ref_items
数组分片,这个存储的是不同层所有涉及的Item,实现输入表和输出表不同的结果。这其中通过tmp_table_param
变量作为原始表和临时表之间的桥,进行不同表之间的值传递。每张临时表都有一个对应的tmp_table_param
变量。
bool JOIN::optimize(bool finalize_access_paths) {
// 这里进行贪婪搜索,给所有表排序
make_join_plan();
// 判断是否需要简化order by、group by和distinct语句,给join->simple_order和join->simple_group赋值
optimize_distinct_group_order();
// 接着给join->need_tmp_before_win赋值,如果这个值为true才需要创建tmp table
// 如果join->need_tmp_before_win=true,那么创建临时表进行最终结果的操作
make_tmp_tables_info();
}
bool JOIN::make_join_plan() {
// 这里进行贪婪搜索,给所有表排序
if (Optimize_table_order(thd, this, nullptr).choose_table_order())
return true;
// 排序结束计算tmp table的数量,预分配内存给tmp table,需要创建tmp table的场景见表一
if (get_best_combination()) return true;
}
bool JOIN::make_tmp_tables_info() {
if (ref_items[REF_SLICE_SAVED_BASE].is_null()) {
if (alloc_ref_item_slice(thd, REF_SLICE_SAVED_BASE)) return true;
// 从REF_SLICE_ACTIVE分片拷贝Item到REF_SLICE_SAVED_BASE分片,因为REF_SLICE_ACTIVE分片后面可能会改变
copy_ref_item_slice(REF_SLICE_SAVED_BASE, REF_SLICE_ACTIVE);
current_ref_item_slice = REF_SLICE_SAVED_BASE;
}
// 创建内部临时表,给tmp_table_param的items_to_copy指定一一对应关系,见表四说明
create_intermediate_table();
// 通过item->get_tmp_table_item(thd)和item->get_tmp_table_field()给分片REF_SLICE_TMP1赋值,这里REF_SLICE_SAVED_BASE和REF_SLICE_TMP1的item之间关系见下面表四。注意这里会改变join->fields包含的item,这是输出到客户端的结果。
if (change_to_use_tmp_fields(curr_fields, thd, ref_items[REF_SLICE_TMP1],
&tmp_fields[REF_SLICE_TMP1],
query_block->m_added_non_hidden_fields))
return true;
// 如果有窗口函数,给REF_SLICE_WIN_1以及以上分片Item赋值
if (qep_tab && m_windowing_steps){
// 给分片REF_SLICE_WIN_1赋值
if (change_to_use_tmp_fields(curr_fields, thd, ref_items[REF_SLICE_WIN_1 + wno],
&tmp_fields[REF_SLICE_WIN_1 + wno],
query_block->m_added_non_hidden_fields))
return true;
// 给窗口函数的frame_buffer再创建一张临时表
CreateFramebufferTable();
}
}
表一:需要建立临时表的场景
序号 | 场景 |
---|---|
1 | GROUP BY |
2 | DISTINCT |
3 | DISTINCT与聚合函数结合 |
4 | ORDER BY |
5 | SELECT_BIG_RESULT |OPTION_BUFFER_RESULT关键字 |
表二:join->ref_items数组分片
序号 | 场景 | 举例 |
---|---|---|
REF_SLICE_ACTIVE | 当前正在使用的分片,初始值是select的列,需要用临时表的时候执行set_ref_item_slice拷贝Item | |
REF_SLICE_TMP1 | 指向第一张内部临时表的列分片 | group by |
REF_SLICE_TMP2 | 指向第二张内部临时表的列分片 | distinct group by rollup |
REF_SLICE_SAVED_BASE | 原始表的列分片 | 正常select |
REF_SLICE_WIN_1 | 指向第一张内部临时表的列相关的窗口item分片 | sum(c1) group by |
注:对于需要内部临时表的查询,输入的表和输出的表是不同的,因此需要切换表分片,让临时表的结果能以Item形式输出到客户端
表三:join->ref_items[REF_SLICE_TMP1]里面Item存放格式
可见列 offset=n | 不可见列 offset=0 |
---|---|
正常列offset | 不可见列倒置存入 |
注:详细说明见函数change_to_use_tmp_fields_except_sums()
表四:REF_SLICE_SAVED_BASE和REF_SLICE_TMP1的item之间关系
项目 | REF_SLICE_SAVED_BASE分片 | REF_SLICE_TMP1分片 | 说明 |
---|---|---|---|
所属Item | 主表Item | 内部临时表Item | func_ptr=主表Item,result_field=主表Item->get_result_field() |
Field | result_field | field | 这两个相等,用法: tmp_table_param->items_to_copy->push_back(Func_ptr(func_ptr, (func_ptr)->get_result_field())); 这里func_ptr和func_ptr->get_result_field()一一对应,需要值的时候通过copy_funcs进行赋值,这之前需要经过Copy_func_type类型判断,见表五 |
表五:Copy_func_type类型
Copy_func_type | 说明 |
---|---|
CFT_ALL | 非窗口函数的步骤拷贝所有函数,默认模式 |
CFT_WF_FRAMING | 窗口函数的步骤,拷贝非framing窗口函数,比如:SUM, AVG and FIRST_VALUE, LAST_VALUE |
CFT_WF_NON_FRAMING | 窗口函数的步骤,拷贝framing窗口函数,比如ROW_NUMBER, RANK, DENSE_RANK。即需要执行两阶段的函数 |
CFT_WF_NEEDS_PARTITION_CARDINALITY | 窗口函数的步骤,拷贝需要框架基数的窗口函数,比如partition by |
CFT_WF_USES_ONLY_ONE_ROW | 窗口函数的步骤,拷贝只需要一行的framing窗口函数 |
CFT_HAS_NO_WF | 在窗口函数的步骤第一步,拷贝不需要窗口函数的函数 |
CFT_HAS_WF | 在窗口函数的步骤最后一步,拷贝不需要窗口函数的函数,比如1+RANK |
CFT_WF | 拷贝所有窗口函数 |
CFT_FIELDS | 只拷贝Item_field |
三、实际例子说明
接下来看最初的例子来说明上面的代码。
greatsql> SELECT to_char(t1.c1),t1.c2+1 FROM t1 JOIN t2 ON t1.c1=t2.cc1 GROUP BY t1.c1,t1.c2;
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "<temporary>",
"in_plan_at_position": 2,
"columns": 4, 这里是临时表的列数量
"row_length": 64, 这里是exec_tmp_table->s->reclength=64
"key_length": 9,
"unique_constraint": false,
"makes_grouped_rows": true,
"cannot_insert_duplicates": false,
"location": "TempTable"
}
}
},
{
"materialize": {
"select#": 1,
"steps": [
]
}
}
]
}
}
上面的临时表结构,可以看到hidden列是倒序存放的,可见列把Item转为result_field存放。
field offset | 3 | 2 | 1 | 0 |
---|---|---|---|---|
hidden | false | false | true | true |
Field name | temp_table.c2+1 | to_char(temp_table.c1) | temp_table.c1 | temp_table.c2 |
Field type | Field_longlong | Field_varstring | Field_long | Field_long |
临时表QEP_TAB的tmp_table_param->items_to_copy
,这里通过tmp_table_param->copy_funcs()
就可以实现m_func
和m_result_field
之间的值传递。
Item offset | 3 | 2 | 1 | 0 |
---|---|---|---|---|
m_func | Item_field | Item_field | Item_func_plus | Item_typecast_char |
m_func name | t1.c2 | t1.c1 | t1.c2+1 | to_char(t1.c1) |
m_result_field | temp_table.c2 | temp_table.c1 | temp_table.c2+1 | to_char(temp_table.c1) |
REF_SLICE_SAVED_BASE分片Item信息
Item offset | 3 | 2 | 1 | 0 |
---|---|---|---|---|
Item | Item_field | Item_field | Item_func_plus | Item_typecast_char |
Item name | t1.c2 | t1.c1 | t1.c2+1 | to_char(t1.c1) |
Item->result_field | temp_table.c2 | temp_table.c1 | temp_table.c2+1 | to_char(temp_table.c1) |
REF_SLICE_TMP1分片Item信息
Item offset | 3 | 2 | 1 | 0 |
---|---|---|---|---|
Item | Item_field | Item_field | Item_field | Item_field |
Item name | temp_table.c2 | temp_table.c1 | temp_table..c2+1 | to_char(temp_table..c1) |
Item->result_field | temp_table.c2 | temp_table.c1 | temp_table.c2+1 | to_char(temp_table.c1) |
以上值传递过程:
copy_funcs() val_str()
REF_SLICE_SAVED_BASE分片的Item ------------> result_field(temp_table.field) -----------> REF_SLICE_TMP1分片的Item --->输出到客户端
下面是最终的执行计划,可以看到临时表排序是在最后的。
greatsql> EXPLAIN FORMAT=TREE select to_char(t1.c1),t1.c2+1 from t1 join t2 on t1.c1=t2.cc1 group by t1.c1,t1.c2;
| EXPLAIN
| -> Table scan on <temporary> (cost=4.26..6.31 rows=5)
-> Temporary table with deduplication (cost=3.75..3.75 rows=5)
-> Nested loop inner join (cost=3.25 rows=5)
-> Index scan on t2 using idx2_1 (cost=1.50 rows=5)
-> Single-row index lookup on t1 using PRIMARY (c1=t2.cc1) (cost=0.27 rows=1)
四、总结
从上面优化器的步骤我们认识了优化器创建内部临时表的过程,以及知道了如何实现输入表和输出表不同的处理,还通过具体例子知道了临时表的结构以及值传递的过程,需要注意的是,创建内部临时表后会改变输入的fields值,从原始表的Item变为临时表的Item,如果开发用到这个fields值的话,需要注意取值时机有可能取到临时表的值,更甚者临时表被释放后取值会导致crash。
Enjoy GreatSQL 😃
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html
技术交流群:
微信:扫码添加
GreatSQL社区助手
微信好友,发送验证信息加群
。