（十三）Join原理，Join优化

29 Join

• Index Nested-Loop Join

联结两个表时，如果第二个表上有联结键索引，那么就会根据第一个表中查出的联结键值搜索索引，然后根据索引得到的主键id去第二张表找

在这种情况下：

1. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
2. 如果使用 join 语句的话，需要让小表做驱动表。

• Simple Nested-Loop Join

联结两个表时，如果第二个表上没有联结键索引，那么就会根据第一个表中查出的联结键值，去第二张表上作全表扫描

• Block Nested-Loop Join

对于上面这种情况，MySQL使用了 Block Nested-Loop Join

1. 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的判断是内存操作，速度上会快很多，性能也更好。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。在使用小表作驱动表时，被驱动表的扫描次数更少。

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率,需要依靠后续的查询请求慢慢恢复内存命中率。

• 能不能使用 join 语句

1. 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
2. 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

• 驱动表的选择

使用join buffer 时:在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

30 Join优化

• MMR

1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
2. 将 read_rnd_buffer 中的 id 进行递增排序；
3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

采用该优化方案可以增加顺序读，减小随机读

如果想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch=“mrr_cost_based=off”。

• Batched Key Access（BKA）

在MMR的基础上，对NLJ的优化

1. 把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。
2. 将 join_buffer中的 a 进行递增排序；
3. 排序后的 id 数组，依次到 a 索引中查记录，并作为结果返回。

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

• BNL 转 BKA

一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了。

如果不适合建索引，我们可以考虑使用临时表。
使用临时表的大致思路是：

1. 把表 t2 中满足条件的数据放在临时表 tmp_t 中；
2. 为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引；
3. 让表 t1 和 tmp_t 做 join 操作。

• 配合应用端模拟hash join