join 是怎样工作的

本文深入探讨了数据库查询中的两种连接方法:IndexNested-Loop Join (NLJ) 和 BlockNested-Loop Join (BNL)。NLJ利用被驱动表的索引提高效率,而BNL在未使用索引时可能导致大量扫描。文章分析了如何选择驱动表,以及MRR和BKA优化技术,揭示了性能影响因素和优化策略。
摘要由CSDN通过智能技术生成

目录

数据准备

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 算法的性能问题

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

一图胜千言

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值