为什么SQL子查询被称作黑盒操作？理解查询优化器对嵌套的处理
SQL子查询常被冠以“黑盒”之名，这倒不是因为它的语法有多神秘，而是其背后的执行逻辑往往出人意料。关键在于，数据库优化器在大多数情况下无法穿透子查询的边界进行跨层优化。结果就是，外层查询的条件推不进去，该用的索引用不上，一个完整的执行计划被硬生生切割成几段独立操作。
子查询为什么常被物化（Materialized）
无论是PostgreSQL还是MySQL 8.0+，当优化器遇到CTE或显式子查询时，如果判断“逻辑合并的代价太高”或者“语义上需要隔离”，通常会选择一条更稳妥的路：先把子查询的结果计算出来，存成一个临时结果集。这个过程就是物化。本质上，这是优化器在权衡后，放弃了复杂的逻辑重写，转而采用分步执行的策略。
- CTE默认物化：即使用户只引用一次，WITH子句定义的CTE也倾向于被物化。加上
MATERIALIZED关键字只是显式强调这一行为，而NOT MATERIALIZED则是一个提示，可能触发内联，但并不保证。 - 排序与限制的组合：带有
ORDER BY ... LIMIT的子查询几乎必然被物化。优化器在这种情况下通常不敢冒险将外层的WHERE条件下推，因为这会改变排序和限制的语义。 - 相关子查询的困境：对于包含外部列引用的相关子查询（例如
WHERE t1.id IN (SELECT t2.ref_id FROM t2 WHERE t2.status = t1.status)），执行计划可能走向Nested Loop连接。但这依然不等于子查询内部能高效利用t2.status上的索引——因为t1.status的值在运行时才能确定。
MySQL 中子查询未下推的典型表现
查看EXPLAIN输出时，如果看到select_type显示为DEPENDENT SUBQUERY或UNCACHEABLE SUBQUERY，基本就可以判定这个子查询被当作一个独立的执行单元处理了。此时，即便外层有WHERE id = ?这样的精确过滤条件，子查询内部也不会自动获得同等的过滤待遇。
- 一个典型的性能陷阱：考虑查询
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'CN')。假设customers.id是主键，但region字段没有索引，那么这个子查询就很可能导致对customers表的全表扫描。 - 常见的解决思路：将子查询改写为
JOIN。这相当于给优化器“开了扇门”，让它有机会将region = 'CN'这样的条件下推到驱动表的扫描阶段，从而可能利用上更合适的索引。 - 关于语义与优化：需要注意的是，
IN和= ANY()在NULL值处理上行为不同，但它们的优化路径大体一致。而EXISTS通常比IN更容易被优化器转换为半连接（semi-join），尤其是在子查询只用于校验存在性时。
PostgreSQL 里 CTE 与子查询的执行差异
这里有个关键认知：CTE不仅仅是语法糖，它实际上强制引入了一个潜在的物化点。即使你写的是WITH cte AS (SELECT * FROM large_table WHERE flag = true) SELECT * FROM cte JOIN small_table USING(id)，优化器通常也不会主动将flag = true和JOIN条件合并下推——除非你显式声明WITH cte AS NOT MATERIALIZED (...)，并且满足内联的条件（比如没有被多次引用、不包含排序或聚合等）。
- 非CTE子查询的机会：在PostgreSQL中，普通的非CTE子查询（尤其是非相关子查询）更有可能被内联优化。但是，一旦子查询包含了
GROUP BY、DISTINCT或窗口函数，物化的概率就会大大增加。 - 如何识别瓶颈：使用
EXPLAIN (ANALYZE, VERBOSE)分析执行计划。如果看到CTE Scan on cte节点，并且其前面的CTE Materialize步骤耗时占比很高，那么这里很可能就是性能瓶颈所在。 - 破除一个迷思：不要想当然地认为“使用了WITH子句查询就会更快”。它的主要优势在于提升代码的可读性和可维护性，而非自动带来性能优化。真正决定速度的，还是要看执行计划中的数据是否在最合适的阶段被过滤了。
说到底，最容易被忽略的一点在于：你以为自己写的是一条完整的SQL语句，但数据库引擎很可能正在背后执行两条甚至多条逻辑上相互隔离的查询。物化本身并非缺陷，而是优化器在语义安全性与执行性能之间所做的权衡。至于要不要打破这个“黑盒”，则取决于具体的数据分布、索引覆盖情况，以及——你是否愿意为了代码的清晰可维护，暂时接受一点执行效率上的妥协。
