Subquery子查询如何调优

在数据库性能优化中，**子查询（Subquery）**往往是慢查询的重灾区。调优的核心思路是：减少嵌套、减少重复执行、利用索引、让优化器更容易生成高效执行计划。

下面按「常见类型 + 优化手段 + 示例」系统说明。

一、子查询的常见性能问题

1. 关联子查询（Correlated Subquery）

   • 子查询依赖外层字段
   • 外层每一行都执行一次子查询

   SELECT *
   FROM orders o
   WHERE o.amount > (
     SELECT AVG(amount)
     FROM orders o2
     WHERE o2.customer_id = o.customer_id
   );
   

2. IN 子查询返回大量数据

   SELECT *
   FROM users
   WHERE id IN (SELECT user_id FROM logs);
   

3. 子查询无法使用索引

   • 出现在 SELECT 列中
   • 使用了函数或表达式

4. 子查询导致执行计划不稳定

   • 优化器误判行数

二、子查询调优核心方法

✅ 1. 用 JOIN 替代子查询（最重要）

❌ 关联子查询

SELECT *
FROM orders o
WHERE o.amount > (
  SELECT AVG(amount)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

✅ 改写为 JOIN

SELECT o.*
FROM orders o
JOIN (
  SELECT customer_id, AVG(amount) avg_amt
  FROM orders
  GROUP BY customer_id
) t ON o.customer_id = t.customer_id
WHERE o.amount > t.avg_amt;

✅ 优势：

• 子查询只执行一次
• 可使用索引
• 执行计划更清晰

✅ 2. EXISTS 替代 IN（大表场景）

❌ IN（子查询大）

SELECT *
FROM users
WHERE id IN (SELECT user_id FROM logs);

✅ EXISTS

SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM logs l
  WHERE l.user_id = u.id
);

✅ 原因：

• IN 会先物化全部结果
• EXISTS 命中即停

✅ 3. 用派生表 / CTE 提前聚合

WITH avg_amount AS (
  SELECT customer_id, AVG(amount) avg_amt
  FROM orders
  GROUP BY customer_id
)
SELECT o.*
FROM orders o
JOIN avg_amount a ON o.customer_id = a.customer_id
WHERE o.amount > a.avg_amt;

✅ CTE（MySQL 8+ / PostgreSQL / Oracle）：

• 可读性更好
• 优化器可复用

✅ 4. 避免 SELECT 子句中的子查询

❌ 每行都执行

SELECT 
  id,
  (SELECT COUNT(*) FROM logs l WHERE l.user_id = u.id) AS cnt
FROM users u;

✅ 改为 LEFT JOIN

SELECT u.id, COUNT(l.user_id) AS cnt
FROM users u
LEFT JOIN logs l ON u.id = l.user_id
GROUP BY u.id;

✅ 5. 确保子查询字段有索引

重点关注：

• JOIN 字段
• WHERE 过滤字段
• GROUP BY / ORDER BY 字段

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_logs_user ON logs(user_id);

✅ 6. 控制子查询返回数据量

• 加 LIMIT
• 加时间范围

SELECT *
FROM users
WHERE id IN (
  SELECT user_id
  FROM logs
  WHERE create_time > NOW() - INTERVAL 7 DAY
);

✅ 7. 用窗口函数替代子查询（现代数据库）

SELECT *
FROM (
  SELECT *,
         AVG(amount) OVER (PARTITION BY customer_id) avg_amt
  FROM orders
) t
WHERE amount > avg_amt;

✅ 性能通常优于嵌套子查询

三、不同数据库特别提示

四、调优 Checklist

✅ 是否能改成 JOIN
✅ 是否可用 EXISTS
✅ 是否避免 SELECT 子查询
✅ 是否使用索引
✅ 是否减少返回行数
✅ 是否查看执行计划

如果你有 具体 SQL / 数据库类型 / 执行计划，我可以直接帮你改写到最优版本。