你知道如何优化Join语句吗？

join语句的两种算法，分别是：NLJ和BNL

测试数据：

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的设计思路就是：大多数数据都是按照主键递增的顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，多磁盘的读比较接近于顺序读，能够提升读的性能。

此时，执行语句将会变成这样：

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

如果我们想稳定低使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based =off"参数

总结一下：MRR的核心就是，这条查询语句在索引a上做的是一个范围查询（多值查询），可以得到足够多的主键id，这样通过排序后，再去主键索引差数据，才能体现出“顺序性”

Batched key Access优化

MySQL在5.6版本后，引入了BKA算法，其实就是对NLJ算法的优化

NLJ的逻辑是，从驱动表t1，逐行取出a的值，再到驱动表t2去做join。

我们可以把表t1 的数据取出来一部分，先放到一个临时内存中，这个临时内存就是join_buffer

启动BKA算法的参数是：

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

BNL性能优化

在通常情况下，我们可以直接在被驱动表上建立索引，这样就可以将BNL算法成NLJ算法了。但是有一些被驱动表不适合做索引，例如：

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

这句SQL表示，在经过where条件过滤，需要参与join的只有2000行数据。如果这条数据是一个低频的SQL，那么在为表t2建立一个字段b的索引，就很浪费了。

但是如果我们使用BNL算法，那么我们首先会将表t1存入join_buffer，然后扫描表t2与join_buffer中的数据进行比对，这里的数据是指所有的数据。这个判断等值条件的次数是1000*100万=10亿次。

这个时候，我们可以考虑使用临时表来优化，大致的思路是：

1. 把表中t2满足条件的数据，先放在临时表tmp_t中；
2. 为了让join可以使用BKA算法， 给临时表tmp_t的字段加上索引
3. 让临时表和t1做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);

 

 

此时，可以明显看出，速度快了很多。

总结：

在被驱动表的字段上加索引

创建临时表，在临时表中加索引

hash join

我们可以自己在业务段，创建hash结构，然后将数据读出来，自己进行数组的匹配和数组集的组装。也可以提高join的性能。

总结：

BKA优化是MySQL内置支持的，建议使用

BNL效率低，建议转成BKA算法

临时表的方案，对于能提前过滤出小数据的join语句来说，效果很好

hash join的效果也是很好的，基于内存计算，路论上效果优于临时表