MySQL Join

1. 什么是 JOIN（核心概念）

JOIN 用于将两个或多个表按关系字段关联起来，返回符合条件的记录。

SQL 标准支持的 JOIN 类型很多，但在 MySQL InnoDB 中实际使用频率最高的是：

INNER JOIN（内连接）
LEFT JOIN（左外连接）
RIGHT JOIN（右外连接）
CROSS JOIN（笛卡尔积）
SELF JOIN（自连接）

从本质上讲：

JOIN = 驱动表扫描行 × 被驱动表查找行（Nested Loop Join）

关键理解：

选择驱动表非常重要
驱动表越小越好
被驱动表必须有索引

2. JOIN 的执行方式（MySQL 重要底层原理）

MySQL（InnoDB）JOIN 本质上是 Nested Loop Join（嵌套循环）：

for row in 驱动表:
    用 row 的关联字段去被驱动表查找对应记录

查找方式依赖：

方式	原因	性能
Index Lookup	被驱动表有索引	🚀 快（最理想）
Block Nested Loop（BNL）	被驱动表没索引	🐌 非常慢
Hash JOIN（MySQL 8.0.18+）	大 JOIN，被优化器选择	🚀🚀（大表适用）

InnoDB 默认使用 Index Nested Loop（最常见、最需优化的模式）。

3. 不同 JOIN 的语义（简单 + 非常实用示例）

3.1 INNER JOIN（最常用）

只返回两个表都匹配的记录。

SELECT *
FROM user u
INNER JOIN order o ON u.id = o.user_id;

结果：交集。

应用场景：

查用户和订单的匹配数据

3.2 LEFT JOIN（第二常用）

返回左表全部 + 右表能匹配的部分，不匹配为 NULL。

SELECT *
FROM user u
LEFT JOIN order o ON u.id = o.user_id;

应用场景：

查用户是否下过订单（无订单也要显示）

3.3 RIGHT JOIN

返回右表全部。

推荐避免 RIGHT JOIN -> 可等价转换为 LEFT JOIN。

3.4 CROSS JOIN（笛卡尔积）

返回 m × n。

危险（容易造成爆炸性数据量）

3.5 SELF JOIN（自连接）

表内关联。

SELECT e.name, m.name
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;

4. JOIN 的执行计划（非常关键）

使用 EXPLAIN：

EXPLAIN SELECT ...

关键关注字段：

字段	意义
type	访问类型：ALL、index、range、ref、eq_ref
key	是否用到索引
rows	预估扫描行数
Extra	是否使用临时表、文件排序

JOIN 最关键指标：

被驱动表的 “type” 必须为 ref / eq_ref
绝不能出现 ALL（全表扫描）

5. 选择驱动表（优化重点）

MySQL 的优化器一般按以下原则选择驱动表：

WHERE 过滤后较小的表优先做驱动表
被驱动表必须有可用索引（关联字段索引）
使用 BKA/HASH JOIN 时，优化器可能调整顺序

正确理解驱动表：

语句	实际驱动表
FROM a JOIN b	优化器决定（不是 a 也不是 b）
STRAIGHT_JOIN	强制左表为驱动表

6. JOIN 场景最佳实践（非常实战）

6.1 场景 1：大表 JOIN 小表 -> 小表做驱动表

SELECT *
FROM small s
JOIN big b ON s.id = b.sid;

6.2 场景 2：JOIN 字段必须加索引（最重要的 JOIN 优化规则）

不加索引时：

MySQL 使用 Block Nested Loop -> 扫描被驱动表全表
性能直接爆炸

给被驱动表加索引：

ALTER TABLE order ADD INDEX idx_user_id(user_id);

6.3 场景 3：避免跨类型 JOIN

严重影响优化器选择：

varchar vs int
utf8 vs utf8mb4

会导致隐式类型转换 -> 索引失效。

6.4 场景 4：避免在 ON/WHERE 对关联字段做运算

错误（索引失效）：

JOIN order o ON u.id + 1 = o.user_id

正确：

JOIN order o ON u.id = o.user_id - 1

6.5 场景 5：尽量避免 JOIN 过多表（>5 表）

优化器过度复杂，执行计划不稳定。

6.6 常用优化技巧（实际项目用得最多）

使用 EXPLAIN 找到错误的驱动表
强制驱动表（使用 STRAIGHT_JOIN）
避免跨类型 JOIN
必须对 “被驱动表” 的关联字段建索引
保证统计信息最新：

ANALYZE TABLE table;

避免 ON 条件里计算、函数

7. MySQL JOIN 底层：三种 JOIN 算法详解

7.1 Index Nested Loop Join（最常见）

流程：

扫描驱动表一行
用关联字段在被驱动表中 “走索引” 查记录
合并结果

优点：非常快

缺点：被驱动表必须有索引，否则退化成 BNL -> 极慢

7.2 Block Nested Loop Join（最糟糕）

被驱动表没索引 ⇒ MySQL 将大块缓存拿来保存驱动表行，再循环对比。

性能：

🚫 性能指数级下降
🚫 非常容易打爆 CPU / IO

优化办法：

👉 给被驱动表的 JOIN 字段创建索引

7.3 Hash Join（MySQL 8.0.18+ 才有）

MySQL 优化器会判断是否使用 Hash Join，例如：

无法使用索引
表非常大
等值连接

底层通过哈希表加速 JOIN。

8. JOIN 常见错误与解决方案（实战）

❌ 8.1 多表 JOIN + GROUP BY 回排序太慢

解决：

增加索引
调整 SQL 顺序
减少 join 表数量

❌ 8.2 JOIN 字段缺索引导致大事务卡住

解决：

ALTER TABLE order ADD INDEX idx_user_id (user_id);

❌ 8.3 JOIN 条件写错，返回大量重复行

解决：使用明确字段 JOIN，不用 SELECT *

❌ 8.4 JOIN 时使用 OR 导致索引失效

错误：

ON a.id = b.id OR a.name = b.name

改成 UNION ALL 拆分：

SELECT …
FROM a JOIN b ON a.id = b.id
UNION ALL
SELECT …
FROM a JOIN b ON a.name = b.name;

9. JOIN 调优 Checklist（最终落地版）

必须做

被驱动表 JOIN 字段建立索引
避免跨类型 JOIN
让过滤后更小的表做驱动表
更新统计信息

尽量做

JOIN 不超过 5 张表
避免在 ON/WHERE 中运算
明确选择字段，不用 SELECT *

可以使用的技巧

使用 STRAIGHT_JOIN 强制驱动表
在某些场景用 HASH JOIN（MySQL 8+ 自动选择）

总结（一句话记住 JOIN）

JOIN 性能 = 驱动表大小 × 被驱动表的查询效率

所以：驱动表要小，被驱动表要有索引，这是 JOIN 的核心优化逻辑。