写在前面
在《刨根问底拦不住的MySQL系列之Join》中，我们聊到了JOIN的连接原理。一种是基于索引JOIN的Index Neste-Loop Join常常被我们称为NLJ，另外一种是连接字段没有进行索引的JOIN，这种JOIN使用的Block Nested-Loop Join,常常被我们称为BNL。同时，我们也在前面留下了两个坑MRR和BKA,那么这倆到底是什么意思？同时，针对于这样两种JOIN方式，MySQL有着不同的优化策略，让我们一层层的揭开这个面纱。
BKA的提速&MRR的巧妙回表
我们先回顾一下NLJ的流程，现在有t1和t2两张表，idx是两张表的连接字段，并且都进行了索引。
select * from t1 straight_join t2 on t1.idx = t2.idx;
复制代码之前，我们聊到NLJ的流程：
第一步：从表t1读出一行数据ROW
第二步：从数据行中取出字段idx到表t2中去查找
第三步：在t2中筛选出满足条件的行，跟t1读出的数据ROW组装，构成结果集的一行
重复步骤一、二、三步，直到t1全部查完
BKA
这里有第一个优化点，在第一步中，从表t1读出一行数据ROW，在MYSQL5.6的时候，启用了BKA（Batched Key Access）算法，批量数据查询，由原先的每次读取一行数据变为查询出一批数据，去被驱动表进行匹配。
这种优化的必须要解决的问题就是一次查出的数据放在哪里，MYSQL单独开辟了一个内存，你一定听过，就是join_buffer。这样就能够，缓存住批量的数据，减少t1的读取频率。原先的JOIN流程就变成了：
MRR
如果，我们仔细想想，在JOIN的整个流程里面，匹配的过程是索引上的B+Tree查找，这部分很难有更好的优化空间了，那么剩下的就是在回表上做文章了。
首先想到的肯定是覆盖索引，但是现实中并没有那么简单的情况能够让我不回表，就把数据查出来。所以此时需要考虑的是顺序读写的问题。一般而言，插入都是按照主键递增的顺序来插入的，这就让磁盘的读取按页读取能够有优化的空间。
众所周知，在上述流程中，一般索引检索到匹配的值时候，会再去主键索引找到具体的数据，这个过程被我们称为回表。如果回表的扫描过程中，是顺序的，那么依据磁盘的读写特性，将大大的提高效率，这也是第二个优化点Multi-Range Read（MRR）。
MRR在一般索引匹配到主键索引的时候，单独开辟了一个空间read_rnd_buffer对主键索引先进行排序，使得主键索引查找顺序的时候，是尽可能的顺序查找。MRR的加入使得原先的JOIN流程变成了：
几点说明
- 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的内存淘汰策略，欢迎关注催更。