子查询性能优化关键在于识别三类低效场景并改写：1.相关子查询→join+聚合；2.in+大量结果→exists或join；3.select中标量子查询→窗口函数或cte。需先确认null处理、索引和语义一致性。
子查询在SQL面试中高频出现，考察点不只是语法是否写对，更关注你能否识别低效写法、判断执行逻辑、并合理改写为高性能方案。关键不是“会不会用子查询”，而是“知不知道什么时候不该用”。
哪些子查询容易拖慢性能？
以下三类最常见：
- 相关子查询（Correlated Subquery）：外层每查一行，内层就执行一次。比如SELECT name FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id)——部门平均工资需反复计算，数据量大时极慢。
- WHERE中使用IN + 子查询返回大量结果：如WHERE id IN (SELECT id FROM log WHERE dt > '2024-01-01')，若子查询返回几十万ID，IN可能退化为多次索引查找或全表扫描。
- SELECT列表中的标量子查询：如SELECT id, (SELECT COUNT(*) FROM order o WHERE o.user_id = u.id) cnt FROM user u，用户数多时等价于N次独立查询。
优先考虑的替代方案
多数低效子查询都有更优解，核心思路是“把多次计算变成一次计算，把嵌套逻辑拉平”：
HiShop网店代理分销系统
下载
Hishop.5.2.BETA2版主要更新： [修改] 进一步优化了首页打开速度 [修改] 美化了默认模板 [修改] 优化系统架构,程序标签及SQL查询效率,访问系统页面的速度大大提高 [修改] 采用了HTML模板机制,实现了前台模板可视化编辑,降低模板制作与修改的难度. [修改] 全新更换前后台AJAX技术框架,提升了用户操作体验. 店铺管理 [新增] 整合TQ在线客服 [修改] 后台广告位增加
- 相关子查询 → 改为JOIN + 聚合：上面部门平均工资例子，可先用GROUP BY算出各部均值，再JOIN关联：WITH dept_avg AS (SELECT dept_id, AVG(salary) avg_sal FROM emp GROUP BY dept_id) SELECT e.name FROM emp e JOIN dept_avg d ON e.dept_id = d.dept_id WHERE e.salary > d.avg_sal
- IN子查询 → 改为EXISTS或JOIN：EXISTS只关心是否存在，通常比IN快；若需去重或要取子查询字段，直接LEFT JOIN更可控。例如WHERE EXISTS (SELECT 1 FROM log l WHERE l.id = t.id AND l.dt > '2024-01-01')
- SELECT中子查询 → 改为窗口函数或预聚合CTE：用户订单数例子，用COUNT(*) OVER (PARTITION BY user_id) 或先聚合再JOIN，避免逐行调用。
改写前必须确认的3件事
别急着改，先看清楚原始意图和数据特征：
- 子查询是否可能返回NULL或多行？IN/NOT IN遇到NULL会整体失效；=子查询若返回多行直接报错。改写时要用LEFT JOIN+IS NOT NULL或COALESCE兜底。
- 外层和子查询涉及的字段是否有索引？特别是JOIN或EXISTS的关联字段。没索引，换写法也白搭。
- 业务语义是否允许近似结果？比如“最新一条订单”用ROW_NUMBER()排序取1，比(SELECT * FROM order WHERE user_id = u.id ORDER BY create_time DESC LIMIT 1)更稳定，也更易优化。
面试时怎么答才显深度？
不要只说“我改成JOIN了”，要体现判断链路：
- 先指出原写法的问题类型（比如“这是相关子查询，会导致笛卡尔积级执行次数”）；
- 说明改写后的执行差异（“现在只需扫描emp表两次，一次聚合、一次关联，复杂度从O(N×M)降到O(N+M)”）；
- 补充边界情况处理（“如果某部门无员工，原查询不返回该部门，改写后需加LEFT JOIN并过滤NULL”）；
- 最后提一句验证方式（“上线前我会对比执行计划，重点看rows和type字段，确保没出现ALL或Using temporary”）。
