join 是怎样工作的

目录

数据准备

Index Nested-Loop Join(NLJ)（扫描被驱动表用到索引）

Block Nested-Loop Join(BNL)（扫描被驱动表未用到索引）

假设 join bufer 足够大

实际中 join buffer 是有限制的

如何选择驱动表

走索引的情况

不走索引的情况

文中“小表”的含义

举例1

举例2

举例3

结论

Multi-Range Read（MRR）

Batched Key Access（BKA）

BNL 算法的性能问题

---

数据准备

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a` (`a`)
) ENGINE=InnoDB;

在表 t2 中插入数据 1000 条数据
for(int i=1; i<=1000; i++) {
    insert into t2(id, a, b) values(i, i, i);
}

创建表 t1，并插入 100 条记录
create table t1 like t2;
insert into t1 (select * from t2 where id<=100);

说明：如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。

Index Nested-Loop Join(NLJ)（扫描被驱动表用到索引）

执行sql

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

由于使用了 straight_join 表 t1 是驱动表，t2是被驱动表

通过 explain 查看执行计划

分析执行计划

对 t1 表做了全表扫描，扫描行数为 100 行

扫描 t2 表是时，使用到了索引“index_a”，执行计划中的 ref 显示为 “lp.t1.a” 说明，扫描 t2 表二级索引使用的值是 t1 表的字段 a 的值。

通过分析执行计划，可得出该语句的执行流程如下。

1. 从表 t1 中读入一行数据 R；
2. 从数据行 R 中，取出 a 字段到表 t2 里去查找符合条件的记录（扫描 二级索引获取主键id，然后回表）；
3. 将步骤2中的记录跟 R 组成一行，作为结果集的一部分；
4. 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

由于扫描被驱动表的时候，使用了被驱动表的索引，所以我们称之为“Index Nested-Loop Join” 简称“NLJ”

我们再来看一下，这个 sql 语句的执行，一共扫描了多少行，

对表 t1 做了全表扫描，也就是扫了 100 行。

在对表 t2 进行扫描的时候由于走了索引 t2.a 所以也扫描了 100行（回表次数为 100 次）。那么总的扫描行数是 200 行。

Block Nested-Loop Join(BNL)（扫描被驱动表未用到索引）

假设 join bufer 足够大

执行sql

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

由于使用了 straight_join 表 t1 是驱动表，t2是被驱动表

explain 查看执行计划

分析执行计划

对表 t1 做了全表扫描，扫描行数为 100 行

对表 t2 做了全表扫描，Extra 内容包含“Using join buffer (Block Nested Loop)”，说明使用了 join buffer

通过分析执行计划，可得出该语句的执行流程如下。

1. 对表 t1 进行全表扫描，并把数据放入 join_buffer（由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存）
2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分响应客户端。

我们再来看一下，这个 sql 语句的执行，一共扫描了多少行，

• 对表 t1 做了全表扫描，扫描行数 100 行。
• 对表 t1 做了全表扫描，扫描行数 1000 行。
• 那么总的扫描行数是 1100 行。

实际中 join buffer 是有限制的

如果驱动表 t1 是个大表，join buffer 放不下，t1 表的数据怎么办。

mysql的处理方法也是简单粗暴，如果放不下，就是将驱动表的数据分段放。执行流程如下

1. 扫描表 t1，将数据让如 join buffer 中，等放到第 88 行 join buffer man了，就继续执行第2步
2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分响应客户端。
3. 清空 join buffer
4. 继续扫描 t1，将数据放入 join buffer中，继续执行第2步，直到表 t1 的数据扫描完为止。

我们看下，join buffer 不够大时，一共扫描了多少行。这里我们假设分 t1 表的数据分三次放入 join buffer

• 对表 t1 做了全表扫描，扫描行数 100 行。
• 表 t1 的数据分三次放入join buffer，每次都对表 t2 做了全表扫描，扫描行数就是 3*1000=3000
• 那么总的扫描行数是 3100 行。

如何选择驱动表

走索引的情况

假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 index_a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M（乘以2是因为搜索一次就要回表一次）。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是 N + N*2*log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

不走索引的情况

假设驱动表的行数是 N，被驱动表的行数是 M， join buffer 每次执行放入 H 条记录。

那么扫描行数就是N + (N/H + 1) * M，驱动表行数越少，扫描的总行数就越少，因此应该让小表来做驱动表。

文中“小表”的含义

小表不一定表的总记录数少的表

举例1

比如 select * from t1 join t2 on (t1.b=t2.b) where t2.id <= 10（这里举的例子是没有被驱动表未使用索引）

会将 t2 作为驱动表，因为只需要将 t2 中的 10 条数据放入 join buffer 中即可，如果将 t1 作为驱动表 就需要放入 100 条数据到 join buffer中。

举例2

select t1.b, t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;（与 select t1.b,t2.* from t1 join t2 on (t1.b=t2.b) where t2.id<=100 等价）

select t1.b, t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;

表 t1 和 t2 都是只有 100 行参加 join。但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的：

• 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，则 join_buffer 中只需要放入 b 的值；
• 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字段 id、a 和 b。

所以应该选择 t1 作为驱动表

举例3

例子2中，以下两条sql是等价的，都是选择 t1 作为驱动表

select t1.b, t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;

select t1.b,t2.* from t1 join t2 on (t1.b=t2.b) where t2.id<=100；

那么与sql A “select t1.b,t2.* from t2 join t1 on (t1.b=t2.b) where t2.id<=100；”是否是等价的呢。

答案是，不等价的，sql A，选择是将 t2 作为驱动表，根据举例2中的说明，需要将 t2 中的三个字段都放入 join buffer 中。所以在做表 join 的时候需要将作为驱动表的表名现在 “join” 的前面

结论

哪张表作为驱动表，不是看哪张表记录少，也不是看哪张表放入 Join buffer 的记录少。

而是看哪张表放入join buffer 中的数据量少，哪张表就是驱动表

Multi-Range Read（MRR）

MRR 的优化思路是，由于大多数情况下数据都是按照主键递增顺序插入得到的，所以可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

做实验前，先打开 MRR，mysql默认是关闭 MRR的 。如果需要使用MRR 需设置 set optimizer_switch="mrr_cost_based=off"。

我们来看下使用了MRR后，sql的执行流程，字段 a 是非唯一二级索引

select * from t where a > 100 and a < 200

1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
2. 将 read_rnd_buffer 中的 id 进行递增排序；
3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

我们看下sql的执行计划

可以看到 Extra 字段出现了“Using MRR”

Batched Key Access（BKA）

BKA 算法是对 NLJ 算法的优化，其实就是在 NLJ 的基础上使用了 MRR

我们看sql select * from t1 straight_join t2 on (t1.a=t2.a) 的执行过程

1. 对表t1进行全表扫描，并将数据放入 Join buffer 中
2. 对 join buffer 中的数据，根据主键 id 进行排序
3. 使用 t1.a 字段的值去，扫描 t2.a 二级索引（这里可以理解为 select * from t2 where t2.a in (1,2,3,4,5······)），并使用 MRR 优化
4. 将符合条件的记录发送给客户端

BNL 算法的性能问题

• 将被驱动表数据放入临时表中，给临时表创建索引
• 业务自行处理

一图胜千言