> 子查询的坑与优化方案：子查询 vs JOIN

---

大家好，我是一名拥有10年以上经验的DBA老兵。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第11天内容。

---

> 🎯 **面试考点**

- 子查询和 JOIN 怎么选？各自优缺点？
- MySQL 对 IN 子查询做了什么优化（半连接 Semi-Join）？
- 关联子查询为什么慢？怎么改？
- NOT IN 和 NOT EXISTS 有什么区别？（面试高频坑）
- 标量子查询在 SELECT 里会导致什么问题？

---

## 背景引入

> 💡 说白了：子查询写起来真爽，但跑起来可能让你想哭

前阵子有个开发找我，说他的 SQL 在测试环境 0.1 秒，上了生产跑了 18 秒。我说你把 SQL 贴我看看。

他发过来：

```sql
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE register_time > '2026-01-01'
);
```

我一看就明白了。测试环境 `users` 表才 2000 行，没问题。生产？**200 万行。**

"你知道这条 SQL 在 MySQL 5.7 上是怎么执行的吗？"

他很自信："先查子查询拿到 id 列表，再传到外面 IN 嘛。"

我说——**错。恰恰相反。在某些版本里，MySQL 是拿外面每一行，去子查询里匹配一遍。** 里外循环颠倒，200 万 × 200 万，不慢才怪。

他当时那个表情，真的绝了。

**今天的目标**：把子查询的雷区一个一个踩给你看——面试必问、工作必踩。

---

## 核心概念

### 子查询的两种"人格分裂"

先说清楚——子查询其实有两种，它们的执行方式天差地别：

**非关联子查询（Independent Subquery）**

> 自己就能跑，不依赖外面的查询。MySQL 执行一次，结果存起来给外面用。

```sql
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level = 'VIP');
```

这里的子查询 `SELECT id FROM users WHERE level = 'VIP'` 可以独立执行，MySQL 跑一次拿到结果列表，再给外面用。看起来没问题，对吧？

**关联子查询（Correlated Subquery）**

> 子查询里引用了外层的字段——外面的每一行，子查询都得重新跑一次。

```sql
SELECT * FROM orders o
WHERE o.amount > (
    SELECT AVG(amount) FROM orders WHERE user_id = o.user_id
);
```

你品品——每一行 `orders` 都要执行一次 `AVG` 子查询。10 万行订单，子查询就跑 10 万次。**这不是 SQL，这是慢镜头回放。**

---

### 面试第一问：IN 子查询真的比 JOIN 慢吗？

> 这个问题本身就有坑——**看版本。**

MySQL 5.5 及之前，`WHERE id IN (SELECT ...)` 的处理方式非常粗暴：对**外层每一行**，执行一次子查询。等效于：

```
for each row in orders:
    for each row in (SELECT id FROM users WHERE level = 'VIP'):
        ...
```

外层 10 万行 × 内层 5 万行，硬碰硬。

**从 MySQL 5.6 开始**，引入了 **Semi-Join 优化**（半连接）和 **子查询物化**（Materialization）。优化器会把 `IN` 子查询改写成类似 JOIN 的执行计划。

说人话：**5.6+ 的 IN 子查询，很多时候已经被优化器改成了 JOIN，性能差距没那么大了。**

但——看 EXPLAIN，确认优化器真的用了 Semi-Join。

**面试必问**：

**Q: 什么时候 IN 子查询有性能问题？**

> 场景一：MySQL 5.5 及更早版本 → 没有 Semi-Join，逐行执行
> 场景二：子查询结果集超大 → 物化临时表没有索引，外层每行都要扫一遍临时表
> 场景三：多级嵌套子查询 → 优化器可能摆烂，退化成逐行执行
> 场景四：关联子查询 → 外面每行跑一次子查询，数据量大了必然爆炸

**Q: 那什么时候应该改成 JOIN？**

> 1. 子查询结果集很大，且 JOIN 能利用索引的时候——走 Index NLJ 比扫物化临时表快得多
> 2. 你 EXPLAIN 看到 `DEPENDENT SUBQUERY`（关联子查询）的时候——赶紧改
> 3. 需要返回外层表之外的数据的时候——子查询只能当过滤条件，JOIN 可以查出两张表任意字段

---

### 面试高频坑：NOT IN vs NOT EXISTS

这题面试必问，而且 10 个人 8 个答不对。

```sql
-- 写法 A
SELECT * FROM orders
WHERE user_id NOT IN (SELECT id FROM users WHERE level = 'VIP');

-- 写法 B
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM users u
    WHERE u.id = o.user_id AND u.level = 'VIP'
);
```

**惊天大坑：NOT IN 遇到 NULL 会全军覆没。**

如果 `users.id` 允许为 NULL，或者子查询结果里有 NULL，`NOT IN` 的逻辑会变成这样：

```
user_id NOT IN (1, 2, 3, NULL)
→ user_id != 1 AND user_id != 2 AND user_id != 3 AND user_id != NULL
→ user_id != 1 AND user_id != 2 AND user_id != 3 AND NULL
→ NULL
```

**整条 SQL 一行都不会返回。** 不报错，不出异常，就是没结果——你怎么查都查不出原因。

而 `NOT EXISTS` 用的是关联子查询的二值逻辑（真/假），NULL 不影响判断。

**经验法则**：

| 场景 | 推荐写法 | 原因 |
|------|---------|------|
| 确定无 NULL | IN 或 JOIN | 可读性好，5.6+ 有 Semi-Join 优化 |
| 可能有 NULL | EXISTS 或 JOIN | 避免 NOT IN 全军覆没 |
| 大表 + 关联条件 | JOIN | 执行计划最可控 |
| 小表 + 确定无 NULL | IN | 够用 |

---

### 标量子查询：SELECT 里的"隐形炸弹"

这个坑太隐蔽了，很多人栽过都不知道。

```sql
SELECT
    o.id,
    o.amount,
    (SELECT name FROM users WHERE id = o.user_id) AS user_name
FROM orders o;
```

你可能会想："`users.id` 是主键，查询很快吧？"

没错，单次是快。但如果 `orders` 有 10 万行，这个标量子查询要执行 **10 万次**。

每次 0.001 秒 × 10 万 = 100 秒。

而改成 JOIN：

```sql
SELECT o.id, o.amount, u.name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;
```

一次 JOIN，一次索引查找，0.1 秒搞定。

**标量子查询不是不能用**——它的优势是语法直观、不改变结果集行数（不会因为 JOIN 产生重复行）。但**数据量大就别用了**，改成 JOIN 或者 CTE。

---

## 面试解答

**Q: 子查询和 JOIN 怎么选？一句话总结？**

> **优先用 JOIN，除非你有理由用子查询。** 理由可以包括：语法更直观（尤其是 EXISTS 相关子查询）、不需要返回外部表字段、数据量小不在乎那点性能。但默认选 JOIN，因为 JOIN 的执行计划更可控、更容易加索引优化。

**Q: Semi-Join 是什么？怎么确认走了 Semi-Join？**

> Semi-Join 是 MySQL 5.6+ 对 IN 子查询的优化策略。优化器将 IN 子查询改写成类似 JOIN 的执行方式，但只返回外层表的匹配行（不会因为内层匹配多行而产生重复）。
> EXPLAIN 中看到 `select_type` 为 `SIMPLE`（而不是 `SUBQUERY`），且 Extra 中没有 `Full scan on NULL key` 之类的警告，说明走了 Semi-Join。
> 也可以用 `EXPLAIN FORMAT=TREE` 看有没有 `<semijoin>` 关键字。

**Q: MySQL 8.0 对子查询有什么新优化？**

> 最大的变化是引入了 CTE（Common Table Expression，WITH 子句）：`WITH cte AS (SELECT ...) SELECT ... FROM cte`。CTE 只会物化一次，可以被外部多次引用。8.0 还引入了 Lateral Derived Table（横向派生表），允许派生表引用前面的表的字段——这在某些复杂的子查询场景下非常有用。

---

## 实战案例

### 案例一：IN 子查询在 5.7 和 8.0 的执行差异

#### 准备工作

```sql
DROP TABLE IF EXISTS orders, users;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    level VARCHAR(10),
    register_time DATETIME
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    order_time DATETIME,
    INDEX idx_user_id (user_id)
);

-- 插入数据（请根据实际环境调整行数）
-- users: 10 万行，orders: 50 万行
-- 以下为简化演示，插入少量数据
INSERT INTO users (name, level, register_time) VALUES
('张三', 'VIP', '2026-01-15'),
('李四', 'Normal', '2025-08-20'),
('王五', 'VIP', '2026-03-01'),
('赵六', 'Normal', '2025-12-01'),
('钱七', 'VIP', '2026-02-14');

INSERT INTO orders (user_id, amount, order_time) VALUES
(1, 100.00, '2026-03-01'),
(2, 200.00, '2026-03-02'),
(3, 150.00, '2026-03-03'),
(1, 300.00, '2026-03-04'),
(4, 250.00, '2026-03-05'),
(5, 180.00, '2026-03-06');

ANALYZE TABLE users, orders;
```

#### ① IN 子查询（有索引的情况）

```sql
EXPLAIN SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level = 'VIP');
```

```
+----+-------------+-------+------------+------+---------------+---------------+---------+----------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref                  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+---------------+---------+----------------------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | ref  | PRIMARY       | idx_level     | 13      | const                |    3 |   100.00 | Using index; Start temporary |
|  1 | SIMPLE      | orders| NULL       | ref  | idx_user_id   | idx_user_id   | 5       | test.users.id        |    1 |   100.00 | End temporary            |
+----+-------------+-------+------------+------+---------------+---------------+---------+----------------------+------+----------+--------------------------+
```

`Start temporary` 和 `End temporary` 就是 Semi-Join 优化的标志。优化器把 IN 改成了类似 JOIN 的执行计划——先扫描 `users` 过滤出 VIP，再通过索引 `idx_user_id` 匹配 `orders`。

这就是 5.6+ 的进步：**IN 子查询已经被优化器改写成了 JOIN 的形式。**

EXPLAIN FORMAT=TREE（MySQL 8.0.16+）看得更清楚：

```sql
EXPLAIN FORMAT=TREE SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level = 'VIP');
```

```
-> Nested loop semijoin  (cost=4.05 rows=3)
    -> Index scan on users using idx_level  (cost=1.05 rows=3)
    -> Index lookup on orders using idx_user_id (user_id = users.id)  (cost=0.35 rows=1)
```

`Nested loop semijoin`——走了半连接 Index NLJ，和直接写 JOIN 几乎没有区别。

#### ② 子查询无索引可用的退化

```sql
-- 去掉 orders.user_id 上的索引
ALTER TABLE orders DROP INDEX idx_user_id;
EXPLAIN SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level = 'VIP');
```

```
+----+-------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+--------------------------+
| id | select_type | table       | partitions | type   | possible_keys | key        | key_len | ref                | rows | filtered | Extra                    |
+----+-------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+--------------------------+
|  1 | SIMPLE      | users       | NULL       | ref    | PRIMARY       | idx_level  | 13      | const              |    3 |   100.00 | Using index; Start temporary |
|  1 | SIMPLE      | orders      | NULL       | ALL    | NULL          | NULL       | NULL    | NULL               |    6 |   100.00 | Using where; End temporary   |
+----+-------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+--------------------------+
```

关键变化：`orders` 的 `type` 从 `ref` 变成了 `ALL`，`rows` = 6（全表扫描）。虽然还是 Semi-Join，但内层没法用索引了。

```sql
-- 恢复索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
```

#### ③ 标量子查询 vs JOIN 实测对比

```sql
-- 标量子查询写法
EXPLAIN SELECT o.*,
    (SELECT name FROM users WHERE id = o.user_id) AS user_name
FROM orders o;
```

```
+----+--------------------+-------+------------+------+---------------+-------------+---------+-----------------+------+----------+-------+
| id | select_type        | table | partitions | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+-------------+---------+-----------------+------+----------+-------+
|  1 | PRIMARY            | o     | NULL       | ALL  | NULL          | NULL        | NULL    | NULL            |    6 |   100.00 | NULL  |
|  2 | DEPENDENT SUBQUERY | users | NULL       | ref  | PRIMARY       | PRIMARY     | 4       | test.o.user_id  |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+-------------+---------+-----------------+------+----------+-------+
```

`select_type = DEPENDENT SUBQUERY`——关联子查询，外面扫一行、里面查一次。`orders` 表多大，子查询就执行多少次。

改成 JOIN 后：

```sql
EXPLAIN SELECT o.*, u.name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;
```

```
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------------+------+----------+-------+
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL        | NULL    | NULL            |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | ref  | PRIMARY       | PRIMARY     | 4       | test.o.user_id  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------------+------+----------+-------+
```

`select_type = SIMPLE`——一次 JOIN，一次索引查找。不管你多少行，就这一次。

**你要说性能差距——行数多了就是天和地的区别。**

---

### 案例二：NOT IN 的 NULL 陷阱实测

```sql
-- 构造一个含 NULL 的子查询
SELECT * FROM orders
WHERE user_id NOT IN (
    SELECT id FROM users WHERE level = 'VIP'
);
```

假设我们往 `users` 表里插一行 `(NULL, '测试', 'Normal', NULL)`：

| 场景 | 结果行数 | 说明 |
|------|---------|------|
| 子查询无 NULL | 正常返回 | 返回非 VIP 用户的订单 |
| 子查询有 NULL | **返回 0 行** | NOT IN 全军覆没！ |
| 改成 NOT EXISTS | 正常返回 | 不受 NULL 影响 |

```sql
-- ✅ 安全的写法：NOT EXISTS
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM users u
    WHERE u.id = o.user_id AND u.level = 'VIP'
);

-- ✅ 或者用 LEFT JOIN ... IS NULL
SELECT o.* FROM orders o
LEFT JOIN users u ON o.user_id = u.id AND u.level = 'VIP'
WHERE u.id IS NULL;
```

---

## 避坑指南

> ⚠️ 真实踩过的坑：

1. **MySQL 5.5 及之前的 IN 子查询 = 灾难**
   - 没有 Semi-Join，子查询逐行执行
   - 建议：能改 JOIN 就改 JOIN，别犹豫

2. **NOT IN 遇到 NULL 返回空结果**
   - 不报错，不出异常，就是没有数据
   - 建议：优先用 NOT EXISTS 或 LEFT JOIN ... IS NULL，除非你 100% 确认子查询不会返回 NULL

3. **SELECT 后面的标量子查询，行数多了就爆炸**
   - 每个子查询对外层每一行执行一次
   - 建议：能改成 LEFT JOIN 就改，或者用 CTE（MySQL 8.0+）

4. **多层嵌套子查询 = 优化器摆烂**
   - 3 层以上的子查询嵌套，优化器可能直接放弃治疗，退化成最原始的执行方式
   - 建议：要么拆成 CTE，要么拆成多个临时表，让优化器一次只处理一层

5. **派生表（FROM 子句里的子查询）没有索引**
   - MySQL 会把派生表的结果物化成临时表，且**默认不建索引**
   - 建议：如果派生表结果集大，外层还有 JOIN，考虑改用 CTE 或直接建临时表加索引

---

## 💡 快速判断：你的子查询有没有踩坑

```
子查询写在 WHERE IN 里？
├── MySQL 5.6+ 且 EXPLAIN 看到 Start temporary → ✅ 走了 Semi-Join
└── 否则 → ❌ 逐行执行，改 JOIN

子查询写在 SELECT 里（标量子查询）？
├── 表很小（< 1000 行）→ ✅ 问题不大
└── 表很大 → ❌ 改 LEFT JOIN

写了 NOT IN？
├── 确定子查询无 NULL → ✅ 可以用
└── 不确定 → ❌ 改 NOT EXISTS 或 LEFT JOIN ... IS NULL

子查询 3 层嵌套以上？
└── ❌ 拆成 CTE 或多个临时表

用了关联子查询（DEPENDENT SUBQUERY）？
├── 外层表很小 → ✅ 可以接受
└── 外层表很大 → ❌ 改 JOIN 或改写 SQL
```

---

## 思考题

> 🤔 互动时间：

1. 你在实际工作中遇到过子查询引发的性能问题吗？是怎么排查和解决的？
2. 业务上有个需求：查询"最近 30 天下过单的 VIP 用户"，你分别用 JOIN、IN 和 EXISTS 写出三种写法，你觉得哪个最好？为什么？
3. 面试官问你"IN 和 EXISTS 哪个更快"，你怎么回答？（提示：这题的正确答案是"看情况"）

---

## 总结

> 🎯 **面试考点**

- **子查询分为关联和非关联**：关联子查询对外层每一行执行一次，数据量大必慢
- **MySQL 5.6+ 的 Semi-Join 优化**：很多 IN 子查询已被改写成 JOIN，但要看 EXPLAIN 确认
- **NOT IN 的 NULL 陷阱**：子查询结果有 NULL 时，NOT IN 返回空结果。优先用 NOT EXISTS
- **标量子查询 = 行级别的 for 循环**：数据量大时改 LEFT JOIN
- **多层嵌套子查询**：拆成 CTE 或临时表，让优化器一次只处理一层
- **子查询 vs JOIN**：默认选 JOIN（执行计划更可控），有充分理由再用子查询

---

> 🎯 **今日行动**
>
> 现在就做一件事：打开你的数据库，搜一下有没有 `DEPENDENT SUBQUERY` 的慢查询——
>
> ```
> SELECT * FROM information_schema.processlist
> WHERE info IS NOT NULL;
> ```
>
> 或者在慢查询日志里搜 `SUBQUERY`。发现一条就改一条。改完之后跑一下 EXPLAIN，确认 `select_type` 变成了 `SIMPLE`。
>
> 截图发到评论区，告诉我你救回了多少毫秒。

---

**下期预告**：GROUP BY 与 COUNT 的效率问题 —— 面试必问！

**全本合集**：[《每天一个MySQL知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  

---

*有问题欢迎评论区交流，明天见！*


做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

【MySQL百日打怪升级第11天】 子查询的坑与优化方案：子查询 vs JOIN

本文是MySQL系列第11讲，深度剖析子查询常见陷阱与优化方案：详解关联/非关联子查询执行差异、MySQL 5.6+ Semi-Join优化机制、NOT IN的NULL致命陷阱、标量子查询性能爆炸原理，并对比IN/JOIN/EXISTS适用场景。干货满满，直击面试高频考点。（239字）

编程语言

MySQL子查询优化指南：详解IN/EXISTS区别、NOTIN的NULL陷阱、标量子查询性能问题及JOIN改写方案。掌握MySQL5.6+半连接优化原理，避免关联子查询性能瓶颈，提升SQL执行效率。

数据库

MySQL

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

【MySQL百日打怪升级第11天】 子查询的坑与优化方案：子查询 vs JOIN-腾讯云开发者社区-腾讯云

【MySQL百日打怪升级第11天】子查询的坑与优化方案：子查询 vs JOIN

【MySQL百日打怪升级第11天】子查询的坑与优化方案：子查询 vs JOIN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

【MySQL百日打怪升级第11天】 子查询的坑与优化方案：子查询 vs JOIN

【MySQL百日打怪升级第11天】 子查询的坑与优化方案：子查询 vs JOIN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

【MySQL百日打怪升级第11天】子查询的坑与优化方案：子查询 vs JOIN

【MySQL百日打怪升级第11天】子查询的坑与优化方案：子查询 vs JOIN