深入理解MySQL Join优化：MRR与BKA机制详解

Aesthetis
173 阅读5分钟

前言

我们了解了join语句有三种模式，分别是

  1. 索引Index匹配：Nested Loop Join
  2. 简单join匹配：Simple Nested Loop Join
  3. Block阻塞匹配：Block Nested Loop Join

让我们看看join内部的优化算法是什么样的，可以帮助我们更好的理解优化join本身

MRR回表优化

join的优化是基于回表优化的基础上升级的，我们把按索引顺序读取数据，称为MRR优化，让我们详细了解，它是怎么优化的？

mysql回表优化 Multi-Range Read(MRR) 优化机制

简单概括join语句的过程：

  1. 计算驱动表和被驱动表的大小，选择驱动表
  2. 将驱动表 通过索引查询找到主键，再根据主键回表，把整行数据存储到内存中
  3. ...

而MRR优化，就是用来优化第2步的.

第二步，通过索引再查找到一堆主键集合，再通过这个集合回表查询全数据项。

我们会发现，按普通索引查询到的主键集合，其id是无序、离散的（即不连续）。

无序会导致，没有办法对磁盘进行顺序读取。

不连续导致，没办法进行范围查询，只能进行等值查询。

无序性是可以被解决的，通过对主键集合排序，我们可以利用磁盘的顺序性，提升查找效率。

便于理解：这里扩展下磁盘的知识，磁盘访问耗时最高的部分，就是磁盘寻道，即磁盘驱动器移动其磁头到包含所请求数据的磁道上。如果数据都在一块，那磁头只需要顺序去读；如果不在一块，磁头就需要反复移动

比如 从数据1 -> 3 -> 5 -> 2(走7步)，比从数据1 -> 2 -> 3 -> 5(走4步) 要多 3步花费 。

而对主键集合进行排序后，再统一回表，保证磁盘的顺序访问，我们把这个过程称为MRR优化。

所以MRR的本质，其实就是利用了索引数据的顺序性，完成了磁盘寻道优化.

image.png

Index Nested Loop Join(NLJ)算法

索引匹配，会从驱动表，一行行地取出 a ，再到被驱动表一行行做 join。相当于先读驱动表，再读被驱动表，再读驱动表... 所以导致了MRR优化失效

Batched Key Access(BKA优化)

为了利用MRR的优化，需要join匹配时，驱动表一次输入多行数据，。被驱动表一次根据多条数据取值，就可以利用MRR的机制，加速磁盘访问了。BKA为了满足MRR，开辟了一块空间，单独存储多行驱动表数据，我们把这部分空间叫join buffer。

image.png

Block Nested Loop Join(BNL)算法的问题

回顾Block Nested Loop Join，我们需要先把驱动表的数据，一行一行读入到Innodb内存中，也就是另一篇文章里的Buffer Pool，详细了解可参考： juejin.cn/post/748147…

而之前我们了解过，Buffer Pool 本身LRU改进算法维护的，本质是，把访问时间久的数据页交换到队首，提高了缓存命中率，减少磁盘访问的成本。

但如果被驱动表是个大表，其访问的数据页时间久(超过1s以上)，导致LRU的策略失效，缓存命中率下降 。

治标不治本的做法是，调大join_buffer的空间，避免频繁扫描被驱动表。

BNL 算法对系统的影响主要包括三个方面：

  1. 可能会多次扫描被驱动表，占用磁盘 IO 资源；
  2. 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
  3. 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

实际场景中，可以用explain语句，判断是否使用BNL算法。

BNL转BKA优化算法

容易想到，BNL已经把驱动表加载到Buffer Pool中，相当于也是一块join buffer。这时只需要在被驱动表上也建立索引，就可以使用MRR优化了。

如果实在没有索引怎么办？join-SQL优化

举例

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

t1表，数据量 1000

t2表，数据量 100w

  • 当被驱动表没有索引，同时被驱动表又是大表，待扫描的数据集又很小时：为了使用刚才的BKA优化算法，我们可以想办法加上索引。可以在被驱动表上创建个临时表，给临时表加上索引，就可以避免join时，多次扫描。

对应SQL可以改为：

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);

业务服务自己实现hash-join，不用mysql的join

分别执行两个SQL，取出t1和t2的数据，在业务代码里用hash匹配

理论上，这个过程会比临时表方案的执行速度还快

总结

  1. MRR优化：通过将无序的主键集合排序，利用磁盘的顺序读取特性，减少磁盘寻道时间，从而提升回表查询的效率。
  2. BKA优化：为了利用MRR优化，BKA算法引入了join buffer，一次性处理多行驱动表数据，减少了对被驱动表的多次访问，进一步提升了Join操作的性能。
  3. BNL算法的问题：Block Nested Loop Join在处理大表时，可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。通过调整join_buffer大小或将被驱动表的数据加载到临时表中并建立索引，可以缓解这一问题。
  4. BNL转BKA优化：当被驱动表没有索引时，可以通过创建临时表并为其建立索引，将BNL算法转换为BKA算法，从而利用MRR优化提升性能。
  5. 业务层优化：在某些情况下，业务层可以通过Hash Join的方式，手动实现Join操作，避免MySQL的Join操作带来的性能瓶颈。
avatar
文章
阅读
粉丝