写点什么

SQL 优化(四):如何使用 join

作者:hungxy
  • 2023-07-04
    广东
  • 本文字数:1775 字

    阅读完需:约 6 分钟

SQL 优化(四):如何使用 join

在工作的时候经常听到的一句话就是,“这条 SQL 因为 join 了很多表,导致查询速度比较慢”,可以从侧面反映出,join语句对性能的影响是比较大的,而且大部分人不知道如何进行优化。这片文章我们来讲讲join的执行过程,以及改如何对join语句进行优化


AB两张表,表的初始化语句如下

CREATE TABLE `A` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `a`(`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE B like A;
复制代码

接着我们向表A插入 100 条数据,向表B插入 1000 条数据

Index Nested-Loop Join

我们看下这条语句

select * from A straight_join B on A.a = B.a
复制代码

explain 查看该语句的执行计划



可以看到,该语句对 A 表做了全表扫描,并使用了 B 表上的索引,完整的执行流程如下:

  1. 从 A 表中读取一行数据 R

  2. 从 R 中取出 a 字段,根据 a 字段到 B 表中查询(使用B表的索引a

  3. 从 B 表中取出 B 表的数据行

  4. 重复 1~3,直到 A 表遍历结束


可以看到,整个查询流程扫描了 200 行,其中对 A 表做全表扫描了 100 行,另外 100 行是对 B 表的嵌套查询中产生的


对于这种遍历驱动表A,然后根据A中每一行的值,去被驱动表B查找,并使用驱动表B的索引的搜索过程,由于在形式上与我们写代码时的嵌套查询类似,因此该过程称之为 Index Nested-Loop Join (简称 NLJ)

Simple Nested-Loop join

如果我们将查询语句写成这样

select * from A straight_join B on A.a = B.b
复制代码

由于 B 表的 b 字段上没有索引,因此 sql 的执行流程如下

  1. 从 A 表中读取一行数据 R

  2. 从 R 中取出 a 字段,根据 a 字段到 B 表中查询(对 B 表进行全表遍历)

  3. 从 B 表中取出 B 表的数据行

  4. 重复 1~3,直到 A 表遍历结束


NLJ不同的是,由于进行A.a=B.b的时候,无法使用索引,所以整个查询总共扫描了 1000 * 100 = 10w 行,这种查询方式也被称为 Simple Nested-Loop Join

Block Nested-Loop Join

由于Simple Nested-Loop Join对两张表都做了全表扫描,比较笨重,在性能上表现很差。为了解决这个问题,MySQL 使用了另外一种叫做Block Nested-Loop Join的算法(简称 BNL)


查看select * from A straight_join B on A.a = B.b;的执行计划,可以看到,MySQL 并没有使用Simple Neststed-Loop Join,而是使用Block Nested Loop Join



BNL的执行流程如下:

  1. 把 A 表的数据读出来放到join_buffer中,由于我们是select *,所以相当于把整个 A 表放入到内存中

  2. 遍历 B 表,取出 B 表的每行数据,将其与内存中的 A 表进行比对,满足 join 条件的,作为结果集的一部分返回


这时你可能会说,如果 A 表的数据太多,内存放不下怎么办呢?这也很简单,只需要对 A 表进行分段缓存就行啦,这时候的执行流程如下:

  1. 扫描 A 表,顺序将 A 表的数据加入到join_buffer中,直到join_buffer满了,执行第二步

  2. 扫描 B 表,取出 B 表的每行数据,将其与join_buffer中的数据进行比对,满足 join 条件的,作为结果集的一部分返回

  3. 清空join_buffer,继续读取 A 表的数据,重复 1、2 步,直到遍历结束


对 A 表进行分段(分块)缓存,这也是Block Nested-Loop Join名字的由来。可以看到,Block Nested-Loop JoinSimple Nested-Loop Join的唯一区别,就是 NBL 对驱动表进行了缓存,将嵌套匹配的操作都放到内存中进行,大大提升了关联查询的性能

10w 次内存操作跟 10w 次磁盘操作,性能相差还是很大的

如何优化 join

了解 join 的三种查询算法后,可以知道,NLJ 的查询性能是最快的。所以第一个优化方案是:尽量使用索引字段进行关联,让 MySQL 能使用 NLJ


还有一点是,不管哪种查询算法,都对驱动表做了全表扫描,所以我们在关联的时候,应该用“小表”作为驱动表,“大表”作为被驱动表,这样就能减少全表扫描的行数了,提高性能

注意,“小表”、“大表” 指的不一定是表的数据量大小,而是在查询的时候,扫描行数多的称之为“大表”,反之称之为“小表”


如 t1 表有 10w 数据,t2 表有 1000 数据,那么可以用 t2 表做驱动表,sql 可以这样写

select * from t2 straight_join t1 on t2.a = t1.a;
复制代码

如果 SQL 带 where 条件,where 后的 t1 表只有 100 条数据,这时候,t1 就是“小表”,t2 则是“大表”,那么就应该用 t1 做驱动表

select * from t1 straight_join t2 on t1.a = t2.awhere t1.id <= 100;
复制代码


关注公众号:huangxy,一起学习,一起进步

发布于: 刚刚阅读数: 5
用户头像

hungxy

关注

还未添加个人签名 2018-07-19 加入

还未添加个人简介

评论

发布
暂无评论
SQL 优化(四):如何使用 join_hungxy_InfoQ写作社区