我要评论在mysql日常开发中，join关联查询是高频操作，但相同的业务需求，不同的关联方式可能导致数倍的性能差异。其核心症结在于join算法的选择与执行计划的优化。本文将系统拆解mysql中5种核心关联查询算法，结合实战案例分析适用场景，并总结可落地的优化策略。
一、关联查询的核心算法总览
mysql的关联查询本质是“驱动表”与“被驱动表”的匹配过程，不同算法的差异体现在“如何高效匹配两表数据”。先通过一张表快速掌握各算法的核心逻辑：
| join算法 | 核心原理 | 适用场景 | 关键优势/劣势 |
|---|---|---|---|
| simple nested-loop join | 驱动表每行→被驱动表全表扫描匹配 | 无（mysql未实际采用） | 逻辑简单，扫描行数m*n，效率极低 |
| index nested-loop join | 驱动表每行→通过索引定位被驱动表匹配数据 | 被驱动表关联字段有索引 | 扫描行数少，依赖索引效率 |
| block nested-loop join | 驱动表数据批量写入join_buffer→被驱动表每行与缓冲区数据对比 | mysql 8.0.20前，被驱动表无索引 | 减少全表扫描次数，依赖缓冲区大小 |
| hash join | 驱动表构建哈希表→被驱动表逐行通过哈希函数匹配 | mysql 8.0.20后，被驱动表无索引 | 减少io，比bnl更省资源 |
| batched key access | 驱动表数据批量入join_buffer→mrr接口排序主键→批量匹配被驱动表索引 | 被驱动表有索引，大数据量关联 | 批量处理+顺序io，效率最优 |
二、逐个拆解：5种join算法的原理与实战
2.1 被淘汰的“基础款”：simple nested-loop join
原理
最朴素的关联逻辑：遍历驱动表（数据量m）的每一行，都去被驱动表（数据量n）做全表扫描，满足条件则返回结果。
扫描总行数 = m * n，若两表均为1万行，需扫描1亿次，性能极差。
关键结论
mysql未实际采用该算法——即使被驱动表无索引，也会用block nested-loop join或hash join优化，此算法仅作为理解其他算法的基础。
2.2 索引依赖型：index nested-loop join（nlj）
原理
当被驱动表的关联字段有索引时，mysql优先选择nlj，流程如下：
- 选择“小表”作为驱动表（减少外层循环次数）；
- 遍历驱动表每行，提取关联字段值；
- 通过关联字段的索引，快速定位被驱动表的匹配行；
- 合并两表结果返回。
实战案例
1. 准备测试数据
-- 创建表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;
2. 执行关联查询并分析计划
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算法，核心是“批量匹配减少io”：
- 将驱动表数据批量写入
join_buffer（默认大小256kb，可通过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，核心是“哈希表快速匹配”：
- 将驱动表数据加载到内存，构建“关联字段→行数据”的哈希表；
- 逐行读取被驱动表，通过哈希函数计算关联字段的哈希值；
- 查找哈希表中匹配的哈希值，对比原始数据后返回结果。
实战对比
同上述bnl案例，在mysql 8.0.25中执行：
explain select * from t1 inner join t2 on t1.b = t2.b;
执行计划关键信息：
- extra字段显示“using join buffer (hash join)”，确认使用hash join；
- 无需将被驱动表数据写入磁盘/内存，io次数比bnl更少，性能提升30%+。
关键结论
- hash join是无索引场景下的最优选择，建议将mysql升级至8.0.20+；
- 若驱动表过大，哈希表会溢出到磁盘，需通过
join_buffer_size确保哈希表在内存中。
2.5 性能天花板：batched key access（bka）
原理
bka是nlj的优化版，结合“批量处理”与“顺序io”，需满足被驱动表有索引，流程如下：
- 驱动表数据批量写入
join_buffer； - 批量将关联字段值发送到mrr（multi-range read）接口；
- mrr按主键排序关联字段对应的主键id，减少随机io；
- 按排序后的主键批量读取被驱动表数据，匹配后返回。
如何开启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排序减少随机io，大数据量下比nlj快2-5倍。
三、关联查询优化：4个核心策略
1. 关联字段必须加索引
这是最核心的优化！将“无索引场景”（bnl/hash join）转化为“有索引场景”（nlj/bka），性能提升可达10倍以上。
案例对比：
- 无索引（bnl）：select * from t1 join t2 on t1.b=t2.b，耗时0.08秒；
- 有索引（nlj）：select * from t1 join t2 on t1.a=t2.a，耗时0.01秒。
2. 强制选择小表作为驱动表
当优化器选择错误时（如统计信息过时），用straight_join强制指定小表为驱动表：
-- 强制t2（小表）为驱动表 select * from t2 straight_join t1 on t2.a = t1.a;
3. 大数据量用bka优化
对于百万级以上数据的关联查询，开启bka可大幅减少io次数，尤其适合“驱动表大、被驱动表有索引”的场景。
4. 升级mysql至8.0.20+
用hash join替代bnl，无索引场景下性能提升30%+，同时减少资源占用。
四、总结
mysql关联查询的效率，本质是“算法选择”与“资源利用”的平衡：
- 有索引优先用bka/nlj，核心是“索引+小表驱动”；
- 无索引优先用hash join（8.0.20+），避免bnl的高io；
- 大数据量必开bka，通过批量处理和mrr优化io。
掌握这些算法原理与优化策略，可轻松应对90%以上的mysql关联查询性能问题。
到此这篇关于mysql join关联查询的几种实现方式优化小结的文章就介绍到这了,更多相关mysql join关联查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论