MySQL两个表的亲密接触-连接查询的原理

数据库 其他数据库
MySQL对于被驱动表的关联字段没索引的关联查询,一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法,有 索引的情况下 NLJ 算法比 BNL算法性能更高。

关系型数据库还有一个重要的概念:Join(连接)。使用Join有好处,也会坏处,只有我们明白了其中的原理,才能更多的使用Join。切记不可以:

业务之上,再复杂的查询也在一个连表语句中完成。

敬而远之,DBA每次上报的慢查询都是连接查询导致的,我再也不用了。

连接简介

连接的本质

我们先来创建两个简单的表,再初始化一些数据

CREATE TABLE t1 (m1 int, n1 varchar(1));

CREATE TABLE t2 (m2 int, n2 varchar(1));

INSERT INTO t1 VALUES(1, 'a'), (2 , 'b') ,(3 ,'c') ;

INSERT INTO t2 VALUES(2 , 'b'), (3 , 'c '),(4 , 'd');

从本质上来说,连接就是把各个表的数据都取出来进行匹配,t1 和 t2 的两个表连接起来就是这样的:

连接语法:

select * from t1, t2;

如果乐意,我们可以连接任意数量的表。但是如果不加任何限制条件的话,这个数据量是非常大的,我们现实中使用都是会加上限制条件的。我们来看下下面这条语句

select * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 = 'c';

这个连接查询的执行过程大致如下

首先确定第一个需要查询 表称为驱动表(t1)

步骤1中从驱动表 (t1) 中每获得一条记录,都要去被驱动表 (t2) 中查询匹配。

从上面的步骤,可以看出上述的连表查询我们需要查询一次t1,两次t2。也就是说,两表的连接查询中,需要查询一次驱动表,被驱动表需要查询多次。

这里需要注意下,并不是将所有满足条件的驱动表记录先查询出来放到一个地方,然后再去被驱动表中查询,(如果满足条件的驱动表中的数据非常多,那要需要多大的内存呀。) 所以是每获得一条驱动表记录就去被驱动表中查询。

内连接和外连接

我们再来创建两个表,并插入一些数据

CREATE TABLE student ( 
number INT NOT NULL Auto_increment comment'学号',
name varchar (5) COMMENT '姓名',
major varchar (30) comment '专业',
PRIMARY KEY (number));

CREATE TABLE score (
number INT comment'学号',
subject varchar (30) COMMENT '科目',
score TINYINT comment '成绩',
PRIMARY KEY (number, subject));


INSERT INTO `student` (`number`, `name`, `major`)
VALUES ('20230301', '小赵', '计算机科学');
INSERT INTO `student` (`number`, `name`, `major`)
VALUES ('20230302', '小钱', '通信');
INSERT INTO `student` (`number`, `name`, `major`)
VALUES ('20230303', '小孙', '土木工程');

INSERT INTO `score` (`number`, `subject`, `score`)
VALUES ('20230301', '高等数学', '60');
INSERT INTO `score` (`number`, `subject`, `score`)
VALUES ('20230301', '英语', '70');
INSERT INTO `score` (`number`, `subject`, `score`)
VALUES ('20230302', '高等数学', '80');
INSERT INTO `score` (`number`, `subject`, `score`)
VALUES ('20230302', '英语', '90');

如果我们想把所有的学生的成绩都查出来,只需要这样执行:

select s1.number, s1.name, s1.major, s2.subject, s2.score 
from student as s1 , score as s2
where s1.number = s2.number;

有个问题就是小孙因为某些原因没有参加考试,所以在结果表中没有对应 的成绩记录。如果老师想查看所有学生的考试成绩,即使是缺考的学生 他们的成绩也应该展示出来。

为了解决这个问题,就有了内连接和外连接的概念:

  • 对于内连接的两个表,若驱动表中的记录在被驱动表找不到匹配的记录,则该记录不会加入到最后的结果集。前面提到的连接都是内连接。
  • 对于外连接的两个表,时驱动表中的记录在被驱动表中没有匹配的记录,也仍然需要加入到结果集。

MySQL 中,根据选取的驱动表的不同,外连接可以细分为

  • 左外连接 选取左侧的表为驱动表。
  • 右外连接·选取右侧的表为驱动表。

当我们使用外连接的时候 有时候我们也不想把驱动表的全部记录都加入到最后的结果集中,这个时候我们就要使用过滤条件了。

• WHERE 子句中的过滤条件:不论是内连接还是外连接 凡是不符合 WHERE 子句中过滤条件的记录都不会被加入到最后的结果集。

• ON 子句中的过滤条件:对于外连接的驱动表中的记录来说,如果无法在被驱动表中找到匹配 ON 子句 中过滤条件的记录 那么该驱动表记录仍然会被加入到结果集中,对应的被驱动表记录的各个字段使用NULL 值填充。

所以上述的需求我们可以左查询这样来做:

select s1.number, s1.name, s1.major, s2.subject, s2.score 
from student as s1 left join score as s2
on s1.number = s2.number;

语法:

#左连接
select * from t1 left join t2 on '连接条件' where '普通过滤条件'
#右连接
select * from t1 right join t2 on '连接条件' where '普通过滤条件'

内连接的另一种写法,也是常用写法

select s1.number, s1.name, s1.major, s2.subject, s2.score 
from student as s1 inner join score as s2
where s1.number = s2.number;

语法:

select * from t1 inner join t2 on '连接条件' where '过滤条件'

连接原理

上述说了这么多,知识简单回顾一下连接,左连接,右连接这些概念。接下来我们重点说一下 MySQL 采用了什么样的算法来进行表与表之前的连接。

Nested-Loop Join (嵌套循环连接) NLJ

前面我们已经介绍过了执行连接查询的大致步骤了,我们再来简单回顾一下

  • 步骤1:选取驱动表,使用相关的过滤条件,选取代价最低的单表访问方法来执行访问。
  • 步骤2:对步骤1中查询到的驱动表结果中的每一条记录,都分别在被驱动表中匹配符合条件的记录。
  • 如果有三个表,那么步骤2中得到的结果集就像是新的驱动表,然后第三个表就成为了驱动表,重复上述的过程。

整个过程就像是一个嵌套循环,所以这种连接方式称为 嵌套循环连接 ,这是最简单也是最笨的一种连接查询算法。大致处理过程如下:

for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}

需要注意的是对于获套循环连接算法法来说,每当我们从驱动表中得到了一条记录时,就根据这条记录立时到被驱动表中查询一次,如果得到了匹配的记录, 就把组合后 的记录发送给客户端,然后再到驱动表中获取下一条记录。这个过程将重复进行。

有什么方式可以优化吗

使用索引加快连接速度

这个是我们比较熟悉的方式,也是相对来说最有用的方式,在被驱动表上创建合适的索引,只返回必要的字段等都可以起到一些优化的作用。

Block Nested-Loop Join(块嵌套循环连接)BNL

每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取出一条与其匹配,匹配结束后清楚内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录加载到内存匹配,如果这个被驱动表中的数据特别多而且不能使用索引进行访问,那就相当于要从磁盘上读这个表好多次,这个IO的代价就非常大了。所以我们得想办法,尽量减少被驱动表的访问次数,于是就出现了下面这种方式。

不再是逐条获取驱动表的数据,而是一块一块的获取,引入join buffer 缓冲区, 将驱动表join 相关的部分数据列(大小受join buffer的限制)缓存到 join buffer中,然后开始扫描被驱动表,被驱动表的每一条记录一次性和join buffer中所有的驱动表记录进行匹配(内存中操作)。将简单嵌套循环中的多次比较合并成一次,降低了备驱动表的访问频率。

这里缓存的不只是关联表的列,select后面的列也会缓存起来。所以查询的时候尽量减少不必要的字段,可以让join buffer中可以存放更多的列。

join_buffer_size的最大值在32为系统中可以申请4G,在64为操作系统中可以申请大于4G的空间。

MySQL对于被驱动表的关联字段没索引的关联查询,一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法,有 索引的情况下 NLJ 算法比 BNL算法性能更高。

关联查询优化总结

  1. 超过三个表禁止 join。【阿里巴巴JAVA开发手册】
  2. 需要 join 的字段,数据类型必须绝对一致;【阿里巴巴JAVA开发手册】
  3. 多表关联查询时,保证被关联的字段需要有索引,尽量选择NLJ算法。【阿里巴巴JAVA开发手册】
  4. 小表驱动大表,写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间
责任编辑:武晓燕 来源: 今日头条
相关推荐

2015-11-03 11:39:18

清华大学OpenStackEasyStack

2010-05-25 13:17:28

MySQL数据库

2009-05-15 08:59:32

Windows 7微软操作系统

2018-08-16 11:30:12

JavaCPU缓存

2009-06-08 16:00:00

ASP.NET 3.5

2017-08-08 12:50:51

Serverless云端数据库

2013-02-25 09:43:22

LambdasJava8

2020-05-06 07:18:59

数据中台架构

2015-08-05 10:54:49

2011-06-04 17:31:24

台式机评测

2009-11-27 14:18:37

Scala

2022-04-11 10:21:35

GNU命令行工具

2010-01-29 09:01:40

.NET 4.0

2010-11-22 15:48:40

MySQL修复表

2009-10-20 09:57:31

Windows 7系统崩溃

2013-09-22 11:29:32

2015-07-27 17:48:20

麦迪

2018-06-12 11:19:28

2010-10-15 11:05:31

MYSQL查询结果

2011-03-01 13:07:36

MySQLOrder by查询
点赞
收藏

51CTO技术栈公众号