SQL改写实战（续）：子查询vs JOIN的深层原理

​关键词​子查询JOIN半连接物化查询优化器SQL改写大家好我是小耶写功课只是为了我踩过的坑你们别再踩了上周我们讲了COUNT进阶这周回到SQL改写的一个经典话题子查询和JOIN到底哪个更快网上有很多说法“能用JOIN就别用子查询”但我在实际工作中见过反例——有时候子查询反而更快。今天我们从原理到实测彻底讲透这件事。一、子查询与JOIN的本质差异​JOIN​你把两个朋友圈表A和表B的所有人直接配对找出共同认识的人。相当于先做笛卡尔积再过滤但优化器会通过索引、哈希等算法优化。​子查询​你先从表B中找出符合条件的人子查询结果然后拿着这个名单去表A中找对应的人。相当于分两步走。从关系代数角度看一个子查询可以改写为JOIN的前提是子查询的结果集可以被“物化”为一个临时表然后再与原表进行连接。但优化器是否会这样做取决于代价估算。二、优化器如何处理子查询MySQL优化器对子查询的处理策略主要有以下几种策略机制适用场景实例半连接semi-join将IN/EXISTS子查询转换为类似JOIN的操作但只返回外表的行子查询不复杂结果集不大SELECT * FROM t1 WHERE col IN (SELECT col FROM t2)物化先执行子查询将结果存入临时表带索引再与外表连接子查询结果集较小同上半连接EXISTS改写对每一行外表执行子查询判断是否存在子查询结果集非常大且外表小SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.idt1.id)相关子查询子查询依赖外表的当前行每行执行一次无法改写为JOIN时SELECT * FROM t1 WHERE col (SELECT MAX(col) FROM t2 WHERE t2.idt1.id)​重要​MySQL从5.6版本开始引入了半连接优化很多IN子查询会自动转为半连接性能与JOIN相当。所以“子查询慢”的说法在MySQL 5.6已经不成立了。三、实测对比子查询 vs JOIN环境MySQL 8.0.328C32G表t11000万行表t2500万行都有索引。场景1IN子查询可转为半连接-- 子查询 SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE status 1); -- JOIN写法 SELECT t1.* FROM t1 JOIN t2 ON t1.id t2.t1_id WHERE t2.status 1;实测结果两者执行时间都在1.2-1.5秒之间几乎无差别。查看执行计划子查询被优化为半连接实际执行相同的操作。场景2相关子查询无法转为JOIN-- 子查询每行执行一次 SELECT * FROM t1 WHERE amount (SELECT AVG(amount) FROM t2 WHERE t2.cat_id t1.cat_id);这种相关子查询无法直接转为JOIN改写JOIN需要先聚合再连接SELECT t1.* FROM t1 JOIN (SELECT cat_id, AVG(amount) as avg_amount FROM t2 GROUP BY cat_id) t2_avg ON t1.cat_id t2_avg.cat_id AND t1.amount t2_avg.avg_amount;实测子查询耗时38秒每行执行一次子查询1000万次JOIN聚合耗时0.9秒。此时JOIN完胜。​结论​能否优化的关键不在于“子查询还是JOIN”而在于子查询是否可以被优化器转为半连接或物化。可转换的性能相近不可转换的尤其是相关子查询必须改写。四、什么时候子查询可能比JOIN更快​子查询结果集极小​物化临时表比JOIN的哈希表构建更快。​外表极大子查询高度过滤​先执行子查询过滤掉大部分数据再与外表连接减少扫描量。​子查询使用了覆盖索引​子查询直接从索引返回数据避免回表。示例SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status 1 LIMIT 10)子查询只返回10个id物化成本极低可能比JOIN全表扫描快。五、系统化的改写决策子查询类型优化器处理是否建议改写改写方法IN (SELECT ...)子查询不依赖外表半连接/物化通常不需要可保持原样EXISTS (SELECT ...)外表小、子查询大外层循环可改写为JOIN改为JOIN或IN相关子查询WHERE条件依赖外表每行执行一次强烈建议改为JOIN聚合NOT IN子查询可能产生全表扫描建议改为NOT EXISTS或LEFT JOIN ... IS NULLIN子查询结果集极大物化开销大可考虑改写为EXISTS改为EXISTS​特别注意​NOT IN子查询在子查询结果包含NULL时结果永远是空因为NOT IN (NULL, 1, 2)的语义这是SQL标准行为。建议改用NOT EXISTS或LEFT JOIN避免逻辑错误。六、实战案例优化一个慢查询原SQL耗时26秒SELECT * FROM orders o WHERE o.status PENDING AND o.order_date 2026-01-01 AND o.user_id IN (SELECT user_id FROM users WHERE register_date 2025-01-01);执行计划显示子查询被物化用了索引但物化表有200万行然后外层表扫描后与物化表半连接效率尚可但仍有优化空间。改写为JOIN耗时18秒SELECT o.* FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.status PENDING AND o.order_date 2026-01-01 AND u.register_date 2025-01-01;为什么只快了一点因为orders表本身有1000万行索引过滤后仍有300万行JOIN也需要扫描这些行。进一步优化在orders(user_id, status, order_date)上建复合索引并将查询改为覆盖索引再回表取其他列最终降到6秒。​启示​不要只纠结子查询vs JOIN索引设计往往影响更大。七、总结子查询和JOIN孰优孰劣没有绝对答案取决于优化器版本、数据分布、索引设计、子查询类型。掌握优化器处理子查询的机制半连接、物化、相关结合EXPLAIN分析执行计划才能做出正确的改写决策。盲目相信“永远用JOIN”可能会错过子查询在某些场景下的优势。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~参考文献MySQL官方文档《Subquery Optimization》《高性能MySQL》第4版第9章查询优化