mysql5.7 中Nested Loop Join和 Block Nested-Loop Join对比
在mysql5.7之前两个表join的方式只有迭代循环，即Nested loop join，在实际实现中有3中算法
1. Simple Nested loop join :SNLP 简单嵌套循环
2. Index Nested loop join :INLP 简单嵌套循环
3. Block Nested-Loop Join:BNLJ 缓存块嵌套循环连接
在嵌套循环中，一般将小表作为外层表，也成驱动表，大表作为内部表，或被驱动表，在实际工作中就是用小表的数据去大表中进行查询并返回所需数据。
Simple Nested loop join :SNLP 简单嵌套循环
该嵌套查询类似于for循环，简单粗暴，将两个表的数据进行笛卡尔即操作，假设两个表各100条记录，那需要100*100=10000次对比，效率非常地下，MySQL在该算法基础上继续优化出 INLP和BNLP, 下图是 SNLP的说明，我们看到table1是外表（驱动表），我们先从table1中获取记录r1，然后再到tables2中全表变量去匹配，目前mysql中已不再使用该低效方法
Index Nested-LoopJoin（减少内层表数据的匹配次数）
我们在SNLP基础上进行优化，如果在table2上添加一个索引，那么从table1中来的数据到table2进行匹配的时候就不需要全表扫描了，只需要走索引获取相应的匹配即可，这就大大提高了迭代效率，我们看下图
INLP 使用前提为内表关联字段上有索引
但是我们看上图有个书签跳转的情况，即从table2 index 获取相应的行之后还要跳转到聚集索引（主键）上去获取其他列数据，这样就会导致了额外的io，这种情况我们可以通过创建覆盖索引进行解决，即table2 index 包含了所需所有数据，这样上面的图就变成了
Block Nested-Loop Join 减少内层表数据的循环次数
正如我们上面所说的当内表中关联键有索引情况下mysql会使用INLJ，但如果没有索引呢？ 这里mysql会使用BNLI，它会把外表放到缓存中，然后将数据分批到内部表中去匹配，而不是每次一条条的去匹配，这样就建设了匹配次数，也可成为减少了内部表的逻辑读次数，那放到缓存中那个地方呢？ 就是所谓的join buffer中去了，当然mysql的join buffer默认并不是很大，它是session级别的设置，我们可以同步下面指令进行查看
show variables like '%join_buffer_size%'
默认大小为256K。BNLI的说明图如下
如何优化sql
1. 用小结果集驱动大结果集，减少外层循环的数据量：
2. 如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。
3. 为匹配的条件增加索引：争取使用INLJ，减少内层表的循环次数
4. 增大join buffer size的大小：当使用BNLJ时，一次缓存的数据越多，那么外层表循环的次数就越少
5. 减少不必要的字段查询：
（1）当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少；
（2）当用到INLJ时，如果可以不回表查询，即利用到覆盖索引，则可能可以提示速度。（未经验证，只是一个推论）
閱讀更多 大數據和數據庫學習 的文章
