为什么JOIN查询比子查询快？—— 驱动表选择的秘密

摘要：从一次"子查询执行30秒超时"的线上故障出发，深度剖析JOIN和子查询的执行原理。通过EXPLAIN分析、Nested Loop算法图解、以及驱动表选择的性能对比，揭秘为什么MySQL对子查询的优化很弱、JOIN如何选择最优驱动表、以及什么时候必须用子查询。配合时序图展示查询流程，给出JOIN优化的5个最佳实践。

---

💥 翻车现场

周三下午，运营同学反馈数据导出功能超时。

错误信息：
Timeout: Query execution exceeded 30 seconds

哈吉米查看SQL：

-- 查询购买过商品的用户信息
SELECT * FROM user u
WHERE u.user_id IN (
  SELECT o.user_id FROM order_info o 
  WHERE o.user_id = u.user_id   -- 明确关联外部查询
);

哈吉米："这SQL有啥问题？很简单啊！"

用EXPLAIN分析：

EXPLAIN SELECT * FROM user 
WHERE user_id IN (
  SELECT user_id FROM order_info
)\G

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: user
         type: ALL             ← 全表扫描
         rows: 1000000         ← 扫描100万用户

*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY  ← 依赖子查询（坏信号）
        table: order_info
         type: index_subquery
         rows: 50              ← 每个用户都要执行一次子查询
        Extra: Using index

哈吉米："DEPENDENT SUBQUERY是啥？"

南北绿豆过来看了一眼。

南北绿豆："这是相关子查询，每个用户都要执行一次，相当于执行了100万次子查询！"
哈吉米："？？？"
阿西噶阿西（凑过来）："改成JOIN试试，性能能提升几百倍！"

-- 改写成JOIN
SELECT DISTINCT u.* 
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;

-- 执行时间：从30秒降到0.5秒（快60倍）

哈吉米："卧槽，为啥JOIN这么快？"

---

🤔 子查询的执行原理

子查询的两种类型

南北绿豆在白板上画了一个对比。

类型1：非相关子查询（独立子查询）

-- 子查询不依赖外部查询
SELECT * FROM user 
WHERE city_id IN (
  SELECT id FROM city WHERE province = '广东'
);

执行流程：

sequenceDiagram
    participant MySQL
    participant SubQuery as 子查询
    participant MainQuery as 主查询

    Note over MySQL: 非相关子查询（先执行子查询）
    MySQL->>SubQuery: 1. 执行子查询（一次）
    SubQuery->>SubQuery: SELECT id FROM city WHERE province = '广东'
    SubQuery->>MySQL: 返回：[1, 2, 3]
    
    MySQL->>MainQuery: 2. 执行主查询
    MainQuery->>MainQuery: SELECT * FROM user WHERE city_id IN (1, 2, 3)
    MainQuery->>MySQL: 返回结果
    
    Note over MySQL: 总查询次数：2次

特点：

• ✅ 子查询只执行1次
• ✅ 性能还可以

---

类型2：相关子查询（依赖子查询）

-- 子查询依赖外部查询（user_id）
SELECT * FROM user 
WHERE user_id IN (
  SELECT user_id FROM order_info  -- 每个user.user_id都要执行一次
);

执行流程：

sequenceDiagram
    participant MySQL
    participant MainQuery as 主查询
    participant SubQuery as 子查询

    Note over MySQL: 相关子查询（主查询驱动子查询）
    
    loop 遍历user表每一行
        MainQuery->>MainQuery: 读取user.user_id = 1
        MainQuery->>SubQuery: SELECT user_id FROM order_info WHERE user_id = 1
        SubQuery->>MainQuery: 返回结果（是否有订单）
        
        MainQuery->>MainQuery: 读取user.user_id = 2
        MainQuery->>SubQuery: SELECT user_id FROM order_info WHERE user_id = 2
        SubQuery->>MainQuery: 返回结果
        
        Note over MainQuery: ...循环100万次
    end
    
    Note over MySQL: 总查询次数：1 + 100万次

特点：

• ❌ 子查询执行100万次
• ❌ 性能极差

阿西噶阿西："看到了吗？相关子查询是性能杀手！"

---

如何判断是相关子查询？

看EXPLAIN的select_type：

EXPLAIN SELECT * FROM user 
WHERE user_id IN (
  SELECT user_id FROM order_info
)\G

select_type: DEPENDENT SUBQUERY  ← 相关子查询（危险）

select_type类型：

类型	含义	性能
SIMPLE	简单查询（无子查询）	⭐⭐⭐⭐⭐
PRIMARY	主查询	-
SUBQUERY	非相关子查询	⭐⭐⭐
DEPENDENT SUBQUERY	相关子查询	⭐ 慢
DERIVED	派生表（FROM子查询）	⭐⭐

---

🚀 JOIN的执行原理

Nested Loop Join（嵌套循环连接）

MySQL最常用的JOIN算法。

执行流程：

sequenceDiagram
    participant MySQL
    participant DrivingTable as 驱动表(小表)
    participant DrivenTable as 被驱动表(大表)

    Note over MySQL: Nested Loop Join
    
    MySQL->>DrivingTable: 1. 全表扫描驱动表
    
    loop 驱动表的每一行
        DrivingTable->>DrivingTable: 读取一行
        DrivingTable->>DrivenTable: 2. 用JOIN条件查询被驱动表（走索引）
        DrivenTable->>MySQL: 返回匹配的行
    end
    
    Note over MySQL: 总查询次数：<br/>驱动表行数 + 驱动表行数 * 1（索引查询）

示例：

SELECT u.*, o.* 
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;

-- 假设：
-- user表：1000行
-- order_info表：100万行
-- order_info.user_id有索引

执行流程：
1. 扫描user表（1000行）← 驱动表
2. 每个user.user_id在order_info中查询（走索引，很快）
3. 总查询次数：1000 + 1000 = 2000次（索引查询很快）

关键优化：

• ✅ 小表做驱动表
• ✅ 被驱动表的JOIN列必须有索引

---

驱动表的选择

MySQL怎么选择驱动表？

原则：选择结果集小的表做驱动表

规则：
1. INNER JOIN：优化器自动选择小表
2. LEFT JOIN：左表是驱动表（固定）
3. RIGHT JOIN：右表是驱动表（固定）

测试：

-- INNER JOIN（优化器自动选择）
EXPLAIN SELECT * FROM user u INNER JOIN order_info o ON u.user_id = o.user_id\G

*************************** 1. row ***************************
   id: 1
table: u          ← user表做驱动表（user表小）
 type: ALL
 rows: 1000

*************************** 2. row ***************************
   id: 1
table: o          ← order_info做被驱动表
 type: ref
  key: idx_user_id
 rows: 50         ← 每个user_id匹配50个订单

性能计算：

驱动表：user（1000行）
被驱动表：order_info（100万行）

总扫描次数：
1000（驱动表） + 1000 * 1（索引查询） = 2000次

如果反过来（order_info做驱动表）：
100万（驱动表） + 100万 * 1（索引查询） = 200万次

性能差距：1000倍！

南北绿豆："看到了吗？驱动表选对了，性能差几百上千倍！"

---

LEFT JOIN的陷阱

-- LEFT JOIN：左表固定是驱动表
SELECT * FROM order_info o 
LEFT JOIN user u ON o.user_id = u.user_id;

-- 驱动表：order_info（100万行）← 大表做驱动表（危险）
-- 被驱动表：user（1000行）

EXPLAIN:
table: o
 type: ALL
 rows: 1000000    ← 扫描100万行

table: u
 type: eq_ref
  key: PRIMARY
 rows: 1

优化：交换表的位置

-- ❌ 错误（大表做驱动表）
SELECT * FROM order_info o LEFT JOIN user u ON o.user_id = u.user_id;

-- ✅ 正确（小表做驱动表）
SELECT * FROM user u LEFT JOIN order_info o ON u.user_id = o.user_id;
-- 或者改成RIGHT JOIN
SELECT * FROM user u RIGHT JOIN order_info o ON u.user_id = o.user_id;

哈吉米："原来LEFT JOIN不能乱用，要考虑驱动表大小！"

---

📊 性能对比测试

测试环境：

• user表：100万行
• order_info表：500万行
• order_info.user_id有索引

测试1：子查询 vs JOIN

-- 子查询（相关子查询）
SELECT * FROM user 
WHERE user_id IN (
  SELECT user_id FROM order_info
);
-- 执行时间：32.5秒
-- 扫描次数：1 + 100万次

-- JOIN
SELECT DISTINCT u.* 
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 执行时间：0.8秒
-- 扫描次数：1 + 100万次（但走索引，很快）

-- 性能提升：40倍

---

测试2：驱动表选择的影响

-- 小表驱动大表（正确）
SELECT * FROM user u 
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 执行时间：0.8秒
-- 驱动表：user（100万行）

-- 大表驱动小表（错误，用STRAIGHT_JOIN强制）
SELECT * FROM user u 
STRAIGHT_JOIN order_info o ON u.user_id = o.user_id;
-- 执行时间：12.3秒
-- 驱动表：order_info（500万行）

-- 性能差距：15倍

---

测试3：被驱动表无索引

-- 被驱动表有索引
SELECT * FROM user u 
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 执行时间：0.8秒

-- 删除索引
ALTER TABLE order_info DROP INDEX idx_user_id;

-- 被驱动表无索引
SELECT * FROM user u 
INNER JOIN order_info o ON u.user_id = o.user_id;
-- 执行时间：85秒（超时）

-- 性能差距：100倍以上

阿西噶阿西："看到了吗？被驱动表的索引至关重要！"

---

🎯 什么时候必须用子查询？

场景1：聚合函数后再过滤

-- 查询订单数超过10的用户
SELECT u.* FROM user u
WHERE (
  SELECT COUNT(*) FROM order_info o WHERE o.user_id = u.user_id
) > 10;

-- 改写成JOIN很复杂：
SELECT u.* FROM user u
INNER JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM order_info
  GROUP BY user_id
  HAVING order_count > 10
) o ON u.user_id = o.user_id;

推荐：这种场景用子查询更简洁。

---

场景2：NOT EXISTS

-- 查询没有下过订单的用户
SELECT * FROM user u
WHERE NOT EXISTS (
  SELECT 1 FROM order_info o WHERE o.user_id = u.user_id
);

-- 改写成LEFT JOIN：
SELECT u.* FROM user u
LEFT JOIN order_info o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;

推荐：NOT EXISTS语义更清晰。

---

场景3：UPDATE/DELETE中的子查询

-- 删除没有订单的用户
DELETE FROM user 
WHERE user_id NOT IN (
  SELECT DISTINCT user_id FROM order_info
);

-- 必须用子查询（无法改成JOIN）

---

🛠️ JOIN优化的5个最佳实践

实践1：小表驱动大表

-- ✅ 正确
SELECT * FROM small_table s
INNER JOIN large_table l ON s.id = l.s_id;

-- ❌ 错误（如果用LEFT JOIN）
SELECT * FROM large_table l
LEFT JOIN small_table s ON l.s_id = s.id;

-- 改成RIGHT JOIN
SELECT * FROM small_table s
RIGHT JOIN large_table l ON s.id = l.s_id;

---

实践2：被驱动表的JOIN列必须有索引

-- 检查索引
SHOW INDEX FROM order_info;

-- 如果没有，创建索引
CREATE INDEX idx_user_id ON order_info(user_id);

---

实践3：避免SELECT *

-- ❌ 错误
SELECT * FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;

-- ✅ 正确（只查需要的列）
SELECT u.username, u.phone, o.order_no, o.amount
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id;

---

实践4：用STRAIGHT_JOIN强制驱动表

-- 如果优化器选错了驱动表，用STRAIGHT_JOIN强制
SELECT * FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.s_id;

-- STRAIGHT_JOIN：强制按写的顺序（small_table做驱动表）

---

实践5：子查询改写成JOIN

-- ❌ 子查询（慢）
SELECT * FROM user 
WHERE user_id IN (
  SELECT user_id FROM order_info WHERE status = 1
);

-- ✅ JOIN（快）
SELECT DISTINCT u.* 
FROM user u
INNER JOIN order_info o ON u.user_id = o.user_id
WHERE o.status = 1;

---

🎓 面试标准答案

题目：为什么JOIN比子查询快？

答案：

主要原因：

1. MySQL对子查询优化很弱

   • 容易产生相关子查询（DEPENDENT SUBQUERY）
   • 相关子查询会执行N次（N=外表行数）

2. JOIN可以选择最优驱动表

   • 自动选择小表做驱动表
   • 大大减少查询次数

3. JOIN可以利用索引

   • 被驱动表走索引查询（eq_ref/ref）
   • 子查询可能无法利用索引

性能对比：

• 子查询：1 + 100万次查询
• JOIN：1000次扫描 + 1000次索引查询

什么时候用子查询：

• NOT EXISTS（语义清晰）
• 聚合后再过滤
• UPDATE/DELETE中的子查询
• 逻辑更清晰的场景

优化建议：

• 小表驱动大表
• 被驱动表JOIN列建索引
• 能改JOIN就改JOIN
• 用EXPLAIN检查执行计划

---

🎉 结束语

晚上8点，哈吉米终于把SQL优化完了。

哈吉米："原来子查询这么慢，改成JOIN性能提升40倍！"

南北绿豆："对，关键是小表驱动大表，以及被驱动表必须有索引。"

阿西噶阿西："记住：能用JOIN就别用子查询，除非逻辑上必须用子查询。"

哈吉米："还有DEPENDENT SUBQUERY是危险信号，一看到就要改！"

南北绿豆："对，这是相关子查询，性能杀手！"

---

记忆口诀：

JOIN优于子查询，驱动表要选小
被驱动表建索引，性能提升不得了
DEPENDENT子查询，看到就要改
EXPLAIN来分析，type和rows要看好

---

希望这篇文章能帮你彻底理解JOIN和子查询的性能差异！下次写SQL，记得优先用JOIN！💪

---