MySQL Join连接算法深入解析-CSDN博客

本文链接：https://blog.csdn.net/weixin_41165867/article/details/147888539

引言

在关系型数据库中，Join操作是实现多表数据关联查询的关键手段，直接影响查询性能和资源消耗。MySQL支持多种Join算法，包括经典的索引嵌套循环连接（Index Nested-Loop Join）、块嵌套循环连接（Block Nested-Loop Join）以及针对大数据量场景优化的批量键访问（Batched Key Access，BKA）和多范围读取（Multi-Range Read，MRR）等先进技术。本文将系统介绍这些Join算法的原理、工作流程及其适用场景，重点解析BKA和MRR如何通过批量化访问和顺序读取优化I/O性能。同时，结合实际案例讲解如何通过临时表和索引优化Join执行过程，最后简要比较MySQL未支持的排序归并连接（Sort-Merge Join）算法，帮助读者全面掌握MySQL多表关联查询的优化策略与实践。

Index Nested-Loop Join（NLJ）

select * from t1 straight_join t2 on (t1.a=t2.a);

从表 t1 中读入一行数据 R；
从数据行 R 中，取出 a 字段到表 t2 里去查找；
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束；

Block Nested-Loop Join（BNL）

select * from t1 straight_join t2 on (t1.a=t2.b);

BNL使用join buffer存储左表数据，左表数据太多会分段与右表进行关联。

把表 t1 的数据读入内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存。
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

Batched Key Access（BKA）

MySQL 在 5.6 版本后开始引入的 Batched Key Access（BKA）算法，BKA 算法就是对 NLJ 算法的优化。

Multi-Range Read（MRR）

MRR 优化的主要目的是尽量使用顺序读盘。

select * from t1 where a>=1 and a<=100;

未使用 MRR 查询思路：

主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。因此，回表肯定是一行行搜索主键索引的。

MRR 优化的设计思路：

根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
将 read_rnd_buffer 中的 id 进行递增排序；
排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回；

将原先的随机回表变成了顺序回表，可以提高回表效率。

如何开启 BKA

如果要使用 BKA 优化算法的话，需要开启相应配置，两个参数的作用是要启用 MRR。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BKA 工作原理

将外部表（左表）中相关的列放入Join Buffer中。
内部表（右表）批量的将Join Buffer中的索引列（索引键值）发送到Multi-Range Read（MRR）接口。
Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。
将读取到的数据和左表的数据组合，得到结果集，最后将结果集返回给客户端。

BNL 转 BKA

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

如果使用 BNL 算法来 join 的话，这个语句的执行流程是这样的：

把表 t1 的所有字段取出来，存入 join_buffer 中。这个表只有 1000 行，join_buffer_size 默认值是 256k，可以完全存入。
扫描表 t2，取出每一行数据跟 join_buffer 中的数据进行对比。
- 如果不满足 t1.b=t2.b，则跳过。
- 如果满足 t1.b=t2.b, 再判断其他条件，也就是是否满足 t2.b 处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过。

临时表 + BKA优化后的执行流程：

把表 t2 中满足条件的数据放在临时表 tmp_t 中；
为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；
让表 t1 和 tmp_t 做 join 操作；

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);