MySQL图解JOIN算法,超简单,很详细

·  阅读 643
MySQL图解JOIN算法,超简单,很详细

「这是我参与11月更文挑战的第21天,活动详情查看:2021最后一次更文挑战

前言

在上一篇中,花哥和大家分享了JOIN的初步优化,以及两种对应的算法 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。读完上篇的小伙伴应该都知道,BNL算法由于查询过程中比较次数较大(两种表数量乘积),非常消耗内存,很明显是有优化空间的,而NLJ算法其实效率还是不错的,是否可以继续优化呢?

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

  1. 什么是回表
  2. MRR算法是什么
  3. BKA算法是什么
  4. BNL如何优化
  5. NLJ算法优化

回表是什么

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

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

select * from t where a = 50

image.png

  1. 根据辅助索引 a ,获取到值等于5的数据,因为辅助索引只会存储主键,因此可以获取主键ID=500;
  2. 因为我们sql中需要的结果还包含b字段,所以我们还需要继续第三步;
  3. 遍历右边聚簇索引的B+树,拿到最终数据。

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

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

MRR算法认识

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

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

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

image.png

  1. 根据辅助索引a,查询区间(10,100)的所有id,并放入到内存 read_rnd_buffer中;
  2. 在内存中,将id递增排序;
  3. 根据顺序,到主键索引中查询数据,并返回结果集。

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

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

如何开启MRR

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

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

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

image.png

NLJ算法优化

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

  • NLJ算法

image-20211119152513214.png

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

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

image.png

如何开启BKA

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

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

BNL 算法优化

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

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

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

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

  1. 新建临时表temp;
  2. 将满足条件的数据插入到新表中;
  3. 将链式表temp添加索引;
  4. 使用驱动表和临时表temp进行join操作

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

总结

通过本文,总结起来有以下几点:

  1. 尽量使用BKA算法;
  2. BNL算法效率最低,可以通过添加被驱动表索引来转换为BKA算法;
  3. 根据实际需求,可以考虑临时表方案。
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改