MySQL有哪几种join方式,底层原理是什么

本文深入解析了SQL中的三种主要联接类型:左联接、右联接和内联接,并通过具体实例展示了每种联接如何操作两个表的数据。同时,文章还探讨了MySQL的Join算法,包括Simple Nested-Loop Join、Index Nested-Loop Join和Block Nested-Loop Join,以及它们在数据库性能上的影响。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

下面是例子分析
表A记录如下:
aID        aNum
1           a20050111
2           a20050112
3           a20050113
4           a20050114
5           a20050115 
 
表B记录如下:
bID        bName
1            2006032401
2           2006032402
3           2006032403
4           2006032404
8           2006032408 
 
创建这两个表SQL语句如下:
CREATE TABLE  a
  aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
  aNum char( 20 )
)
CREATE TABLE b(
  bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  bName char( 20 )
) 
 
INSERT INTO a
VALUES ( 1, 'a20050111' ) , ( 2, 'a20050112' ) , ( 3, 'a20050113' ) , ( 4, 'a20050114' ) , ( 5, 'a20050115' ) ; 
 
INSERT INTO b
VALUES ( 1, ' 2006032401' ) , ( 2, '2006032402' ) , ( 3, '2006032403' ) , ( 4, '2006032404' ) , ( 8, '2006032408' ) ; 
 
实验如下:
1.left join(左联接) 
 
sql语句如下:
SELECT * FROM a
LEFT JOIN  b
ON a.aID =b.bID 
 
结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
5            a20050115         NULL       NULL
(所影响的行数为 5 行) 
 
结果说明:
        left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL. 
 
2.right join(右联接) 
 
sql语句如下:
SELECT  * FROM a
RIGHT JOING b
ON a.aID = b.bID 
 
结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
NULL    NULL                   8              2006032408
(所影响的行数为 5 行) 
 
结果说明:
        仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充. 
 
3.inner join(相等联接或内联接) 
 
sql语句如下:
SELECT * FROM  a
INNER JOIN  b
ON a.aID =b.bID 
 
等同于以下SQL句:
SELECT *
FROM a,b
WHERE a.aID = b.bID 
 
结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404 
 
结果说明:
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录. 

mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:

1.Simple Nested-Loop Join :

r为驱动表,s为匹配表,可以看到从r中分别取出r1、r2、......、rn去匹配s表的左右列,然后再合并数据,对s表进行了rn次访问,对 数据库 开销大

2.Index Nested-Loop Join(索引嵌套):

这个要求非驱动表(匹配表s)上有索引,可以通过索引来减少比较,加速查询。

在查询时,驱动表(r)会根据关联字段的索引进行查找,挡在索引上找到符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表查询。

如果非驱动表(s)的关联健是主键的话,性能会非常高,如果不是主键,要进行多次回表查询,先关联索引,然后根据二级索引的主键ID进行回表操作,性能上比索引是主键要慢。

3.Block Nested-Loop Join:

如果有索引,会选取第二种方式进行join,但如果join列没有索引,就会采用Block Nested-Loop Join。

可以看到中间有个join buffer缓冲区,是将驱动表的所有join相关的列都先缓存到join buffer中,然后批量与匹配表进行匹配,将第一种多次比较合并为一次,降低了非驱动表(s)的访问频率。

默认情况下join_buffer_size=256K,在查找的时候 MySQL 会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

 

参考文献:

【1】mysql join详解

【2】MySQL Join的底层实现原理

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值