join原理、join算法

https://blog.csdn.net/u014756578/article/details/52795545

MySQL Join算法

MySQL使用Nested-Loop Join(嵌套循环连接)算法优化Join；

在Mysql的实现中，Nested-Loop Join有3种实现的算法：

1. Simple Nested-Loop Join：SNLJ，简单嵌套循环连接
2. Index Nested-Loop Join：INLJ，索引嵌套循环连接
3. Block Nested-Loop Join：BNLJ，缓存块嵌套循环连接

select * from t1 inner join t2 on t1.id=t2.tid
（1）t1称为外层表，也可称为驱动表。
（2）t2称为内层表，也可称为被驱动表。

---

Simple Nested-Loop Join 简单嵌套循环连接

将表1中的每一行数据 与 整张表2匹配，效率低；

匹配次数 = 外层表行数 * 内层表行数

如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 =1亿次，这种查询效率会非常慢。

---

Index Nested-Loop Join 索引嵌套循环连接

索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数；

匹配次数= 外层表的行数 * 内层表索引的高度

使用场景：只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接。

---

Block Nested-Loop Join 缓存块嵌套循环连接

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。

即，将join buffer中的部分table数据，与table2全表匹配（批量）；

例如，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数.

---

Join Buffer

mysql官方对join buffer的介绍 https://dev.mysql.com/doc/internals/en/join-buffer-size.html

1. 用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较;

2. 通过调整join_buffer_size调整join buffer大小

3. join buffer中只会保存参与join的列, 并非整个数据行。

4. 只有join类型为ALL 或者 index 时才会使用join buffer；

5.6版本及以后，优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启。

---

如何优化Join速度

1. 用小结果集驱动大结果集
   如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。
2. 为匹配的条件增加索引
   争取使用INLJ，减少内层表的循环次数
3. 增大join buffer size的大小
   当使用BNLJ时，一次缓存的数据越多，那么内层表循环的次数就越少
4. 减少不必要的字段查询
   当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，内层表的循环次数就越少；