备案控制台
探索云世界
开发者社区 数据库 关系型数据库 文章 正文

MySQL · 特性分析 · common table expression

2017-04-21 2230
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
云原生数据库 PolarDB 分布式版，标准版 2核8GB
推荐场景：
使用PolarDB-X与Flink搭建实时数据大屏
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： common table expressionCommon table expression简称CTE，由SQL:1999标准引入，目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), Mar

common table expression

Common table expression简称CTE，由SQL:1999标准引入，
目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.

CTE的语法如下：

WITH [RECURSIVE] with_query [, ...]
SELECT...

with_query:
query_name [ (column_name [,...]) ] AS (SELECT ...)

以下图示来自MariaDB

Non-recursive CTEs
screenshot.png

Recursive CTEs
screenshot.png

CTE的使用

  • CTE使语句更加简洁

例如以下两个语句表达的是同一语义，使用CTE比未使用CTE的嵌套查询更简洁明了。

1) 使用嵌套子查询

SELECT MAX(txt), MIN(txt)
FROM
(
  SELECT concat(cte2.txt, cte3.txt) as txt
  FROM
  (
    SELECT CONCAT(cte1.txt,'is a ') as txt
    FROM
    (
      SELECT 'This ' as txt
    ) as cte1
  ) as cte2,
  (
    SELECT 'nice query' as txt
    UNION
    SELECT 'query that rocks'
    UNION
    SELECT 'query'
  ) as cte3
) as cte4;

2) 使用CTE

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
  • CTE 可以进行树形查询
    树
    初始化这颗树
create table t1(id int, value char(10), parent_id int);
insert into t1 values(1, 'A', NULL);
insert into t1 values(2, 'B', 1);
insert into t1 values(3, 'C', 1);
insert into t1 values(4, 'D', 1);
insert into t1 values(5, 'E', 2);
insert into t1 values(6, 'F', 2);
insert into t1 values(7, 'G', 4);
insert into t1 values(8, 'H', 6);

1) 层序遍历

with recursive cte as (
  select id, value, 0 as level from t1 where parent_id is null
  union all
  select t1.id, t1.value, cte.level+1 from cte join t1 on t1.parent_id=cte.id)
select * from cte;
+------+-------+-------+
| id   | value | level |
+------+-------+-------+
|    1 | A     |     0 |
|    2 | B     |     1 |
|    3 | C     |     1 |
|    4 | D     |     1 |
|    5 | E     |     2 |
|    6 | F     |     2 |
|    7 | G     |     2 |
|    8 | H     |     3 |
+------+-------+-------+

2) 深度优先遍历

with recursive cte as (
  select id, value, 0 as level, CAST(id AS CHAR(200)) AS path  from t1 where parent_id is null
  union all
  select t1.id, t1.value, cte.level+1, CONCAT(cte.path, ",", t1.id)  from cte join t1 on t1.parent_id=cte.id)
select * from cte order by path;
+------+-------+-------+---------+
| id   | value | level | path    |
+------+-------+-------+---------+
|    1 | A     |     0 | 1       |
|    2 | B     |     1 | 1,2     |
|    5 | E     |     2 | 1,2,5   |
|    6 | F     |     2 | 1,2,6   |
|    8 | H     |     3 | 1,2,6,8 |
|    3 | C     |     1 | 1,3     |
|    4 | D     |     1 | 1,4     |
|    7 | G     |     2 | 1,4,7   |
+------+-------+-------+---------+

Oracle

Oracle从9.2才开始支持CTE, 但只支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的树形查询，recursive with 语句可以与connect by语句相互转化。 一些相互转化案例可以参考这里.

Oracle recursive with 语句不需要指定recursive关键字，可以自动识别是否recursive.

Oracle 还支持CTE相关的hint,

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

“MATERIALIZE”告诉优化器产生一个全局的临时表保存结果，多次引用CTE时直接访问临时表即可。而”INLINE”则表示每次需要解析查询CTE。

PostgreSQL

PostgreSQL从8.4开始支持CTE，PostgreSQL还扩展了CTE的功能， CTE的query中支持DML语句，例如

create table t1 (c1 int, c2 char(10));
 insert into t1 values(1,'a'),(2,'b');
 select * from t1;
 c1 | c2
----+----
  1 | a
  2 | b


 WITH cte AS (
     UPDATE t1 SET c1= c1 * 2 where c1=1
     RETURNING *
 )
 SELECT * FROM cte; //返回更新的值
 c1 |     c2
----+------------
  2 | a

 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     UPDATE t1 SET c1= c1 * 2 where c1=1
     RETURNING *
 )
 SELECT * FROM t1;//返回原值
 c1 |     c2
----+------------
  1 | a
  2 | b


 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     DELETE FROM t1
     WHERE c1=1
     RETURNING *
 )
 SELECT * FROM cte;//返回删除的行
 c1 |     c2
----+------------
  1 | a


 truncate table t1;
 insert into t1 values(1,'a'),(2,'b');
 WITH cte AS (
     DELETE FROM t1
     WHERE c1=1
     RETURNING *
 )
 SELECT * FROM t1;//返回原值
 c1 |     c2
----+------------
  1 | a
  2 | b
(2 rows)

MariaDB

MariaDB从10.2开始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2开始支持recursive CTE。 目前的GA的版本是10.1.

MySQL

MySQL从8.0开始支持完整的CTE。MySQL8.0还在development
阶段，RC都没有，GA还需时日。

AliSQL

AliSQL基于mariadb10.2， port了no-recursive CTE的实现，此功能近期会上线。

以下从源码主要相关函数简要介绍其实现，

//解析识别with table引用
find_table_def_in_with_clauses

//检查依赖关系，比如不能重复定义with table名字
With_clause::check_dependencies

// 为每个引用clone一份定义
With_element::clone_parsed_spec

//替换with table指定的列名
With_element::rename_columns_of_derived_unit

此实现对于多次引用CTE，CTE会解析多次，因此此版本CTE有简化SQL的作用，但效率上没有效提高。

select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.25 sec)

//从执行时间来看是进行了3次全表扫描
 with t as (select count(*) from t1 where c2 !='z')
     select * from t union select * from t union select * from t;
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.59 sec)

 select count(*) from t1 where c2 !='z'
     union
     select count(*) from t1 where c2 !='z'
     union
    select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (0.57 sec)

 explain with t as (select count(*) from t1 where c2 !='z')
    -> select * from t union select * from t union select * from t;
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type     | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
|    1 | PRIMARY         | <derived2>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    2 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    3 | RECURSIVE UNION | <derived5>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    5 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    4 | RECURSIVE UNION | <derived6>   | ALL  | NULL          | NULL | NULL    | NULL | 65536 |             |
|    6 | SUBQUERY        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
| NULL | UNION RESULT    | <union1,3,4> | ALL  | NULL          | NULL | NULL    | NULL |  NULL |             |
+------+-----------------+--------------+------+---------------+------+---------+------+-------+-------------+
7 rows in set (0.00 sec)

 explain  select count(*) from t1 where c2 !='z'
    union
    select count(*) from t1 where c2 !='z'
    union
    select count(*) from t1 where c2 !='z';
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
|    1 | PRIMARY      | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    2 | UNION        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
|    3 | UNION        | t1           | ALL  | NULL          | NULL | NULL    | NULL | 65536 | Using where |
| NULL | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL |  NULL |             |
+------+--------------+--------------+------+---------------+------+---------+------+-------+-------------+
4 rows in set (0.00 sec)

以下是MySQL8.0 只扫描一次的执行计划

mysql> explain select count(*) from t1 where c2 !='z' union select count(*) from t1 where c2 !='z' union select count(*) from t1 where c2 !='z';
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY      | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
|  2 | UNION        | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
|  3 | UNION        | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62836 |    90.00 | Using where     |
| NULL | UNION RESULT | <union1,2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

以下是PostgreSQL9.4 只扫描一次的执行计划

postgres=# explain with t as (select count(*) from t1 where c2 !='z')
postgres-# select * from t union select * from t union select * from t;
 HashAggregate  (cost=391366.28..391366.31 rows=3 width=8)
   Group Key: t.count
   CTE t
     ->  Aggregate  (cost=391366.17..391366.18 rows=1 width=0)
           ->  Seq Scan on t1  (cost=0.00..384392.81 rows=2789345 width=0)
                 Filter: ((c2)::text <> 'z'::text)
   ->  Append  (cost=0.00..0.09 rows=3 width=8)
         ->  CTE Scan on t  (cost=0.00..0.02 rows=1 width=8)
         ->  CTE Scan on t t_1  (cost=0.00..0.02 rows=1 width=8)
         ->  CTE Scan on t t_2  (cost=0.00..0.02 rows=1 width=8)

AliSQL还有待改进。

文章标签：
云数据库 RDS MySQL 版
云原生数据库 PolarDB
关系型数据库
Oracle
C语言
SQL
PostgreSQL
关键词：
云数据库 RDS MySQL 版分析
云数据库 RDS MySQL 版特性
云数据库 RDS MySQL 版table
云数据库 RDS MySQL 版分析table
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
目录
相关文章
jianz123
|
14天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
jianz123
12 0
洛神灬殇
|
25天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
洛神灬殇
95 0
jianz123
|
14天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
jianz123
79 1
技术蜜糖罐
|
20天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
技术蜜糖罐
37 0
爱吃糖的范同学
|
22天前
|
SQL 关系型数据库 MySQL
【MySQL】慢SQL分析流程
【4月更文挑战第1天】【MySQL】慢SQL分析流程
爱吃糖的范同学
29 1
洛神灬殇
|
25天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）（一）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）
洛神灬殇
30 0
源码星辰
|
26天前
|
存储 缓存 关系型数据库
MySQL事务的四大特性是如何保证的
在MySQL数据库中还有一种二进制日志，其用来基于时间点的还原及主从复制。从表面上来看其和重做日志非常相似，都是记录了对于数据库操作的日志。但是，从本质上来看有着非常大的不同。
源码星辰
11 1
1941623231718325
|
1月前
|
存储 关系型数据库 MySQL
TiDB与MySQL、PostgreSQL等数据库的比较分析
【2月更文挑战第25天】本文将对TiDB、MySQL和PostgreSQL等数据库进行详细的比较分析，探讨它们各自的优势和劣势。TiDB作为一款分布式关系型数据库，在扩展性、并发性能等方面表现突出；MySQL以其易用性和成熟性受到广泛应用；PostgreSQL则在数据完整性、扩展性等方面具有优势。通过对比这些数据库的特点和适用场景，帮助企业更好地选择适合自己业务需求的数据库系统。
1941623231718325
89 4
人不走空
|
1月前
|
SQL 关系型数据库 MySQL
深入理解MySQL事务特性：保证数据完整性与一致性
深入理解MySQL事务特性：保证数据完整性与一致性
人不走空
87 1
多多数据库
|
1月前
|
存储 安全 关系型数据库
MySQL 临时表的用法和特性
MySQL 临时表的用法和特性
多多数据库
14 0
数据库

关系型数据库

热门文章

最新文章

  • 1
    PolarDB闪电助攻，《香肠派对》百亿好友关系实现毫秒级查询
  • 2
    PostgreSQL 9种索引的原理和应用场景
  • 3
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 4
    PostgreSQL 与基友们的故事之 - Redis (无限缓存,实时标签...)
  • 5
    关于MongoDB Sharding，你应该知道的
  • 6
    从MySQL导出到PostgreSQL
  • 7
    ssh Forward X11 实现远程主机GUI在本地展示 (C/S X)
  • 8
    一个简单算法可以帮助物联网,金融 用户 节约98%的数据存储成本 (PostgreSQL,Greenplum帮你做到)
  • 9
    PostgreSQL 全表 全字段 模糊查询的毫秒级高效实现 - 搜索引擎也颤抖了
  • 10
    MySQL · 8.0版本更新 · 性能优化篇
  • 1
    下次老板问你MySQL如何优化时，你可以这样说，老板默默给你加工资
    6
  • 2
    MySQL慢日志的介绍以及如何使用问题
    3
  • 3
    MySQL的事务（看看也许有帮助呢）
    3
  • 4
    MySQL学习手册（第一部分）
    6
  • 5
    MySQL字段的时间类型该如何选择？千万数据下性能提升10%~30%🚀
    13
  • 6
    【设计模式系列笔记】建造者模式
    96
  • 7
    【设计模式系列笔记】工厂模式
    102
  • 8
    【设计模式系列笔记】抽象工厂模式
    97
  • 9
    【设计模式系列笔记】单例模式
    112
  • 10
    PolarDB闪电助攻，《香肠派对》百亿好友关系实现毫秒级查询
    125265

    • 相关产品

  • 云数据库 RDS MySQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）