详解 Mysql LEFT JOIN和JOIN查询区别及原理

置顶 已于 2023-05-03 15:31:24 修改
阅读量2.4w 收藏 93
点赞数 30
分类专栏： 数据库 mysql 面试精选 文章标签： 数据库 mysql join left join
于 2020-06-28 10:42:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/agonie201218/article/details/106993948
版权
mysql 同时被 3 个专栏收录
94 篇文章 19 订阅
订阅专栏
面试精选
81 篇文章 13 订阅
订阅专栏
数据库
46 篇文章 3 订阅
订阅专栏

一、Join查询原理

查询原理：MySQL内部采用了一种叫做 nested loop join（嵌套循环连接）的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要，驱动表的数据小可以显著降低扫描的行数。

一般情况下参与联合查询的两张表都会一大一小，如果是join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表。简单来说，驱动表就是主表，left join 中的左表就是驱动表，right join 中的右表是驱动表。

二.Nested-Loop Join

在Mysql中，使用Nested-Loop Join的算法思想去优化join，Nested-Loop Join翻译成中文则是“嵌套循环连接”。

举个例子：
select * from t1 inner join t2 on t1.id=t2.tid
（1）t1称为外层表，也可称为驱动表。
（2）t2称为内层表，也可称为被驱动表。

//伪代码表示：
List<Row> result = new ArrayList<>();
for(Row r1 in List<Row> t1){
	for(Row r2 in List<Row> t2){
		if(r1.id = r2.tid){
			result.add(r1.join(r2));
		}
	}
}

mysql只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种：

Simple Nested-Loop Join：SNLJ，简单嵌套循环连接
Index Nested-Loop Join：INLJ，索引嵌套循环连接
Block Nested-Loop Join：BNLJ，缓存块嵌套循环连接
在选择Join算法时，会有优先级，理论上会优先判断能否使用INLJ、BNLJ：

Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

三. Simple Nested-Loop Join：

如下图，r为驱动表，s为匹配表，可以看到从r中分别取出r1、r2、…、rn去匹配s表的左右列，然后再合并数据，对s表进行了rn次访问，对数据库开销大。如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 =1亿次，这种查询效率会非常慢。

在这里插入图片描述

所以Mysql继续优化，然后衍生出Index Nested-LoopJoin、Block Nested-Loop Join两种NLJ算法。在执行join查询时mysql会根据情况选择两种之一进行join查询。

四.Index Nested-LoopJoin（减少内层表数据的匹配次数）

  1. 索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了 join的性能：

    原来的匹配次数 = 外层表行数 * 内层表行数
    优化后的匹配次数= 外层表的行数 * 内层表索引的高度

  2. 使用场景：只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接。

  3. 由于用到索引，如果索引是辅助索引而且返回的数据还包括内层表的其他数据，则会回内层表查询数据，多了一些IO操作。

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

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

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

在这里插入图片描述

五.Block Nested-Loop Join（减少内层表数据的循环次数）

  1. 缓存块嵌套循环连接通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。

  2. 当不使用Index Nested-Loop Join的时候(内层表查询不适用索引)，默认使用Block Nested-Loop Join。

  3. 什么是Join Buffer？
    （1）Join Buffer会缓存所有参与查询的列而不是只有Join的列。
    （2）可以通过调整join_buffer_size缓存大小
    （3）join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
    （4）使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

在这里插入图片描述

六.如何优化Join速度

用小结果集驱动大结果集，减少外层循环的数据量：
如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。
为匹配的条件增加索引：争取使用INLJ，减少内层表的循环次数
增大join buffer size的大小：当使用BNLJ时，一次缓存的数据越多，那么外层表循环的次数就越少
减少不必要的字段查询：
（1）当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少；
（2）当用到INLJ时，如果可以不回表查询，即利用到覆盖索引，则可能可以提示速度。

实例：

假设两张表a 和 b

在这里插入图片描述

其中b的关联有comments_id，所以有索引。

1.join

在这里插入图片描述

使用的是Index Nested-Loop Join，先对驱动表a的主键筛选，得到一条，然后对非驱动表b的索引进行seek匹配，预计得到一条数据。

下面这种情况没用到索引：

在这里插入图片描述

使用Block Nested-Loop Join，如果b表数据少，作为驱动表，将b的需要的数据缓存到join buffer中，批量对a表扫描

2.left join：

在这里插入图片描述

这里用到了索引，所以会采用Index Nested-Loop Join，因为没有筛选条件，会选择一张表作为驱动表去进行join，去关联非驱动表的索引。

如果加了条件

在这里插入图片描述

就会从驱动表筛选出一条来进行对非驱动表的匹配。

left join：会保全左表数据，如果右表没相关数据，会显示null
light join：会保全右表数据，如果左表没相关数据，会显示null
inner join：部分主从表，结果会取两个表针对on条件相匹配的最小集

二、使用原则

尽量使用inner join,避免left join 和NULL。

三、on和where的使用区别

首先要明确一点，where中的条件拿到on里去出来的结果是不同的，如下：

在这里插入图片描述
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

Great:

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

一、总结

一句话总结：

连表操作时：先根据查询条件和查询字段确定驱动表，确定驱动表之后就可以开始连表操作了，然后再在缓存结果中根据查询条件找符合条件的数据

1、找出所有在左表，不在右表的纪录？

我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表，不在右表的纪录， 注意用来判断的那列必须声明为not null的。
如：select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL
注意:

1.列值为null应该用is null 而不能用=NULL
2.这里a.user_id 列必须声明为 NOT NULL 的.

2、using()如何使用？

on a.c1 = b.c1 等同于 using(c1)

3、连表时候，INNER JOIN 和 , (逗号) 的关系是什么？

INNER JOIN 和 , (逗号) 在语义上是等同的

参考

https://blog.csdn.net/agonie201218/article/details/106887646
https://blog.csdn.net/agonie201218/article/details/107026646
https://blog.csdn.net/agonie201218/article/details/106882132
https://www.iteye.com/blog/luxuryzh-1976004
MySQL百万级、千万级数据多表关联SQL语句调优

Young丶
关注
专栏目录
MySQL Left JOIN时指定NULL列返回特定值详解
09-10
在SQL查询中，LEFT JOIN操作用于连接两个或更多表，并保留左表（即第一个表）的所有记录，即使右表没有匹配的记录。当右表的某些字段在LEFT JOIN后为NULL时，我们可以利用函数来将这些NULL值替换为特定值，如0或其他...
mysql数据库中 join 和 left join 的区别
weixin_45151960的博客
02-06 1万+
join等价于inner join内连接抄，是返回两个表中都有的符合条件的行。 left join左连接，是返回左袭表知中所有的行及右表中符合条件的行。 right join右连接，是返回右表中所有的行及左表中符合条件的行。 full join全连接，是返回左表中所有的行及右表中所有的行，并按条件连接。 通常情况下，left join肯定比inner join返回的行数多道。 上面只是对各个连接方式的大致介绍，下面将具体分析下常用的join和left join的区别和含义 left join(左联接) 返回
mysql join left join_详解 Mysql LEFT JOIN和JOIN查询区别及原理
weixin_42494796的博客
02-26 1261
一、Join查询原理查询原理：MySQL内部采用了一种叫做 nested loop join(嵌套循环连接)的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询...
MySQL9个连接：left join、inner join等
最新发布
2301_76419561的博客
09-26 585
在数据库管理系统中，尤其是在使用MySQL这类关系型数据库时，表之间的连接（Joins）是一种非常基础且强大的数据操作方式，它允许我们将来自不同表的数据基于某些共同的属性（通常是主键和外键关系）合并起来。MySQL提供了多种类型的连接操作，每种连接都有其特定的用途和结果集特点。INNER JOINLEFT JOIN（或）、RIGHT JOIN（或）、（虽然MySQL直接不支持，但可以通过其他方式实现）、CROSS JOIN（或），以及简要提及SELF JOINUSING JOIN和ON JOIN。
join，left join区别及使用过程中遇到的问题
weixin_43375482的博客
08-09 1541
一、概念： 把left join左边的表的记录全部找出来。系统会先用表A和表B做个笛卡儿积，然后以表A为基表，去掉笛卡儿积中表A部分为NULL的记录。最后形成你的结果。 进行左连接时，就有涉及到主表、辅表，这时主表条件写在WHERE之后，辅表条件写在ON后面。 二、图解 INNER JOIN 关键字在表中存在至少一个匹配时返回行。 LEFT JOIN 关键字从左表（table1）返回所有的行，即...
一分钟让你搞明白 left join、right join和join的区别
热门推荐
Li_Jian_Hui_的博客
04-27 12万+
left join、join、right join和inner join等等各种join的区别。网上搜，最常见的就是一张图解图，如下： 一张图道清所有join的区别 首先，我们先来建两张表，第一张表命名为kemu，第二张表命名为score： 一、left join 顾名思义，就是“左连接”，表1左连接表2，以左为主，表示以表1为主，关联上表2的数据，查出来的结果显示左边的所有数据，然后右边显...
SQL中的各种JOIN
03-22 1万+
JOIN类型用法说明注意事项INNER JOIN返回满足连接条件的交集，即返回同时存在于两个表中的行简为JOIN- 连接条件需要明确指定- 如果有多个连接条件，可以使用AND或者OR来组合- 如果连接条件不满足，对应的行将被排除在结果中LEFT JOIN返回左表中的所有行，以及右表中满足连接条件的匹配行。如果右表中没有匹配的行，则为NULL- 左表中的所有行都会被返回，无论是否有匹配的行- 右表中没有匹配的行将显示为NULL。
细谈left join和join
爱敲代码的零元
09-09 5234
相信大家都知道在MySQL中的join、left join和right join，在业务中，需要两表联查总会用到联合查询语句。但大家却有可能并不太了解他们底层是如何进行处理的，也就无法在业务量大的情况下，去优化这些sql语句。本节我就为大家通过执行流程层面进行一些讲解，相信大家看完本篇文章对以后的优化处理也就不会毫无头绪了。
join、inner join、left join、right join、outer join的区别
CoderYJ
03-19 2万+
什么是联结？ union、intersect等集合运算，它的特征是以 “行” 为单位进行操作，通俗点说，就是进行这些集合运算，会导致记录行数的增减，使用union会增加记录行数，使用 intersect 或 expect 会减少行记录，集合运算不会导致 “列” 数量的改变。而联结(join) 操作，就是讲其他表中的列添加过来，进行 “列添加”的运算。当需要从多张表中取出不同的列组成一个新的查询结果时，就需要用的联结(join)运算了
MySQL多表查询内连接外连接详解，join、left join、right join、full join的使用
睡竹的博客
04-10 8890
MySQL多表查询内连接外连接详解，join、left join、right join、full join、union、union all的使用及案例
深入理解mysql之left join 使用详解
09-11
MySQL 的 LEFT JOIN 是一种用于联接两个或更多表的 SQL 操作，它返回左表（即在 JOIN 语句左侧的表）的所有记录，以及右表（JOIN 语句右侧的表）与左表匹配的记录。如果右表中没有匹配的记录，结果集中的相应列将...
MySQL left join、right join和join的区别
qq_31728357的博客
09-08 1031
首先有2张表 一、left join 顾名思义，就是“左连接”，表1左连接表2，以左为主，表示以表1为主，关联上表2的数据，查出来的结果显示左边的所有数据，然后右边显示的是和左边有交集部分的数据。 从subjects表中找出，没有在score表出现过的记录 二、right join “右连接”，表1右连接表2，以右为主，表示以表2为主，关联查询表1的数据，查出表2所有数据以及表1和表2有交集的数据 从score表中找出，没有在subjects表中出现过的记
Mysql数据库之左连接left join 右连接right join 内连接inner join
天之道居同文。
03-06 1万+
最近，公司的用户达到了700+万，意味着数据库已经达到700+万，聊聊傻傻分不清的连接查询吧！ 前提:数据库中一共有三个表:class,book,phone,而且每个数据库表中都有10万条数据,三个表一共有30万条数据,从大数据量的角度来检测你写的sql语句性能是如何的. 一.左连接 left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 注...
一分钟搞明白Join、Left Join、Right Join的区别
程序猿小九九的博客
06-13 2万+
目录Join/Inner JoinJoin/Inner Join语法演示结果Left JoinLeft Join语法演示结果Right JoinRight Join语法 演示结果​Full Join Full Join语法总结WHERE和ON的区别演示where语句where语句执行过程on语句on语句执行过程SQL中JOIN相关子句是用于将两个或者多个表结合起来，进行相关关操作。下面是一些关于JOIN的的功能描述，后面会进行详细的分析对比总结。测试表分别为：city表school表 Join和Inner
join, left join, right join , left outer join
qq_35987023的博客
08-11 1382
顾名思义，就是“左连接”,以左为主,查出来的结果显示左边的所有数据，然后右边显示的是和左边有交集部分的数据。
join 、left join 的区别
lovebosom的博客
04-01 4146
Join 等同于 inner join ： 表示两个表都必须存在的  left join 表示以左边的表为基准，不管你右边的表有没有都显示  right join和left join刚好相反  full outer join表示只要在两个表中任何一个表中有的都显示
MySQL中的JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN讲解
Asurplus
07-10 695
在 MySQL 中，JOIN 是一种非常强大的功能，它允许你将两个或多个表中的行结合起来，基于两个表之间的共同字段。这种操作在数据库查询中非常常见，特别是在处理关系型数据库时。下面我将分别解释 JOIN、LEFT JOIN（也称为 LEFT OUTER JOIN）和 RIGHT JOIN（也称为 RIGHT OUTER JOIN）的基本概念和用法。
MSQL系列(十三) Mysql实战-left/right/inner join 使用详解及索引优化
流浪的喵的博客
11-03 4334
前面我们讲解了B+Tree的索引结构，也详细讲解下Join的底层驱动表 选择原理，今天我们来了解一下为什么会出现内连接外连接，两种连接方式，另外实战一下内连接和几种最常用的join语法。这个就会带来一个问题， 比如我的驱动表有100条记录，但是我的外连接 我不想让驱动表全部数据加入到结果集， 但是外连接的本质就是不匹配的就展示到结果集，这种情况该如何处理？要按照错误的这个说法,下面的语句执行完 左边不动，还是4条，1，2，3，4 但是明显不是。根据查询结果，我们可以很明确的知道。
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值