MySQL8.0新特性-CTE語法支援

原文地址：

CTE（common table expression），針對同一個FROM子查詢在SQL中出現多次的情況，在建立計劃的時候，只對其中一個子查詢建立計劃，並將結果放到臨時表中，其它的子查詢直接使用臨時表。比如Oracle中經常使用的with as  /*+materialize*/ 用法。

首先，我們看一下簡單非遞迴的CTE的工作過程

CREATE TABLE t(a int);
INSERT INTO t VALUES(1),(2);
mysql>WITH abc as
(SELECT * FROM t)
SELECT * FROM abc;
+-------------+
| a           |
+-------------+
|           1 |
|           2 |
+-------------+
返回行數：[2]，耗時：9 ms.
--為了清楚的看到OPTIMIZER的最佳化過程，我們先暫且關閉derived_merge特性。
mysql>SET OPTIMIZER_SWITCH='derived_merge=off';
執行成功，耗時：9 ms.
mysql>explain WITH abc as (SELECT * FROM t) SELECT * FROM abc;
+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+
| 1  | PRIMARY     | <derived2> |            | ALL  |               |     |         |     | 2    |      100 |       |
| 2  | DERIVED     | t          |            | ALL  |               |     |         |     | 2    |      100 |       |
+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+
返回行數：[2]，耗時：9 ms.

mysql>SET OPTIMIZER_SWITCH='derived_merge=on';
執行成功，耗時：9 ms.
mysql>explain WITH abc as (SELECT * FROM t) SELECT * FROM abc;
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered   | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+
| 1  | SIMPLE      | t     |            | ALL  |               |     |         |     | 2    |        100 |       |
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+
返回行數：[1]，耗時：9 ms.

啊

mysql>EXPLAIN format = json WITH cte(x) as
		(SELECT * FROM t)
		SELECT * FROM 
			(SELECT * FROM cte) AS t1,
			(SELECT * FROM cte) AS t2;
-----------------------------------------
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.65"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 2,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.52",
            "eval_cost": "0.20",
            "prefix_cost": "2.73",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "x"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "2.72"
              },
              "table": {
                "table_name": "cte",
                "access_type": "ALL",
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 2,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "2.52",
                  "eval_cost": "0.20",
                  "prefix_cost": "2.73",
                  "data_read_per_join": "32"
                },
                "used_columns": [
                  "x"
                ],
                "materialized_from_subquery": {
                  "using_temporary_table": true,
                  "dependent": false,
                  "cacheable": true,
                  "query_block": {
                    "select_id": 3,
                    "cost_info": {
                      "query_cost": "0.45"
                    },
                    "table": {
                      "table_name": "t",
                      "access_type": "ALL",
                      "rows_examined_per_scan": 2,
                      "rows_produced_per_join": 2,
                      "filtered": "100.00",
                      "cost_info": {
                        "read_cost": "0.25",
                        "eval_cost": "0.20",
                        "prefix_cost": "0.45",
                        "data_read_per_join": "32"
                      },
                      "used_columns": [
                        "a"
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 4,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "2.53",
            "eval_cost": "0.40",
            "prefix_cost": "5.65",
            "data_read_per_join": "64"
          },
          "used_columns": [
            "x"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 4,
              "cost_info": {
                "query_cost": "2.72"
              },
              "table": {
                "table_name": "cte",
                "access_type": "ALL",
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 2,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "2.52",
                  "eval_cost": "0.20",
                  "prefix_cost": "2.73",
                  "data_read_per_join": "32"
                },
                "used_columns": [
                  "x"
                ],
                "materialized_from_subquery": {
                  "sharing_temporary_table_with": {
                    "select_id": 3
                  }
                }
              }
            }
          }
        }
      }
    ]
  }
} |