Mysql SQL JOIN 算法

sql join算法指的是当我们的sql join关联查询的时候，底层的逻辑是怎么实现的。

一、 simple nested_loop join 简单的嵌套查询算法

simple nested_loop join 算法类似于我们两个for循环

当我们R表和S表join查询(select * from r,s where r.id = s.r_id)的时候，simple nested_loop join算法就是直接拿R表的id去S表的r_id去对比，例如id = 1，分别到S表中的[1,2,3,4] 中去对比。如果相等就返回成功。这种算法的效率比较慢，所以mysql的不采用这种算法。

for(var i in [1,2,3]) {
    for(var j in [1,2,3,4]) {
        if (i == j) {
            return true;
        } 
        return flase;
    }
}

二、 index nested_loop join 索引的嵌套查询算法

索引的嵌套查询算法指的是，当S表和R表有一方有索引的时候，查询的算法。

假设我们现在在S表的r_id的字段建立索引

当我们R表和S表join查询(select * from r,s where r.id = s.r_id)的时候, 在R表的id去对比S表的r_id时， 并不是在S表中一条条去对比，而是通过S表中 r_id 的索引来比较。因为索引的IO肯定比较小，效率比较高。例如下图：

驱动表和内表

当r_id有索引时，查询 (select * from r,s where r.id = s.r_id)  ，那么现在我们称R表为驱动表，S表为内表。 意思就是R表先全部查询出来，作为驱动然后循环的到内表S表查询。这里就是需要区分驱动表和内表。

那么问题来了：msyql的优化器是怎么选择驱动表和内表的？

一般优化器都是选择小表为驱动表，大表为内表，这是为什么？

现在有A表 100W, B 表10W。

①我们假如A表为驱动表，那么我们需要先扫描A表，假如A表和B表的B+ tree高度都是3，那么IO就是 100w*3. 然后通过索引去查询B表。

②我们假如B表为驱动表，那么我们需要先扫描B表，假如A表和B表的B+ tree高度都是3，那么IO就是 10w*3. 然后通过索引去查询A表。

假设上面的索引查询速度都是很小的，那么30w的IO肯定比300W的IO小，所以肯定会以小表为驱动表更划算的。

三、 block nested-loop join 缓存的嵌套查询算法

block nested-loop join算法，不是像简单的嵌套查询算法一样，每条记录去对比每条记录，而是先把R表的所有记录拿出来后放到join buffer的内存中，以空间换时间，然后通过内存数据一次性的去跟S表的每条记录进行对比。减少了内表表的扫描次数。

我们这里把block nested-loop join算和简单的嵌套查询算法进行比较。

假如A表(1,2,3)  B表(1,2,3,4), A表为外表，B表为内表

	简单嵌套算法	缓冲嵌套算法
外表扫描次数	1	1
内表扫描次数	3	1
比较次数	3*4 = 12	3*4 = 12

解析：外表扫描次数都是1次，这里的扫描是指的取出数据，一次性到B+ tree取出数据，

简单的嵌套算法，内表的扫描次数为什么是3，是因为外表3条记录，每次都需要到B表比较。

缓冲的嵌套算法，内表的扫描次数为什么是1，是因为外表的3条记录放入了join buffer存储后，一次性的跟内表比较。 所以缓冲嵌套算法优势也就是减少了内表的扫描次数。

注意：Join buffer用于连接是ALl, index, range的类型， Join buffer只存储需要进行查询操作的相关列数据，而不是整行的记录。

所以我们应该调大join buffer的值，默认是256KB

 

四、 batched key access join

这个主要是对block nested-loop join升级，当我们关联的列是二级索引，然后需要回表的话，这里对主键进行了排序，进行了MRR操作。