create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

首先需要说下MRR优化，并不是针对join的优化。

MRR优化(Multi-Range Read)

优化的目的主要是尽量使用磁盘顺序读；
回表的过程是

• 从普通索引数上得到主键id的值之后
• 到id索引树上去查，是一行一行执行的。
• 并不能一次查询出多个id，批量去查询id索引树
• 如果查询的普通索引的值不是顺序的，那么回表就会产生磁盘的随机读，导致效率降低

MRR优化的思路就是

• 将读取到的记录放入read_rnd_buffer中
• 对read_rnd_buffer中的id进行排序
• 排序后的id数组，依次到主键id索引树中查记录，并作为返回结果

read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的，如果步骤1中read_rnd_buffer放满了之后，就会先进行排序、查主键id索引树；

• 通过设置setoptimizer_switch="mrr_cost_based=off"才能稳定的使用MRR优化
• 官方文档表明现在的优化器策略在判断消耗的时候，会倾向于不使用MRR，把mrr_cost_based设为off就是固定使用MRR了

  Batched Key Access(BKA算法)

  MySQL在5.6版本以后引入的，是对NLJ算法的优化。
  NLJ算法的执行流程如下

从t1表中一行一行的取出a值，再到t2中去查询，MRR的优化就用不上了。

BKA算法优化流程

• 先将t1中的的数据取出来一部分放入join_buffer中
• 批量join t2，性能会比较好

通过设置set optimizer_switch="mrr=on,mrr_cost_based=off,batched_key_access=on"
前两个参数的作用是要启用MRR，这么做的原因是，BKA算法要依赖于MRR。

BNL算法的优化

BNL转BKL

如果在被驱动表的等值判断字段上加上索引之后，就不会使用BNL算法，但是创建索引又会浪费资源，因此可以使用临时表。

思路

1、把驱动表种满足条件的数据放在临时表种
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);

• BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
• BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
• 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
• MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。