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

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

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

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

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

🎯 面试考点

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

背景引入

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

前阵子有个开发找我，说他的 SQL 在测试环境 0.1 秒，上了生产跑了 18 秒。我说你把 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 执行一次，结果存起来给外面用。

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

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

关联子查询（Correlated Subquery）

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

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？

子查询结果集很大，且 JOIN 能利用索引的时候——走 Index NLJ 比扫物化临时表快得多

你 EXPLAIN 看到 DEPENDENT SUBQUERY（关联子查询）的时候——赶紧改

需要返回外层表之外的数据的时候——子查询只能当过滤条件，JOIN 可以查出两张表任意字段

面试高频坑：NOT IN vs NOT EXISTS

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

-- 写法 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 里的"隐形炸弹"

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

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：

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 的执行差异

准备工作

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 子查询（有索引的情况）

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+）看得更清楚：

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 几乎没有区别。

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

-- 去掉 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，但内层没法用索引了。

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

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

-- 标量子查询写法
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 后：

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 陷阱实测

-- 构造一个含 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 影响

-- ✅ 安全的写法：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;

避坑指南

⚠️ 真实踩过的坑：

MySQL 5.5 及之前的 IN 子查询 = 灾难
- 没有 Semi-Join，子查询逐行执行
- 建议：能改 JOIN 就改 JOIN，别犹豫
NOT IN 遇到 NULL 返回空结果
- 不报错，不出异常，就是没有数据
- 建议：优先用 NOT EXISTS 或 LEFT JOIN ... IS NULL，除非你 100% 确认子查询不会返回 NULL
SELECT 后面的标量子查询，行数多了就爆炸
- 每个子查询对外层每一行执行一次
- 建议：能改成 LEFT JOIN 就改，或者用 CTE（MySQL 8.0+）
多层嵌套子查询 = 优化器摆烂
- 3 层以上的子查询嵌套，优化器可能直接放弃治疗，退化成最原始的执行方式
- 建议：要么拆成 CTE，要么拆成多个临时表，让优化器一次只处理一层
派生表（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

思考题

🤔 互动时间：

你在实际工作中遇到过子查询引发的性能问题吗？是怎么排查和解决的？
业务上有个需求：查询"最近 30 天下过单的 VIP 用户"，你分别用 JOIN、IN 和 EXISTS 写出三种写法，你觉得哪个最好？为什么？
面试官问你"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知识点，百日打怪升级》

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

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

背景引入

核心概念

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

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

面试高频坑：NOT IN vs NOT EXISTS

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

面试解答

实战案例

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

准备工作

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

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

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

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

避坑指南

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

思考题

总结

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

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

背景引入

核心概念

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

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

面试高频坑：NOT IN vs NOT EXISTS

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

面试解答

实战案例

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

准备工作

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

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

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

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

避坑指南

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

思考题

总结

热门文章

最新文章

相关电子书

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