MySQL JOIN 执行流程详解（从优化器到存储引擎的底层原理）

在日常数据库开发中，我们经常会编写各种 JOIN 语句来实现多表关联查询，例如：

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'PAID';

虽然这条语句看起来简单，但 MySQL 在底层执行时会经历多层复杂的优化与计算。本文将深入解析 MySQL 中 JOIN 连表查询的内部执行原理，结合 InnoDB 存储引擎和优化器机制，从底层视角解释 MySQL 是如何高效完成多表查询的。

---

一、JOIN 的本质：笛卡尔积与条件过滤

在关系型数据库理论中，JOIN 的本质是对两个表的 笛卡尔积（Cartesian Product） 再加上筛选条件。

例如：

SELECT * FROM A JOIN B ON A.id = B.a_id;

逻辑上等价于：

SELECT * FROM A, B WHERE A.id = B.a_id;

即：A 表的每一行与 B 表的每一行进行匹配，最终只保留满足 A.id = B.a_id 的组合。

如果 A 有 1000 行，B 有 1000 行，那么理论上会有 100 万种组合。但 MySQL 实际不会真的生成笛卡尔积，而是通过 嵌套循环（Nested Loop Join） 等算法来高效执行。

---

二、JOIN 类型分类

MySQL 支持多种 JOIN 类型，不同类型会影响结果集与执行方式。

JOIN 类型	说明
INNER JOIN	仅返回两表匹配的行
LEFT JOIN	返回左表全部行，不匹配的右表行用 NULL 填充
RIGHT JOIN	返回右表全部行，不匹配的左表行用 NULL 填充
CROSS JOIN	返回两表的笛卡尔积，无连接条件
SELF JOIN	一张表自己与自己连接，用别名区分

示例：

-- INNER JOIN
SELECT * FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;

---

三、JOIN 的执行算法

MySQL 执行多表连接时，会根据优化器的判断选择不同的连接算法。

1. Nested Loop Join（嵌套循环连接）

这是 MySQL 默认的连接算法。其逻辑是：

1. 从驱动表（外层表）读取一行记录；
2. 使用该行的关联字段，到被驱动表中查找匹配行；
3. 找到匹配后组合成结果行返回；
4. 重复以上步骤直到扫描完驱动表。

伪代码：

for row in tableA {
    for match in tableB where match.key = row.key {
        output(row, match);
    }
}

例如：

SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

如果 users 是驱动表，orders 是被驱动表，那么 MySQL 会：

• 逐行扫描 users；
• 对每行用户，使用索引查找其对应订单。

✅ 优化重点：被驱动表（通常是数据量大的一方）必须在 ON 条件字段上建立索引。

---

2. Block Nested Loop Join（BNL）

当被驱动表上没有合适索引时，MySQL 会使用 Block Nested Loop Join（块嵌套循环）。

BNL 的优化思路是：

• 将驱动表的一部分数据（一个块）读入内存；
• 在内存中缓存这些行；
• 扫描被驱动表的所有数据，与内存中的块进行比对。

伪代码：

for block in tableA (batch size = N) {
    for rowB in tableB {
        if (match(block, rowB)) output;
    }
}

BNL 的劣势在于：

• 被驱动表需要全表扫描。
• 每次缓存块大小有限，内存开销较大。

这种算法通常出现在：

SELECT * FROM large_table1 t1 JOIN large_table2 t2 ON t1.name = t2.name;

若 t2.name 无索引，MySQL 会选择 BNL。

---

3. Batched Key Access（BKA）优化

在 MySQL 5.6 引入的 BKA 算法中，BNL 进一步优化为 批量索引访问：

• 将驱动表的一批行（如 100 行）缓存。
• 一次性将这些行的关联键发给存储引擎层。
• 存储引擎批量读取匹配数据，减少随机 I/O。

这种方式能显著提升 JOIN 效率，尤其在 InnoDB + 大量随机访问 场景中表现优异。

开启方式：

SET optimizer_switch = 'batched_key_access=on';

---

4. Hash Join（MySQL 8.0 引入）

MySQL 8.0 引入了 Hash Join 算法，在部分场景替代 Nested Loop。

执行逻辑如下：

1. 选择较小的表作为 构建表（Build Table）。
2. 将构建表的关联键存入哈希表。
3. 扫描另一张表（探测表，Probe Table），对每行根据键查哈希表匹配。

伪代码：

build hash table from small_table;
for row in large_table {
    if hash(row.key) in hash_table {
        output(row);
    }
}

Hash Join 的优势：

• 不依赖索引。
• 在大表 JOIN 场景中性能更好。
• 避免频繁随机 I/O。

⚠️ 目前仅在 EXPLAIN FORMAT=JSON 或特定 SQL Hint 下可确认 Hash Join 是否被使用。

---

四、优化器如何选择驱动表

JOIN 的性能关键之一是 驱动表的选择。驱动表决定了外层循环的次数。

1. 优化器的评估原则

MySQL 优化器会基于以下指标评估：

• 表的行数（rows）。
• 索引选择性（selectivity）。
• 查询条件（WHERE 条件过滤率）。

示例：

SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 25;

优化器会通过表统计信息判断：

• 如果 users 满足条件后只有 100 行，而 orders 有 10 万行 → 则以 users 为驱动表。
• 若反之，则以 orders 为驱动表。

2. 查看优化器决策

使用 EXPLAIN：

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

输出结果：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type       | key  | rows          | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u     | index      | PRIMARY | 100 | Using where |
|  1 | SIMPLE      | o     | ref        | fk_user_id | 100 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到 u 在前，表示它是驱动表。

---

五、JOIN 查询的优化技巧

1. 合理选择驱动表

通常遵循以下原则：

• 小表驱动大表（减少外层循环次数）。
• 高过滤率的表优先作为驱动表。

2. 为关联字段建立索引

在被驱动表的关联字段上建立索引可避免 BNL 出现：

CREATE INDEX idx_user_id ON orders(user_id);

3. 避免在 ON 条件中使用函数

错误示例：

ON DATE(o.created_at) = '2025-11-12'

这会导致索引失效。

正确示例：

ON o.created_at >= '2025-11-12' AND o.created_at < '2025-11-13'

4. 利用 EXPLAIN 和 SHOW PROFILE

分析 SQL 性能瓶颈：

SHOW PROFILE FOR QUERY 1;

可以查看执行阶段时间占比，例如优化器耗时、I/O 等。

---

六、三表及多表 JOIN 执行过程

当涉及三表及以上的 JOIN 时，MySQL 实际会执行多次两表连接。

示例：

SELECT u.name, o.amount, p.name AS product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

MySQL 执行逻辑：

1. 先执行 users JOIN orders，生成中间结果。
2. 再将中间结果与 products 进行 JOIN。

优化器会评估三种可能的连接顺序：

• (users JOIN orders) JOIN products
• (users JOIN products) JOIN orders
• (orders JOIN products) JOIN users

然后选择成本最低的方案。

---

七、实战示例：JOIN 性能对比

假设有两张表：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  amount DECIMAL(10,2),
  INDEX (user_id)
);

测试 SQL：

SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;

分析：

• 若 orders.user_id 有索引 → 使用 Nested Loop Join。
• 若无索引 → 使用 Block Nested Loop Join。

通过 EXPLAIN 可验证差异：

场景	type	key	rows	Extra
有索引	ref	idx_user_id	100	Using index
无索引	ALL	NULL	100000	Using where; Using join buffer (Block Nested Loop)

可以看到，当索引缺失时，MySQL 会自动启用 Join Buffer。

---

八、总结

MySQL 的 JOIN 执行流程可以总结为：

驱动表选择 → 连接算法（NLJ/BNL/BKA/Hash）→ 优化器调度 → 执行器发起请求 → 存储引擎返回结果

性能优化关键在于：

1. 确定正确的驱动表。
2. 为被驱动表建立合理索引。
3. 避免函数、隐式类型转换导致索引失效。
4. 利用 EXPLAIN、SHOW PROFILE 分析执行计划。

当理解 MySQL JOIN 的内部机制后，你会发现很多看似“神秘的慢 SQL”，其实背后都有迹可循。

---