MySQL join 学习

Blocked Nested Loop Join （BNLJ）

select * from t1 straight_join t2 on t1.a = t2.a

t1 叫驱动表 ，t2叫被驱动表

当t2表上不存在包含列a的索引时

1. 先从t1表中取出部分行（这个部分大小小于join_buffer_size），放入join buffer。
2. 扫描t2表，将每一行和join buffer中行进行比对符合 t1.a = t2.a 的拼接成结果放入结果集。
3. 循环执行第一和第二步，直到取出t1所有行

这种join 因为需要多次扫描表，当join buffer变大时，扫描的次数会减小。
join buffer 由参数 join_buffer_size决定。

或者，使用小表当作驱动表，也会减少扫描的次数

Index Nested Loop Join( NLJ)

还是上面那条SQL

当t2表上存在包含列a的索引时，流程如下：

1. 先从t1读出一行，拿到t2表中去查出符合t1.a = t2.a 的一行，拼接成结果，放入结果集。
2. 循环执行第一步，直到取出t1所有行

这里驱动表走的是全表扫描，被驱动表走的索引查询，用小表做驱动表，能减少全表扫描的行数。

Batched Key Access 和 Multi-Range Read

在NLJ里，是驱动表每取一行就去被驱动表里查一次，虽然索引查询很快，但是如果查询时能批量去查也比这种分成单个查快点。而且，根据B+树的结构可以看出，如果是批量查，且key都是有序的，也会比无序的快点。

所以，MySql提供BKA和MRR两种优化来提高NLJ的效率。

BKA指的是批量查，MRR指的是顺序查

用法如下：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
select * from t1 straight_join t2 on t1.a = t2.a;