救命!JOIN到底要怎么优化?

253 阅读4分钟

写在前面

《刨根问底拦不住的MySQL系列之Join》中,我们聊到了JOIN的连接原理。一种是基于索引JOIN的Index Neste-Loop Join常常被我们称为NLJ,另外一种是连接字段没有进行索引的JOIN,这种JOIN使用的Block Nested-Loop Join,常常被我们称为BNL。同时,我们也在前面留下了两个坑MRRBKA,那么这倆到底是什么意思?同时,针对于这样两种JOIN方式,MySQL有着不同的优化策略,让我们一层层的揭开这个面纱。

BKA的提速&MRR的巧妙回表

我们先回顾一下NLJ的流程,现在有t1和t2两张表,idx是两张表的连接字段,并且都进行了索引。

select * from t1 straight_join t2 on t1.idx = t2.idx;

Index Neste-Loop Join.png

之前,我们聊到NLJ的流程:

第一步:从表t1读出一行数据ROW

第二步:从数据行中取出字段idx到表t2中去查找

第三步:在t2中筛选出满足条件的行,跟t1读出的数据ROW组装,构成结果集的一行

重复步骤一、二、三步,直到t1全部查完

BKA

这里有第一个优化点,在第一步中,从表t1读出一行数据ROW,在MYSQL5.6的时候,启用了BKA(Batched Key Access)算法,批量数据查询,由原先的每次读取一行数据变为查询出一批数据,去被驱动表进行匹配

这种优化的必须要解决的问题就是一次查出的数据放在哪里,MYSQL单独开辟了一个内存,你一定听过,就是join_buffer。这样就能够,缓存住批量的数据,减少t1的读取频率。原先的JOIN流程就变成了:

BKA_JOIN.png

MRR

如果,我们仔细想想,在JOIN的整个流程里面,匹配的过程是索引上的B+Tree查找,这部分很难有更好的优化空间了,那么剩下的就是在回表上做文章了。

首先想到的肯定是覆盖索引,但是现实中并没有那么简单的情况能够让我不回表,就把数据查出来。所以此时需要考虑的是顺序读写的问题。一般而言,插入都是按照主键递增的顺序来插入的,这就让磁盘的读取按页读取能够有优化的空间。

众所周知,在上述流程中,一般索引检索到匹配的值时候,会再去主键索引找到具体的数据,这个过程被我们称为回表。如果回表的扫描过程中,是顺序的,那么依据磁盘的读写特性,将大大的提高效率,这也是第二个优化点Multi-Range Read(MRR)。

MRR在一般索引匹配到主键索引的时候,单独开辟了一个空间read_rnd_buffer对主键索引先进行排序,使得主键索引查找顺序的时候,是尽可能的顺序查找。MRR的加入使得原先的JOIN流程变成了:

MRR.png

几点说明

  • MRR的read_rnd_buffer的大小是由参数read_rnd_buffer_size控制的
  • MRR默认是关闭的,MYSQL优化器更加倾向于不使用MRR,因此set optimizer_switch="mrr_cost_based=off"开启MRR
  • BKA算法的主要性能优化是基于MRR的,因此set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';是开启BKA的正确姿势
  • MRR的优化前提是BKA,也就是说有多主键的范围查询

BNL的优化策略

假设,你对MySQL内存页淘汰算法LRU滚瓜烂熟,当然,如果你不熟的话,可以等下期的淘汰策略,基于此分析一下BNL的一些性能瓶颈。

由于join_buffer的大小限制,会导致我们再访问某些冷表的时候,大量的访问频率,把冷表打满Buffer Pool内存中,内存中的Buffer Pool大小是有限的,如果不够了,那么将淘汰热表的一些内存页,这种情况下,从而影响了内存命中率,可能使得我们的业务系统卡顿。

BNL转NLJ或者BKA

由于BNL并不会走索引,从而使得在匹配的过程中,消耗着大量的CPU,因此最好的处理方式就是对连接的被驱动表设置索引,从而转成NLJ,通过BKA的优化进行查询。

就如同,覆盖索引很少能够满足业务场景,所以对于在原被驱动表中添加索引,是很难覆盖所有的场景。

因此,我们很大程度可以使用临时表代替,create temporary table xxxx,把满足条件的数据插入到临时表中,对临时表的连接字段加上索引,然后让驱动表和临时表进行join查询。

最后

至此,对于MySQL的JOIN部分的原理、优化初步的聊完了,如果你有任何问题,或者想了解MySQL的内存淘汰策略,欢迎关注催更。