MySQL 面试系列：MySQL join NLJ、BNLJ 、

通过 上文文章 知道了在 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 的递增查询，对磁盘的读尽可能的接近顺序读，就可以提升性能。

因此，执行语句的执行流程就会变成这样