【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();
      }
}

表一：需要建立临时表的场景

表二：join->ref_items数组分片

注：对于需要内部临时表的查询，输入的表和输出的表是不同的，因此需要切换表分片，让临时表的结果能以Item形式输出到客户端

表三：join->ref_items[REF_SLICE_TMP1]里面Item存放格式

注：详细说明见函数change_to_use_tmp_fields_except_sums()

表四：REF_SLICE_SAVED_BASE和REF_SLICE_TMP1的item之间关系

表五：Copy_func_type类型

三、实际例子说明

接下来看最初的例子来说明上面的代码。

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存放。

临时表QEP_TAB的tmp_table_param->items_to_copy，这里通过tmp_table_param->copy_funcs()就可以实现m_func和m_result_field之间的值传递。

REF_SLICE_SAVED_BASE分片Item信息

REF_SLICE_TMP1分片Item信息

以上值传递过程：

                               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。