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

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


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 条数据
在这里插入图片描述
t2表中 84 条数据,如图所示

在这里插入图片描述

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

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

1 我们先来看看 循环查询

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


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

在这里插入图片描述

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

  • 然后循环遍历这 10 行数据,从每一行 数据 C 中取出字段 id 的值; 执行select * from t2 where activity_user_id=id;(activity_user_id走的是索引树搜索) 把返回的结果和 C 构成结果集的一行。

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

在这里插入图片描述

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

满足 id 为13的有 35条数

在这里插入图片描述
这个过程中扫描35行数据

在这里插入图片描述

然后 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 条数据
在这里插入图片描述

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

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

在这里插入图片描述

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

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

此时 我们将 t2表中的 activity_user_id 索引删除

在这里插入图片描述
我们再查询一下

在这里插入图片描述
我们可以清楚的看到当不走索引搜索时,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 尽量不要用。


完毕

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

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

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

在这里插入图片描述

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页