join 语句的两种实现算法分别是 Index Nested-Loop Join和 Block Nested-Loop Join,针对于这两个算法都还有相应的优化方法。
在这之前,我们先介绍下MRR优化。
34.1Multi-Range Read 优化
Multi-Range Read优化 (MRR) :优化的目的是尽量顺序读盘,MySQL认为磁盘顺序读比磁盘随机读的性能更加好。
这里我们需要明白**“回表”**的概念,MRR优化主要针对于这个过程。
例如下面这个sql语句,假设需要进行回表的情况:
select * from t where a BETWEEN 10 and 60
正常的回表过程:
- 从索引a中得到符合条件的数据集。
- 从数据集一行一行拿到id去主键索引进行回表,得到最后的结果集。
这个我们的问题是:每个回表的数据时一行一行进行地,并且可能逐行拿到的id是无序的,这样就会操作磁盘的随机读,MRR优化就是将id进行排序后,再进行回表。
MRR优化后的流程
- 从索引a中得到符合条件的数据集加入到read_rnd_buffer。
- 将read_rnd_buffer中的数据按照id进行排序
- 排序好的id数组,依次进行回表。
read_rnd_buffer的大小由参数**“read_rnd_buffer_size”**控制,如果一次性装不下,就会分多次装入,同时也会排序操作多次。
如果我们需要使用MRR(MySQL 默认关闭),我们需要进行如下设置:
set optimizer_switch='mrr_cost_based=off';
explain select * from t where a BETWEEN 10 and 60;
从explain语句中的Extra字段中可以看出,使用MRR优化。
MRR优化在需要进行回表的基础上,需要查询足够多的值,将随机访问变化顺序访问的一种操作。
34.2 Batched Key Access
MySQL于5.6版本引入 Batched Key Access 算法,BKA算法是对NLJ算法的一种优化。
NLJ算法的逻辑:驱动表进行全表扫描,一行一行地取出,到被驱动表中走索引来获取相应的结果集。
这个NLJ算法是一行一行来的,无法应用MRR优化,所以,我们给它用上join_buffer作为临时表,便可以解决。
BKA算法也就是用上了join_buffer+MRR优化的NLJ算法。
流程如下图所示:
启用BKA算法的设置如下,前两个的参数设置为开启MRR优化
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
34.3 BNL算法的优化
先来手下BNL算法带来的影响:
- 如果join_buffer不够大,需要分多次执行,会导致多次扫描被驱动表,占用IO资源。
- 多次扫描被驱动表也可能会导致内存中热点数据被淘汰,影响内存命中率。
1.BNL转BKA
在被驱动表上添加索引,但是如果这个sql语句为低频率语句,就有点不知当了。
2.使用临时表
使用临时表的大致思路为:
- 将被驱动表 中满足条件的数据放在临时表 tmp_t 中,并添加上需要的索引;
- 让驱动表 和 tmp_t 做 join 操作,执行BKA算法。
3.hash join
MySQL 8.0引入算法的实现,大致原理如下:
- 根据join_buffer中数据构建一个HashTable。
- Table S与HashTable进行比对,来获取符合的结果集。