CTE是否物化由优化器动态决定，非语法强制；多次引用、不确定性函数或显式提示会触发物化，单次引用且可谓词下推则倾向内联；需通过执行计划节点和IO指标验证实际行为。
CTE（Common Table Expression）在SQL中常被误认为是“临时视图”或“自动物化表”，但实际执行行为取决于数据库引擎和查询上下文。是否内联展开（inline expansion）还是物化（materialization）并非由WITH语法本身决定，而是由优化器基于成本、重复引用、副作用规避等策略动态选择。
哪些情况会触发CTE物化
物化指数据库将CTE结果先计算并暂存（如内存/临时磁盘），后续引用直接读取，避免重复计算。常见触发条件包括：
- 多次引用同一CTE：例如在主查询中JOIN两次、或在WHERE和SELECT中分别引用；PostgreSQL、SQL Server（带RECOMPILE或特定提示）、Oracle（WITH … AS MATERIALIZED）倾向物化
- CTE含不确定性函数：如NOW()、RANDOM()、NEWID()，为保证语义一致性，多数引擎强制物化以避免多次调用产生不同结果
- 显式物化提示：SQL Server可用OPTION (USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))配合统计信息影响决策；PostgreSQL 12+支持MATERIALIZED关键字强制物化；Oracle支持/*+ MATERIALIZE */提示
哪些情况默认内联展开
内联即把CTE定义体直接替换进外层查询，等价于子查询展开，不缓存中间结果。典型场景有：
- 仅单次引用且无复杂逻辑：如CTE仅在SELECT列表中作为标量子查询使用，优化器通常选择内联以减少额外执行计划节点
- CTE可被谓词下推或连接消除：当CTE定义含JOIN或FILTER，且外层查询能将条件下沉（如WHERE cte.id = 100），内联后便于索引利用与剪枝
- 数据库默认策略限制：MySQL 8.0对CTE默认内联（除非含递归或UNION ALL），且不支持强制物化；SQLite也以内联为主，无物化机制
如何验证实际执行方式
不能仅凭语法判断，必须结合执行计划分析：
- 看计划节点是否存在独立扫描/计算步骤：PostgreSQL中出现CTE Scan节点，SQL Server中出现Table Spool (Eager Spool)或Compute Scalar前的独立子树，通常表示物化
- 对比IO与重复计算指标：运行两次相同CTE查询，若第二次逻辑读未显著下降，大概率未物化；若CTE含COUNT(*)且被引用两次，物化后应只扫描一次基表
- 禁用物化观察性能变化：PostgreSQL中用SET enable_material = off；SQL Server中用QUERYTRACEON 8690（需管理员权限），观察计划是否转为多次扫描
实用建议：何时该干预默认行为
多数情况下依赖优化器即可，但以下情形值得主动引导：
- 明确需要稳定结果时（如含RAND()），在支持的数据库中加MATERIALIZED或提示，避免非预期的多次求值
- CTE逻辑重、基表大、且被引用≥2次，而执行计划显示重复扫描，可尝试强制物化提升性能（注意内存/TempDB压力）
- 调试慢查询时，若CTE看似简单却拖慢整体，检查是否因内联导致谓词无法下推——改写为临时表或视图可能更可控
