大家好，我是咔咔 不期速成，日拱一卒
通过上期文章知道了在MySQL中存在三种join的算法，分别为NLJ、BNLJ、BNL，总结来说分为索引嵌套循环连接、缓存块嵌套循环连接、粗暴循环连接。
另外还知道了一个新的概念join_buffer，作用就是把关联表的数据全部读入join_buffer中，然后从join_buffer中一行一行的拿数据去被驱动表中查询。由于是在内存中获取数据，因此效率还是会有所提升。
同时在上期文章中遇到了一个陌生的概念hash_join，在上期中没有详细说明，本期会进行详述。
一、Multi-Range Read优化
在介绍本期主题时先来了解一个知识点Multi-Range Read，主要的作用是尽量让顺序读盘，在任何领域只要是有顺序的都会有一定的性能提升。
比如MySQL的索引，现在你应该知道索引天生具有有序性从而避免服务器对数据再次排序和建立临时表的问题。
接下来使用一个案例来实操一下这个优化是怎么做的
创建join_test1、join_test2两张表
CREATE TABLE `join_test1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) unsigned NOT NULL, `b` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `join_test2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) unsigned NOT NULL, `b` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
给两张表添加一些数据，用于案例演示
drop procedure idata; delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=1000)do insert into join_test1 (a,b) values ( 1001-i, i); set i=i+1; end while; set i=1; while(i<=1000000)do insert into join_test2 (a,b) values (i, i); set i=i+1; end while; end;; delimiter ; call idata();
表join_test1的字段a上存在索引的，那么在查询时就会使用该索引。
执行流程大致为获取到字段a所有的值，然后根据a的值一行一行的进行回表到主键索引上获取数据
现在的情况是如果随着a的值递增顺序查询的话，id的值就会变相的为倒叙，虽然看起来是根据主键ID连续倒叙的，但在生产环境下肯定不是连续的，就会造成随机访问，那就肯定会造成性能变差。
为什么说随机访问会影响性能？
MySQL的索引天生具有有序性，同时MySQL也同样借鉴了局部性原理，局部性原理是数据和程序都默认有聚集成群的倾向，在访问到一行数据后，会有极大可能性再次访问到这条数据或这条数据相邻的数据。
现在你应该知道了MySQL在读取数据时并不是只读查询的数据，默认会读取16kb的数据，这个值是根据innodb_page_size决定的。
因此顺序查询是非常快的，是因为不用每次都通过执行器获取数据，而是直接在内存中获取，但若访问变为随机性就会每次通过执行器进行获取数据，所以这才是性能变差的原因。
MRR的作用
说了这么多现在你应该知道了MRR的作用就是把查询变为主键ID的递增查询，对磁盘的读尽可能的接近顺序读，就可以提升性能。
因此，执行语句的执行流程就会变成这样
先根据索a，获取到所有满足条件的数据，并且将主键id的值放入read_rnd_buffer中
在read_rnd_buffer中把id的值进行正序排序
再根据排序后得主键ID值，依次到主键索引上获取数据，并返回结果集
如何开启read_rnd_buffer
read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的，默认值为256kb，但你要知道的是对于MRR的优化在优化器的判断策略中会更倾向于不使用，如果要使用则需要进行配置修改即可。
set optimizer_switch="mrr_cost_based=off"
mrr默认值
read_rnd_buffer存不下怎么办？
回忆下在上期中提到的join_buffer不够用是怎么处理的，会把上次读取的数据从buffer中清空，再放入剩下的数据，在MySQL中对于存储结果集的buffer内存不够情况下大多数都是这么处理的。
使用了read_rnd_buffer后的SQL执行流程就变成了这样
explain的结果显示
注意点
假设现在把查询范围扩大，看一下会有什么变化
可以看到当把范围扩大至接近全表数据时，会不再使用索引a从而进行了全表扫描，也就无法再使用mrr优化了
因此想要使用MRR进行提升性能是基于两个非常重要的点，一个是在索引上进行范围查询，另一个就是必须能使用上索引，当然这个索引要是范围查询的列
