CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a` (`a`)
) ENGINE=InnoDB;

在表 t2 中插入数据 1000 条数据
for(int i=1; i<=1000; i++) {
    insert into t2(id, a, b) values(i, i, i);
}

创建表 t1，并插入 100 条记录
create table t1 like t2;
insert into t1 (select * from t2 where id<=100);

说明：如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。

Index Nested-Loop Join(NLJ)（扫描被驱动表用到索引）

执行sql

select * from t1 straight_join t2 on (t1.a=t2.a)

由于使用了 straight_join 表 t1 是驱动表，t2是被驱动表

通过 explain 查看执行计划

分析执行计划

对 t1 表做了全表扫描，扫描行数为 100 行

扫描 t2 表是时，使用到了索引“index_a”，执行计划中的 ref 显示为 “lp.t1.a” 说明，扫描 t2 表二级索引使用的值是 t1 表的字段 a 的值。

通过分析执行计划，可得出该语句的执行流程如下。

从表 t1 中读入一行数据 R；
从数据行 R 中，取出 a 字段到表 t2 里去查找符合条件的记录（扫描二级索引获取主键id，然后回表）；
将步骤2中的记录跟 R 组成一行，作为结果集的一部分；
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

由于扫描被驱动表的时候，使用了被驱动表的索引，所以我们称之为“Index Nested-Loop Join” 简称“NLJ”

我们再来看一下，这个 sql 语句的执行，一共扫描了多少行，

对表 t1 做了全表扫描，也就是扫了 100 行。

在对表 t2 进行扫描的时候由于走了索引 t2.a 所以也扫描了 100行（回表次数为 100 次）。那么总的扫描行数是 200 行。

Block Nested-Loop Join(BNL)（扫描被驱动表未用到索引）

假设 join bufer 足够大

执行sql

select * from t1 straight_join t2 on (t1.a=t2.b)

由于使用了 straight_join 表 t1 是驱动表，t2是被驱动表

explain 查看执行计划

分析执行计划

对表 t1 做了全表扫描，扫描行数为 100 行

对表 t2 做了全表扫描，Extra 内容包含“Using join buffer (Block Nested Loop)”，说明使用了 join buffer

通过分析执行计划，可得出该语句的执行流程如下。

对表 t1 进行全表扫描，并把数据放入 join_buffer（由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存）
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分响应客户端。

我们再来看一下，这个 sql 语句的执行，一共扫描了多少行，

对表 t1 做了全表扫描，扫描行数 100 行。
对表 t1 做了全表扫描，扫描行数 1000 行。
那么总的扫描行数是 1100 行。

实际中 join buffer 是有限制的

如果驱动表 t1 是个大表，join buffer 放不下，t1 表的数据怎么办。

mysql的处理方法也是简单粗暴，如果放不下，就是将驱动表的数据分段放。执行流程如下

扫描表 t1，将数据让如 join buffer 中，等放到第 88 行 join buffer man了，就继续执行第2步
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分响应客户端。
清空 join buffer
继续扫描 t1，将数据放入 join buffer中，继续执行第2步，直到表 t1 的数据扫描完为止。

我们看下，join buffer 不够大时，一共扫描了多少行。这里我们假设分 t1 表的数据分三次放入 join buffer

对表 t1 做了全表扫描，扫描行数 100 行。
表 t1 的数据分三次放入join buffer，每次都对表 t2 做了全表扫描，扫描行数就是 3*1000=3000
那么总的扫描行数是 3100 行。

如何选择驱动表

走索引的情况

假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 index_a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M（乘以2是因为搜索一次就要回表一次）。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是 N + N*2*log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

不走索引的情况

假设驱动表的行数是 N，被驱动表的行数是 M， join buffer 每次执行放入 H 条记录。

那么扫描行数就是N + (N/H + 1) * M，驱动表行数越少，扫描的总行数就越少，因此应该让小表来做驱动表。

文中“小表”的含义

小表不一定表的总记录数少的表

举例1

比如 select * from t1 join t2 on (t1.b=t2.b) where t2.id <= 10（这里举的例子是没有被驱动表未使用索引）

会将 t2 作为驱动表，因为只需要将 t2 中的 10 条数据放入 join buffer 中即可，如果将 t1 作为驱动表就需要放入 100 条数据到 join buffer中。

举例2

select t1.b, t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;（与 select t1.b,t2.* from t1 join t2 on (t1.b=t2.b) where t2.id<=100 等价）

select t1.b, t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;

表 t1 和 t2 都是只有 100 行参加 join。但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的：

表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，则 join_buffer 中只需要放入 b 的值；
表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字段 id、a 和 b。

所以应该选择 t1 作为驱动表

举例3

例子2中，以下两条sql是等价的，都是选择 t1 作为驱动表

select t1.b, t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;

select t1.b,t2.* from t1 join t2 on (t1.b=t2.b) where t2.id<=100；

那么与sql A “select t1.b,t2.* from t2 join t1 on (t1.b=t2.b) where t2.id<=100；”是否是等价的呢。

答案是，不等价的，sql A，选择是将 t2 作为驱动表，根据举例2中的说明，需要将 t2 中的三个字段都放入 join buffer 中。所以在做表 join 的时候需要将作为驱动表的表名现在 “join” 的前面

结论

哪张表作为驱动表，不是看哪张表记录少，也不是看哪张表放入 Join buffer 的记录少。

而是看哪张表放入join buffer 中的数据量少，哪张表就是驱动表

Multi-Range Read（MRR）

MRR 的优化思路是，由于大多数情况下数据都是按照主键递增顺序插入得到的，所以可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

做实验前，先打开 MRR，mysql默认是关闭 MRR的。如果需要使用MRR 需设置 set optimizer_switch="mrr_cost_based=off"。

我们来看下使用了MRR后，sql的执行流程，字段 a 是非唯一二级索引

select * from t where a > 100 and a < 200

根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
将 read_rnd_buffer 中的 id 进行递增排序；
排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

我们看下sql的执行计划

可以看到 Extra 字段出现了“Using MRR”

Batched Key Access（BKA）

BKA 算法是对 NLJ 算法的优化，其实就是在 NLJ 的基础上使用了 MRR

我们看sql select * from t1 straight_join t2 on (t1.a=t2.a) 的执行过程

对表t1进行全表扫描，并将数据放入 Join buffer 中
对 join buffer 中的数据，根据主键 id 进行排序
使用 t1.a 字段的值去，扫描 t2.a 二级索引（这里可以理解为 select * from t2 where t2.a in (1,2,3,4,5······)），并使用 MRR 优化
将符合条件的记录发送给客户端