MySQL的join系列二--join的优化

博主是一个还在实习阶段的小菜鸟，别问我为啥显示码龄3年，我也不知道。有啥不对或者不完整的地方请各位大佬指正，非常感谢，给各位大佬端茶了。

上一回的文章链接：唠唠MySQL的join

我又来了，上一回我们说到MySQL中是否能用join，为什么要选择小表驱动大表？在上一回合中提到了join的NLJ和BNL两种算法。稍微来回顾一下咯，NLJ就是走被驱动表的索引，这样比强拆成多个单表执行性能更好一点；BNL就是不走被驱动表的索引，利用join_buff先把数据放进来比对，比对的次数就是表参加join的行数的乘积，这样一旦表的数据量一大，其实还是挺占用CPU的资源的；那么NJL和BNL都还有优化的空间嘛？有的，而且这个目前MySQL也已经实现了的，MySQL流批，yyds。说到这两个的优化就得先说一下MRR。这玩意是啥呢？且容我喝口水再慢慢道来。
MRR，全称：Multi-Range Read
还是上回文章的两张表，我们先来看个例子：

select * from t1 where a>=1 and a<=50;

这条sql的执行过程就不需要多说了：先去a索引树上面查找符合的节点，然后拿出节点里面的主键id，再去主键索引树上面查找对应的数据行放入结果集中，然后返回。在去主键索引树上面查找的时候，每次只能根据一个主键id查到一行数据。因此，回表肯定是一行行搜索主键索引的，这里面涉及了回表上一回也说过了呀。我们在按a的值去查找的时候，主键的值肯定是随机的。但是，数据库中大部分都是按照主键的值递增插入的，那么我们就可以认为，如果按照主键值去顺序查询，对磁盘的读就比较接近顺序读，这样肯定比随机读的性能好，这就是MRR的设计思路。MRR优化后的执行流程图（流程图的地址）：

MRR的原理大概是：
1，将二级索引找到的记录放到一块缓冲区，也就是图中的read_md_buff；
2，二级索引扫描的二级索引树文件的末尾或缓冲区满了，对缓冲区里面的数据进行顺序排序；
3，去缓冲区里面拿数据去主键索引树上面查找数据；
4，但缓冲区的数据读完后重复上面的步骤；
以上是针对InnoDB来说的，对于MyISAM来说也是大同小异。
MRR的优点如下：
1，将随机IO变成了顺序IO，顺序读可以让磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读
3，每一页数据只会从磁盘读一次
索引本身的作用就是为了减少磁盘IO从而加快查询的速度，MRR放大了索引这个减少磁盘IO的作用，从而再次提高查询的速度。打开MRR的设置：

set optimizer_switch='mrr=on' #打开MRR
 set optimizer_switch="mrr_cost_based=off" #告诉优化器固定使用MRR

唠磕完了MRR，我们接下来就说下NLJ和BNL的优化。这两种算法的优化都是需要想办法触发Batched Key Acess算法，MySQL5.6之后推出的一个算法，简称BKA。这个BKA算法只要理解了MRR的思路，那么也就理解了它。因为BKA算法的本质是通过MRR接口将非主键索引对于记录的访问，转化为根据ROWID排序的较为有序的记录获取。
现在来说说NLJ的优化，NLJ的执行流程博主在上一回中说到了：从驱动表（t1）中一行行读出Join的字段(a)的值，然后去被驱动表（t2）中进行匹配，因为t2的a字段是有建索引的，但是因为在二级索引上面读不到整行数据的值，所以就需要回表去主键索引上面读取整一行数据。那么在t1拿数据到t2进行匹配的过程中是一行行的来的，这样就没办法利用到MRR。那么我们就可以先把t1参加join的字段a的值先收集一部分，然后再批量去t2匹配，这不就利用到了MRR了嘛。那么它的优化后流程如下图：

综上所述：BKA算法其实本质上还是NLJ算法，但是因为利用了MRR的一个设计再次加快了查询的速度。
如果需要使用BKA算法：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

接下来我们说说BNL的优化：
要优化肯定是有缺点的，BNL对系统的性能影响如下:
1，可能会多次扫描被驱动表，占用磁盘IO资源；
2，判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常 多的CPU资源；
3，可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。（这个涉及到InnoDB对LRU算法的优化机制，我在下一回再和各位看官细说）
那么BNL怎么优化呢？其实很简单，在被驱动表的字段上面加上索引它就变成NLJ算法了，再加上个MRR就变成BKA算法了。但是有些时候是不合适建索引的。比如以下sql语句：

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

在上一回中，我们t1,t2两张表的数据分别是100，1000.我们现在扩大一下：1000，100万。如果我们给t2的b字段加索引，再开启BKA，它的执行速度确实加快了。但是如果这条SQL是条低频sql，b字段也是冷字段，那么这时候建索引其实是不合适的。但如果不建立吧，BNL算法在Join_buff中判断的次数为：1000 * 100万，这个次数可不小。那么我们还有没有别的方法呢？有，建临时表。大概思路如下：
1，建立临时表，把t2过滤后的数据放入临时表中
2，给临时表的b字段加上索引，开启BKA
3，让t1 join 这个临时表
命令如下：

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<=1000); 
select * from t1 join temp_t on (t1.b=temp_t.b);

这条语句执行过程中，insert的时候全表扫描t2，100万行；全表扫描t1，1000行；join比较过程中，走索引扫描临时表1000次；比较 1000 * 100万的判断次数性能肯定更好。各位看官也可以去试一下两种做法的时间。
综上所述：BNL的算法的优化可以通过建索引或者临时表来触发BKA算法，从而加快查询的速度。当然了，这个过程中肯定是利用空间换时间嘛，至于需不需要这种做法就看各位看官是想要空间还是时间了。

本文依然是博主这个小菜鸡在学习过程中的总结，借鉴了以下大佬们：
知乎大佬：https://zhuanlan.zhihu.com/p/110154066
极客林晓斌老师：https://time.geekbang.org/column/intro/100020801
MySQL官网：https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html?spm=a2c6h.12873639.0.0.345d1e6aJ7HMoP
MRR官网说明：https://mariadb.com/kb/en/multi-range-read-optimization/