MySQL Join深度优化

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();

什么是Multi-Range Read（MRR）优化？

Multi-Range Read优化的目的是尽量使用顺序读盘。

select * from t1 where a>=1 and a<=100;

在上述查询中，我们需要回表主键索引，在主键索引树上，每次只能根据一个主键id查找到一行数据。随着a的值递增查询的话，id的值回表查询就会变成随机访问，性能较差。

对于大多数的数据都是按照主键递增顺序插入得到，如果按照主键递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能，MRR正是借助此思想将语句的执行流程变成如下：

1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中
2. 将read_rnd_buffer中的id进行递增排序
3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回

如果read_rnd_buffer放满了，就需要先执行2-3步，完成以后会清空read_rnd_buffer，再继续执行第1步，read_rnd_buffer的大小由read_rnd_buffer_size参数控制。

show global variables like 'read_rnd_buffer_size';

如果需要稳定使用MRR优化的时候需要执行一下命令(现在的优化器策略判断消耗的时候会更倾向于不使用MRR，执行下面的命令会固定使用MRR)：

set optimizer_switch="mrr_cost_based=off";

什么是BKA算法？

NLJ算法的执行逻辑是：从驱动表t1一行一行地取出a的值，再到被驱动表t2去做Join，对于表t2来说每次都是匹配一个值，MRR无法发挥优势。

如果需要发挥MRR的优势，就需要多取一些值然后再去表t2对比，存储这些值的区域我们可以使用BNL算法中的join_buffer。

BKA算法就是优化后NLJ算法（增加了MRR的优势）。

开启BKA算法需要执行以下SQL语句：

set optimizer_switch="mrr=on,mrr_cost_based=off,batched_key_access=on";

BNL转BKA算法

BNL算法的问题我们在上一篇已经讲过，对于上面的问题我们可以直接在被驱动表上建立索引，此时就可以直接转为BKA算法。

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

比如上述语句是低频SQL并且经过where条件过滤后参与join的只有2000行数据，如果在被驱动表上建立索引会有点浪费，我们可以考虑临时表：

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.a=temp_t.b);

不论是在临时表加索引还是在原表加索引，都是为了让join语句用上被驱动表上的索引来触发BKA算法，提高查询性能。

扩展 -hash join

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

对于BNL算法，join_buffer中维护的是无序数组，需要判断次数过多1000 * 100万 = 10亿次，判断工作量是巨大的，如果我们在join_buffer中存储的是hash表的话，就会转变成100万次hash查找，速度会快很多。

MySQL8.0以后支持Hash Join了，如上图。