JOIN

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《JOIN》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

现在有两张表，t1表100行，t2表1000行
select * from t1 straight_join t2 on (t1.a=t2.b);

Simple Nested-Loop Join（没有被MYSQL使用）

前提：join字段t2.b不存在索引
查询过程：全表扫描t1，然后将每一条记录a字段作为条件到被驱动表t2进行一次全表扫描，也就是100次全表扫描。

Block Nested-Loop Join（简称BNL）

前提：join字段t2.b不存在索引。
查询过程：全表扫描t1，将数据放入join buffer，再将被驱动表t2数据与join buffr里的t1数据进行批量对比。当t1表数据过多，join buffer存不下时，会将数据分段重复执行整个查询过程。
tips：所以某些场景下可以通过设置join_buffer_size增大join buffer提高join速度。
通过explain的Extra 字段有 Using join buffer（Block Nested Loop）。

Index Nested-Loop Join（简称NLJ）

前提：join字段t2.b存在索引。
查询过程：全表扫描t1，循环100次（将第一条记录a字段作为条件去被驱动表t2查询二级索引树，得到主键ID，再回表查询主键索引树）。
使用BKA优化查询过程：全表扫描t1，循环100次（将每一条记录a字段放入join buffer），将join buffer里的a字段作为条件去被驱动表t2范围查询二级索引树，得到主键ID，将主键ID放入read_rnd_buffer，递增排序后，范围查询主键索引树。
时间复杂度：100+1002log2^1000（先扫描驱动表100行，再循环100次扫描二级索引树得到主键ID，再"回表"扫描主键索引树1002log2^1000）。
tips：假设驱动表的行数是 N，被驱动表行数M，也就是N+N2log2^M。可以看出驱动表的行数N对结果影响更大，所以才有了小表驱动大表。

Multi-Range Read（针对回表的优化）

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

开启MRR优化
set optimizer_switch="mrr=on"
稳定使用MRR优化，按照官方文档的说法，是现在的优化器策略，判断消耗的时候，
会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是稳定使用 MRR 了
set optimizer_switch="mrr=on,mrr_cost_based=off"

所以回表会变为：根据二级索引树得到主键ID后存入read_rnd_buffer（read_rnd_buffer_size参数控制），进行递增排序，拿排序后的主键ID去主键索引树进行范围查询。（用上MRR，通过explain的Extra 字段有 Using MRR）

Batched Key Access（针对NLJ的优化，BKA依赖MRR）

开启BKA优化，前两个参数是为了开启MRR
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。但是在 NLJ 算法里并没有用。那么，刚好就可以复用 join_buffer 到 BKA 算法中。
在NLJ过程中，由于从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。所以可以将t1表的a字段放入join buffer，递增排序后，在t2的b字段索引树上进行范围查询，得到主键ID，再利用MRR优化进行后续回表。

hash join

由于join_buffer 里面维护的是一个无序数组，所以在和被驱动表join时就需要逐条匹配，如果通过hash结构去存储驱动表数据的话，就能大大减少等值判断次数。

存在where条件（小表驱动大表）

假设t1中100条c=100的数据
select * from t2 straight_join t1 on (t2.b=t1.a) where t2.id 

这个时候需要选择t2作为驱动表，因为先根据where条件过滤后得到10行的结果集作为驱动表，再进行join，join的过程再对t1.c=100的条件过滤。

终于介绍完啦！小伙伴们，这篇关于《JOIN》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识，快来关注吧！