到底可不可以使用join？

表t2里插入了1000行数据，在表t1里插入的是100行数据。

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

如果以上语句直接使用join语句，mysql优化器可能会选择表t1或者t2作为驱动表，这会影响我们分析mysql的执行过程。为了方便分析过程中的性能问题，我改用straight_join让mysql使用固定的查询方式执行查询。
可以看到这个表t2上有索引，join过程用上了这个索引，因此这个语句的执行流程是：
1.先从t1表中读取一行数据R。
2.然后从数据R中读取a字段到表t2中查找。
3.取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分。
4.重复执行步骤1到3，直到表t1的末尾执行结束。
在这个流程中：
1.对驱动表t1做全表扫描，这个过程需要扫描100行。
2.而对于每一行R，根据a字段去表t2进行查找，走的是树搜索过程。由于我们的数据结构是一一对应的，因此每个搜索过程都只扫描一行，总共也只扫描100行。
3.所以总共扫描了200行。

怎么选择驱动表呢？
应该选择小表作为驱动表(前提是可以使用被驱动表作为小表)。在join语句的执行过程中，驱动表是走的全表扫描，而被驱动表走的是树搜索。假设被驱动表的行数是M，每次在被驱动表查找一行数据的时候要先搜索索引a,再搜索主键索引，所以时间复杂度为2log以2为底M的对数。
假设驱动表行数为N，执行过程要扫描驱动表N行，然后对每一行，到被驱动表上执行一次。时间复杂度为 N + N2*log2M。

假如被驱动表没有索引，会采用Block Nested-Loop Join算法：
1.把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select ，因此把整个表t1放入到内存中。
2.扫描t2，把表的每一行取出来，跟join_buffer中的数据进行对比满足join的条件，作为结果集的一部分进行返回。
以上操作总内存判断次数：1001000= 10万次。但由于是内存操作，速度会快上很多。

结论：无论被驱动表是否有索引，选择小的表作为驱动表都可以加快访问速度。并且如果join语句很慢的话，就将join_buffer_size改大。

能不能使用join语句？
1.如果被驱动表有索引的话，没有问题
2.如果被驱动表没有索引的话，扫描行数过多，尤其是在大表上的join操作，这可能会扫描大表很多次，会占用大量系统资源，所以join语句尽量不要用。
所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

什么叫小表呢？
如果id是主键的话，选择第二行语句相对更好，因为t2获取前50行作为驱动表与t1进行比较。

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

Multi-Range Read优化

这个优化目的是尽量使用顺序读盘。
主键索引是一颗棵b+树，在这棵树上，每次只能根据一个主键id查找到一行数据。因此回表肯定是要一行一行搜索主键索引的。

create table t1(id int primary key, a int, b int, index(a));
select * from t1 where a>=1 and a<=100;

如果随着a的值递增顺序查询的话，id就变成了随机访问，性能就会变得很差。虽然按行查询不能改，但是调整查询的顺序还是能够加速的。
因为大多数数据都是按照主键递增顺序插入得到的，所以我们可以认为如果按照主键递增顺序查询的话，对磁盘读比较接近顺序读能够提升读的性能。
所以MRR的优化顺序变成了这样：
1.根据索引a，定位到满足条件的记录，将id放入到read_rnd_buffer中。
2.将read_rnd_buffer中的id进行递增排序。
3.排序后的id数组，依次到主键id索引中查找此记录，并将结果返回。

想要稳定的使用MRR优化：set optimizer_switch=“mrr_cost_based=off”

Batched Key Access算法
如果是NLJ算法的话，每次从t1一行一行的取出a的值，然后再到被驱动表t2做join。
BKA的优化流程是每次多取一些表数据。使用方式：set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;

BNL算法的性能问题：

使用bnl算法可能会对被驱动表进行多次扫描。如果这个被驱动表是一个大的冷表。除了会导致io压力增大以外，还会对系统有什么影响呢？
由于innodb对buffer pool做了优化，即第一次访问的数据页会被放入到old区，如果1s之后这个数据页不再被访问了，就不会被移动到链表头部，这样对buffer pool的命中率影响不大。
但是如果使用BNL算法的join语句，多次扫描一个冷表，而且这个语句的执行时间超过1s，就会再次扫描冷表的时候，把冷表的数据页移动到链表的头部。
这种情况下，是冷表的数据量小于整个buffer pool的3/8，能够完全放入到old区。
如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入young区域。

由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数。
BNL算法对系统的影响主要包含以下三个方面：
1.可能多次扫描被驱动表，占用磁盘io资源。
2.判断join条件需要执行M*N次对比，如果是大表就会占用非常多的cpu资源。
3.可能会导致buffer pool的热数据被淘汰，影响内存命中率。

BNL转BKA

一些情况下，我们可以之际在被驱动表上建索引，这时候就直接转换为了BKA算法。
但是有些时候确实会遇到不适合建索引的操作，例如以下语句：
t1000行数据 t210万行数据

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

如果t2不建索引的话，就会进行全表扫描，因此判断等值条件的次数是1000*100万=10亿次，这个判断的工作量很大。
而如果建立索引的话，会浪费资源，有没有什么两全其美的解决办法呢？
可以考虑使用临时表：
1.把t2中满足条件的数据放入到临时表tmp_t中。
2.为了让join使用BKA算法，给tmp_t的字段上的字段b加上索引。
3.让t1和tmp_t做join操作。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);