先创建两个表，结构一样：
下面讲解集中join语句的情况。
Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.a);
用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1 是驱动表，t2是被驱动表。
先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录，并且可以用被驱动表上的索引。这种就是“Index Nested-Loop Join”，简称NLJ
1. 对驱动表t1做了全表扫描，这个过程需要扫描100行；
2. 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
3. 所以，整个执行流程，总扫描行数是200。
为了更好的利用被驱动表上的索引，所以一般用小表作索引。
Simple Nested-Loop Join
没有索引的场景下：select * from t1 straight_join t2 on (t1.a=t2.b);
这样子的话，两边都要作一次全扫描，无所谓驱动表还是被驱动表。这样子的话，整个join就会非常慢。为此Mysql采用了“Block Nested-Loop Join”的算法，简称BNL。
Block Nested-Loop Join
算法的流程是这样的：
1. 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
2. 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
BNL的判断是在join_buffer内存里面的，所以执行速度会快很多。join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。
如果放不下的话执行的流程就变成了：
1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
3. 清空join_buffer；
4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。
Join算法的选择
1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。
Join驱动选择
1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
2. 如果是Block Nested-Loop Join算法：
- 在join_buffer_size足够大的时候，是一样的；
- 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。
所以，这个问题的结论就是，总是应该使用小表做驱动表。
小表的定义
select t1.b,t2.* from t1 straight_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只查字段b，因此如果把t1放到join_buffer中，则join_buffer中只需要放入b的值；
- 表t2需要查所有的字段，因此如果把表t2放到join_buffer中的话，就需要放入三个字段id、a和b
小表选择时，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
NLJ算法的优化
一. Multi-Range Read
优化的主要目的是尽量使用顺序读盘。在索引查找的回表的时候，回表肯定是一行行搜索主键索引的，当要查找的数据很多，回表可能就变成了随机访问，那么这里就有一个优化点。我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这，就是MRR优化的设计思路。此时，语句的执行流程变成了这样：
1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;
2. 将read_rnd_buffer中的id进行递增排序；
3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。
二. Batched Key Access
Batched KeyAcess(BKA)算法是对NLJ算法的优化。其实就是用一个join_buffer缓存加入到NLJ的算法中，一次从驱动表中取多行，只去查询需要的字段，再去匹配。
BNL算法的优化
之前的文章提到，扫描数据的时候利用young区和old区的LRU算法保证缓存的命中率，但是如果全盘扫描执行的语句大多数超过1s，那么就可能会影响到young区的缓存，降低命中率。
一. BNL转BKA
加上索引，但是加索引也有浪费的问题，比如select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;这个语句，在表t2中插入了100万行数据，但是经过where条件过滤后，需要参与join的只有2000行数据。其实joinbuffer上只放了满足条件的1000行，然后再扫描t2，与join buffer中的所有数据对比。这个工作量也大，而且在表t2的字段b上创建索引浪费了建立索引要用的资源。
但是如果不建立索引的话，对于表t2的每一行，判断join是否满足的时候，都需要遍历join_buffer中的所有行。因此判断等值条件的次数是1000*100万=10亿次，这个判断的工作量很大。
二. 临时表
1. 把表t2中满足条件的数据放在临时表tmp_t中；
2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
3. 让表t1和tmp_t做join操作。
对应的SQL语句的写法如下
1. 执行insert语句构造temp_t表并插入数据的过程中，对表t2做了全表扫描，这里扫描行数是100万。
2. 之后的join语句，扫描表t1，这里的扫描行数是1000；join比较过程中，做了1000次带索引的查询。相比于优化前的join语句需要做10亿次条件判断来说，这个优化效果还是很明显的。
三. 扩展-hash join
如果join_buffer里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是10亿次判断，而是100万次hash查找。但是当前mysql并没有作这个优化，实际上，这个优化思路，我们可以自己实现在业务端。实现流程大致如下：
1. select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构。
2. select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。
3. 把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。
