为何SQL中的非相关子查询只需要执行一次

非相关子查询只执行一次，因为优化器能静态识别其不依赖外层表，从而提前物化结果；若含NOW()、RAND()、用户变量或隐式别名污染等，则会退化为重复执行。

非相关子查询为什么只执行一次？看执行计划就清楚

因为数据库优化器能静态识别它不依赖外层数据，于是提前物化（materialize）结果——不是“约定俗成”，而是执行引擎的真实行为。

关键证据在 EXPLAIN 输出里：
- MySQL 中看到 select_type: SUBQUERY（不是 DEPENDENT SUBQUERY）
- PostgreSQL 中看到 InitPlan 节点，且无 Correlated Subquery 标记
- Oracle 中显示为 UNION ALL 或独立的 VIEW 步骤，而非嵌套循环

只要子查询里没出现类似 e.department_id、t1.id 这种指向外层表的列引用，优化器就会把它拎出来单独算一次。

哪些写法会让非相关子查询“假相关”？

表面看不引用外层，实则被优化器判为相关，导致重复执行——这是最隐蔽的性能陷阱。

• NOW()、RAND()、UUID() 等不确定性函数：优化器认为结果不可缓存，每次重算
• 子查询含 LIMIT 但没 ORDER BY：MySQL 可能拒绝物化（结果不稳定）
• 引用用户变量（如 @counter := @counter + 1）：状态依赖，无法复用
• 子查询中误写了外层别名却未实际使用（比如 SELECT * FROM employees e WHERE e.id IN (SELECT id FROM departments)，虽然 e.id 没在子查询里出现，但别名污染可能干扰解析）

标量子查询（SELECT 列里的子查询）真的一次性执行吗？

是的，但前提是它真是标量且非相关——否则会退化为逐行执行。

典型安全写法：

• SELECT name, (SELECT COUNT(*) FROM departments) AS dept_count FROM employees ✅
• SELECT id, (SELECT MAX(updated_at) FROM config_log) AS last_sync FROM users ✅

危险写法：

• SELECT id, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) ❌（这是相关子查询，users.id 是外层引用）
• SELECT id, (SELECT value FROM config WHERE key = 'timeout' AND updated_at > NOW() - INTERVAL 1 DAY) ❌（NOW() 导致每次重求值）

物化结果太大反而拖慢？什么时候该换 JOIN？

非相关子查询只执行一次，但不等于永远快——物化成本本身可能成为瓶颈。

当子查询返回百万级行时（比如 SELECT * FROM huge_log_table WHERE status = 'error'），即使只跑一遍，内存/磁盘临时表构建也会卡住。

这时改写要考虑三点：

• 子查询是否真需要全量结果？能否加 WHERE 缩小范围？
• 主查询是否对子查询结果做 IN 或 = 匹配？可转为 JOIN + 索引字段关联
• 聚合类子查询（如 AVG(salary)）通常不该改写——JOIN 后再聚合容易放大中间集，尤其外层表大时

最容易被忽略的是：物化是优化手段，不是语义保证。一旦子查询含不确定性因素或隐式依赖，所谓“只执行一次”就只是幻觉。