JOIN

user_yYvrkNPO
现在有两张表,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 <= 10 and t1.c = 100;

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

阅读 371
1 声望
0 粉丝
0 条评论
1 声望
0 粉丝
文章目录
宣传栏