MySQL-34：join语句优化

r囧r小猫 2022-12-30 03:48 148阅读 0赞

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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

从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算法。

流程如下图所示：

![img][]

启用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引入算法的实现，大致原理如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

*  根据join\_buffer中数据构建一个HashTable。
 *  Table S与HashTable进行比对，来获取符合的结果集。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/af222625eb8f422a8b60bcaebe40e061.png
[img]: https://img-blog.csdnimg.cn/img_convert/6608ca0b41ef8a2ec05c8e7fe7ee468f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201223235852987.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center