第 50 章 子查询与CTE踩坑

📖 本篇简介：子查询和 CTE（WITH 公用表表达式）让 SQL 写起来更模块化，但用错位置就会从"清晰"变成"很慢"。本章汇总子查询与 CTE 方向最经典的 12 个坑，覆盖关联子查询逐行执行、IN 与 EXISTS 选择、派生表条件下推、CTE 递归终止、CTE 物化、子查询返回多行与半连接。示例基于 MySQL 8.0。读完你能既写得清晰又跑得动。

目录

• 坑 1：关联子查询逐行执行
• 坑 2：IN 子查询套大表
• 坑 3：EXISTS 和 IN 用反了
• 坑 4：派生表阻止条件下推
• 坑 5：递归 CTE 没有终止条件
• 坑 6：CTE 被物化导致重复扫描
• 坑 7：子查询返回多行
• 坑 8：UPDATE 里套子查询更新同一张表
• 坑 9：把子查询当临时表滥用
• 坑 10：IN 列表拼得过长
• 坑 11：相关子查询塞进 SELECT 列表
• 坑 12：CTE 里写复杂逻辑拖慢优化
• 速查表：子查询CTE速记
• 注意事项：子查询改写思路
• 自测：你能避坑吗

坑 1：关联子查询逐行执行

场景 / 错误示范：

-- 相关子查询：子查询里引用了外层的 user.id
SELECT u.name,
       (SELECT COUNT(*) FROM order_t o WHERE o.user_id = u.id) AS order_cnt
FROM user u;
-- user 表 10 万行，子查询就执行 10 万次！

为什么是坑：相关子查询（ correlated subquery ）里引用了外层查询的列，外层每来一行，子查询就重新执行一次。外层 N 行，子查询跑 N 遍。如果被查询的表又大、又没索引，就是 N × M 的灾难。这是子查询慢的头号原因。

正确做法：

-- 改 JOIN + GROUP BY，一次聚合代替逐行查
SELECT u.name, IFNULL(o.cnt, 0) AS order_cnt
FROM user u
LEFT JOIN (SELECT user_id, COUNT(*) cnt FROM order_t GROUP BY user_id) o
  ON u.id = o.user_id;
-- order_t 只扫一次做聚合，再 JOIN，不用逐行查

加分点：SELECT 列表里的相关子查询是最危险的写法。看到 SELECT ..., (SELECT ... FROM ... WHERE 外表=外表)，就想想能不能改 JOIN 聚合。

坑 2：IN 子查询套大表

场景 / 错误示范：

-- 查"下过单的用户"
SELECT * FROM user
WHERE id IN (SELECT user_id FROM order_t);
-- order_t 巨大，IN 子查询要把它全扫一遍再匹配

为什么是坑：IN (子查询) 在老版本里会被当成"先把子查询结果物化成一个列表，再逐个匹配"。子查询结果集一大，这个列表又占内存又慢。虽然 8.0 优化器改进了半连接策略，但仍要警惕子查询返回巨大结果集。

正确做法：

-- 半连接语义明确，用 EXISTS 或 JOIN
SELECT u.* FROM user u
WHERE EXISTS (SELECT 1 FROM order_t o WHERE o.user_id = u.id);

-- 或直接 JOIN DISTINCT
SELECT DISTINCT u.* FROM user u JOIN order_t o ON o.user_id = u.id;

加分点：8.0 的 IN 子查询通常会被改写成半连接（semi-join）优化，但理解"IN 本质是半连接"有助于你判断何时该改写。

坑 3：EXISTS 和 IN 用反了

场景 / 错误示范：

-- 外表小、子查询表大，用了 IN
SELECT * FROM user WHERE id IN (SELECT user_id FROM order_t);

-- 外表大、子查询表小，用了 EXISTS
SELECT * FROM huge_log l WHERE EXISTS (SELECT 1 FROM region r WHERE r.id=l.region_id);

为什么是坑：IN 和 EXISTS 都是半连接，但效率取决于谁是驱动表。外表小、子查询大时，IN（把子查询物化后匹配）或 EXISTS 哪个快要看索引和数据分布。用反了就慢。经验：外表小用 IN，外表大、子查询小用 EXISTS。

正确做法：

-- 外表（user）小：用 IN 或半连接
SELECT * FROM user WHERE id IN (SELECT user_id FROM order_t);

-- 外表（huge_log）大：用 EXISTS，子查询 region 小且有索引
SELECT * FROM huge_log l
WHERE EXISTS (SELECT 1 FROM region r WHERE r.id = l.region_id);

加分点：现代优化器常常能把 IN 和 EXISTS 互相改写，但被驱动表关联列一定要有索引，否则哪种写法都慢。

坑 4：派生表阻止条件下推

场景 / 错误示范：

-- 派生表里套了 GROUP BY/聚合，外层 WHERE 无法下推
SELECT * FROM (
  SELECT user_id, SUM(amount) total FROM order_t GROUP BY user_id
) t
WHERE t.user_id = 123;
-- 优化器没法把 user_id=123 下推进派生表，被迫先全表分组再过滤

为什么是坑：派生表（FROM 里的子查询）如果里面有 GROUP BY、聚合、DISTINCT、UNION，外层的 WHERE 条件可能无法下推到派生表内部。于是派生表先算了全量，外层再过滤，白做了大量计算。

正确做法：

-- 把过滤条件直接写进子查询内部，让它先过滤再聚合
SELECT * FROM (
  SELECT user_id, SUM(amount) total FROM order_t
  WHERE user_id = 123          -- 下推进去
  GROUP BY user_id
) t;

-- 或干脆：需要按 user_id 过滤时，直接单层查询
SELECT user_id, SUM(amount) FROM order_t WHERE user_id = 123 GROUP BY user_id;

加分点：MySQL 8.0 对派生表做了不少条件下推优化，但聚合型派生表仍可能挡住。写派生表时把能提前过滤的条件写进去。

坑 5：递归 CTE 没有终止条件

场景 / 错误示范：

-- 递归 CTE 查"组织树"，忘了写终止条件
WITH RECURSIVE org_tree AS (
  SELECT id, parent_id, name FROM org WHERE id = 1   -- 锚点
  UNION ALL
  SELECT o.id, o.parent_id, o.name
  FROM org o JOIN org_tree t ON o.parent_id = t.id   -- 递归
)
SELECT * FROM org_tree;
-- 若数据里有循环（parent 链成环），永远递归不完直到报错

为什么是坑：递归 CTE 由"锚点 + 递归部分"组成。如果数据里有环（A 的 parent 是 B，B 的 parent 又是 A），没有终止条件就会死循环，直到触达 cte_max_recursion_depth 上限报错。这是递归 CTE 最容易踩的运行时坑。

正确做法：

WITH RECURSIVE org_tree AS (
  SELECT id, parent_id, name, 1 AS lvl FROM org WHERE id = 1
  UNION ALL
  SELECT o.id, o.parent_id, o.name, t.lvl + 1
  FROM org o JOIN org_tree t ON o.parent_id = t.id
  WHERE t.lvl < 10              -- 显式限制递归深度，防死循环
)
SELECT * FROM org_tree;

加分点：递归 CTE 一定要加深度或 visited 集合防环。8.0 默认 cte_max_recursion_depth=1000，生产上递归树查询要主动限制层级。

坑 6：CTE 被物化导致重复扫描

场景 / 错误示范：

-- CTE 被引用了两次，8.0 早期会物化它一次再复用
WITH big AS (
  SELECT * FROM huge_t WHERE status = 1   -- 大结果集
)
SELECT * FROM big WHERE type = 'a'
UNION ALL
SELECT * FROM big WHERE type = 'b';
-- 物化 big 占临时表空间，若其实能下推，不如直接写

为什么是坑：CTE 被多次引用时，优化器可能把它物化成临时表。物化本身要扫一遍、占空间。如果 CTE 并不复杂、条件能下推，物化反而是多余开销。CTE 的"复用"不是免费的。

正确做法：

-- CTE 不复杂、只引用一次时，直接写成子查询或 JOIN，别过度抽象
SELECT * FROM huge_t WHERE status = 1 AND type = 'a'
UNION ALL
SELECT * FROM huge_t WHERE status = 1 AND type = 'b';

-- 真正要复用复杂计算时才用 CTE，让逻辑模块化

加分点：CTE 的价值是"可读性、模块化"，不是"性能魔法"。别为了"高级"硬套 CTE，简单查询直接写更高效。

坑 7：子查询返回多行

场景 / 错误示范：

-- = 后面跟了可能返回多行的子查询
SELECT * FROM user
WHERE id = (SELECT user_id FROM order_t WHERE amount > 100);
-- 若子查询返回多个 user_id，报错：Subquery returns more than 1 row

为什么是坑：= 右边的子查询预期只返回一行一列。一旦它返回多行，MySQL 直接报错。开发者没考虑"这个子查询到底会返回几行"，运行时才炸。

正确做法：

-- 预期多行就用 IN
SELECT * FROM user
WHERE id IN (SELECT user_id FROM order_t WHERE amount > 100);

-- 预期严格一行，用 =，但要确保结果唯一（加 LIMIT 1）
SELECT * FROM user
WHERE id = (SELECT user_id FROM order_t ORDER BY id LIMIT 1);

加分点：写 = 子查询前先想清楚"它会返回几行"。不确定就用 IN，或 LIMIT 1 兜底。

坑 8：UPDATE 里套子查询更新同一张表

场景 / 错误示范：

-- 想把高于平均工资的员工涨薪，子查询却查了同一张表
UPDATE employee
SET salary = salary * 1.1
WHERE salary > (SELECT AVG(salary) FROM employee);
-- MySQL 报错：You can't specify target table for update in FROM

为什么是坑：MySQL 不允许你在 UPDATE 的 WHERE 子查询里直接查"正在更新的那张表"——怕一边改一边读产生不一致。直接写会报错。这是 UPDATE/DELETE 带子查询的经典限制。

正确做法：

-- 包一层派生表，让 MySQL 先把结果物化成临时表
UPDATE employee
SET salary = salary * 1.1
WHERE salary > (
  SELECT avg_s FROM (SELECT AVG(salary) avg_s FROM employee) tmp
);

-- 或先算出平均值存变量，再 UPDATE

加分点：UPDATE/DELETE ... WHERE 子查询查同表 要套一层 (SELECT ...) tmp 把结果物化，绕过限制。

坑 9：把子查询当临时表滥用

场景 / 错误示范：

-- 为了分步，把一个简单查询硬拆成多层嵌套子查询
SELECT * FROM (
  SELECT * FROM (
    SELECT * FROM user WHERE status = 1
  ) x WHERE age > 18
) y WHERE city = '北京';
-- 三层嵌套毫无意义，就是一个 WHERE 能搞定的事

为什么是坑：子查询嵌套层数过多，SQL 可读性直线下降，优化器也更难优化。很多人学了子查询就"万物皆嵌套"，把一个单层 WHERE 能写清的事套了三四层，纯属自找麻烦。

正确做法：

-- 能单层 WHERE 就单层
SELECT * FROM user
WHERE status = 1 AND age > 18 AND city = '北京';

-- 真需要分步、复用中间结果，才用 CTE
WITH active_users AS (
  SELECT * FROM user WHERE status = 1
)
SELECT * FROM active_users WHERE age > 18 AND city = '北京';

加分点：CTE 比嵌套子查询更可读，但"需要才用"。能一句话说清的查询，别为了显得高级而套娃。

坑 10：IN 列表拼得过长

场景 / 错误示范：

-- 应用把几万个 id 拼进 IN
SELECT * FROM t WHERE id IN (1,2,3,...,50000);
-- SQL 文本超长，解析慢

为什么是坑：和第 46 章坑 9 同源——IN 列表过大，SQL 文本膨胀，优化器解析和执行都慢。很多"先查 id 再用 id IN 反查"的两段式，本身就该改成 JOIN。

正确做法：

-- 小批量：IN 控制在几百内
SELECT * FROM t WHERE id IN (1,2,...,500);

-- 大批量：写临时表再 JOIN
CREATE TEMPORARY TABLE tmp_ids(id BIGINT);
INSERT INTO tmp_ids VALUES (1),(2),...;
SELECT t.* FROM t JOIN tmp_ids ON t.id = tmp_ids.id;

加分点：IN 列表上千就要警惕。两段式查询能合并成 JOIN 就合并。

坑 11：相关子查询塞进 SELECT 列表

场景 / 错误示范：

-- SELECT 列表里塞了多个相关子查询
SELECT u.name,
       (SELECT COUNT(*) FROM order_t o WHERE o.user_id=u.id) AS cnt,
       (SELECT MAX(create_time) FROM order_t o WHERE o.user_id=u.id) AS last_time,
       (SELECT SUM(amount) FROM order_t o WHERE o.user_id=u.id) AS total
FROM user u;
-- 每个用户跑 3 次子查询，10 万用户 = 30 万次子查询

为什么是坑：这是坑 1 的加重版——SELECT 列表里塞多个相关子查询，每个都逐行执行。外层 N 行 × M 个子查询 = N×M 次子查询，性能灾难。

正确做法：

-- 一次聚合算完三个指标，再 JOIN
SELECT u.name,
       IFNULL(o.cnt,0)    AS cnt,
       o.last_time,
       IFNULL(o.total,0)  AS total
FROM user u
LEFT JOIN (
  SELECT user_id,
         COUNT(*) cnt,
         MAX(create_time) last_time,
         SUM(amount) total
  FROM order_t GROUP BY user_id
) o ON o.user_id = u.id;

加分点：SELECT 列表里的多个相关子查询，几乎都能合并成"一次聚合 + JOIN"。看到就想着合并。

坑 12：CTE 里写复杂逻辑拖慢优化

场景 / 错误示范：

-- CTE 里塞了一堆 CASE WHEN、函数、DISTINCT
WITH cte AS (
  SELECT DISTINCT UPPER(name), COUNT(*), ... FROM huge_t GROUP BY ...
)
SELECT * FROM cte WHERE ...;
-- CTE 内部已经算了一大坨，外层再过滤，白做

为什么是坑：CTE 让逻辑"看起来"模块化，但内部复杂计算（DISTINCT、聚合、函数）和外层过滤如果没配合好，就会"先算全量、再过滤"。CTE 的可读性优势不能掩盖它可能做了多余计算。

正确做法：

-- 把能下推的过滤条件写进 CTE 内部，先过滤再计算
WITH cte AS (
  SELECT UPPER(name) uname, COUNT(*) c
  FROM huge_t
  WHERE create_time > '2026-01-01'   -- 先过滤，减少后续计算量
  GROUP BY UPPER(name)
)
SELECT * FROM cte WHERE c > 10;

加分点：用 EXPLAIN 看 CTE 是被物化还是被合并下推。复杂 CTE 要检查"是不是先算了全量再过滤"。

速查表：子查询CTE速记

| 写法 | 陷阱 | 正确做法 |

|---|---|---|

| SELECT 里相关子查询 | 逐行执行 N 次 | 改 JOIN 聚合 |

| IN 子查询大表 | 物化慢 | EXISTS/JOIN 半连接 |

| EXISTS/IN 用反 | 驱动表选错 | 外表小用 IN，大用 EXISTS |

| 派生表挡条件下推 | 先全量再过滤 | 条件写进子查询内部 |

| 递归 CTE 无终止 | 死循环报错 | 加深度限制 |

| CTE 多次引用 | 物化占空间 | 简单查询直接写 |

| = 子查询返回多行 | 报错 | 用 IN 或 LIMIT 1 |

| UPDATE 查同表 | 报错 | 套派生表物化 |

| 多层嵌套子查询 | 难读难优化 | 单层 WHERE 或 CTE |

| IN 列表上万 | SQL 超长 | 临时表 JOIN |

注意事项：子查询改写思路

拿到一条慢的子查询 SQL，按这个顺序想怎么改。

1. 是相关子查询吗：外层每来一行跑一遍？改 JOIN 聚合。

2. SELECT 列表里有子查询吗：多个就合并成一次聚合。

3. 条件能下推吗：过滤条件写进子查询内部了吗？

4. IN 还是 EXISTS：外表大小、被驱动表索引判断选哪个。

5. 递归有终止吗：深度限制加上了吗？

6. CTE 是不是过度抽象：简单查询直接写更高效。

一句话：子查询/CTE 是可读性工具，不是性能银弹。能用 JOIN + 一次聚合解决的，就别逐行相关子查询。

自测：你能避坑吗

题 1：SELECT ..., (SELECT COUNT(*) FROM order WHERE order.uid=user.id) FROM user 为什么慢？怎么改？

<details>

<summary>参考答案</summary>

这是相关子查询，外层 user 每行都执行一次 order 聚合。改成 LEFT JOIN (SELECT uid,COUNT(*) FROM order GROUP BY uid) o ON ...，order 只扫一次聚合再 JOIN。

</details>

题 2：= (SELECT user_id FROM order ...) 报 "Subquery returns more than 1 row"，为什么？

<details>

<summary>参考答案</summary>

= 右边的子查询预期只返回一行，实际返回了多行。预期多行就改用 IN；预期严格一行就加 LIMIT 1。

</details>

题 3：递归 CTE 为什么要加深度限制？

<details>

<summary>参考答案</summary>

数据里若 parent 链成环，没有终止条件会无限递归直到触达 cte_max_recursion_depth 报错。应在递归部分加 WHERE lvl < N 之类的深度限制，防死循环。

</details>

题 4：UPDATE employee SET ... WHERE salary > (SELECT AVG(salary) FROM employee) 为什么报错？

<details>

<summary>参考答案</summary>

MySQL 不允许 UPDATE 的子查询直接查"正在更新的同一张表"。套一层派生表 (SELECT AVG(salary) FROM employee) tmp 把结果物化即可绕过。

</details>

题 5：外表小、子查询表大，半连接该用 IN 还是 EXISTS？

<details>

<summary>参考答案</summary>

外表小用 IN（把小外表逐行匹配大子查询结果）。反过来外表大、子查询表小且有索引时用 EXISTS。现代优化器常能互相改写，但被驱动表关联列一定要有索引。

</details>

题 6：CTE 被引用了两次，会不会有性能问题？

<details>

<summary>参考答案</summary>

可能。优化器会把 CTE 物化成临时表复用，占空间和一次扫描。CTE 不复杂时直接写成普通 JOIN 或子查询更高效；CTE 的价值是可读性，别当性能魔法用。

</details>

题 7：派生表里套了 GROUP BY，外层 WHERE 为什么有时没法下推？

<details>

<summary>参考答案</summary>

派生表内有聚合/GROUP BY/DISTINCT 时，外层条件可能无法下推到派生表内部，导致先全量聚合再过滤。应把过滤条件写进子查询内部，让它先过滤再聚合。

</details>

下一篇预告：第 51 章 窗口函数踩坑