5大模块带你MySQL图解JOIN算法

2022-05-16 17:37:39

前言

大家应该都知道，BNL算法由于查询过程中比较次数较大（两种表数量乘积），非常消耗内存，很明显是有优化空间的，而NLJ算法其实效率还是不错的，是否可以继续优化呢？

本文就以下几个知识点展开讲解：

这里需要了解一个知识点，什么是回表？这里大致解释一下MySQL根据辅助索引查询数据时的过程：

-- a是辅助索引，t表中有字段id,a,b

select * from t where a = 50

上述这个过程就叫做回表，回表最大的弊端是随机访问，比如我们进行一段范围查找 select id,a,b from t where a > 10 and a< 100 ，在实际需求中，我们拿到的主键ID并不一定就是递增的，那在回表的过程中就会产生大量的随机访问，极大的影响性能。

如何解决这种情况呢，这里就引入了MRR算法。

MRR算法的全称是Multi-Range-Read，它是MySQL 5.6引入的新特性，其目的是为了减少磁盘的随机访问，尽量使用顺序读盘。

上面不是说到回表可能导致大量的随机访问，从而影响性能嘛，MRR的做法简单理解就是先给你ID在内存中先排序，保证ID是递增的，然后再去查询，这样就可以尽可能保证，在读取磁盘时是顺序读取，从而提高性能。

比如上面例子中的范围查询a在区间（10,100）的值，可以分解为下面几个步骤：

read_rnd_buffer：由read_rnd_buffer_size 变量定义的内存大小中，默认256K

上面就是MRR的执行过程，它的优势是针对范围查找的语句，可以将大量的主键id排序后，能够保证读取主键索引时是顺序读写，从而提高性能。

我们可以通过设置参数来开启MRR

set optimizer_switch="mrr_cost_based=off"
复制代码

通过执行计划，在Extra中就可以看到，我们已经开启了MRR。

MySQL 5.6引入了Batched Key Access（简称BKA），它是对NLJ算法的一种优化，NLJ算法在上一章我们就已经知道其内部原理了，这里就不再赘述，小伙伴们可以回顾上一篇文章查看。

NLJ算法其实效率还是不错的，但是该算法是通过单值来匹配获取结果，那我们能不能同时传递多个值给t2表来查询呢，如今我们学习完上面的MRR，小伙伴们是不是也有一个想法，可以通过MRR的思想对NLJ进行优化呢？

其实BKA确实是基于MRR算法的，观察下面这幅图，查询的时候把驱动表的数据取出部分放入到join_buffer，如果出现join_buffer放不下，就会进行分段策略，然后再执行MRR算法。

我们可以通过设置参数来开启BAK，前面两个参数用于设置MRR，因为BAK依赖MRR

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
复制代码

上一章我们学完了BNL算法，也知道该算法的缺点，总结起来可归结以下几点：

join过程中需要M*N（M、N为两张表行数）次的对比次数，对于大表这个是相当可怕的。
使用 join 语句多次扫描一个冷表，并且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部，从而造成Buffer Pool的热数据被淘汰，影响内存命中率，这段内容后续章节会详细讲解，这里简单了解即可。

对于上面出现的问题，我们最简单的方法就是在被驱动表上新建索引，但是这种方式并不是对所有情况都适合，比如我们例子中，被驱动表中有千万条数据，同时查询的sql又是低频sql，直接添加索引就非常浪费。

还有一种方式我们可以添加一个临时表，大致过程如下：

整体看来，上面的做法目的都是为了能够使用索引，从而触发BAK算法，用以提升性能。

通过本文，总结起来有以下几点：