35 | join语句怎么优化？

MRR

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

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

怎么是顺序读

1. 根据索引将数据放入read_rnd_bufer;
2. 将read_rnd_buffer中的id进行递增排序
3. 将排序后的id数组，依次到主键id索引中查记录。

read_rnd_buffer 的大小是由read_rnd_buffer_size参数控制。read_rnd_size放满了，就持续执行2，3，然后清空read_rnd_buffer 。

如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch=”mrr_cost_based=off”。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

如果 explain 看到Extr多出了Using MRR，表示用上了MRR优化。

Batched Key Access

这个BKA算法，就是对NLJ算法的优化

主要优化 批量从驱动表取记录，放到join_buffer。

注意使用BKA，需要先启用MRR，至于BKA依赖MRR，可以看这篇文章：http://mysql.taobao.org/monthly/2016/01/04/。BKA的存储需要调用MRR的接口。

BNL 优化

也就是说，BNL 算法对系统的影响主要包括三个方面：

1. 可能会多次扫描被驱动表，占用磁盘 IO 资源；
2. 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
3. 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

BNL 优化

1. 转BKA 直接在被驱动表上建立索引
2. 建立临时表，将针对被驱动表的筛选存放到临时表，减少对比次数。

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

在原表上加索引，还是用有索引的临时表，我们的思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。

hash join

业务上先查出小表的数据并建立hash表，根据筛选条件筛出t2表，在业务代码比较。

MySQL8.0已经支持hash join 了