在数据库面试和日常SQL开发中，子查询是最容易被写出性能隐患的语法结构之一。很多同学习惯用嵌套的SELECT来表达业务逻辑，却忽略了数据库优化器在处理子查询时的执行策略差异。理解子查询的底层展开方式，是做对优化和改写的前提。
一、子查询为什么容易变慢
从执行原理来看，子查询分为非相关子查询和相关子查询。非相关子查询不依赖外层表的字段，数据库通常先独立执行一次，把结果物化成临时表；而相关子查询的WHERE条件中引用了外层列，优化器往往只能对外层每一行都执行一遍子查询，形成所谓的“重复驱动”。当外层有十万行，子查询内部又要扫表时，复杂度直接变成乘法关系。
另外，多层嵌套的子查询会阻碍优化器做谓词下推和连接顺序调整。比如写在SELECT列表里的标量子查询，在MySQL某些版本中会被强行逐行求值，即便实际只需要少量数据。下面是典型的相关子查询示例，它会让用户表每条记录都去订单表查一次：
SELECT u.user_id, u.user_name, (SELECT MAX(o.pay_amount) FROM orders o WHERE o.user_id = u.user_id) AS max_pay FROM users u;
这种写法在用户量增长后延迟会线性恶化，也是面试中常被要求改写的重点题型。
二、用JOIN改写替代子查询
最常见的优化思路是把相关子查询转成LEFT JOIN。以上面的例子为例，我们可以先按用户聚合出最大支付额，再关联回用户表。这样数据库只需对订单表做一次分组扫描，然后通过哈希连接完成匹配，避免了逐行执行。
改写后的SQL如下，逻辑等价但执行路径更友好：
SELECT u.user_id, u.user_name, t.max_pay FROM users u LEFT JOIN ( SELECT user_id, MAX(pay_amount) AS max_pay FROM orders GROUP BY user_id ) t ON t.user_id = u.user_id;
在百万级订单、十万级用户的场景下，原写法可能要几秒甚至超时，改写后通常能降到几百毫秒。需要注意，如果业务要求“只输出下过单的用户”，则应把LEFT JOIN改为INNER JOIN，或用EXISTS配合索引，避免返回大量NULL行影响下游计算。
三、用窗口函数取代分组子查询
面试题里另一类高频题是“取每个用户最近一笔订单”。新手容易写成先分组查最大时间，再拿时间去原表关联，或者直接在SELECT里套子查询。更现代且高效的写法是使用ROW_NUMBER窗口函数，在一次扫描中完成分区排序。
示例代码如下，它为每用户的订单按时间倒序编号，外层只取编号为一的记录：
SELECT user_id, order_id, pay_amount, create_time
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY create_time DESC
) AS rn
FROM orders
) sub
WHERE rn = 1;
相比多层子查询和自连接，窗口函数减少了重复扫描，代码也更易于维护。多数主流数据库如PostgreSQL、MySQL 8.0、SQL Server都已良好支持该语法，面试时能主动提出窗口函数方案，往往比单纯说加索引更得分。
四、EXISTS与IN的取舍
当子查询用于过滤存在性时，EXISTS和IN都能实现，但执行表现不同。IN会把子查询结果集拉到外层做哈希或排序去重，若子查询返回NULL还可能引发语义陷阱；EXISTS则是遇到第一条匹配就返回，更适合子查询表大、外层表小的场景。
下面用EXISTS改写“查有订单的用户”，比用IN查全量用户ID更安全高效：
SELECT u.user_id, u.user_name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.user_id );
如果orders表的user_id上有索引，EXISTS能快速反查命中。反过来，当子查询去重后结果极小而外层极大时，IN配合常量列表也可能更优，因此实际改写要结合EXPLAIN观察执行计划，而不是死记规则。
五、改写时的注意点
子查询优化不是无条件“去嵌套化”。某些数据库对派生表合并有开关，若子查询含LIMIT、DISTINCT或聚合，优化器可能不会展开，此时JOIN改写才是真正生效的手段。此外，改写必须保证语义一致，比如LEFT JOIN聚合可能把无订单用户的最大金额变成NULL，而标量子查询同样返回NULL，二者等价；但若原意是过滤掉无订单用户，则漏写INNER JOIN就会导致数据变多。
建议在面试中回答此类题时，先说清原语句的性能瓶颈在哪里，再给出一种改写并说明执行计划变化，最后补一句会用EXPLAIN验证。这种结构既能展现原理理解，也体现了工程严谨性，比背优化口诀更有说服力。