mysql中join连接底层原理——嵌套循环算法、哈希算法：NLJ、INLJ、BNLJ、hash join。笛卡尔积。

外表a有r行，内表b有s行，join条件a.id = b.aid

mysql大致选用规则

先用Index Nested-Loop Join，若不能用（连接条件无索引）则用Block Nested-Loop Join（在 8.0.18版本之前）或hash join（在8.0.20版本以后）

Netsted（嵌套） Loop Join（NLJ）

外表a的一行，会和内表b的每一行比较，如果b.aid=a.id，则记录结果，然后遍历外表，r行都会与b的每一行做比较。

B3（excel表格中的行和列，B3对应NLJ的内表扫描次数：r）：外表一行，内表就循环一次；外表一共r行，内表就循环r*1次。

B5：获取外表a第一行，内表b的s行逐个与第一行比较，如果b.aid=a.id，则保存结果，所以要比较s次。外表有r行，每行比较s次，所以一共比较r*s次。

B6：因为内表是全表遍历，全表数据都在，所以不需要回表读取记录

Index Nested Loop Join（INLJ）

选外表的一行，获取到a.id，然后获取内表连接字段b.aid的索引树，索引树中找b.aid=a.id，如匹配，则回表查询数据，记录结果。然后循环遍历外表r行，每行都在索引树中做匹配。

c3：内表索引树只需要获取一次，外表的每一行都是同一颗索引树，所以外表扫描次数是1。

c4：r是外表读出了r次，index_match是所有命中索引后回表查询的次数

c5：外表1行，最多匹配索引树index_height次。外表一共有r行，所以join最多比较r*index_height次。详细过程：外表一行，内表要在索引树中找b.aid=a.id，如果索引树是一层，那就只用比较一次，如果是两层，可能最多需要比较2次，如果是index_height层，可能最多需要比较index_height次才能找到b.aid=a.id，也就是说

Block Nested Loop Join（BNLJ）

假设join_buffer一次能放入n行数据。把外表前n行放入内存join buffer中，内表每一行（共s行）都与join buffer中所有行做匹配，匹配上就记录结果。然后把外表第n到第2n行放入内存join buffer中，内表每行（共s行）与join buffer中的n行做匹配。外表每次都放n行到join buffer中，直到r行都放完。

D3：外表的join buffer有r/n个，每个join_buffer都要与内表所有行匹配一次，也就是每个join buffer都要扫描一次内表，所以是r/n次

D4：内表读取一条记录与join buffer所有行做对比，一共s条，所以是s次读取，然后外表join buffer 有r/n个，所以是s*r/n

hash join

外表根据a.id为key生成hash表，内表一行行去hash表里根据b.aid生成的hash值获取value，可能一个hash值对应多个value（value就是行数据，多个行数据有相同的hash值），那这些行就要进行join对比，满足b.aid=a.id，就记录结果。

E3：遍历一次内表，每行都去外表hash表里找值。所以只扫描了一次

E4：外表生成hash表的时候，一共r行，读取了r次。内表也是，一共s行，每行都去hash表找值，读取了s次。所以是r+s。

E5：内表一行，去hash表找，找到后，如果hash分散的好，一个hash值只对应一行数据，那么join对比一次就够了，如果一个hash值对应两行数据，那么最多要比较2次才能满足b.aid=a.id。这里的hash_same是说相同hash中的行数个数。

E6：内表已经全表加载到内存中，命中后不需要回表读取数据

hash join和INLJ的异同

同

底层思想都是映射（通过一个key，命中一个值）。都是通过一个表的值，然后获取另一个表中这个值的映射。

hash join中，映射是hash表。key为内表aid，通过hash表，直接找到aid对应的外表数据。

BNLJ中，映射是索引树。key为外表id，通过索引树，直接找到外表id对应的内表aid所在的内表主键，进而回表查内表主键对应的数据

异

映射的算法不同：

hash join是hash算法

INLJ是索引，是二叉树

key的表不同：

hash join是外表id为key，遍历内表，在外表中寻找

INLJ是外表索引为key，遍历外表，在内表索引中寻找

数据量不同、内存占用不同：

hash join数据全，内存占用大，不需要回表

INLJ是索引树，内存占用小，数据不全只有主键id，需要回表