CTE会不会变慢：物化与内联验证——复杂查询中的执行策略、实验SQL与计划差异实战

文章目录每日一句正能量1. 背景与问题CTE本身不会天然变慢真正影响性能的是“它有没有成为优化屏障”2. 环境与数据必须先明确版本因为旧版本和新版本的CTE经验可能相反2.1 为什么版本必须记录2.2 实验至少做三组2.3 什么叫“内联/折叠”3. 复现过程单次引用CTE为什么有时和普通子查询几乎一样3.1 单次引用默认CTE3.2 显式 MATERIALIZED 后会发生什么3.3 多次引用默认CTE3.4 改成 NOT MATERIALIZED4. 方案实施决定物化还是内联要比较“物化成本”和“重复计算成本”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. 结果对比同一个CTENOT MATERIALIZED可以快8倍也可能慢2倍E0默认多次引用E1显式 MATERIALIZEDE2NOT MATERIALIZEDE3保留物化但把过滤推入CTEE4昂贵函数 NOT MATERIALIZEDE5昂贵函数 MATERIALIZED5.1 实验汇总5.2 前后计划最值得观察什么5.3 监控数据要一起保存5.4 多次引用的“重复计算”要量化6. 风险与复盘CTE性能事故最常见的根因是把“可读性结构”误当成“物理执行边界”6.1 风险一老经验套新版本6.2 风险二为了“强制优化”全部NOT MATERIALIZED6.3 风险三全部MATERIALIZED6.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 一次时通常会发生这种折叠。这意味着下面 SQLWITHwAS(SELECT*FROMbig_table)SELECT*FROMwWHEREkey123;有机会被优化成接近SELECT*FROMbig_tableWHEREkey123;如果key上有索引那么父查询条件就能直接进入基表扫描。但当同一个 CTE 被父查询引用多次时默认策略往往不同。例如WITHwAS(SELECT*FROMbig_table)SELECT*FROMw w1JOINw w2ONw1.keyw2.refWHEREw2.key123;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 实验至少做三组同一业务 SQLA. 默认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 单次引用默认CTEWITHrecent_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 多次引用默认CTESQLWITHbaseAS(SELECTorder_id,customer_id,ref_order_id,status,amountFROMtrade_order)SELECTa.order_id,b.order_idFROMbase aJOINbase bONa.ref_order_idb.order_idWHEREa.customer_id:customer_aANDb.customer_id:customer_b;假设trade_order5000万两个客户分别只对应几十条订单默认多次引用可能形成CTE base - Seq Scan trade_order 5000万 CTE Scan a CTE Scan b物化结果5000万行却只为了两个几十行的小集合。示例Temp14GB P9573s这就是非常典型的物化阻止过滤下推3.4 改成 NOT MATERIALIZEDWITHbaseASNOTMATERIALIZED(SELECT...FROMtrade_order)SELECT...允许优化器把两个引用分别折叠到父查询。可能形成Index Scan customer_a Index Scan customer_b Nested Loop / Hash Join此时底表可能扫描两次但每次只访问几十/几百行而不是先物化5000万示例Temp≈0 P958.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.fb.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万Temp14GB →0.6GBP9572s →4.9s这说明有时你不需要消灭物化只需要物化更小、更窄的数据。4.4 SELECT * 会让物化更贵CTESELECT*可能带大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. 结果对比同一个CTENOT MATERIALIZED可以快8倍也可能慢2倍E0默认多次引用CTE 5000万行 基表扫描 1次 CTE Scan 2次 Temp 14GB P95 73sE1显式 MATERIALIZED计划基本一致P95 72s说明默认策略本来就在物化E2NOT MATERIALIZED两个引用分别下推customer_id并使用索引。基表逻辑引用 2次 Temp ≈0 P95 8.6s这时重复读取比巨大物化便宜得多。E3保留物化但把过滤推入CTECTE5000万 →42万P954.9s比 E2 甚至更快。原因小CTE只计算一次 后续复用两种收益同时得到。E4昂贵函数 NOT MATERIALIZEDCTE 中expensive_func()被两个引用重复计算。CPU约翻倍P9518sE5昂贵函数 MATERIALIZED函数每行只算1次Temp0.8GBP959.2s这次MATERIALIZED反而明显更快。5.1 实验汇总实验策略CTE输出基表/函数重复TempP95E0默认多引用5000万1次14GB73sE1MATERIALIZED5000万1次14GB72sE2NOT MATERIALIZED不独立物化2次08.6sE3前置过滤物化42万1次0.6GB4.9sE4NOT MATERIALIZED昂贵函数不物化2次计算018sE5MATERIALIZED昂贵函数38万1次计算0.8GB9.2s以上为方法示例数据不是生产实测。这个表非常直接地证明“物化慢”与“内联快”都不是定律。真正的比较是Materialization Cost vs Repeated Computation Cost5.2 前后计划最值得观察什么默认物化CTE base - Seq Scan 5000万 CTE Scan on base a CTE Scan on base bNOT MATERIALIZEDIndex 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打爆磁盘一个物化 CTETemp5GB10并发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*FROMwWHEREkey123;对照WITHwASMATERIALIZED(SELECT*FROMbig_table)SELECT*FROMwWHEREkey123;附录 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欢迎 点赞✍评论⭐收藏欢迎指正