为何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
);

问题：

• 外层每一行都会执行一次子查询
• 如果外层 100 万行 → 子查询执行 100 万次
• 几乎无法利用索引优势

✅ 优化方式：

• 改成 JOIN + 聚合
• 使用窗口函数（如 AVG() OVER()）

2. 子查询未“展开”（Subquery Unnesting 失败）

很多数据库（如 Oracle、MySQL）会尝试把子查询转成 JOIN：

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

如果优化器无法展开：

• 可能变成“先跑子查询 → 再逐条比对”
• 产生临时表 + 排序 + 去重

✅ 影响因素：

• 子查询中有 DISTINCT / GROUP BY / LIMIT / UNION
• 使用 NOT IN + 可空字段

二、索引无法被有效利用

3. 子查询中字段没有索引

WHERE x IN (SELECT y FROM t WHERE z > 10)

如果：

• y 没索引
• z 没索引

→ 子查询本身全表扫描

4. NOT IN 子查询特别慢

WHERE id NOT IN (SELECT user_id FROM blacklist)

问题：

• 子查询有 NULL → 结果直接为空（语义问题）
• 优化器往往选择“逐行排除”
• 不如 NOT EXISTS

✅ 推荐：

WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.user_id = u.id
)

三、临时表 & 排序开销大

5. 子查询产生中间结果集

例如：

SELECT *
FROM (
  SELECT * FROM logs ORDER BY create_time DESC
) t
LIMIT 100;

可能：

• 先对全表排序
• 再取前 100 行
• 排序在磁盘上完成（filesort / tempdb）

✅ 优化：

• 把 ORDER BY + LIMIT 放到最外层
• 或加索引

四、优化器“看不懂”复杂子查询

6. 多层嵌套子查询

SELECT ...
FROM (
  SELECT ...
  FROM (
    SELECT ...
    FROM t
  )
)

问题：

• 优化器难以推导谓词下推（predicate pushdown）
• 统计信息失准
• 执行计划不稳定

✅ 优化：

• 拆成 CTE（WITH）
• 或临时表 / 物化视图

五、不同数据库的差异

六、如何判断子查询是否慢？

1. 看执行计划

关注：

• Nested Loop（尤其带子查询）
• Materialize
• Filesort
• 行数估算偏差大

2. 常见“红旗”

• 执行时间随数据量指数增长
• rows examined >> rows returned
• 子查询出现在 WHERE / SELECT 中

七、通用优化原则（很重要）

✅ 能用 JOIN 就别用子查询
✅ 相关子查询 → 窗口函数 / JOIN
✅ NOT IN → NOT EXISTS
✅ 子查询结果小 → 可物化
✅ 避免 SELECT 子查询（每行算一次）

如果你愿意，可以把具体的 SQL + 数据库类型 + 表结构发出来，我可以直接帮你改写得更快。