【GreatSQL优化器-12】make_tmp_tables_info

最新推荐文章于 2025-09-30 17:07:51 发布

原创

最新推荐文章于 2025-09-30 17:07:51 发布 · 710 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#GreatSQL #MySQL

【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,