这篇文章将为大家详细讲解有关MySQL8.0新特性中什么是CTE语法支持,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。

CTE(common table expression),针对同一个FROM子查询在SQL中出现多次的情况,在创建计划的时候,只对其中一个子查询创建计划,并将结果放到临时表中,其它的子查询直接使用临时表。比如Oracle中经常使用的with as /*+materialize*/ 用法。

首先,我们看一下简单非递归的CTE的工作过程

CREATETABLEt(aint);INSERTINTOtVALUES(1),(2);mysql>WITHabcas(SELECT*FROMt)SELECT*FROMabc;+-------------+|a|+-------------+|1||2|+-------------+返回行数:[2],耗时:9ms.--为了清楚的看到OPTIMIZER的优化过程,我们先暂且关闭derived_merge特性。mysql>SETOPTIMIZER_SWITCH='derived_merge=off';执行成功,耗时:9ms.mysql>explainWITHabcas(SELECT*FROMt)SELECT*FROMabc;+----+-------------+------------+------------+------+---------------+-----+---------+-----+------+----------+-------+|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],耗时:9ms.

mysql>SETOPTIMIZER_SWITCH='derived_merge=on';执行成功,耗时:9ms.mysql>explainWITHabcas(SELECT*FROMt)SELECT*FROMabc;+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+|1|SIMPLE|t||ALL|||||2|100||+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+------------+-------+返回行数:[1],耗时:9ms.


mysql>EXPLAINformat=jsonWITHcte(x)as(SELECT*FROMt)SELECT*FROM(SELECT*FROMcte)ASt1,(SELECT*FROMcte)ASt2;-----------------------------------------|{"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":"BlockNestedLoop","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语法支持就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。