标量子查询常被误认为“先执行外层再执行内层”，实则不相关子查询（无外层列引用）会被优化器识别为可物化或常量折叠的独立单元，通常只执行一次。

标量子查询为什么常被误认为“先执行外层再执行内层”

标量子查询（SELECT ... (SELECT ...) FROM ...）在逻辑上是“每行外层数据触发一次子查询”，但实际执行时，数据库几乎从不这么干——尤其是当子查询不相关（即不含外层表引用，如 WHERE t1.id = t2.id 这类关联条件）时。它会被优化器识别为可“上提”或“物化”的独立计算单元。

关键判断：只要子查询里没出现外层表的列（比如没有 t1.name、orders.user_id），它就是不相关的，SQL 引擎大概率会把它当作一个一次性计算步骤，而非循环调用。

• 常见错误现象：EXPLAIN 显示子查询出现在 SELECT 列中，但执行计划里找不到 DEPENDENT SUBQUERY ——说明它没被当成相关子查询处理
• 使用场景：取全局最大值 (SELECT MAX(price) FROM products)、查配置项 (SELECT value FROM config WHERE key = 'timeout')
• 性能影响：不相关标量子查询通常只执行 1 次，结果缓存复用；若误写成相关形式（比如漏删 WHERE 中的外层引用），性能可能暴跌数个数量级

MySQL 8.0+ 和 PostgreSQL 是怎么把不相关子查询“内存化”的

它们不会真去“循环执行子查询”，而是走常量折叠（constant folding）或子查询物化（subquery materialization）路径。具体策略取决于子查询是否返回单行单列、是否含聚合、是否带 LIMIT 1 等。

例如 (SELECT COUNT(*) FROM logs WHERE status = 'error') 在 MySQL 中可能被转为一个临时表扫描 + 聚合，结果存入一个内部常量槽位；PostgreSQL 则更倾向在规划阶段就评估出该子查询是否可静态求值。

• 参数差异：MySQL 的 optimizer_switch='materialization=on' 控制是否启用物化；PostgreSQL 的 enable_material 影响物化节点生成，但对不相关标量子查询影响较小——它更依赖 subplan 类型判断
• 兼容性注意：SQLite 会严格按语法顺序执行，不相关子查询也可能重复计算（尤其在旧版本）；Oracle 则默认启用子查询自动物化（subquery_pruning 相关机制）
• 验证方法：在 MySQL 中运行 EXPLAIN FORMAT=TREE，看子查询是否出现在顶层 -> Materialize 下；PostgreSQL 用 EXPLAIN (ANALYZE, VERBOSE) 查看是否有 SubPlan 并标记 (never executed) 或 (actual time=... rows=1)

哪些写法会让不相关子查询“失效”，被迫退化为逐行执行

表面看不相关，但 SQL 解析器因语法歧义或优化限制无法确认其独立性时，就会保守地按相关方式处理。最典型的是子查询里混入了看似无关、实则隐式绑定的表达式。

• 容易踩的坑：(SELECT MAX(created_at) FROM events WHERE 1=1) —— 看似不相关，但 MySQL 5.7 及更早版本可能因 WHERE 存在而拒绝常量折叠；升级到 8.0+ 后才稳定支持
• 另一个陷阱：(SELECT name FROM users WHERE id = 123 ORDER BY id LIMIT 1) —— 虽然 id = 123 是常量，但 ORDER BY + LIMIT 组合在某些 PG 版本中会阻止 planner 提前求值
• 还有隐形绑定：(SELECT CURRENT_DATE + INTERVAL 1 DAY) 是安全的；但 (SELECT NOW() + INTERVAL 1 DAY) 在部分数据库中每次调用都重新求值（因为 NOW() 是 volatile 函数），哪怕它不依赖外层数据

想强制控制执行时机？别依赖“写法”，用 WITH 或变量更可靠

如果你发现不相关子查询在不同环境下行为不一致（比如开发用 PG 是单次执行，上线到 MySQL 却变慢），说明你已撞上优化器实现差异的边界。这时候靠改写子查询逻辑意义不大，不如显式拆解。

• 推荐做法：用 WITH 提前定义公共表达式，如 WITH max_price AS (SELECT MAX(price) FROM products) SELECT ..., (SELECT * FROM max_price) FROM orders —— 所有主流数据库都明确保证 CTE 在此场景下只执行一次
• MySQL 替代方案：用用户变量 @max := (SELECT MAX(price) FROM products) 再在 SELECT 中引用 @max，但要注意变量赋值顺序在 ORDER BY / GROUP BY 场景下不可靠
• 真正复杂点在于：有些子查询看似不相关，却因统计信息缺失或索引失效，导致优化器误判为高成本而放弃物化——这时加 /*+ MATERIALIZE */（MySQL 8.0.22+）或 /*+ SubqueryFactoring */（Oracle）才是直击要害的做法

不相关子查询的内存执行不是魔法，是优化器基于确定性、代价估算和实现约束做的选择。一旦涉及函数挥发性、版本差异或统计不准，它就可能绕开你期望的路径。