数据库中的CTE功能

白鳝的洞穴 2021-06-04

4735

CTE（Common Table Expression）是开发人员十分喜欢使用的SQL语法，不过CTE功能刚刚出来的时候，曾经是DBA的噩梦。SQL中出现CTE功能可以让SQL语句变得十分易读，而且也可以把SQL语句中一次性的查询给多个子查询使用，提高SQL的性能。有可能我说了一大堆话了，有些人可能还对CTE这个词汇比较陌生。我就用PostgreSQL官方文档中CTE的例子作为本文的开始吧。

看到上面的With语句，可能很多人会恍然大悟吧，原来高大上的CTE就是WITH语句啊。是的，CTE就是在SQL中构建一个临时数据集，给SQL的其他部分语义使用。在CTE出现之前，这些工作都是使用临时表来实现的，把数据写入临时表，然后做关联查询，最后干掉临时表的数据。这是一个十分麻烦的。CTE出现后，开发人员发现了一个宝库，以前十分复杂的应用变得简洁了。而DBA也发现了一个优化SQL的好方法，如果其中某些数据集在一条SQL中需要多次扫描，那么是不是可以通过CTE先构建一个公共的数据集，然后让不同的行源去和这个临时数据集关联呢？

CTE刚刚出现的时候，是采用物化的方式来实现的，在内存或者临时段中构建一个临时表，写入CTE的结果集，然后使用。不过这种临时构建，如果CTE部分的语法写的不好，会导致SQL的执行效率十分低下。以前老白在做优化项目中，CTE语句也是一个重灾区。当时很多开发人员也对老白十分不满，为啥你这里不让我用CTE，而另外一条SQL你又让我改成CTE呢？确实早期的CBO优化器能力有限，而且数据库对CTE的实现又是物化模式，所以CTE写的不好的时候，就是性能灾难。

从Oracle 11开始，ORACLE数据库对CTE的支持就十分完善了。目前Oracle的优化器十分强大，开发人员也实现了CTE自由。我们首先用ORACLE举个例子，首先创建一张测试表。

这张表上有9万多条记录，object_id上有一个索引。下面我们执行一个带有CTE的SQL语句。

我们可以看到，Oracle的CBO把这个CTE转化为一个子查询了，于是索引发挥了作用，执行效率十分高。我们也可以通过hint来强制物化这个CTE（实际上CTE在早期版本的实现上都是物化的）。

可以看出，物化后的CTE的执行计划完全改变了，首先对WITH语句进行物化，取出了test_cte里的所有记录，这时就产生了一个对TEST_CTE的全表扫描，同时生成了一个临时表SYS_TEMP_XXXX。因为这张临时表上没有索引，所以后面的select * fro cte ...语句也只能做全表扫描。这个物化了的CTE导致了两次全表扫描，执行效率十分低下。这也是早期CTE技术不成熟时候，DBA比较头痛的一个问题。

因为CTE是临时行源，所以在一些复杂的SQL语句中，CBO优化器也不一定总是能够很好的选择最优的执行计划。因此如果子查询优化的模式不起作用而且CTE数据集不是很大的时候，我们还是可以通过物化的方式来确保执行计划的稳定性。

PostgreSQL也支持CTE,我用过的最早支持CTE的PG版本好像是十多年前的8.x版本。早期的PG版本中，CTE也是要首先被物化的，因此使用CTE的时候一定要十分小心，避免类似的情况发生。

如上面的例子，我们在一个PG 10.3的环境中做一个类似的测试，在一张有3000多条记录的测试表上执行相同的SQL。我们看到执行计划中，出现了和Oracle的物化CTE十分类似的执行计划。第一步对test_cte进行全扫描，创建CTE，然后对CTE临时表再做一次全表扫描。难道PG的CBO优化器必须对CTE做物化，而不能像Oracle那样把CTE自动转化为一个子查询，然后自动进行优化吗？如果我们使用的是PG 12以前的版本，那么答案是：“确实如此，PG必须物化CTE，无法通过inline子查询的方式优化CTE”。

幸运的是，PG的发展十分迅速。到了PG 12，这个问题已经被很好的解决了。下面我们看一个在PG 12.3上执行的例子。

我们在一个有10万条记录的测试表里（远比10.3例子中的3000条记录要多的多），CTE被自动优化了，从执行计划上看，CBO选择了使用索引来访问test_cte，这个执行计划和Oracle完全一样了。

通过今天的这个例子，我们可以获得一些经验。如果我们使用PG 12之前的版本，在使用CTE的时候，一定要注意CTE物化的性能问题。而在PG 12以后，我们就可以放宽心了。不过无论使用哪个版本，我们在使用CTE的时候，如果相关的表数据量十分大，我们就需要测试一下执行计划，避免不必要的麻烦。

想想以前也曾经有人告诫过我们，要慎用CTE，因为CTE必须物化子查询，如果子查询性能不好，千万别使用CTE。实际上从今天我们做的实验上看，这个观点不一定正确。PG数据库这些年迭代升级的太快了，如果我们不能及时更新知识，这个前两年似乎还正确的观点，现在就不正确了。PG DBA确实也是挺苦的一个差事，只有不断地学习才能跟上版本升级的节奏。

Mysql的CTE支持出现的比较晚，到了Mysql 8.0才第一次支持了CTE语法。因mysql的CTE功能出现的较晚，所以Mysql 8.0的CBO优化器支持CTE物化和子查询优化两种模式，通过优化器的derived_merge参数我们可以控制是否做子查询合并优化，在Mysql 8.0中，这个参数缺省是on的，所以缺省情况下在Mysql里是和Oracle以及PG 12的特性相同的。如果我们要物化CTE，那么我们必须通过SET OPTIMIZER_SWITCH='derived_merge=off'来关闭优化。

至此大家可能对CTE有了一个初步的了解，应用开发人员在享受CTE的便捷的时候，也别忘了别给DBA添乱啊。在我们的运维自动化工具开发中，如果遇到哪位仁兄写CTE去采集运维对象上的指标数据，那么我会把他叫过来骂一顿的，因为面对复杂的数据环境，指不定到哪个用户那里，这条语句就闯祸了。

数据库

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数据库中的CTE功能

评论