MySQL 表连接 算法优化 MRR BKA
35 | join语句怎么优化？
MRR
Multi-Range Read 优化，指的是尽量使用顺序读盘。
因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
怎么是顺序读
- 根据索引将数据放入read_rnd_bufer;
- 将read_rnd_buffer中的id进行递增排序
- 将排序后的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 算法对系统的影响主要包括三个方面：
- 可能会多次扫描被驱动表，占用磁盘 IO 资源；
- 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
- 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。
BNL 优化
- 转BKA 直接在被驱动表上建立索引
- 建立临时表，将针对被驱动表的筛选存放到临时表，减少对比次数。
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 了
