本文深入探讨了数据库查询中的两种连接方法：IndexNested-Loop Join (NLJ) 和 BlockNested-Loop Join (BNL)。NLJ利用被驱动表的索引提高效率，而BNL在未使用索引时可能导致大量扫描。文章分析了如何选择驱动表，以及MRR和BKA优化技术，揭示了性能影响因素和优化策略。
目录
Index Nested-Loop Join(NLJ)（扫描被驱动表用到索引）
Block Nested-Loop Join(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 的值。
通过分析执行计划，可得出该语句的执行流程如下。
- 从表 t1 中读入一行数据 R；
- 从数据行 R 中，取出 a 字段到表 t2 里去查找符合条件的记录（扫描 二级索引获取主键id，然后回表）；
- 将步骤2中的记录跟 R 组成一行，作为结果集的一部分；
- 重复执行步骤 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
通过分析执行计划，可得出该语句的执行流程如下。
- 对表 t1 进行全表扫描，并把数据放入 join_buffer（由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存）
- 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分响应客户端。
我们再来看一下，这个 sql 语句的执行，一共扫描了多少行，
- 对表 t1 做了全表扫描，扫描行数 100 行。
- 对表 t1 做了全表扫描，扫描行数 1000 行。
- 那么总的扫描行数是 1100 行。
实际中 join buffer 是有限制的
如果驱动表 t1 是个大表，join buffer 放不下，t1 表的数据怎么办。
mysql的处理方法也是简单粗暴，如果放不下，就是将驱动表的数据分段放。执行流程如下
- 扫描表 t1，将数据让如 join buffer 中，等放到第 88 行 join buffer man了，就继续执行第2步
- 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分响应客户端。
- 清空 join buffer
- 继续扫描 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
- 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
- 将 read_rnd_buffer 中的 id 进行递增排序；
- 排序后的 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) 的执行过程
- 对表t1进行全表扫描，并将数据放入 Join buffer 中
- 对 join buffer 中的数据，根据主键 id 进行排序
- 使用 t1.a 字段的值去，扫描 t2.a 二级索引（这里可以理解为 select * from t2 where t2.a in (1,2,3,4,5······)），并使用 MRR 优化
- 将符合条件的记录发送给客户端
BNL 算法的性能问题
- 将被驱动表数据放入临时表中，给临时表创建索引
- 业务自行处理
一图胜千言
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
