MySql 你知道什么情况下适合使用Join 联表查询吗？

比眉伴天荒 2023-10-07 16:01 6阅读 0赞

> 志在巅峰的攀登者，不会陶醉在沿途的某个脚印之中，在码农的世界里，优美的应用体验，来源于程序员对细节的处理以及自我要求的境界，年轻人也是忙忙碌碌的码农中一员，每天、每周，都会留下一些脚印，就是这些创作的内容，有一种执着，就是不知为什么，如果你迷茫，不妨来瞅瞅码农的轨迹。

*  [优美的音乐节奏带你浏览这个效果的编码过程][Link 1]
 *  [坚持每一天，是每个有理想青年的追求][Link 2]
 *  [追寻年轻人的脚步，也许你的答案就在这里][Link 3]
 *  [如果你迷茫 不妨来瞅瞅这里][Link 4]

--------------------

#### 1 前言 ####

如下我这里有两张表，表t1为某活动的报名信息表，部分建表 DDL 如下：

CREATE TABLE `t1` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `activity_id` bigint(20) DEFAULT '0' COMMENT '关联的活动信息',
      `user_id` bigint(20) DEFAULT '0' COMMENT '报名人的ID',
      `create_time` datetime DEFAULT NULL COMMENT '报名时间',
      `remark` varchar(255) DEFAULT NULL COMMENT '备注',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=18  COMMENT='活动报名表';

表t2为 投票信息表，也就是说 t2表中保存的是给t1表中的报名用户投票记录信息，部分建表 DDL 如下：

CREATE TABLE `t2` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `activity_user_id` bigint(20) DEFAULT '0' COMMENT '关联的活动报名信息 t1表中的id',
      `vote_user_id` bigint(20) DEFAULT '0' COMMENT '投票者的信息',
      `create_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `activity_user_id`(`activity_user_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=226 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户投票记录表';

现在 我 t1 表中有 10 条数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]  
t2表中 84 条数据，如图所示

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]

现在有一需求就是查询 户的投票记录以及报名信息，那么我们需要从 t1表中获取报名信息，然后再从t2表中获取每个用户的投票记录。

那么无非就是有两种查询思维，一种是先取t1，再循环取t2，另一种是使用 join ，那到底使用哪种，你是怎么决定的呢？？？

#### 1 我们先来看看 循环查询 ####

在不使用join的情况下，我们需要先从t1表中查出这用户的报名信息，然后循环从t2表中查询投票信息，这个过程如下

--------------------

*  执行select \* from t1 ，每一行数据记为 C,这一步会对t1表进行全表扫描，我们t1表中是10条数据，全表扫描10行

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]

> type = ALL，全表扫描，MYSQL扫描全表来找到匹配的行

*  然后循环遍历这 10 行数据，从每一行 数据 C 中取出字段 id 的值; 执行select \* from t2 where activity\_user\_id=id;（activity\_user\_id走的是索引树搜索） 把返回的结果和 C 构成结果集的一行。

在表t2中，满足 t1表中id为12的有49条数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 3]  
这个过程中 扫描 49行数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 4]

> type = ref ，使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

满足 id 为13的有 35条数

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 5]  
这个过程中扫描35行数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 6]

然后 t1 表中其他 8条数据在表 t2中没有记录，所以查询过程中各扫描一行。

在这个过程中，这样查询下来，需要在业务代码中自己组装循环查询，t1表扫描 10行，t2表扫描 35 + 49 + 8 = 92，查询完成总共扫描 102行数据。

--------------------

#### 2 使用 join 时 ####

当使用 join 时，可以这样写 ：（使用 STRAIGHT\_JOIN 保证固定联表顺序）

SELECT
    	* 
    FROM
    	t1  STRAIGHT_JOIN  t2 
    	ON (t1.id = t2.activity_user_id )

满足条件的有 84 条数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 7]

这个语句的执行流程是这样的:

*  第一步 从表 t1 中读入一行数据 C;
 *  第二步从数据行 C 中，取出 id 字段到表 t2 的 activity\_user\_id 索引树中搜索;
 *  第三步 取出表 t2 中满足条件的行，跟 C 组成一行，作为结果集的一部分;
 *  第四步 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

--------------------

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 8]

这个过程是先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 id 值，去表 t2 中查找满 足条件的记录，这个过程称为 “Index Nested-Loop Join”，简称 NLJ。

在这个过种中，t1表是驱动表，是走全表扫描，t2是被驱动表，是走树搜索，所以在 join过程中，应该让小表作驱动表。

此时 我们将 t2表中的 activity\_user\_id 索引删除

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 9]  
我们再查询一下

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 10]  
我们可以清楚的看到当不走索引搜索时，t1与t2都走了全表扫描，

执行过程如下

*  第一步扫描表 t1，顺序读取数据行放入 join\_buffer 中，假设放完第 3 行 join\_buffer 满了，继续 第二步操作;
 *  第二步 扫描表 t2，把 t2 中的每一行取出来，跟 join\_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回;
 *  第三步 清空 join\_buffer;
 *  第四步 继续扫描表 t1，顺序读取最后的 7 行数据放入 join\_buffer 中，继续执行第 二 步。

这时候由于表 t1 被分成了两次放入 join\_buffer 中，导致表 t2 会被扫描两次，这个过程就是 “Block Nested-Loop Join”。

显然 这两种情况 “Index Nested-Loop Join” 与 “Block Nested-Loop Join” 分析得出，如果可以使用到被驱动表中的索引，就可以使用 join 来查询。

如果无法使用到被驱动表的索引查询，这样可能要扫描被驱动表很多次，会占用大量的系统资源，所以这种情况下 join 尽量不要用。

--------------------

完毕

不局限于思维，不局限语言限制，才是编程的最高境界。

以小编的性格，肯定是要录制一套视频的，随后会上传

有兴趣 你可以关注一下 [西瓜视频 — 早起的年轻人][Link 5]

![在这里插入图片描述][20201031094959816.gif_pic_center]

[Link 1]: https://www.bilibili.com/video/BV1QX4y1G7Sh/
[Link 2]: https://www.zhihu.com/zvideo/1348960604003180544
[Link 3]: https://www.ixigua.com/6933784858019758595
[Link 4]: https://biglead.blog.csdn.net/article/details/93532582
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210325221119572.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210325221205905.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210325221715528.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210325221923866.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210325222136486.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210325222210830.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210325222253855.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20210325222654351.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20210325222726315.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20210325223803342.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20210325223923403.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[Link 5]: https://www.toutiao.com/c/user/token/MS4wLjABAAAAYMrKikomuQJ4d-cPaeBqtAK2cQY697Pv9xIyyDhtwIM/
[20201031094959816.gif_pic_center]: https://img-blog.csdnimg.cn/20201031094959816.gif#pic_center