写点什么

你真的了解 inner join 吗

作者:lixiaofeng
  • 2021 年 12 月 13 日
  • 本文字数:1220 字

    阅读完需:约 4 分钟

你真的了解inner join吗

我们在写 SQL 语句的时候,通常的表连接,通常是 inner join, left join, right join 等等, 其实这些都是逻辑连接,即我们的业务逻辑上的连接。

数据库引擎执行这些连接的方式, 成为物理连接, 也就是说,数据库引擎到底是怎么执行这些连接的。

物理连接主要是三种方式:循环连接, 合并连接,哈希连接。SQL Server, Oracle, MySQL 都支持这三种连接方式(MySQL8 也支持了哈希连接)。


循环连接,把左右两张表的,进行嵌套循环,对比出符合要求的语句。

循环连接的伪代码:


for each row in t1    	for each row in t2          	if t1.row.column1=t2.row.column                	return t1.row,t2.row
复制代码

例如:有两种表。t1:

t2:


我们的查询语句是:

select * from t1 inner join t2 on t1.id=t2.id

若使用循环连接,那么,执行器首先取出 t1 中的第一行的 id=1, 分别和 t2 表的各个行的 id 进行对比, 把符合条件的数据返回;然后取出 t1 中的第二行的 id=2,, 分别和 t2 表的各个行的 id 进行对比, 把符合条件的数据返回;......直到 t1 表的最后一行。所以,循环连接,需要对比 12 次(两张表数据行数的乘积)。实际上,这种方法的效率并不高,一般在两张表中数据不太多的时候使用。


合并连接, 合并连接一般指的是,左右两边的表 join 的连接列都有序的情况下,连接优化的一种方式。因为两边都有序,所以理论上,可以大量的减少对比次数,一会看例子就明白了。

合并连接的伪代码:

get row1 from t1get row2 from t2while row1 not null and row2 not null    	if row1.id=row2.id          	return row1, row2       else if row1.id<row2.id           	row1 : t1.nextRow         else            	row2 : t2.nextRow
复制代码

例如,我们还是上例中的数据和语句:

select * from t1 inner join t2 on t1.id=t2.id
复制代码

因为两种表确实是按照 id 有序, 所以可以使用哈希连接。执行过程如下:

第一次对比:t1 的第一行和 t2 的第一行, t1.id<t2.id, t1 获取下一行;

第二次对比:t1 的第二行和 t2 的第一行, t1.id>t2.id, t2 获取下一行;

第三次对比:t1 的第二行和 t2 的第二行, t1.id<t2.id, t1 获取下一行;

第四次对比:t1 的第三行和 t2 的第二行, t1.id>t2.id, t2 获取下一行;

第五次对比:t1 的第三行和 t2 的第三行, t1.id<t2.id, t1 获取下一行;

t1 表没有下一行了,执行结束。

假如 t1 有 m 行数据,t2 有 n 行数据。循环连接的次数=m*n。合并连接的次数,最小值的 m,n 中的较小值;最大值是 m*n。想一想,什么情况下是最小值?什么情况是最大值?


哈希连接, 哈希连接的分为两步,第一步是对 t1 表的各个行的 id(匹配列)求哈希值;第二步是对 t2 上的 id 列求哈希值,并进行哈希匹配。

哈希连接的伪代码如下:

for each row in t1    	calculate hash value hash(t1.row.id)      insert hash value into hash bucket hashT1for each row in t2      	if hashT1[hash(t2.row.id)] is not null           return t2.row, hashT1[hash(t2.row.id)]
复制代码


三种连接当时对比:


发布于: 17 小时前阅读数: 12
用户头像

lixiaofeng

关注

还未添加个人签名 2018.04.25 加入

还未添加个人简介

评论

发布
暂无评论
你真的了解inner join吗