ITPub博客

首页 > 数据库 > MySQL > MySQL8.0新特性-CTE语法支持

MySQL8.0新特性-CTE语法支持

原创 MySQL 作者:哎呀我的天呐 时间:2020-04-13 08:46:15 0 删除 编辑

原文地址: https://yq.aliyun.com/articles/71981? spm=5176.10695662.1996646101.searchclickresult.345a7ffczH8LaJ


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
                  }
                }
              }
            }
          }
        }
      }
    ]
  }
} |




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-2685719/,如需转载,请注明出处,否则将追究法律责任。

下一篇: haproxy和lvs
请登录后发表评论 登录
全部评论
从事Oracle/MySQL工作多年,Oracle OCM、MySQL OCP,擅长Oracle/MySQL SQL Tuning & DB Performance

注册时间:2014-10-30

  • 博文量
    293
  • 访问量
    1944651