MySQL原理-Join原理

本文完全照搬《MySQL实战45讲》内容

MySQL执行2张表的数据连接，一定需要用到一些算法，今天我们就来看一下用了哪些算法。本文目的还是为了应付面试。

Index Nested-Loop Join

使用索引的嵌套循环连接：适用于被驱动表连接字段上有索引

现在有t1表、t2表，2表都拥有字段a且是连接字段，字段a是t2的索引列

从t1表中取出一行数据
用该行的a字段的值，去t2表中查询对应数据（先在t2表的a索引上检索到记录主键id，再回表，返回t2表中对应的记录）
将t1表中数据和t2表中查询出来的数据进行连接
若t1表中还有数据，重复1~3

所以在日常写SQL时，可以注意：

以小表作为驱动表

被驱动表的连接列上建好索引

Simple Nested-Loop Join

简单嵌套循环连接：被驱动表连接字段上没有索引

Java代码都写过吧。比如现在有一个用户对象List集合和一个地址对象List集合。我现在要将用户对象的地址属性进行填充。

@Data
public class User {
    private Long id;
    private String name;
    private Long addressId;
    private List<Address> addressList;
}

@Data
public class Address {
    private Long id;
    private String address;
}

public static void main(String[] args) {
    List<User> userList=new ArrayList<>();
    List<Address> addressList = new ArrayList<>();
    for(User user:userList){
        Long addressId = user.getAddressId();
        user.addressList = new ArrayList<>();
        for(Address address:addressList){
            if(address.getId().equals(addressId)){
                user.addressList.add(address);
            }
        }
    }
}

可以看到每个用户都要遍历所有的地址。

Simple Nested-Loop Join的原理就是这样，每次从磁盘读取驱动表的一行数据，再到磁盘上全表扫描被驱动表，找出匹配数据，读取出来并连接。（注意用的是磁盘）

Block Nested-Loop Join

块嵌套循环连接：是对简单嵌套循环连接的优化，针对被驱动表连接列上没索引的情况

块嵌套循环还是2重for循环的方式，但是它开辟了一块join_buffer在内存上，将t1的数据和t2的数据读入join_buffer，在内存中查找连接。自然会快

判断是否使用join_buffer,可以通过explain查看Extra字段

那么问题来了，join_buffer有可能放不下t1和t2表的所有数据。因此就需要分段连接。比如t1是驱动表、t2是被驱动表，将t1表分成2半。

将t1前一半数据放入join_buffer
扫描t2所有数据，将能连接上当前join_buffer内t1表的数据的加入join_buffer
join_buffer中执行连接后放入结果集，清空join_buffer
将t1的另一半数据重复1~3操作

可以看到，join_buffer大小恒定情况下，t1表的数据越多，分的块就越多，t2表全表扫描的次数就越多。因此还是要小表作为驱动表

MRR（Multi-Range Read）优化

在二级索引中查询到记录的主键id集合，然后回表查询主键索引获取记录数据。每一个主键都需要回表一次，而不能将查出来的主键id集合一次性完成回表操作

有几个主键id就要回表几次，这个逻辑无法更改。不同id的物理位置不同，操作磁盘上随机读，速度很慢。于是我们可以按照id递增的方式回表，由于id顺序就是数据物理顺序，这样回表对磁盘的读取就是顺序读，速度比随机读快

MRR优化

二级索引中查询到的记录主键id集合放入read_rnd_buffer中

read_rnd_buffer将主键集合进行排序后再按序回表

BKA(Batched Key Access)：Index Nested-Loop Join优化

Index Nested-Loop Join优化：join_buffer+mrr优化

我们先来回顾一下Index Nested-Loop Join

每遍历t1表中的一行，再去t2表中的索引a中找到可以关联的数据主键列表，然后主键列表不排序情况下依次回表查询t2表记录。这个过程BKA进行了2点优化

每次只取t1表中一行数据去关联->BKA每次取t1的多行放入join_buffer去关联

主键列表的回表没有用MRR优化->BKA使用MRR优化t2表回表操作

Hash Join

t1表和t2表，连接条件是t1.a=t2.a，两表连接前，将t2表，按照连接字段作为key（即t2.a），记录本身作为value。生成hash表，随后只要扫描t1表，并依次从hash表中取t1.a对应的数据然后连接即可

总结

若被驱动表连接字段有索引：join算法使用Index Nested-Loop Join，若开启mrr和BKA，则用的是BKA算法

若被驱动表连接字段无索引：join算法使用Block Nested-Loop Join

一般选取小表作为驱动表，小表是真实小

join_buffer：连表操作时的一块内存，可以通过join_buffer_size调整

MRR优化：回表操作是一个主键回表一次，无批量主键回表操作。MRR用read_rnd_buffer将主键排好序后回表将磁盘随机读转为顺序读，从而提升速度。