文章目录
- 每日一句正能量
- 1. 背景与问题：CTE本身不会天然变慢，真正影响性能的是“它有没有成为优化屏障”
- 2. 环境与数据：必须先明确版本，因为旧版本和新版本的CTE经验可能相反
- 2.1 为什么版本必须记录？
- 2.2 实验至少做三组
- 2.3 什么叫“内联/折叠”？
- 3. 复现过程：单次引用CTE为什么有时和普通子查询几乎一样？
- 3.1 单次引用默认CTE
- 3.2 显式 MATERIALIZED 后会发生什么？
- 3.3 多次引用默认CTE
- 3.4 改成 NOT MATERIALIZED
- 4. 方案实施：决定物化还是内联，要比较“物化成本”和“重复计算成本”
- 4.1 场景一：大CTE + 每个消费者只取极少数据
- 4.2 场景二：昂贵函数被重复引用
- 4.3 场景三：先把CTE本身缩小，再物化
- 4.4 SELECT * 会让物化更贵
- 4.5 Material节点和CTE物化不是完全同一个概念
- 4.6 临时文件是验证物化代价的重要证据
- 4.7 CTE Scan本身不是“坏节点”
- 4.8 递归CTE不要套普通内联规则
- 4.9 数据修改CTE首先考虑语义，不是性能
- 4.10 volatile函数也不能随意内联
- 4.11 统计信息仍然会影响最终决策
- 4.12 执行计划缓存要进入回归范围
- 5. 结果对比：同一个CTE，NOT MATERIALIZED可以快8倍，也可能慢2倍
- E0：默认多次引用
- E1：显式 MATERIALIZED
- E2：NOT MATERIALIZED
- E3：保留物化，但把过滤推入CTE
- E4：昂贵函数 + NOT MATERIALIZED
- E5：昂贵函数 + MATERIALIZED
- 5.1 实验汇总
- 5.2 前后计划最值得观察什么？
- 5.3 监控数据要一起保存
- 5.4 多次引用的“重复计算”要量化
- 6. 风险与复盘：CTE性能事故最常见的根因，是把“可读性结构”误当成“物理执行边界”
- 6.1 风险一：老经验套新版本
- 6.2 风险二：为了“强制优化”全部NOT MATERIALIZED
- 6.3 风险三：全部MATERIALIZED
- 6.4 风险四：只看SQL文本，不看计划
- 6.5 风险五：NOT MATERIALIZED改变volatile函数调用次数
- 6.6 风险六：物化宽表导致Temp爆炸
- 6.7 风险七：单会话快，并发Temp打爆磁盘
- 推荐判定模型
- 推荐调优顺序
- 回退方案
- 最终复盘
- 附录 A：单次引用实验
- 附录 B：多次引用实验
- 附录 C：最低计划检查项
- 附录 D：最低验收门禁
每日一句正能量
相遇了就好好珍惜吧，就算终有一散，也不辜负相遇。
不因害怕失去而拒绝开始，我因珍视过程而坦然面对结局。
主题：CTE执行策略 / 复杂查询 / 物化与内联
重点：WITH、MATERIALIZED、NOT MATERIALIZED、CTE Scan、谓词下推、重复引用、临时文件、执行计划、P95/P99
适用场景：KingbaseES 中复杂报表、多阶段 SQL、重复子查询、数据清洗、批处理以及从旧版本迁移后出现 CTE 性能回归的系统。
1. 背景与问题：CTE本身不会天然变慢，真正影响性能的是“它有没有成为优化屏障”
很多团队对 CTE 有两个完全相反的印象。
第一种：
CTE只是把子查询写得更清楚 性能和子查询一样第二种：
CTE一定会先物化 所以一定比子查询慢这两句话在现代 KingbaseES 上都过于绝对。
官方 WITH 查询文档明确说明：如果一个 WITH 查询是非递归、无副作用的普通 SELECT，并且不包含 volatile 函数，那么优化器可以把它折叠进父查询，允许两个查询层级联合优化。
默认情况下，当父查询只引用该 CTE 一次时，通常会发生这种折叠。
这意味着下面 SQL：
WITHwAS(SELECT*FROMbig_table)SELECT*FROMwWHEREkey=123;有机会被优化成接近：
SELECT*FROMbig_tableWHEREkey=123;如果key上有索引，那么：
父查询条件就能直接进入基表扫描。
但当同一个 CTE 被父查询引用多次时，默认策略往往不同。
例如：
WITHwAS(SELECT*FROMbig_table)SELECT*FROMw w1JOINw w2ONw1.key=w2.refWHEREw2.key=123;CTE 可能只计算一次并被多次读取。
优点：
避免重复扫描/计算缺点：
父查询过滤条件可能无法继续下推到底层于是可能出现：
先扫描5000万 →形成CTE临时结果 →CTE Scan →最后才过滤几十行这才是很多人口中的：
“CTE变慢”真正含义。
CTE本身不是慢点；真正的问题是 CTE 在某个版本、某种引用方式下是否形成物化边界，以及这个边界阻断了什么优化。
2. 环境与数据：必须先明确版本，因为旧版本和新版本的CTE经验可能相反
示例：
数据库： KingbaseES V9 交易表： trade_order 数据量： 5000万 字段： order_id customer_id ref_order_id status amount order_date 索引： PK(order_id) idx_customer(customer_id) idx_order_date(order_date)2.1 为什么版本必须记录？
KingbaseES 官方文档特别说明：
在v12之前的版本中 WITH查询从未做这样的折叠也就是说，旧版本里很多应用可能故意利用：
WITH作为优化屏障。
升级以后：
单次引用CTE可能被折叠执行计划就可能改变。
反过来，从其他数据库或旧 KingbaseES 迁入时，如果团队一直认为：
CTE必然物化也可能对当前版本做出错误判断。
所以任何 CTE 性能结论第一列应该是：
database_version而不是：
“网上说CTE会……”2.2 实验至少做三组
同一业务 SQL：
A. 默认CTE B. MATERIALIZED C. NOT MATERIALIZED固定：
数据快照 SQL参数 work_mem 应用用户 并发 缓存策略保存：
Execution Plan CTE Scan Material Base Scan次数 actual rows Buffers Temp CPU P95/P99只有这样才能判断：
物化到底是收益还是损失2.3 什么叫“内联/折叠”？
本文里的内联不是：
字符串替换而是优化器把 CTE 与父查询：
联合规划最关键表现通常是：
父查询过滤条件可以下推 索引可以直接被选择 Join顺序可以重新优化计划里也可能不再看到独立：
CTE Scan3. 复现过程：单次引用CTE为什么有时和普通子查询几乎一样？
3.1 单次引用默认CTE
WITHrecent_orderAS(SELECTorder_id,customer_id,amount,order_dateFROMtrade_order)SELECTorder_id,amountFROMrecent_orderWHEREcustomer_id=:customer_idANDorder_date>=:start_date;如果：
CTE非递归 无volatile函数 只引用一次默认允许折叠。
理想计划可能直接是：
Index Scan / Bitmap Scan on trade_order过滤：
customer_id order_date已经推到底层。
所以这种场景说：
“CTE一定先算完整trade_order”是不准确的。
3.2 显式 MATERIALIZED 后会发生什么？
改成：
WITHrecent_orderASMATERIALIZED(SELECTorder_id,customer_id,amount,order_dateFROMtrade_order)SELECT...FROMrecent_orderWHEREcustomer_id=:customer_idANDorder_date>=:start_date;这时 CTE 成为显式独立计算边界。
如果底层：
5000万行而父查询最终：
只需要100行就可能形成：
Seq Scan 5000万 →CTE →CTE Scan →Filter父查询索引机会被阻断。
所以：
MATERIALIZED 本质上是一把优化围栏。
围栏有时非常有用。
有时则非常昂贵。
3.3 多次引用默认CTE
SQL：
WITHbaseAS(SELECTorder_id,customer_id,ref_order_id,status,amountFROMtrade_order)SELECTa.order_id,b.order_idFROMbase aJOINbase bONa.ref_order_id=b.order_idWHEREa.customer_id=:customer_aANDb.customer_id=:customer_b;假设：
trade_order=5000万两个客户分别只对应：
几十条订单默认多次引用可能形成：
CTE base -> Seq Scan trade_order 5000万 CTE Scan a CTE Scan b物化结果：
5000万行却只为了两个：
几十行的小集合。
示例：
Temp=14GB P95=73s这就是非常典型的：
物化阻止过滤下推3.4 改成 NOT MATERIALIZED
WITHbaseASNOTMATERIALIZED(SELECT...FROMtrade_order)SELECT...允许优化器把两个引用分别折叠到父查询。
可能形成：
Index Scan customer_a + Index Scan customer_b + Nested Loop / Hash Join此时：
底表可能扫描两次但每次只访问：
几十/几百行而不是：
先物化5000万示例：
Temp≈0 P95=8.6s官方文档也明确指出，NOT MATERIALIZED的代价是：
有重复计算风险但当每个引用只需要 CTE 全部输出中的少量行时，联合优化可能带来净收益。
4. 方案实施：决定物化还是内联，要比较“物化成本”和“重复计算成本”
4.1 场景一：大CTE + 每个消费者只取极少数据
例如：
CTE输出5000万 引用2次 每次只取100行优先测试：
NOT MATERIALIZED因为最大的收益是：
谓词下推 索引使用虽然底表可能被访问两遍，但：
100 + 100和：
5000万物化不是一个数量级。
4.2 场景二：昂贵函数被重复引用
例如：
WITHcalcAS(SELECTid,very_expensive_function(payload)ASfFROMevent_data)SELECT...FROMcalc aJOINcalc bONa.f=b.f;官方文档专门给了类似示例：
物化：
昂贵函数每行只计算一次NOT MATERIALIZED：
可能计算两次如果函数本身 CPU 很重：
内联反而更差所以这时：
MATERIALIZED可能是正确方案。
4.3 场景三：先把CTE本身缩小，再物化
很多 CTE 的真正错误不是：
物化而是：
物化之前没有过滤原：
WITHbaseASMATERIALIZED(SELECT*FROMtrade_order)SELECT...WHEREorder_dateBETWEEN...;更合理：
WITHbaseASMATERIALIZED(SELECTorder_id,customer_id,amount,order_dateFROMtrade_orderWHEREorder_date>=:start_dateANDorder_date<:end_date)SELECT...FROMbase;示例：
CTE Output: 5000万 →42万Temp：
14GB →0.6GBP95：
72s →4.9s这说明：
有时你不需要消灭物化，只需要物化更小、更窄的数据。
4.4 SELECT * 会让物化更贵
CTE：
SELECT*可能带：
大VARCHAR JSON LOB 几十列即使：
行数一样物化临时数据体积也完全不同。
如果消费者只用：
id status amount就只投影这些列。
优化 CTE 不仅看：
Rows还看：
Width执行计划里的width就是很有价值的线索。
4.5 Material节点和CTE物化不是完全同一个概念
执行计划里可能出现：
Material节点。
KingbaseES SQL 调优指南把 Material 归为物化类计划节点。
但：
看到Material不能直接断言：
就是CTE语义物化Material 节点也可能由其他执行策略产生，用来保存中间结果供重复读取。
诊断时应该结合：
CTE Scan CTE name 计划树一起看。
4.6 临时文件是验证物化代价的重要证据
如果 CTE 输出巨大，内存无法保存全部中间结果，就可能产生：
临时文件监控：
log_temp_files 磁盘IO Temp GB/min很有价值。
但同样要注意：
临时文件还可能来自Sort/Hash所以必须按：
PID SQL 执行计划 时间关联。
4.7 CTE Scan本身不是“坏节点”
如果：
CTE只产出5000行并且：
被引用5次一次计算后：
CTE Scan×5反而可能比：
重复跑5次复杂子查询更省。
所以：
看到 CTE Scan → 判慢也是误区。
4.8 递归CTE不要套普通内联规则
递归：
WITHRECURSIVE...本身需要：
迭代工作表 递归执行不能简单讨论：
NOT MATERIALIZED是否更快应该重点看：
递归层数 每层输出 RecursiveUnion 循环次数4.9 数据修改CTE首先考虑语义，不是性能
WITH 可以结合：
INSERT UPDATE DELETE执行数据修改。
这类 CTE：
执行一次 快照 RETURNING 并发语义非常重要。
不能为了追求：
内联破坏数据修改语义。
4.10 volatile函数也不能随意内联
官方文档对可折叠 CTE 的条件之一就是：
无副作用 不包含volatile函数因为重复执行：
volatile函数可能不仅是性能问题，还会：
改变结果所以优化之前必须先判断：
这个CTE是否具备安全内联条件4.11 统计信息仍然会影响最终决策
即使：
NOT MATERIALIZED让优化器可以联合规划。
如果：
表统计信息严重不准优化器依然可能选择：
错误Scan 错误Join所以 CTE 调优仍要执行：
estimated vs actual检查。
官方执行计划分析文档也明确指出，统计信息准确性会影响执行计划选择。
4.12 执行计划缓存要进入回归范围
应用可能通过：
JDBC PBE PreparedStatement执行 CTE。
KingbaseES 支持执行计划缓存，当：
表定义 函数定义 统计信息改变时相关缓存计划会失效。
因此改：
统计信息 索引 SQL后，需要在：
真实JDBC调用方式下重新观察。
不能只在：
ksql手工EXPLAIN里验证。
5. 结果对比：同一个CTE，NOT MATERIALIZED可以快8倍，也可能慢2倍
E0：默认多次引用
CTE： 5000万行 基表扫描： 1次 CTE Scan： 2次 Temp： 14GB P95： 73sE1：显式 MATERIALIZED
计划基本一致：
P95： 72s说明默认策略：
本来就在物化E2：NOT MATERIALIZED
两个引用分别下推：
customer_id并使用索引。
基表逻辑引用： 2次 Temp： ≈0 P95： 8.6s这时：
重复读取比：
巨大物化便宜得多。
E3：保留物化，但把过滤推入CTE
CTE：
5000万 →42万P95：
4.9s比 E2 甚至更快。
原因：
小CTE只计算一次 + 后续复用两种收益同时得到。
E4：昂贵函数 + NOT MATERIALIZED
CTE 中：
expensive_func()被两个引用重复计算。
CPU：
约翻倍P95：
18sE5：昂贵函数 + MATERIALIZED
函数：
每行只算1次Temp：
0.8GBP95：
9.2s这次：
MATERIALIZED反而明显更快。
5.1 实验汇总
| 实验 | 策略 | CTE输出 | 基表/函数重复 | Temp | P95 |
|---|---|---|---|---|---|
| E0 | 默认多引用 | 5000万 | 1次 | 14GB | 73s |
| E1 | MATERIALIZED | 5000万 | 1次 | 14GB | 72s |
| E2 | NOT MATERIALIZED | 不独立物化 | 2次 | 0 | 8.6s |
| E3 | 前置过滤+物化 | 42万 | 1次 | 0.6GB | 4.9s |
| E4 | NOT MATERIALIZED+昂贵函数 | 不物化 | 2次计算 | 0 | 18s |
| E5 | MATERIALIZED+昂贵函数 | 38万 | 1次计算 | 0.8GB | 9.2s |
以上为方法示例数据，不是生产实测。
这个表非常直接地证明：
“物化慢”与“内联快”都不是定律。
真正的比较是：
Materialization Cost vs Repeated Computation Cost5.2 前后计划最值得观察什么？
默认物化：
CTE base -> Seq Scan 5000万 CTE Scan on base a CTE Scan on base bNOT MATERIALIZED：
Index Scan trade_order for customer_a Index Scan trade_order for customer_b Join这时最重要的不是：
计划节点少了几个而是：
5000万行中间结果消失了5.3 监控数据要一起保存
每次至少：
P50 P95 P99 CPU Buffers hit/read Temp GB 磁盘写MB/s 返回行数如果：
P95下降但：
CPU翻3倍高并发下可能反而更差。
5.4 多次引用的“重复计算”要量化
NOT MATERIALIZED 后：
Base Scan次数可能从：
1 →2 →5如果每次都能：
索引点查问题不大。
如果每次都是：
5000万Seq Scan就会灾难。
所以：
重复引用次数必须进入成本模型。
6. 风险与复盘：CTE性能事故最常见的根因，是把“可读性结构”误当成“物理执行边界”
6.1 风险一：老经验套新版本
旧版本：
WITH常被视为优化屏障新版本：
单次、无副作用CTE可折叠升级以后执行计划可能变化。
所以跨版本必须重新做：
EXPLAIN6.2 风险二：为了“强制优化”全部NOT MATERIALIZED
如果 CTE：
被引用6次而且每次都会执行：
复杂Join 昂贵函数强制内联可能把 CPU 放大很多倍。
6.3 风险三：全部MATERIALIZED
这会形成大量：
优化围栏让父查询：
WHERE JOIN LIMIT无法进入底层联合优化。
尤其：
SELECT * FROM 1亿行再物化，是非常危险的写法。
6.4 风险四：只看SQL文本，不看计划
两段 SQL：
看起来一个是CTE 一个是子查询优化以后可能产生：
完全相同计划所以语法外观不能代替执行计划。
6.5 风险五：NOT MATERIALIZED改变volatile函数调用次数
这不只是：
性能还可能：
结果变化因此含副作用 CTE 不应只按性能策略改写。
6.6 风险六：物化宽表导致Temp爆炸
行数：
100万看似不大。
如果：
每行5KB就是：
GB级中间数据所以一定同时看：
Rows × Width6.7 风险七：单会话快，并发Temp打爆磁盘
一个物化 CTE：
Temp=5GB10并发：
50GB再叠加：
Sort Hash可能把临时盘打满。
必须测试：
Temp GB/min和磁盘延迟。
推荐判定模型
CTE 策略可以粗略写成：
物化收益 = 避免重复扫描/计算 物化代价 = 生成完整中间结果 + 阻断谓词下推/Join重排 + 内存/临时文件 内联收益 = 联合优化 + 谓词下推 + 索引使用 内联代价 = 重复扫描 + 重复函数计算最终选：
成本更低的一边而不是：
CTE一律怎么写推荐调优顺序
1. 确认数据库版本 2. 确认CTE是否递归/有副作用 3. 数引用次数 4. EXPLAIN ANALYZE 5. 看CTE Scan / Material 6. 看父谓词是否下推 7. 看CTE输出Rows×Width 8. 默认/MATERIALIZED/NOT MATERIALIZED三组实验 9. 过滤前推+列裁剪 10. 并发与Temp验收回退方案
如果 CTE 改写灰度后出现：
P95回归 CPU暴涨 Temp变大 结果差异执行：
1. 停止扩大新SQL流量 2. Feature Flag恢复旧SQL 3. 恢复原MATERIALIZED/NOT MATERIALIZED策略 4. 保存新旧EXPLAIN ANALYZE 5. 保存Temp、CPU、IO监控 6. 新索引先保留，确认依赖后再删除 7. 重新验证行数、金额和函数结果最终复盘
CTE 不是一个：
“快”或者：
“慢”的 SQL 特性。
它更像一个：
“优化边界是否存在”的问题。
单次引用、无副作用时：
默认折叠可能让它和普通子查询几乎一样。
多次引用时：
物化一次可能避免重复计算。
但如果每个引用只需要极少数据：
NOT MATERIALIZED又可能通过谓词下推快一个数量级。
如果只记住一句话：
CTE 性能优化不是决定“要不要用 WITH”，而是验证这个 WITH 在当前 KingbaseES 版本里到底是被内联、被物化，还是应该由你显式选择
MATERIALIZED/NOT MATERIALIZED。
最终裁决者不是语法偏好。
而是：
Execution Plan + Buffers + Temp + CPU + P95/P99附录 A：单次引用实验
WITHwAS(SELECT*FROMbig_table)SELECT*FROMwWHEREkey=123;对照：
WITHwASMATERIALIZED(SELECT*FROMbig_table)SELECT*FROMwWHEREkey=123;附录 B：多次引用实验
WITHwASNOTMATERIALIZED(SELECT*FROMbig_table)SELECT...FROMw w1JOINw w2...观察：
CTE Scan 底表扫描次数 谓词下推 索引附录 C：最低计划检查项
CTE引用次数 CTE Scan Material actual rows width loops Buffers Temp Execution Time附录 D：最低验收门禁
[ ] 当前KingbaseES版本行为已确认 [ ] 递归/volatile/修改型CTE已分类 [ ] CTE引用次数已记录 [ ] 谓词下推已验证 [ ] CTE输出Rows×Width已量化 [ ] Temp在预算内 [ ] 重复计算成本已量化 [ ] 默认/MATERIALIZED/NOT MATERIALIZED已对照 [ ] P95/P99达到SLA [ ] 并发CPU/IO通过 [ ] 结果差异=0 [ ] 回退SQL已准备转载自：https://blog.csdn.net/u014727709/article/details/163949313
欢迎 👍点赞✍评论⭐收藏，欢迎指正
