【MYSQL】JOIN优化

join

MYSQL 中并没有 full join 的语法，需要借助 union 关键字来实现：

现在假设有两张表 用户表user，和 部门表depart

 select user.name, user.age, depart.department
 from user left join depart
 on user.name = depart.name
 
 union
 
 select user.name, user.age, depart.department
 from user right join depart
 on user.name = depart.name;

在这个例子中，驱动表就是 user，是主动发起查询的表，被驱动表是 depart，是根据 on 条件被查询的表。

当然了，MySQL 优化器其实会对驱动表有一个选择的过程，并不会固定说就是 user 或者就是 depart，为了便于下面的分析，我们可以用 straight_join 来固定驱动表。

select *
 from user straight_join depart
 on user.name = depart.name;

Index Nested-Loop Join（NLJ）

join查询在有索引条件下

• 驱动表有索引 不会使用到索引
• 被驱动表建立索引 会使用到索引

所以用小表驱动大表，再给大表建立索引会大大提高执行速度

具体操作

1. 从 user 表中读入一行数据 R
2. 从数据行 R 中，取出 name 字段到表 depart 的 name 索引树上去找并取得对应的主键
3. 根据主键回表查询，取出 depart 表中满足条件的行，然后跟 R 组成一行，作为结果集的一部分
4. 重复执行步骤 1 到 3，直到 user 表的末尾则循环结束

Simple Nested-Loop Join（SNL）

在被驱动表没有索引的情况下，具体操作：

1. 从 user 表中读入一行数据 R
2. 从数据行 R 中，取出 name 字段到表 depart 上做全表查询，并取得对应的主键
3. 根据主键回表查询，取出 depart 表中满足条件的行，然后跟 R 组成一行，作为结果集的一部分
4. 重复执行步骤 1 到 3，直到 user 表的末尾则循环结束

Block Nested-Loop Join （BNL）

BNL 引入了 join_buffer 的概念，具体操作：

首先，扫描 user 表全部数据并加入 join_buffer，一共 100 行；然后，对表 depart 中的每一行，取出来跟 join_buffer 中的数据分别做判断，每行数据都需要做 100 次判断，因此，总共需要做的判断次数是：100 * 1000 = 10 万次

这个和 Simple Nested-Loop Join（SNL） 时间复杂度差不多，但是这个操作是在内存中做的，所以速度会很快

注意，这里 join_buffer 大小是有限的，如果表 user 中的数据比较大，join_buffer 一次性放不下，那么会 分块去join，具体操作：

假设我们调小了 join_buffer_size，使得 user 表在存入第 60 行数据的时候 join_buffer 就存不下了

1. 扫描表 user，顺序读取数据行放入 join_buffer 中，放完第 60 行 时 join_buffer 满了，执行下一步
2. 扫描表 depart，把 depart 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回
3. 清空 join_buffer（重点就是这一步）
4. 继续扫描表 user，顺序读取最后的 40 行数据放入 join_buffer 中，然后继续执行第 2 步