这篇文章将为大家详细讲解有关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
}
}
}
}
}
}
}
]
}
} |关于MySQL8.0新特性中什么是CTE语法支持就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：http://blog.itpub.net/29990276/viewspace-2685719/
