【Mysql】join

1.要不要使用join

使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
如果使用 join 语句的话，需要让小表做驱动表。
但是，你需要注意，这个结论的前提是“可以使用被驱动表的索引”。

什么是小表？
计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

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

2.Block Nested-Loop Join

2.1 示例

被驱动表上没有可用的索引。
使用的数据库：表 t2 里1000 行数据，表 t1 里100 行数据。
执行语句：select * from t1 straight_join t2 on (t1.a=t2.a);


次数：对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次。
比较：如果使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好。

2.2 join_buffer

join_buffer的大小

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。


这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去 join”。

2.3 BNL算法的影响

3.Multi-Range Read 优化 (MRR)

这个优化的主要目的是尽量使用顺序读盘。

示例

使用语句：select * from t1 where a>=1 and a<=100;
优化思路：
1.使用索引a查找，将表t1满足条件的记录id放到read_rnd_buffer中；
2.将read_rnd_buffer中的id进行递增排序；
3.排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

使用设置

如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch=“mrr_cost_based=off”。
（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

4.Batched Key Access

介绍

BKA 算法，其实就是对 NLJ 算法(NLJ:根据索引)的优化。

流程

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。

那怎么才能一次性地多传些值给表 t2 呢？方法就是，从表 t1 里一次性地多拿些行出来，一起传给表 t2。

启用方式

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

//其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

5.hash-join

MySQL 的优化器和执行器一直被诟病的一个原因：不支持哈希 join。并且，MySQL 官方的 roadmap，也是迟迟没有把这个优化排上议程。