mysql join底层实现

两个表join底层实现：

5.5 版本之前，MySQL本身只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

mysql底层join实现只支持一种算法：嵌套循环连接（Nested-Loop Join）,nested-Loop-Join有三种变种：

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

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

Block Nested-Loop Join  块索引嵌套连接

1.Simple Nested-Loop Join：

如图，A为驱动表，B为匹配表。 从A中取出数据1，遍历B，将匹配到的数据放到result.. 以此类推，每条A表数据都会轮询B表。

2.Index Nested-Loop Join（索引嵌套）：

这个需要查询时，关联非驱动表（也就是匹配表）的索引，通过索引来减少比较，加速查询。

在查询时驱动表会根据关联字段的索引 到非驱动表查找数据，找到对应的值，此时分为两种情况：

如果索引不是主键索引的话，需要进行回表查询（根据索引携带的主键信息查询数据） 不是主键时，要进行多次回表查询，先关联索引，再根据主键ID查询，性能上要慢很多。

如果关联字段是非驱动表的主键时，性能会非常高，直接就能定位到数据。

这里不懂的可以看下我的这个博客中的Inoodb引擎下的索引与主键存储图：

https://blog.csdn.net/qq_37113604/article/details/88831254

3.Block Nested-Loop Join（块嵌套）：

如果关联的是非驱动表的索引会走索引嵌套，但如果join的列不是索引，就会采用Block Nested-Loop Join。  首先将驱动表的结果集中 所有与join相关的列都先缓存到join buffer中（这样当查找完成时，就可以将匹配到的记录从内存与非驱动表放到result返回），然后批量与匹配表进行匹配，将第一种中的多次比较合并为一次，降低了非驱动表的访问频率。 默认情况下join_buffer_size=256K（可以通过show variables like 'join_%' 查看大小）。

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

 

 

当有两个表以上join时：

两个表以上join时也会用到join buffer，会将前两个表的结果集缓存下来，然后与第三个表比较，再返回result。建议把join buffer开大点，因为当join buffer不够用时会对数据进行分段（例如将后一千条数据放入硬盘）将内存存不下的数据放入硬盘，这样读写会产生IO从而减缓速度。