为什么在SQL中用JOIN替换子查询能大幅提升大表关联速度

相关子查询慢是因为外层表每行都触发一次内层查询，10万行即执行10万次索引查找；JOIN通过批量连接、谓词下推和单次扫描优化性能，但需注意NULL处理、字符集一致性和索引匹配等改写陷阱。

因为子查询（尤其是相关子查询）在大表上容易触发“逐行执行”，而JOIN允许数据库优化器做批量连接、谓词下推和索引复用，避免重复扫描。

相关子查询为什么慢到离谱

当外层表有 10 万行，子查询又依赖外层字段时，数据库大概率会执行 10 万次内层查询。比如：

SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) FROM user u;

哪怕 orders.user_id 有索引，每次仍要回表或走一次索引查找——不是“查一次索引，扫一遍数据”，而是“查 10 万次索引，每次扫一部分”。

• 执行计划里 select_type 显示 DEPENDENT SUBQUERY 就是典型信号
• 如果外层表没过滤条件，扫描行数等于全表行数；加了 LIMIT 可能掩盖问题，但本质未变
• 某些引擎（如 MySQL 5.7）甚至会为每次子查询生成临时结构，进一步拖慢速度

JOIN 怎么做到只扫一次小表

等价的 LEFT JOIN 写法把逻辑从“对每行求值”转为“先关联再聚合”：

SELECT u.name, COUNT(o.id) FROM user u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;

这时优化器可以：

• 选定 user 为驱动表（若它更小），或反过来选 orders（若过滤后更小）
• 把 WHERE 条件（如 u.status = 1）下推到驱动表扫描阶段，提前减少输入行数
• 用 orders.user_id 索引一次性定位所有匹配记录，而不是反复 seek

关键点：orders 表只被顺序扫描（或索引范围扫描）一次，而不是 10 万次随机访问。

改写时最容易踩的坑

不是所有子查询都能无脑换 JOIN，以下情况必须小心：

• NOT IN 含 NULL 时语义不同，得换成 LEFT JOIN ... WHERE o.id IS NULL，否则结果错
• 标量子查询（如 SELECT (SELECT name FROM dept WHERE id = u.dept_id)）改写后需 GROUP BY u.id 或加 DISTINCT，否则可能多出重复行
• 关联字段字符集不一致（比如 utf8mb4 vs utf8）会导致 JOIN 失效，退化成全表比对——比子查询还慢
• 没给 JOIN 字段建索引，或者索引顺序不匹配 WHERE + JOIN 条件，优化器照样选错执行路径

真正起作用的从来不是“用了 JOIN”，而是“JOIN 让优化器有了足够信息做全局决策”。别只改语法，盯着 EXPLAIN 里的 type、key、rows 和 Extra 看清楚到底扫了几行、用了什么索引、有没有临时表——这才是性能差异藏得最深的地方。