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 了