JOIN优化

开篇：为什么 JOIN 查询经常变慢？

如果你在大厂工作过，大概率听过这样一条规矩："不建议使用多表 JOIN"。甚至阿里巴巴开发手册直接说：超过三个表的 JOIN 禁止使用。

为什么 JOIN 这么不受待见？因为它的底层实现方式------嵌套循环------天然就是 O(NM) 的复杂度。两张表 JOIN 还好，三张表就是 O(NM*K)，表越多、数据量越大，执行时间呈指数级增长。

打个比方：你要从两叠名片中找出同一个人的信息，你得拿第一叠中的每一张，去第二叠中逐张比对。如果第一叠有 1000 张，第二叠有 10000 张，那你最多要比对 1000 万次。这就是 JOIN 最朴素的执行方式------简单嵌套循环。

但 MySQL 并没有这么"傻"。从 5.5 到 8.0，它在 JOIN 算法上做了持续的优化。理解这些算法，才能写出高效的 JOIN 查询。

一、三种 JOIN 算法

MySQL 的 JOIN 算法经历了三代进化：

1.1 简单嵌套循环（Simple Nested-Loop Join）

这是最原始的方式：从驱动表取一行，去被驱动表中逐行匹配，匹配成功就放入结果集。

for (驱动表中的每一行 r1) {
    for (被驱动表中的每一行 r2) {
        if (r1.key == r2.key) {
            输出 (r1, r2);
        }
    }
}

如果驱动表 N 行、被驱动表 M 行，那么比较次数就是 N * M。而且每次比较都可能涉及磁盘 IO（从磁盘加载被驱动表的数据到内存），性能非常差。

1.2 索引嵌套循环（Index Nested-Loop Join）

如果被驱动表的 JOIN 列上有索引，MySQL 就不需要逐行扫描了，而是直接通过索引查找匹配行：

for (驱动表中的每一行 r1) {
    通过索引在被驱动表中查找 key == r1.key 的行
}

因为索引查找的复杂度是 O(log M)，所以总复杂度变成了 N * log(M)------比 N*M 好了太多。

这就是为什么被驱动表的 JOIN 列一定要有索引。它是 JOIN 优化中最重要的一条规则，没有之一。

如果索引是主键索引，一次就能拿到完整数据行；如果是普通索引，还需要回表查一次聚簇索引。所以，被驱动表用主键做 JOIN 效率最高。

1.3 块嵌套循环（Block Nested-Loop Join, BNL）

如果被驱动表没有索引怎么办？MySQL 5.5 引入了 BNL 来优化这种情况。

核心思想：不再逐行把驱动表的数据送去和被驱动表比较，而是一批一批地送。MySQL 会先把驱动表的一批数据（包括 JOIN 列和 SELECT 列）缓存到一块叫 join_buffer 的内存中，然后扫描被驱动表，让被驱动表的每一行和 join_buffer 中的所有行做比较。

while (驱动表还有数据) {
    将一批驱动表行加载到 join_buffer;
    for (被驱动表中的每一行 r2) {
        和 join_buffer 中的每一行比较;
    }
}

虽然总的比较次数还是 N*M，但关键改进在于：被驱动表的扫描次数大幅减少了。

原来：每读驱动表一行，就要扫一遍被驱动表，扫 N 遍。
现在：每填满一次 join_buffer，才扫一遍被驱动表。如果 join_buffer 能装 100 行，那只需要扫 N/100 遍。

这就好比你原来是一张一张名片去另一叠中对比，现在是一次拿 100 张过去，一次对比 100 张，来回跑的次数少了很多。

join_buffer 的大小由参数 join_buffer_size 控制，默认 256KB。增大它可以减少被驱动表的扫描次数，但不要设太大------每个连接都会分配。另外，join_buffer 中只缓存需要的列，所以查询时**尽量少用 SELECT ***，可以让 buffer 装下更多行。

1.4 Hash Join（MySQL 8.0.18+）

从 MySQL 8.0.18 开始，BNL 被废弃了，取而代之的是 Hash Join。它借鉴了大数据领域的经典做法，效率远高于嵌套循环。

Hash Join 分为两个阶段：构建和探测。

构建阶段：MySQL 选择较小的表（通常是驱动表），把它的 JOIN 列值计算哈希，存入一个内存中的哈希表。

探测阶段：扫描另一张表（被驱动表），对每一行的 JOIN 列值计算哈希，去哈希表中查找匹配行。哈希查找的复杂度是 O(1)，所以总复杂度是 O(N + M)------线性的！

当哈希表装不进内存怎么办？

如果驱动表太大，join_buffer 装不下，MySQL 会退化为基于磁盘的 Hash Join：

1. 先用哈希函数把驱动表分成多个分片，写到磁盘临时文件
2. 用同样的哈希函数对被驱动表分片
3. 逐个分片加载到内存中做匹配

虽然涉及磁盘 IO，但比 BNL 的多次全表扫描还是快得多。

三种算法的对比

算法	适用版本	复杂度	是否需要索引	适用场景
Simple NLJ	所有	O(N*M)	不需要	永远不该出现
Index NLJ	所有	O(N*logM)	被驱动表需要索引	最常见、最推荐
BNL	5.5~8.0	O(N*M) 但 IO 少	不需要	被驱动表无索引时的兜底
Hash Join	8.0.18+	O(N+M)	不需要	等值 JOIN，无索引时最优

二、驱动表的选择

2.1 什么是驱动表

在 JOIN 执行过程中，总有一张表先被扫描（驱动表/外层循环），另一张表后被扫描（被驱动表/内层循环）。驱动表的选择直接影响性能。

2.2 小表驱动大表原则

MySQL 的优化器通常会选择较小的表作为驱动表。为什么？

假设有两张表：employees（1000 行）和 departments（10 行），被驱动表的 JOIN 列都有索引（查找复杂度 log(N)）：

大表驱动小表：1000 次外循环 * log(10) 次查找 = 1000 * 3.3 ≈ 3300 次操作
小表驱动大表：  10 次外循环 * log(1000) 次查找 =   10 * 10  ≈  100 次操作

差距 33 倍！所以小表驱动大表的效率远高于大表驱动小表。

注意：这里的"小表"不是指物理上行数少的表，而是经过 WHERE 过滤后结果集小的表。一张百万行的表，WHERE 条件过滤后只剩 10 行，它也是"小表"。

2.3 不同 JOIN 类型的驱动表选择

• INNER JOIN：优化器自动选择驱动表，通常选小表
• LEFT JOIN：左表是驱动表（因为要保留左表所有行）
• RIGHT JOIN：右表是驱动表

但这不是绝对的。MySQL 优化器有时会把 LEFT JOIN 优化成 INNER JOIN（当 WHERE 条件使得左表的非匹配行一定会被过滤时），然后重新选择驱动表。

2.4 如何判断哪张表是驱动表

用 EXPLAIN 查看执行计划，排在第一行的表就是驱动表：

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID';

+----+--------+--------+---------+------+
| id | table  | type   | key     | rows |
+----+--------+--------+---------+------+
|  1 | o      | ref    | idx_st  | 5000 |  -- 驱动表
|  1 | u      | eq_ref | PRIMARY |    1 |  -- 被驱动表
+----+--------+--------+---------+------+

2.5 手动指定驱动表

如果你确定优化器选错了驱动表，可以用 STRAIGHT_JOIN 强制指定顺序：

-- 强制 departments 先执行（作为驱动表）
SELECT * FROM departments STRAIGHT_JOIN employees
ON departments.id = employees.dept_id;

注意：STRAIGHT_JOIN 只适用于 INNER JOIN，不能用于 LEFT/RIGHT JOIN。

三、JOIN 优化实战

了解了原理之后，我们来总结实战中的优化手段。

3.1 被驱动表的 JOIN 列必须有索引

这是最重要的一条。有没有索引的差距是 O(NM) vs O(NlogM)，数量级的差别。

-- 确保 ON 条件中的字段都有索引
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;
-- users.id 是主键，天然有索引
-- orders.user_id 也应该有索引

而且要注意：JOIN 的两个字段类型必须一致。如果一个是 INT 一个是 VARCHAR，MySQL 会做隐式类型转换，导致索引失效。

3.2 先过滤再 JOIN

不要把大表和大表直接 JOIN。先用 WHERE 条件把数据量缩小，再做连接：

-- 不好：先 JOIN 再过滤
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.create_time >= '2024-01-01';

-- 更好：先过滤再 JOIN（让优化器识别过滤条件）
SELECT o.id, c.name
FROM (
    SELECT id, customer_id
    FROM orders
    WHERE create_time >= '2024-01-01'
) o
JOIN customers c ON o.customer_id = c.id;

当然，很多时候 MySQL 的优化器足够聪明，会自动做这种优化（谓词下推）。但在复杂查询中，手动拆分可以帮助优化器做出更好的决策。

3.3 优先使用 INNER JOIN

如果业务上不需要保留左表/右表的所有行，就用 INNER JOIN 而不是 LEFT JOIN。原因有两个：

1. INNER JOIN 允许优化器自由选择驱动表，LEFT JOIN 只能左表做驱动表
2. INNER JOIN 两侧都能用到索引，LEFT JOIN 如果右表为 NULL，某些场景下索引会失效

3.4 控制 JOIN 的表数量

每多一张表 JOIN，复杂度就多乘一个数量级。建议：

• 2 张表 JOIN：正常操作，注意索引
• 3 张表 JOIN：谨慎使用，确保每个被驱动表都有索引
• 4 张及以上：强烈建议拆成多次查询，在应用层组装数据

3.5 不能 JOIN 时怎么办

如果业务场景确实不适合 JOIN，有三种替代方案：

1. 应用层关联：先从 A 表查出数据，提取关联 ID，再去 B 表批量查询，在代码中组装
2. 数据冗余：把需要联合查询的关键字段冗余到同一张表（空间换时间）
3. 宽表：把多张表的数据打平成一张大宽表，存到 MySQL 或同步到 ES

四、常见面试题精选

Q1：MySQL 为什么推荐小表驱动大表？

核心原因是减少索引查找次数。在 Index NLJ 算法下，驱动表的每一行都要在被驱动表上做一次索引查找（O(logM)）。驱动表行数越少，索引查找次数就越少。

小表驱动大表：10 * log(1000000) = 10 * 20 = 200 次
大表驱动小表：1000000 * log(10) = 1000000 * 3 = 3000000 次

差距一万五千倍。

Q2：MySQL 的 Hash Join 是什么？

Hash Join 是 MySQL 8.0.18 引入的 JOIN 算法，用于替代 BNL。它将驱动表数据构建成内存中的哈希表，然后遍历被驱动表逐行做哈希查找。复杂度从 O(N*M) 降到 O(N+M)。

但它只适用于等值连接（ON a.id = b.id），不支持非等值条件（如 ON a.id > b.id）。如果哈希表太大装不进内存，会退化为基于磁盘的分片 Hash Join。

Q3：如果 SQL 中一定要用 JOIN，怎么优化？

五条核心优化手段：

1. 给 JOIN 列加索引，尤其是被驱动表的 JOIN 列
2. 小表驱动大表，用 EXPLAIN 确认驱动表选择是否合理
3. 先过滤再 JOIN，用 WHERE 条件提前缩小数据量
4. 优先用 INNER JOIN，给优化器更多选择空间
5. 升级到 MySQL 8.0，利用 Hash Join 提升无索引 JOIN 的性能

小结

JOIN 优化的核心知识可以归纳为三句话：

1. 被驱动表一定要有索引------这是 JOIN 性能的命脉，有没有索引是 O(NlogM) 和 O(NM) 的差距
2. 小表驱动大表------减少外层循环次数，让索引查找的优势充分发挥
3. 能不 JOIN 就不 JOIN------表越多复杂度越高，超过 3 张表就要考虑应用层关联

记住一个判断标准：用 EXPLAIN 检查 JOIN 查询时，如果被驱动表的 type 是 eq_ref 或 ref，那就是健康的 JOIN；如果是 ALL 或者 Extra 出现了 Using join buffer，那就必须优化了。

附录：JOIN 优化检查清单

在写 JOIN 查询或做代码 Review 时，按这个清单逐条检查：

索引检查

• 被驱动表的 JOIN 列是否有索引？
• JOIN 两侧的字段类型是否一致？（INT 和 VARCHAR 会导致隐式转换，索引失效）
• 如果是联合索引，JOIN 列是否是索引的前导列？

驱动表检查

• 用 EXPLAIN 确认驱动表是否是小表（或过滤后的小结果集）
• 如果用了 LEFT JOIN，是否真的需要保留左表全部行？能否改成 INNER JOIN？

数据量检查

• JOIN 涉及几张表？超过 3 张的建议拆分成多次查询
• 每张表参与 JOIN 的数据量有多大？能否先用 WHERE 过滤缩小再 JOIN？

EXPLAIN 结果检查

• 被驱动表的 type 是否为 eq_ref 或 ref？
• Extra 中是否出现 Using join buffer？（如果出现，说明被驱动表缺索引）
• rows 的乘积是否在可接受范围内？（驱动表 rows * 被驱动表 rows 不要太大）

一个真实的优化案例

优化前：

SELECT o.order_no, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.create_time > '2024-01-01';

EXPLAIN 结果：products 表的 type=ALL，Extra 出现 Using join buffer。原因是 products.id 虽然是主键，但 orders.product_id 列的类型是 VARCHAR，而 products.id 是 INT，发生了隐式类型转换，索引失效。

优化方案：将 orders.product_id 的列类型从 VARCHAR 改为 INT（和 products.id 保持一致）。

优化后：products 表的 type 变为 eq_ref，Using join buffer 消失，查询时间从 3.2 秒降到 0.05 秒。