MySql系列：JOIN的原理和算法小总结 | 八月更文挑战

Sunny_Chen
1,063 阅读6分钟

这是我参与8月更文挑战的第7天，活动详情查看：8月更文挑战

📖前言

能把复杂的知识讲的简单很重要

在学习的过程中我们看过很多资料、视频、文档等，因为现在资料视频都较多所以往往一个知识点会有多种多样的视频形式讲解。除了推广营销以外，确实有很多人的视频讲解非常优秀，例如李永乐老师的短视频课，可以在一个黑板上把那么复杂的知识，讲解的那么容易理解，那么透彻。而我们学习编程的人也是，不只是要学会把知识点讲明白，也要写明白。

以下所写内容均与以前的文章有联系可以前往博文查看，陈永佳的博客

💕什么是JOIN？

JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接。这里描述先甩出一张用烂了的图，然后插入测试数据。

用烂了的图

🚀笛卡尔积：CROSS JOIN

要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

👏建表：（想查询先建表）
CREATE DATABASE db0206;
USE db0206;

CREATE TABLE `db0206`.`tbl_dept`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30),
  `locAdd` VARCHAR(40),
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4;

CREATE TABLE `db0206`.`tbl_emp`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20),
  `deptId` INT(11),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`deptId`) REFERENCES `db0206`.`tb_dept`(`id`)
) ENGINE=INNODB CHARSET=utf8mb4;
/*插入数据*/
INSERT INTO tbl_dept(deptName,locAdd) VALUES('CC',21);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MM',21);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('SC',22);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('SC',23);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('EH',65);

INSERT INTO tbl_emp(NAME,deptId) VALUES('c1',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('c2',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('c3',1);

INSERT INTO tbl_emp(NAME,deptId) VALUES('m5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('m6',2);

INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);

INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);

下列查询与该表数据有异请自行忽略

内连接：

内连接文氏图

  • 执行的sql语句以及执行的查询结果：两个内连表的共有部分 在这里插入图片描述
左外连接：

在这里插入图片描述

  • 执行的sql语句以及执行的查询结果：左表的独有部分， 右表的为空部分置为空 在这里插入图片描述
右外连接：

在这里插入图片描述

  • 执行的sql语句以及执行的查询结果： 在这里插入图片描述
左连接：

在这里插入图片描述

  • 执行的sql语句以及执行的查询结果： 在这里插入图片描述
右连接：

在这里插入图片描述

  • 执行的sql语句以及执行的查询结果： 在这里插入图片描述
全连接：

在这里插入图片描述

  • 执行的sql语句以及执行的查询结果 在这里插入图片描述
两张表中都没有出现的数据集：

在这里插入图片描述

  • 执行的sql语句以及执行的查询结果 在这里插入图片描述

表连接算法

ested Loop Join（NLJ）算法：

首先介绍一种基础算法:NLJ，嵌套循环算法。循环外层是驱动表，循坏内层是被驱动表。驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录，然后从头扫描被驱动表，逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。被驱动表查找完后，再从驱动表中取出第二个记录，然后从头扫描被驱动表，逐一查找与驱动表第二条记录匹配的记录，连接起来形成结果表中的一条记录。重复上述操作，直到驱动表的全部记录都处理完毕为止。这就是嵌套循环连接算法的基本思想，伪代码如下。

foreach row1 from t1
    foreach row2 from t2
        if row2 match row1 //row2与row1匹配，满足连接条件
            join row1 and row2 into result //连接row1和row2加入结果集

首先加载t1，然后从t1中取出第一条记录，之后加载t2表，与t2表中的记录逐个匹配，连接匹配的记录。

Block Nested Loop Join(BNLJ)算法：

再介绍一种高级算法：BNLJ，块嵌套循环算法，可以看作对NLJ的优化。大致思想就是建立一个缓存区，一次从驱动表中取多条记录，然后扫描被驱动表，被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配，如果匹配则连接并加入结果集。缓冲区越大，驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。（博主用的很少哈哈！）

影响性能的因素

1.内循环的次数：现在考虑这么一个场景，当t1有100条记录，t2有10000条记录。那么，t1驱动t2与t2驱动t1，他们之间在效率上孰优孰劣？如果是单纯的分析指令执行次数，他们都是100*10000,但是考虑到加载表的次数呢。首先分析t1驱动t2，t1表加载1次，t2表需要加载100次。然后分析t2驱动t1，t2表首先加载1次，但是t1表要加载10000次。所以，t1驱动t2的效率要优于t2驱动t1的效率。小表驱动大表能够减少内循环的次数从而提高连接效率。 另外，如果使用Block Nested Loop Join算法的话，通过扩大一次缓存区的大小也能减小内循环的次数。由此又可得，设置合理的缓冲区大小能够提高连接效率

2.快速匹配：扫描被驱动表寻找合适的记录可以看做一个查询操作，如何提高查询的效率呢？建索引啊！由此还可得出，在被驱动表建立索引能够提高连接效率

3.排序：假设t1表驱动t2表进行连接操作，连接条件是t1.id=t2.id，而且要求查询结果对id排序。现在有两种选择，方式一[...ORDER BY t1.id]，方式二[...ORDER BY t2.id]。如果我们使用方式一的话，可以先对t1进行排序然后执行表连接算法，如果我们使用方式二的话，只能在执行表连接算法后，对结果集进行排序（Using temporary），效率自然低下。由此最后可得出，优先选择驱动表的属性进行排序能够提高连接效率。

✨引用

《数据库系统概论》：my.oschina.net/xinxingegey…

😎如何使用

5.6版本及以后，优化器管理参数 optimizer_switch 中中的 block_nested_loop 参数控制着BNL是否被用于优化器。默认条件下是开启，若果设置为off，优化器在选择 join方式的时候会选择NLJ算法。

🎉总结：

  • 关于MySql的优化需要各位小伙伴们在实际开发过程中去运用去总结。
  • 希望各位小伙伴站在前人的肩膀上可以走的更高更远！
  • 喜欢我的小伙伴记得加关注、点赞哦！
avatar
文章
阅读
粉丝