在日常的 MySQL 数据库开发中，JOIN 关联查询几乎是无法避免的操作。针对同一个业务需求，即使查询写法略有不同，执行效率也可能相差数倍。决定查询性能的关键因素，归根结底在于 JOIN 算法的选择 以及 执行计划的走向。下面我们将逐一拆解 MySQL 中 5 种核心的关联算法，结合真实案例解析各自适用场景，并给出可直接落地的优化策略。

一、关联查询核心算法总览

从本质上看，MySQL 执行关联查询就是“驱动表”逐行或批量去“匹配”“被驱动表”的过程。不同算法的核心差异集中在“匹配方式”上。先通过下表快速掌握各算法的原理与适用条件：

二、逐个拆解：5 种 JOIN 算法的原理与实战

2.1 被淘汰的“基础款”：Simple Nested-Loop Join

原理

这是最朴素的关联逻辑：遍历驱动表（假设有 m 行数据）的每一行，然后对被驱动表（数据量 n）执行一次全表扫描。如果条件匹配则返回，否则继续扫描。在此算法下，扫描总行数 = m × n。假如两张表各有 1 万行，就需要扫描 1 亿次，性能可想而知。

关键结论

MySQL 实际上 从未采用 过该算法。即便被驱动表没有任何索引，MySQL 也会自动选择 Block Nested-Loop Join 或 Hash Join 进行优化。之所以提及它，主要是为了帮助理解其他优化算法的基础逻辑。

2.2 索引依赖型：Index Nested-Loop Join（NLJ）

原理

当被驱动表的关联字段 建有索引 时，MySQL 会优先选用 NLJ。流程大致为：先选择小表作为驱动表，然后遍历驱动表的每一行，取出关联字段的值，通过索引快速在被驱动表中定位匹配行，最后合并结果返回。

实战案例

首先准备测试数据：

-- 创建表 t1（1 万行）和 t2（100 行，小表）
use martin;
drop table if exists t1;
CREATE TABLE `t1` (
  `id` int NOT NULL auto_increment,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) -- 关联字段 a 建索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入 1 万行数据
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)
do
insert into t1(a,b) values(i, i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();

-- 复制 t1 为 t2，仅保留 100 行（小表）
drop table if exists t2;
create table t2 like t1;
insert into t2 select * from t1 limit 100;

接着执行关联查询并分析执行计划：

explain select * from t1 inner join t2 on t1.a = t2.a;

执行计划关键信息：

• 驱动表是 t2（小表，explain 第一行），被驱动表是 t1；
• Extra 字段未出现“Using join buffer”，说明使用了 NLJ 算法；
• 被驱动表通过 idx_a 索引匹配，扫描行数极少。

关键结论

• NLJ 的效率核心在于被驱动表的索引，无索引则无法使用；
• 驱动表选择“小表”可减少外层循环次数，优化器通常会自动选择小表（也可通过 straight_join 手动指定）。

2.3 无索引方案 1：Block Nested-Loop Join（BNL）

原理

当被驱动表没有索引，且 MySQL 版本 ≤ 8.0.19 时，BNL 会被启用。其核心思路是“批量匹配，减少 I/O”。具体做法：将驱动表的数据分批写入 join_buffer（默认 256 KB，可通过 join_buffer_size 调整），然后遍历被驱动表的每一行，与 join_buffer 中所有驱动表数据逐一对比，符合条件的再返回。

实战案例

-- 关联字段 b 无索引（t1、t2 的 b 字段均未建索引）
explain select * from t1 inner join t2 on t1.b = t2.b;

MySQL 5.7 执行计划关键信息：

• Extra 字段显示“Using join buffer (Block Nested Loop)”，确认使用了 BNL；
• 扫描行数 = 驱动表行数 + 被驱动表行数（批量匹配减少了全表扫描次数）。

关键结论

• BNL 比 Simple Nested-Loop Join 效率高很多，但仍需扫描被驱动表的全表；
• 如果 join_buffer_size 设置过小，驱动表需要多次写入缓冲区，被驱动表也会被多次全表扫描，因此需合理调整缓冲区大小。

2.4 无索引方案 2：Hash Join（MySQL 8.0.20+）

原理

从 MySQL 8.0.20 开始，官方用 Hash Join 替代了 BNL。其核心是“哈希表快速匹配”：先将驱动表的数据加载到内存，构建一张“关联字段 → 行数据”的哈希表；然后逐行读取被驱动表，通过哈希函数快速找到匹配项，对比原始数据后返回结果。

实战对比

在 MySQL 8.0.25 中执行同样的 BNL 案例：

explain select * from t1 inner join t2 on t1.b = t2.b;

执行计划关键信息：

• Extra 字段显示“Using join buffer (hash join)”，确认使用了 Hash Join；
• 无需将被驱动表的数据写入磁盘或额外内存，I/O 次数比 BNL 更少，性能通常提升 30% 以上。

关键结论

• Hash Join 是无索引场景下的最优选择，推荐将 MySQL 升级至 8.0.20 或更高版本；
• 若驱动表过大，哈希表可能溢出到磁盘，此时需依靠 join_buffer_size 确保哈希表驻留内存。

2.5 性能天花板：Batched Key Access（BKA）

原理

BKA 可视为 NLJ 的增强版，融合了“批量处理”与“顺序 I/O”两个理念，前提是被驱动表拥有索引。流程如下：先将驱动表的数据批量写入 join_buffer，然后批量将关联字段的值传递给 MRR（Multi-Range Read）接口，MRR 按主键排序后，再按排序后的主键批量读取被驱动表的数据，匹配后返回。这样就将随机 I/O 转化为顺序 I/O，效率自然更高。

如何开启 BKA

BKA 需要手动开启 MRR 相关参数：

-- 开启 MRR 和 BKA
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
-- 验证 BKA 是否生效
explain select * from t1 inner join t2 on t1.a = t2.a;

执行计划关键信息：

• Extra 字段显示“Using join buffer (Batched Key Access)”，确认 BKA 已生效；
• 批量处理减少了索引查询次数，MRR 排序降低了随机 I/O，在大数据量下比 NLJ 快 2 到 5 倍。

三、关联查询优化：4 个核心策略

1. 关联字段必须加索引

这是最核心的优化点，没有之一。将“无索引场景”（BNL / Hash Join）转变为“有索引场景”（NLJ / BKA），性能提升轻松超过 10 倍。

案例对比：1 万行数据，无索引用 BNL 耗时 0.08 秒，有索引用 NLJ 仅需 0.01 秒。

2. 强制选择小表作为驱动表

若优化器因统计信息过时等原因选错驱动表，可使用 straight_join 手动指定：

-- 强制 t2（小表）为驱动表
select * from t2 straight_join t1 on t2.a = t1.a;

3. 大数据量用 BKA 优化

对于百万级以上的数据关联查询，开启 BKA 能大幅降低 I/O 次数。尤其在“驱动表大、被驱动表有索引”的场景下，效果极为显著。

4. 升级 MySQL 至 8.0.20+

使用 Hash Join 替代 BNL，在无索引场景下性能提升 30% 以上，资源占用也更低，是值得推荐的长期策略。

四、总结

MySQL 关联查询的效率，本质上就是“算法选择”与“资源利用”之间的平衡：

• 有索引时，优先考虑 BKA / NLJ，核心是“索引 + 小表驱动”；
• 无索引时，优先使用 Hash Join（MySQL 8.0.20+），极力避免 BNL 的高 I/O 开销；
• 大数据量场景务必开启 BKA，借助批量处理与 MRR 优化 I/O。

吃透这几种算法的原理及对应优化策略，应对 90% 以上的 MySQL 关联查询性能问题，基本不在话下。