到底可不可以使用join
- 不让使用join，使用join有什么问题？
- 如果有两个大小不同的表做Join，应该用哪个表做驱动表？
Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.a);
1.从表t1中读入一行数据R;
2.从数据行R中，取出a字段到表t2里去查找；
3.取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4.重复执行步骤1 到 3，直到表t1的末尾循环结束。
这个过程就成我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，索引我们称之为”Index Nested-Loop Join“，简称NLJ。
在驱动表时走的全表扫描，而被驱动表走的树搜索。
假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，在搜索主键索引，每次搜索一颗树近似复杂度是以2为底的M的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。
假设驱动表的行数为N，执行过程就要扫描驱动表N行，对于每一行，到被驱动表上匹配一次。
近似复杂度是N + N2log2M。
显示，N对扫描行数的影响更大，因此应该让小表来做驱动表。
结论：
1.使用join语句，性能比强行拆分多个单表执行SQL语句的性能要好；
2.如果使用join语句的话，需要让小表做驱动表。
Simple Nested-Loop Join
被驱动表没有索引
select * from t1 straight_join t2 on (t1.a=t2.b);
如果继续使用算法2的话，这个算法的结果是正确的，算法叫做”Simple Nested-Loop Join”。整个扫描需要N*M行，显然效率和扫描的行数就十分的低下。
Block Nested-Loop Join
被驱动表上没有索引
1.把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入内存；
2.扫描表t2，把表t2的每一行取出来，跟Join buffer中的数据做对比，满足join条件的，作为结果集返回。
总的扫描行数是1100行，由于join_buffer是以无序数组的方式组织，因此，对表t2中的每一行，都要做一百次判断，总共需要在内存中做的判断次数是：100* 1000 = 10万次。
假设小表的行数是N，大表的行数是M，那么在这个算法里：
1.两个表都做一次全表扫描，所以总的扫描行数是M+N。
2.内存中的 判断次数是M*N。
如果由于join_buffer过小，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。
注意，这个这里的K不是常数，N越大K就会越大，因此把 K 表示为λ*N，显然λ的取值范围是 (0,1)。
这个算法的执行过程
1.扫描行数N+λNM；
2.内存判断 N*M 次。
join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，被驱动表的全表扫描次数就越少。
问题解答
- 能不能使用join语句。
1.如果可以使用Index Nested-Loop Join算法，也就是使用被驱动表上的索引，其实没有问题。
2.如果使用Block Nesetd-Loop Join算法，扫描的行数就会过多，尤其大表上的join，这样可能要扫描被驱动表很多次，会占用大量的系统资源，所以这种join尽量不要有。
- 如果要使用join，应该选择大表做驱动还是选择小表做驱动。
1.如果是Index Nested-Loop join算法，应该选择小表做驱动表。
2.如果是Block Nestd-Loop join算法
在join_buffer_size足够大的时候，是一样的。
在join_buffer_size不够大的时候，应该选择小表做驱动表。
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成后，计算参与join的各个字段的总数据量，数据小的那张表，就是“小表”，应该作为驱动表。
join该怎么优化
Multi-Range Read（MMR）优化
这个优化的主要目的是尽量使用顺序读盘。
因为大多数数据都是按照主键递增顺序插入得到的，所以可以认为i，如果按照主键的递增顺序查询的话，对磁盘的读比交接近顺序读，能够提升读性能。
MRR优化思路
1.根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中；
2.将read_rnd_buffer中的id进行递增排序
3.排序后的id数组，依次到主键id索引中查记录，并作为结果返回。
MRR能够提升性能的核心在于，可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
Batched Key Access
MySQL5.6以后引入这个算法，其实就是对NLJ算法的优化。
一次性从表驱动表中多拿一些行出来，一起传给被驱动表。
如果要使用BKA优化算法的话，需要执行SQL语句之前，先设置
set optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;
BNL算法的性能问题
大表join操作虽然对IO有影响，但是语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
BNL算法对系统的影响主要包括3个方面：
1.可能会多次扫描被驱动表，占用磁盘IO资源；
2.判断join条件需要执行M*N次对比，如果是大表就会占用非常多的CPU资源；
3.可能回暖导致Buffer Pool的热数据被淘汰，影响内存命中率。
BNL转BKA
一些情况下，我们可以在被驱动表上建立索引，这是就可以直接转成BKA算法了。
创建临时表
1.把表t2中满足条件的数据放在临时表tmp_t中；
2.为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
3.让表t1和tmp_t做join操作。
总体思路，无论是在原表上加索引，还是用有索引的临时表，我们的思路都是让join语句能够用上被驱动表上的索引，来触发BKA算法，提升查询性能。
扩展 - hash join
join_buffer里面维护的不是一个无序数组，而是一个hash表的话，那么就不是10亿次判断，而是100万次hash查找。这样的话，整条语句的执行速度就会快很多。
这，也是MySQL的优化器和执行器一直被诟病的一个原因：不支持哈希join.
具体的优化思路，可以自己在业务端实现。
1.select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构
2.select * from t2 where b> =1 and b<=2000；获取t2中满足条件的2000行数据。
3.把这2000行数据，一行一行去到业务端，到hash结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据你，就作为结果集的一行。