MySQL Join查询的Nested Loop和Hash Join执行方式对比
数据库查询性能低下往往是由于Join算法选择不当导致的。在MySQL中，理解Nested Loop Join（嵌套循环连接）与Hash Join（哈希连接）的区别，是优化SQL语句的关键一步。本文将直接解析这两种机制的工作原理，并通过实际操作演示如何观察和选择它们。
一、 核心机制解析
1. Nested Loop Join (嵌套循环连接)
这是MySQL中最基础、历史最悠久的Join算法。你可以把它想象成两层嵌套的for循环。
- 基本逻辑：外层循环遍历“驱动表”的每一行数据，对于每一行，去内层循环的“被驱动表”中查找匹配的行。
- 执行流程：
- 读取驱动表的第一行。
- 根据关联条件，去被驱动表查找数据。
- 如果被驱动表有索引，利用索引快速定位（Index Nested Loop）；如果没有索引，则全表扫描（Block Nested Loop）。
- 重复上述步骤，直到驱动表所有行处理完毕。
当数据量较小，或者被驱动表上有高效索引时，这种算法非常高效。
2. Hash Join (哈希连接)
这是从MySQL 8.0.18版本开始引入的算法，专门用于处理大数据量且无索引关联的场景。它是一种“以空间换时间”的策略。
- 基本逻辑：将输入表分为“构建输入”（通常是较小的表）和“探测输入”（通常是较大的表）。
- 执行流程：
- 构建阶段：读取构建输入的所有行，根据Join Key计算哈希值，并存放在内存中的哈希表里。
- 探测阶段：读取探测输入的每一行，同样计算Join Key的哈希值，去哈希表中寻找匹配的数据。
当两张表数据量都很大，且关联字段没有索引时，Hash Join通常比Nested Loop快得多。
二、 执行流程对比图
为了更直观地展示差异，下面使用流程图描述两种算法的数据处理逻辑。
Nested Loop Join 流程
graph TD
A["开始: 驱动表 (表A)"] --> B["读取表A一行数据"]
B --> C{被驱动表 (表B) 有索引?}
C -- 是 --> D["通过索引查找表B"]
C -- 否 --> E["全表扫描表B"]
D --> F["组合结果集"]
E --> F
F --> G{表A还有数据?}
G -- 是 --> B
G -- 否 --> H["结束: 返回结果"]
Hash Join 流程
graph TD
subgraph Build ["构建阶段"]
A["读取小表 (构建输入)"] --> B["计算Join Key的哈希值"]
B --> C["存入内存哈希表"]
end
subgraph Probe ["探测阶段"]
D["读取大表 (探测输入)"] --> E["计算Join Key的哈希值"]
E --> F["去哈希表探测匹配"]
F --> G{匹配成功?}
G -- 是 --> H["组合结果集"]
G -- 否 --> I["丢弃"]
end
C --> D
H --> J{大表读完?}
J -- 否 --> D
J -- 是 --> K["结束: 返回结果"]
三、 实战：如何观察与切换算法
通过以下步骤，你可以亲自动手观察MySQL在不同场景下如何自动选择或强制使用这两种算法。
1. 准备测试环境
执行以下SQL脚本，创建两张无索引的大表，模拟Hash Join的典型场景。
-- 创建表 t1 (作为小表)
CREATE TABLE t1 (
id INT PRIMARY KEY,
col_a INT,
filler CHAR(100)
);
-- 创建表 t2 (作为大表)
CREATE TABLE t2 (
id INT PRIMARY KEY,
col_b INT,
filler CHAR(100)
);
-- 插入测试数据 (注意: 生产环境请勿随意插入大量数据)
-- 这里假设使用存储过程或脚本插入数据
-- t1 插入 1000 条
INSERT INTO t1 SELECT seq, seq % 100, 'test' FROM seq_1_to_1000;
-- t2 插入 10000 条
INSERT INTO t2 SELECT seq, seq % 100, 'test' FROM seq_1_to_10000;2. 观察 Hash Join (默认行为，MySQL 8.0.18+)
在较新版本的MySQL中，优化器通常会在没有索引的情况下自动选择Hash Join。
执行 EXPLAIN 命令查看执行计划：
EXPLAIN FORMAT=TREE
SELECT * FROM t1 JOIN t2 ON t1.col_a = t2.col_b;检查输出结果中的关键字：
- 输出内容会包含
Inner hash join字样。 - 你会看到
t1作为buildinput，t2作为probeinput。
3. 强制使用 Nested Loop Join
为了对比性能，我们需要禁用 Hash Join，强制数据库回退到 Nested Loop。
执行以下命令关闭 Hash Join 开关：
SET SESSION optimizer_switch = 'hash_join=off';再次执行查询并观察：
EXPLAIN FORMAT=TREE
SELECT * FROM t1 JOIN t2 ON t1.col_a = t2.col_b;检查输出结果：
hash_join=off生效后，执行计划将变为Nested loop inner join。- 在没有索引的情况下，你可能会看到
Block Nested Loop的警告或执行路径，这意味着性能将急剧下降，因为算法复杂度从接近线性 $O(N)$ 变成了乘积级 $O(N \times M)$。
4. 恢复默认设置
测试完成后，执行命令恢复优化器默认设置：
SET SESSION optimizer_switch = 'hash_join=on';四、 核心差异总结表
为了方便记忆和查阅，以下是两种算法在关键维度的对比。
| 特性 | Nested Loop Join | Hash Join |
|---|---|---|
| 适用场景 | 驱动表数据量小，或被驱动表有高效索引 | 大表关联大表，且关联字段无索引 |
| 时间复杂度 | $O(R \times S)$ 或 $O(R \times \log S)$ | 约 $O(R + S)$ (构建+探测) |
| 内存消耗 | 较低 (主要依赖磁盘I/O) | 较高 (需在内存构建Hash表) |
| 是否依赖索引 | 是 (极度依赖索引提升性能) | 否 (完全不需要索引) |
| MySQL版本支持 | 所有版本 | 8.0.18 及以上版本 |
五、 优化建议
在实际工作中，根据数据特征选择正确的算法至关重要。
- 优先利用索引：如果关联条件上有索引，Nested Loop Join（特别是 Index Nested Loop）通常是最高效的，因为它避免了全表扫描和内存构建开销。
- 关注大数据量无索引场景：当执行大表Join且发现
EXPLAIN中出现Block Nested Loop时，应检查是否为MySQL 8.0以下版本，或显式开启hash_join。 - 监控内存使用：Hash Join需要将构建表加载到内存。如果内存不足（
join_buffer_size不够），MySQL会将数据分片写到磁盘上，这会导致性能骤降。此时应调整参数join_buffer_size或限制查询数据量。 - 利用Hint控制：在极端情况下，如果优化器选择错误，可以使用 Hint 强制指定算法：
- 强制 Hash Join:
SELECT /*+ HASH_JOIN(t1, t2) */ ... - 强制 Nested Loop:
SELECT /*+ NO_HASH_JOIN(t1, t2) */ ...
- 强制 Hash Join:
通过理解这两者的底层逻辑，你能够更精准地诊断慢查询原因，并通过调整索引或配置参数来显著提升查询效率。
暂无评论，快来抢沙发吧！