MYSQL之JOIN算法

创建表

t1里有100行数据(id是主键,a有索引) t2里有1000行数据，同样是这三列。

Index Nested-Loop Join（基于索引的嵌套循环联接）

通过explain分析，可以看到t1表扫描了100行，而t2表使用了索引a。
执行伪算法如下：

For each row r in R do                     -- 扫描R表 驱动表
    lookup s in S index                    -- 查询S表(被驱动表)的索引（固定3~4次IO，B+树高度）
        If find s == r                     -- 如果r匹配了索引s
            Then output the tuple <r, s>   -- 返回结果集

由于被驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。
这里t1执行的是全表扫描，需要扫描100行。
由于t2表走a的索引，所以每次定位到指定的1行，一共也是100行。
总扫描行为200行。

Simple Nested-Loop Join（基于索引的嵌套循环联接）

select * from t1 straight_join t2 on (t1.a=t2.b);

这条语句就是SNLJ，因为b字段并没有索引。每次到t2去匹配时，都是全表扫描。 MySQL并未采用该算法。

Block Nested-Loop Join

流程如下：
1.把驱动表中的需要的数据取出放入线程的join_buffer中。
2.然后扫描被驱动表，每一行取出来，和join_buffer中数据做对比，满足条件，则作为结果集的一部分返回。
当join_buffer不够放下所有的驱动表数据时，选择小表作为驱动表更好。

MRR优化

目的是尽量使用顺序读盘。主要是利用了磁盘预读性原理，提升读取性能，防止随机读。

字段a是有索引的，可以看到Extra里有个Using MRR。

Batched Key Access（对INLJ算法优化）

流程：
1.将驱动表的数据加载到join_buffer中
2.然后通过MRR优化，从被驱动表中取出匹配的数据，作为结果集的一部分返回。