MySQL-34：join语句优化

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进行比对，来获取符合的结果集。