在SQL查询场景中，子查询是处理关联逻辑的常用手段，但当子查询嵌套层级过多或者数据量较大时，很容易出现查询耗时过长、数据库资源占用过高的问题。下面我们先看一张基础的子查询示例，再逐步展开优化方法。
常见的子查询性能问题
子查询的性能问题大多来自执行计划的不合理，比如相关子查询会对外层表的每一行都执行一次子查询，当外层表数据量达到十万甚至百万级时，子查询的执行次数会呈倍数增长。还有部分子查询会触发全表扫描，即使关联字段存在索引也无法被有效利用，进一步拖慢整体查询速度。
核心优化方法
1. 子查询转连接查询
大多数非相关子查询都可以转换为连接查询，数据库优化器对连接查询的处理逻辑更成熟，通常能生成更高效的执行计划。比如下面是常见的子查询写法：
-- 原低效子查询：查询订单金额大于平均订单金额的用户信息
SELECT u.user_id, u.user_name
FROM users u
WHERE u.user_id IN (
SELECT o.user_id
FROM orders o
WHERE o.order_amount > (SELECT AVG(order_amount) FROM orders)
);转换为连接查询后，执行效率会有明显提升：
-- 优化后连接查询写法 SELECT DISTINCT u.user_id, u.user_name FROM users u JOIN orders o ON u.user_id = o.user_id JOIN (SELECT AVG(order_amount) AS avg_amount FROM orders) t ON o.order_amount > t.avg_amount;
2. 合理使用索引
子查询的关联字段、过滤字段如果没有索引，很容易触发全表扫描。需要根据子查询的执行逻辑，给相关字段创建合适的索引：
- 子查询中作为过滤条件的字段，比如
order_amount，可以创建普通索引 - 连接场景下的关联字段，比如
users.user_id、orders.user_id，建议创建普通索引或者主键索引 - 如果子查询中包含多字段过滤，可以考虑创建联合索引，注意联合索引的字段顺序要符合最左匹配原则
3. 拆分复杂嵌套子查询
当子查询嵌套超过3层时，建议拆分逻辑，先把子查询的结果存入临时表，再基于临时表做后续查询。这样既能简化SQL逻辑，也方便数据库优化器生成更合理的执行计划：
-- 第一步：创建临时表存储平均订单金额 CREATE TEMPORARY TABLE tmp_avg_amount AS SELECT AVG(order_amount) AS avg_amount FROM orders; -- 第二步：基于临时表查询目标用户 SELECT DISTINCT u.user_id, u.user_name FROM users u JOIN orders o ON u.user_id = o.user_id JOIN tmp_avg_amount t ON o.order_amount > t.avg_amount; -- 第三步：使用完临时表后可以手动删除（部分数据库会话结束会自动删除） DROP TEMPORARY TABLE IF EXISTS tmp_avg_amount;
4. 避免相关子查询
相关子查询的执行逻辑是外层表的每一行都会触发一次子查询执行，数据量大时性能极差。如果业务中必须使用相关子查询，可以尝试改写为窗口函数实现，比如下面的相关子查询示例：
-- 原相关子查询：查询每个用户中金额最高的订单信息
SELECT o1.order_id, o1.user_id, o1.order_amount
FROM orders o1
WHERE o1.order_amount = (
SELECT MAX(o2.order_amount)
FROM orders o2
WHERE o2.user_id = o1.user_id
);改写为窗口函数后，只需要扫描一次表即可完成计算：
-- 优化后窗口函数写法
SELECT order_id, user_id, order_amount
FROM (
SELECT order_id, user_id, order_amount,
MAX(order_amount) OVER (PARTITION BY user_id) AS max_amount
FROM orders
) t
WHERE order_amount = max_amount;优化效果验证
每次调整SQL后，建议使用EXPLAIN命令查看执行计划，重点关注以下几个指标：
| 指标项 | 优化目标 |
|---|---|
| type | 尽量达到ref、eq_ref级别，避免ALL全表扫描 |
| rows | 扫描行数尽可能少，最好和返回结果行数接近 |
| Extra | 避免出现Using temporary、Using filesort等低效操作 |
通过执行计划的对比，可以直观看到优化前后的性能差异，也能针对性调整优化策略。