MySQL Nested Loop Join

Nested Loop Join分为 Index Nested Loop JOIN 和 Block Nested Loop Join两种

INJ全称Index Nested Loop JOIN

将 “驱动表/外部表” 的结果集作为循环基础数据，然后循环该结果集，每次获取一条数据作为下一个表的过滤条件查询数据，然后合并结果，获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内层循环便要执行多少次，效率非常差。

一. 被驱动表有可用索引的情况:
如果内层循环(被驱动表)利用到了索引，可以视为一种新的算法Index Nested_Loop JOIN，简称为  INJ 。

示例(t1有100条记录,t2有1000条记录)：
1. 执行select * from t1，查出表 t1 的所有数据，这里有 100 行；
2. 循环遍历这 100 行数据：
2.1 从每一行 R 取出字段 a 的值 $R.a；
2.2 执行select * from t2 where a=$R.a；需要检查t2中利用到了索引
2.3 把返回的结果和 R 构成结果集的一行。

在这个查询过程，也是扫描了 200 行，但是总共执行了 101 条语句。

二. 被驱动表无可用索引的情况：

BNL全称Block Nested-Loop

使用原理

1. 将外层循环的行的结果集存入join buffer
2. 内层循环读取被驱动表的每一行，并跟join buffer中的记录作比较，满足条件的行加入结果集，
   用于被驱动表上无索引情况

如果join的两个表数据过大，join_buffer 放不下怎么办呢？ join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。

执行过程就变成了：
1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取最后的一批行数据放入 join_buffer 中，继续执行第 2 步。

可以看出对表被驱动表t2扫描次数是由Block的数量决定的，有几个Block就需要全表扫描被驱动表t2几次。

这时，内存判断次数是不受选择哪个表作为驱动表影响的。但被驱动表的全表扫描次数受Block的块数影响，所以驱动表数量越少分块的次数也小，相应被驱动表的扫描次数也随之减少。

所以结论是，应该让小表当驱动表。