子查询改写为 JOIN
子查询的性能问题
MySQL 对子查询的处理经历了多个版本的优化，但子查询的性能仍然不如 JOIN。尤其是 MySQL 5.5 及更早版本中，子查询的执行方式往往会导致性能灾难。
经典问题：相关子查询
-- 子查询会对外层表的每一行都执行一次
SELECT * FROM user u WHERE (
SELECT COUNT(*) FROM `order` o WHERE o.user_id = u.id
) > 5;
这种相关子查询（Correlated Subquery）的执行方式是：外层表每扫描一行，就执行一次子查询。如果外层表有 10 万行，子查询就会被执行 10 万次！
改写场景一：IN 子查询 → JOIN
原始写法
SELECT * FROM user WHERE id IN (
SELECT user_id FROM `order` WHERE amount > 1000
);
JOIN 改写
SELECT DISTINCT u.* FROM user u
JOIN `order` o ON u.id = o.user_id
WHERE o.amount > 1000;
注意：IN 语义是”存在即返回”，JOIN 可能会产生重复行，所以需要加 DISTINCT。
性能对比
| 写法 | 执行原理 | 性能 |
|---|---|---|
| IN 子查询 | 先执行子查询 → 结果集放入内存 → 外层 IN 判断 | 子查询结果大时内存消耗高 |
| JOIN + DISTINCT | 优化的 Nested Loop Join | 依赖索引，通常更快 |
当order 表的 user_id 和 amount 有合适的索引时，JOIN 通常比 IN 子查询快好几倍。
改写场景二：EXISTS 子查询 → JOIN
原始写法
SELECT * FROM user u WHERE EXISTS (
SELECT 1 FROM `order` o WHERE o.user_id = u.id AND o.amount > 1000
);
JOIN 改写
SELECT DISTINCT u.* FROM user u
JOIN `order` o ON u.id = o.user_id
WHERE o.amount > 1000;
EXISTS 本身是相关子查询，外层每行执行一次。而 JOIN 可以利用哈希连接（MySQL 8.0+ 的 Hash Join）或 Nested Loop Join 一次性完成。
改写场景三：子查询在 WHERE 中的聚合
原始写法
SELECT * FROM product WHERE id = (
SELECT product_id FROM order_items
GROUP BY product_id
ORDER BY COUNT(*) DESC LIMIT 1
);
JOIN 改写
SELECT p.* FROM product p
JOIN (
SELECT product_id FROM order_items
GROUP BY product_id
ORDER BY COUNT(*) DESC LIMIT 1
) hot ON p.id = hot.product_id;
这个改写中，子查询依然存在但变成了派生表（Derived Table）。MySQL 会先执行派生表子查询，结果作为临时表，再和 product 表 JOIN。这种方式避免了相关子查询的外层遍历。
改写场景四：子查询在 SELECT 中
原始写法
SELECT
u.*,
(SELECT COUNT(*) FROM `order` o WHERE o.user_id = u.id) AS order_count
FROM user u;
JOIN 改写
SELECT u.*, COALESCE(o.order_count, 0) AS order_count
FROM user u
LEFT JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM `order`
GROUP BY user_id
) o ON u.id = o.user_id;
这个改写就是把分组聚合提前做一次，再用 LEFT JOIN 关联，避免了外层逐行执行子查询。
更高效的改写：CROSS JOIN 替代相关子查询
-- 原始：找有超过5个订单的用户
SELECT * FROM user u WHERE (
SELECT COUNT(*) FROM `order` o WHERE o.user_id = u.id
) > 5;
-- JOIN 改写
SELECT u.* FROM user u
JOIN (
SELECT user_id, COUNT(*) AS cnt
FROM `order`
GROUP BY user_id
HAVING COUNT(*) > 5
) o ON u.id = o.user_id;
这种改写效果最明显——聚合查询只执行一次，而不是每行一次。
MySQL 8.0 的改进
MySQL 8.0 引入了几个重要的优化：
1. Hash Join：自然支持 JOIN 的大表关联
2. 优化器对子查询的物化：MySQL 会主动将某些子查询转换为派生表（物化）
3. Lateral Derived Table：支持横向派生表
但即使有这些优化，显式改写为 JOIN 仍然是最可靠的方式——你不依赖于优化器的复杂推论。
改写原则
| 原子查询类型 | 建议改写方式 | 说明 |
|---|---|---|
| WHERE IN (子查询) | INNER JOIN | 注意 DISTINCT 去重 |
| WHERE EXISTS (子查询) | INNER JOIN | 注意 DISTINCT 去重 |
| WHERE NOT IN (子查询) | LEFT JOIN + IS NULL | 避免 NULL 陷阱 |
| WHERE NOT EXISTS (子查询) | LEFT JOIN + IS NULL | 推荐 |
| SELECT (子查询) | LEFT JOIN + GROUP BY | 先聚合再关联 |
| WHERE (聚合子查询) | JOIN + 派生表 | 子查询作为临时表 |
总结
子查询改写为 JOIN 的核心原则：把相关子查询转换为非相关查询，让聚合/子查询只执行一次而不是每行一次。
在实际开发中，子查询可读性更强，但 JOIN 性能更好。建议：性能敏感代码用 JOIN，工具类/一次性查询用子查询。当然，所有改写的最后一步都是用 EXPLAIN 验证执行计划。
暂无评论内容