CTE性能瓶颈解析这事我在过去几年的SQL优化工作里反复碰过好多回。CTECommon Table Expression公共表表达式写起来确实顺手特别是把一段复杂逻辑拆成“先算A再算B最后关联C”的链条时整条SQL的阅读体验会好非常多。但代码读起来舒服不代表数据库执行起来也舒服。不少线上慢查询恰恰就是从“加了一个WITH子句”开始的。这篇文章不打算把CTE语法从头讲一遍重点放在性能侧什么情况下CTE会成为瓶颈、执行计划里哪些信号表示CTE已经拖后腿了、我用过哪些有效的改写与调参手段以及常见问题怎么排查。无论你是刚接触SQL优化还是已经在生产环境里被慢SQL折磨过几次里面提到的排查思路和实操记录应该都能直接拿去用。1. CTE性能瓶颈的核心成因1.1 物化与内联CTE执行方式的底层差异很多人对CTE有个误解认为WITH子句只是“语法糖”数据库会把CTE展开成子查询和内联视图没区别。这个说法在早期版本的数据库里大方向上还成立但具体到不同数据库、不同版本差异非常大。以PostgreSQL为例12.0版本之前CTE默认是“物化”的。什么叫物化就是把CTE子查询先执行一遍把结果临时存起来后续引用这个CTE时直接读这份临时结果。这个行为有一个显著影响CTE结果集一旦生成就和基表的统计信息、索引分布“断了联系”。外层查询再做JOIN、过滤时优化器的信息来源只是一份“不知道内部结构”的中间结果。估算难免失真执行计划的全局最优性也就无从谈起。PostgreSQL 12.0之后改了默认行为非递归CTE默认会内联到主查询里相当于把WITH里的逻辑展开成子查询让优化器把CTE内容当作普通子查询一起参与计划生成。这个改动对大多数场景是性能利好但内联带来的问题也不少CTE内容会被复制到多处引用点如果同一个CTE在主查询里被引用了两次那这段逻辑就可能被计算两次。内联还会让原本物化时“所有CTE计算完毕后外层再处理”的执行边界被打乱。MySQL 8.0引入CTE后优化器选择的是偏向内联的执行方式但递归CTE例外必须物化。SQL Server则把CTE看成临时派生表优化器可以透视到内部但涉及递归CTE时同样使用类似物化的工作表机制。所以CTE性能瓶颈的第一个关键点就是这“物化”和“内联”的底层差异。你写的CTE在不同数据库版本里执行策略根本不一样。同一段SQL换一个版本性能天差地别的现象往往就源自这里。1.2 统计信息缺失导致基数估算失真CTE另一个不能忽视的问题是优化器对结果集规模的误判。物化CTE生成中间结果后PostgreSQL不会自动为这份结果生成统计直方图。优化器面对CTE时只能靠执行时一次性扫描把估计行数“猜”出来但猜的过程依赖的往往是CTE子查询源表的统计信息再乘一些粗糙的选择率。一旦源表数据分布偏斜估算值会严重偏离实际。举个例子一张订单表里有1000万行status字段取值有30种分布并不均匀其中statusclosed的比例占79%。你写WITH closed_orders AS ( SELECT * FROM orders WHERE status closed ) SELECT ... FROM closed_orders co JOIN customers c ON c.customer_id co.customer_id如果优化器按30种取值平均估算估算结果就是33万行左右而实际是790万行。后续JOIN时优化器可能会选错关联顺序、选错JOIN算法最终执行计划质量非常差。基数估算错了整个计划就歪了。我在实际排查里经常看到执行计划里的预估行数和实际行数差了几十倍甚至上百倍。第一步去核对执行计划里的rows和actual rows十有八九能发现是CTE或者其他过滤条件把估算带跑偏了。1.3 谓词无法下推与重复扫描第三个瓶颈成因是CTE和SQL优化器之间的交互被切断。物化的CTE是一个明确的执行边界。外层的过滤条件、JOIN条件默认情况下不会下推到CTE内部去执行。这就带来两个后果一是“提前过滤”失效。假如CTE内部已经做了全量聚合外层只需要一条具体记录的某几个字段理想的执行方式是把外层条件提前到聚合前减少计算量。但物化CTE条件下的执行边界决定了CTE必须先把全量数据算完、存好外层才拿条件过来过滤。哪怕外层最后只输出一行中间过程也可能背着几百万行的临时结果集跑了一趟。二是同一个CTE被引用多次时内联模式下可能被重复扫描。比如WITH base AS ( SELECT id, amount, created_at FROM orders WHERE created_at 2024-01-01 ) SELECT ... FROM base b1 JOIN base b2 ON b1.id b2.id如果优化器选择内联base就会被展开两次orders表可能被扫描两遍。物化模式则只扫描一遍存下来供两处使用。所以CTE本身没有“一定快”或“一定慢”的结论关键看它在具体执行计划里是被物化、被内联以及外部条件下推的程度。2. 一次真实的排查过程慢SQL里的CTE陷阱2.1 模拟项目背景与慢SQL特征这里我用一次模拟项目X的排查过程来说明。项目里有一个数据看板需要统计商品维度的销售指标。原先是单表聚合速度尚可。后来业务加了“只统计最近一次有效订单之后的商品”这种条件逻辑复杂了不少开发顺手用三层CTE重写了整条SQL。表结构和数据大致如下order_items订单明细表约1200万行主键id订单号order_id商品id product_id数量quantity状态statusproducts商品表约30万行customer_orders客户订单表约800万行含下单时间paid_at、订单状态order_status重写后的SQL大概是这样WITH latest_orders AS ( SELECT customer_id, MAX(paid_at) AS last_paid_at FROM customer_orders WHERE order_status paid GROUP BY customer_id ), valid_order_items AS ( SELECT oi.product_id, oi.quantity, oi.order_id FROM order_items oi JOIN customer_orders co ON co.order_id oi.order_id JOIN latest_orders lo ON lo.customer_id co.customer_id WHERE co.paid_at lo.last_paid_at ), aggregated AS ( SELECT product_id, SUM(quantity) AS total_qty FROM valid_order_items GROUP BY product_id ) SELECT * FROM aggregated;重构后原来50毫秒左右的查询变成了1.8秒左右有时候冲到3秒。数据量没有变SQL逻辑在原版是能跑的差别就出在CTE组织方式上。2.2 执行计划里看到的真相遇到慢查询我最先做的不是猜原因而是执行EXPLAIN ANALYZE把真实执行计划拿在手里看。EXPLAIN (ANALYZE, BUFFERS) 上面那条SQL执行计划里最扎眼的是一个节点CTE Scan on valid_order_items (actual time... rows... loops...)CTE Scan节点的rows显示约120万行且这个CTE被扫描时外层聚合之前还经过了一次不小的join操作。再看预估行数优化器给出的rows只有几十万行和实际相差很大。这意味着基数估算已经失真。继续细看valid_order_items内部order_items和customer_orders做了JOINJOIN结束后又去关联latest_orders。这里的关键问题是latest_orders这个CTE本身是物化执行边界它算出来的结果集是每个客户最近一次支付订单时间。而外层JOIN时co.paid_at lo.last_paid_at这个过滤条件实际上没法干净地下推到customer_orders表和order_items表的初始扫描阶段。每一行order_items都要跑到JOIN完成之后才做一次“是否大于最近支付时间”的判定计算量全部堆积在内存里。另外还发现一个非常典型的信号CTE被引用的位置在外层聚合之前整个120万行中间结果都被推送到聚合节点而聚合节点之前没有任何下推过滤。执行计划走的时间都耗在构建哈希表、扫描大结果集上。2.3 一轮改写后的结果拿到计划后我把三层CTE改写成了尽量让优化器能看到全部过滤条件的写法核心思路就是把latest_orders拿到JION里当派生表并把valid_order_items的过滤条件下推SELECT oi.product_id, SUM(oi.quantity) AS total_qty FROM order_items oi JOIN customer_orders co ON co.order_id oi.order_id JOIN ( SELECT customer_id, MAX(paid_at) AS last_paid_at FROM customer_orders WHERE order_status paid GROUP BY customer_id ) lo ON lo.customer_id co.customer_id WHERE co.paid_at lo.last_paid_at GROUP BY oi.product_id;改写后的查询执行计划里不再出现CTE Scanlo子查询被优化器内联并参与了全局计划生成。过滤条件的估算准确了很多且可以在JOIN前提前筛选。同样是EXPLAIN ANALYZE最终耗时回落到60毫秒以内。我把这轮对比整理成了一张表方便直观感受差距执行方式预估行数偏差关键节点耗时三层CTE偏差约5倍CTE Scan结果集120万行1.8s派生表内联偏差很小无CTE Scan条件下推提前过滤58ms这个案例很典型。不是CTE语法本身有罪而是它在当前执行计划中制造了一个隔离边界阻止了优化器做全局调度。而这种问题只有在数据规模上来、过滤条件本身能显著减少数据量时才会暴露得这么明显。3. CTE性能优化的实操方案3.1 按需选择MATERIALIZED / NOT MATERIALIZEDPostgreSQL 12以后CTE默认内联但开发人员可以手动干预行为。如果CTE体量小、外层逻辑并不复杂用默认内联即可。但如果CTE是一次性计算大结果集、且在主查询里被引用多次内联反而会导致重复扫描。此时可以显式加上MATERIALIZED关键字强制物化WITH base AS MATERIALIZED ( SELECT * FROM events WHERE event_type click AND occurred_at 2024-01-01 ) SELECT ... FROM base b1 JOIN base b2 ON b1.id b2.id;相反如果CTE体量不大或者希望外层过滤条件下推可以用NOT MATERIALIZEDWITH filtered AS NOT MATERIALIZED ( SELECT * FROM products WHERE category premium ) SELECT * FROM filtered WHERE price 100;注意这是PostgreSQL的语法MySQL和SQL Server不支持显式控制。在MySQL 8.0里非递归CTE默认按内联优化想强制物化只能通过临时表替代。SQL Server无此控制词通常按照派生表方式处理。是不是一定要显式声明以我的经验大多数情况下不用。优化器默认行为已经足够好。但当你明确遇到“同一个CTE被引用多次导致重复扫描”或者“预估行数歪得离谱”时就值得手动干预了。3.2 让谓词尽可能下推写CTE时一个非常实用的原则能把过滤条件放多靠前就放多靠前。CTE内部的条件越严格中间结果集越小后续执行越轻松。-- 不推荐写法外层过滤似乎也行但早了更好 WITH big_result AS ( SELECT id, amount, product_id FROM orders ) SELECT * FROM big_result WHERE amount 10000; -- 推荐写法条件直接推到源头 WITH big_result AS ( SELECT id, amount, product_id FROM orders WHERE amount 10000 ) SELECT * FROM big_result;第二个写法orders表扫描时就可以走索引或提前跳过不满足条件的行。第一个写法里优化器在多数数据库下也能内联下推但遇到物化边界就做不到。另一个我常用的技巧不要把多条业务逻辑强行揉进一个CTE里。比如一个CTE里既做了多表JOIN又做了GROUP BY还做了窗口排序中间任何一步无法下推都可能拖慢整体。拆成多个CTE逻辑更清晰的同时也让优化器有更多机会针对每个中间结果单独估算。3.3 临时表与CTE的取舍CTE毕竟不是物理存储它只是一个中间结果抽象。遇到中间结果集很大、且会被多次复用或者需要在中间结果上建索引的场景临时表是更合理的方案。以我过去的经验判断标准通常是这三条中间结果集是否需要被重复使用多次同一个查询内多次引用或跨查询使用结果集大小是否达到百万级以上并且关联时无法利用索引是否需要对中间结果建立额外的二级索引帮助后续JOIN满足其中任何一条创建临时表并写入数据通常比CTE更适合。CREATE TEMP TABLE tmp_order_summary AS SELECT customer_id, product_id, SUM(quantity) AS qty FROM order_items GROUP BY customer_id, product_id; CREATE INDEX idx_tmp_customer ON tmp_order_summary(customer_id);临时表相比CTE有实打实的优势它有自己的统计信息优化器能正确估算行数可以建索引可以多次复用还可以在调试时查看内容。缺点是会占用临时表空间也带来写盘开销不适合在非常高频的小查询里乱用。我见过一些项目把所有复杂查询都用临时表处理结果每来一个请求都创建好几张临时表资源消耗巨大。合理做法是中小结果集、单查询生命周期内使用CTE跨查询复用、超大结果集、需要索引加速时再上临时表。3.4 用窗口函数替代部分CTECTE经常用来实现去重取最新、排名筛选这类逻辑。其实这类逻辑用窗口函数通常更高效。比如按客户取最近一笔订单-- CTE写法 WITH ranked AS ( SELECT order_id, customer_id, paid_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY paid_at DESC) AS rn FROM customer_orders ) SELECT * FROM ranked WHERE rn 1; -- 窗口函数直接写法原理一样但可读性更好 SELECT order_id, customer_id, paid_at FROM ( SELECT order_id, customer_id, paid_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY paid_at DESC) AS rn FROM customer_orders ) t WHERE rn 1;严格说大部分数据库会把这两种写法解析成同一种执行计划。但在某些物化CTE行为下第一种写法可能把整个排序结果物化出来第二种写法则有机会让优化器感知到外层rn 1的过滤提前终止排序。窗口函数另一个优势是可以在同一层做多个不同粒度的计算避免多次扫描同一张表。这类场景比CTE硬套逻辑更轻量。4. 边界场景递归CTE与大数据量CTE4.1 递归CTE的执行机制与性能天花板递归CTE是CTE家族里最特殊的一类。它分为递归项和迭代项执行时反复迭代直到不再产生新行。功能很强大树形结构查询、层级展开、连续数列生成都能做。但性能上它也是最大的坑。递归CTE为什么慢最根本原因是迭代次数与数据深度强相关而每次迭代都要扫描上一轮产生的全部结果。如果每次迭代还涉及大表JOIN代价会指数级上升。一个典型的组织架构树查询WITH RECURSIVE org_tree AS ( SELECT id, parent_id, name, 1 AS depth FROM employees WHERE id 1 UNION ALL SELECT e.id, e.parent_id, e.name, ot.depth 1 FROM employees e JOIN org_tree ot ON e.parent_id ot.id ) SELECT * FROM org_tree;当组织架构深度为5层、每层大概1万人时中间工人表数量会接近5万行每次迭代都要重新扫描并JOIN employees表整体开销远高于直接用一张扁平视图加一次聚合。深度达到100层以上时递归方式很容易把整条查询拖到秒级开外。我自己的经验是递归CTE最适合“层级不深、每层数据量不大”的场景。如果层级很深、数据量又大建议改造成如下策略先计算一个带层级的扁平表利用窗口函数或程序层迭代再对这个扁平表做查询或者直接用循环在应用层把层级展开再一次性关联业务表。另外一定要防止递归条件写错导致无限循环很多现代数据库支持递归深度限制但依赖这个限制不是一个好方案。4.2 大数据量下内存不足与临时文件落盘CTE尤其物化CTE会把中间结果先写到内存或磁盘的临时文件里。很多数据库有work_mem或类似参数控制排序、哈希表的内存上限。当中间结果远大于work_mem时数据库会把临时数据写入磁盘执行时间会急剧增加。表现特征也很明显查询本身逻辑并不复杂但执行时磁盘IO明显上升临时表空间持续增长执行计划里出现大量Sort或Hash节点并伴随“Temp file”字样的日志。碰到这种情况第一步不是盲目调大work_mem而是先看执行计划确认哪些节点导致了大量临时文件写盘。如果问题出在一次排序适当提高该查询的会话级内存参数可以立竿见影SET work_mem 256MB;但千万别在生产库全局调大work_mem。一个会话大内存可能没事几十个并发会话同时大内存内存就会被打爆。正确做法是用事务或会话级设置让单个大查询受益但不影响其他并发负载。或者干脆通过改写SQL减少排序数据量、缩小中间结果集。5. 常见问题排查速查与经验收尾5.1 CTE性能排查速查表把平时常用的排查点整理成表遇到问题可以快速对照现象可能原因排查方向处理建议CTE执行计划中出现多次扫描同一CTE内联模式下多次引用查看计划中的loops确认同一CTE节点反复出现显式物化或改临时表预估行数明显小于实际行数中间结果基数估算失真EXPLAIN ANALYZE比对rows和actual rows拆分CTE、提前过滤、更新统计信息加了CTE后查询变慢逻辑却一样物化边界阻断条件下推检查是否存在CTE Scan节点改派生表内联形态递归CTE数据量稍大就爆炸每层迭代扫描全量大表分析迭代深度与每层行数用扁平表或应用层循环替代大结果集写临时文件导致慢work_mem不足查看临时文件日志和Sort/Hash节点会话级加大内存、缩减结果集这个表看起来简单实际排查时要结合完整执行计划看不能只看表象。比如“多次扫描同一CTE”可能在执行计划里表现为两个相同的CTE Scan节点但如果你只看总耗时会误以为是整体扫描慢而忽略了重复扫描这个本质。5.2 我一直沿用的几条实操经验最后分享几条我自己多年踩坑换来的经验。第一所有CTE优化问题第一步永远是看执行计划。不要凭直觉猜“是不是数据量太大”或者“是不是索引没生效”。把EXPLAIN ANALYZE跑出来实际行数、循环次数、临时文件、预估偏差全在纸面上。有了这些数据再决定怎么改。第二CTE写得整齐工整不代表执行高效。有些同事把整条SQL都套进CTE里看起来逻辑分层得很舒坦但执行上是灾难。写CTE之前先问自己一句这个中间结果真的需要单独计算并保存吗如果它只被用一次、且没有物化必要那它更应该作为子查询出现。第三查询语句里的WITH不是越短越好。有些数据库优化器对CTE的可见性有限计划生成时会把CTE当作黑盒。如果CTE内部逻辑本身很重外层无论如何优化都解决不了内层的代价。这时候要么把CTE内容简化要么把计算前移到一个更小的数据漏斗上。第四统计信息随手更新。今天索引建得再好统计信息过旧优化器也可能选错计划。特别是在CTE中间结果依赖了多张基表的情况下抽查一下ANALYZE的执行频率让数据库对数据分布有足够的把握。第五CTE不是性能问题的“免死金牌”。有些慢SQL单看子查询部分跑得很快但套上CTE就慢得离谱这种情况大多不是CTE本身的问题而是CTE出现的位置改变了优化器的视角。替换成派生表是一种低成本验证手段能快速定位到边界阻断还是计算代价真的大。老实说CTE是我在SQL优化中最常遇到的一种“聪明反被聪明误”的写法。它极大地提升了代码可读性却在某些场景下牺牲了执行效率。掌握好内联、物化的差异学会看执行计划写SQL时多留个心眼这类性能问题真正常见的解决方案其实并不复杂。希望这些记录下来的经验能帮你少走几趟弯路。