Nested Loops Join、Hash join、Merge Sort Join三大经典表连接浅谈（笔记）

最新推荐文章于 2024-04-01 09:07:59 发布
原创 最新推荐文章于 2024-04-01 09:07:59 发布 · 705 阅读 · 5 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

今天上完梁老师的课，感觉表连接的意义非常深刻，知识消化可以转化成Sql优化方面的强力武器！

必须认真记下来！

一、SQL写法上的限制（CBO选择执行计划时的限制）
Nested Loops Jion：无限制
Hash Join：作为经典的等值算法，不支持非等值条件（like、>、<、<>）
Merge Sort Join：支持>、<，不支持like和<>

二、各自特性

表访问次数：
Nested Loops Jion：驱动结果集的条数决定被驱动表的访问次数
Hash Join：两表各自只被访问1次或0次
Merge Sort Join：两表各自只被访问1次或0次

表驱动顺序与性能：
Nested Loops Jion：记录少的表作为驱动表性能更好
Hash Join：记录少的表作为驱动表性能更好
Merge Sort Join：顺序与性能无关

是否排序：
Nested Loops Jion：不排序
Hash Join：不排序但是消耗内存
Merge Sort Join：产生排序


三、表连接优化的注意事项

Nested Loops Jion：
1.适合OL

最低0.47元/天 解锁文章

200万优质内容无限畅学
Oracle典型的表连接类型（sort merge join(SMJ) 排序-合并连接、nested loops(NL) 嵌套循环、hash join 哈希连接）
林世广
02-02 2456
1、sort merge join(SMJ) 排序-合并连接 连接过程： 将两个表排序，然后再进行join ①首先生产drving table【驱动表】需要的数据，然后对这些数据按照连接操作关联列进行排序； ②然后生产probed table【被探查表】需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序； ③最后两边已经排序的行被放在一起执行合并操作。 应用场景： ①排序是一个费时、费资源的操作，特别对于大表。所以SMJ通常不是一个特别有效的连接方法，但是如果driving
Nested loops、Hash join、Sort merge join（三种连接类型原理、使用要点）
weixin_33671935的博客
11-12 520
nested loop 嵌套循环（原理）： oracle从较小结果集（驱动表、也可以被称为outer）中读取一行，然后和较大结果集（被侦查表，也可以叫做inner）中的所有数据逐条进行比较（也是等值连接，也可以是非等值连接），如果符合规则，就放在结果集中，然后取驱动表的下一条数据继续循环，直到结束。 简单的理解就是：从A表抽一条记录，遍历B表查找匹配记录...
Nested Loops Join(嵌套连接)
wangyihbu的专栏
02-26 2233
Nested Loops Join(嵌套连接)Sql Server支持三种物理连接：nested loops join,merge join和hash join.这篇文章,我将描述nested loops join(或者简称为NL)。基本算法最简单的情况是,nested loop会以连接谓词为条件取出一张表里的每一行(称为外部表)与另外一张表(称为内部表)的每一行进行比较来寻找符合条件的行。(注意这里的"内部"和"外部"是具有多层含义的,必须从上下文中来理解它们。"内部表"和"外部表"是指连接的输入,"内连
三种类型的表关联hash join和merge join和nested loop详解
rede
02-24 6792
https://blog.youkuaiyun.com/gao1440156051/article/details/52388644 多表之间的连接有三种方式： Nested Loops Hash Join Sort Merge Join 一． NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行...
nested loops/hash join
iteye_4222的博客
12-24 309
nested loops适用于一大一小表。其中内表是小表，每取一次值，然后与外表匹配。 若内表复杂度为n，外表复杂度为m，则整体为O(n*m) hash join 适用于大表关联。若内表复杂度为n，外表复杂度为m，则整体为O(n+m) 索引： 特殊情况下，索引扫描反而不如全表扫描效率高。如索引字段值90%为同一值时，全表扫描效率更高。当过滤字段加上后，选取数据量较少时，索引能够大大发挥...
Oracle优化器、优化模式、表的连接方式（Hash Join、Nested Loop、Sort Merge Join）
wwlhz的专栏
06-30 1128
查询优化器Oracle的查询优化器（QO）分为两种： 1. RBO：Ruled-Based Optimization， 基于规则的优化器； 2. CBO ：Cost-Based Optimization， 基于代价的优化器；从 Oracle 10g开始，Oracle已放弃RBO，但为了兼容性，仍然可以设置RBO.优化模式优化模式分为： FIRST_ROWS: 尽可能快的先返回几
多表连接的三种方式详解 hash join、merge join、 nested loop
weixin_33836223的博客
04-30 2237
在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join.具体适用哪种类型的连接取决于 当前的优化器模式 （ALL_ROWS 和 RULE） 取决于表大小 取决于连接列是否有索引 取决于连接列是否排序 下面来介绍三种不同连接工作方式的不同： 实验s...
NESTED LOOPS HASH JOIN
神谕梦醒
12-31 473
嵌套循环(NESTED LOOPS) Oracle从较小结果集(驱动表/外部表)中读取一行，然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接)，如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循环，直到结束。嵌套循环只适合输出少量结果集或者是用于快速输出结果集。 哈希连接(HASH JOIN) 如果要返回大量结果集(比如返回200
表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN（修改）
Seas_小庙的专栏
02-05 5011
表连接方式及使用场合 NESTED LOOP 嵌套循环连接        由两个for循环组成。不管什么连接，本算法都可以使用。连接的两个关系，分别称之为外层关系和内层关系，把数据块数大的关系作为外层关系，小的关系作内层关系。分为块嵌套循环连接（简单点说，就是把已经放在内存的两个关系块全部连接完成后再进行下一数据库块的比较，减少内存中数据块的IO）和索引嵌套循环连接（如果内层关系有索引，使用
ob优化之：Nested Loops Joins
viviliving的专栏
04-01 279
的伪代码。looploopthenend ifend loopend loop第一层循环用于遍历左侧表的每一行数据，然后遍历右侧表的每一行数据，检查两行数据是否满足连接条件的约束，如果满足则输出连接后的数据行。Nested Loops Join 计划如下所示。
嵌套循环连接(Nested Loops Join)
viviliving的专栏
08-08 4318
嵌套循环连接(Nested Loops Join)是一种两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内存循环）来得到连接结果集的表连接方法。即外层循环对应的驱动结果集有多少条记录，遍历被驱动表的内层循环就要做多少次，这就是所谓的“嵌套循环”的含义。 对于嵌套循环连接的优缺点及适用场景如下： a,如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的
Oracle的Filter,Nest loop,Merge sort join和Hash join(原创)
czmmiao的专栏
02-18 393
 Merge Sort Join按照Merge Sort Join连接的两表地位完全相同。这种算法会把每个表按照连接列进行排序，生成两个排序集。然后对两个排序集进行一次遍历便可以得到最终结果集。这个算法的特点是，每个表都需要排序，排序后都需要遍历一次。 以下面的例子说明，Merge Sort Join的执行过程如下：SQL&gt; explain plan for select /*+ us...
多表连接的三种方式详解 HASH JOIN、MERGE JOIN、NESTED LOOP
微信搜：import_bigdata，大数据领域硬核原创作者
10-04 4070
点击上方蓝色字体，选择“设为星标”回复”资源“获取更多资源大数据技术与架构点击右侧关注，大数据开发领域最强公众号！大数据真好玩点击右侧关注，大数据真好玩！多表之间的连接有三种方式：Nes...
oracle表连接----->嵌套循环(Nested Loops Join)
Watson的专栏
05-15 8064
嵌套循环连接(Nested Loops Join)是一种两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内存循环）来得到连接结果集的表连接方法。即外层循环对应的驱动结果集有多少条记录，遍历被驱动表的内层循环就要做多少次，这就是所谓的“嵌套循环”的含义。
oracle 表连接方式： nested loop 嵌套循环和Hash Join的比较
热门推荐
daf380的专栏
10-08 1万+
一、创建两张实验用表：wireless_site.merchant和wireless_site.bb SQL&gt; select count(*) from wireless_site.merchant;   COUNT(*) ----------      14005 SQL&gt; SQL&gt; select count(*) from wireless_site.clickthr...
Oracle数据库的连接方式之一：NESTED LOOPS join
' 忍一时风平浪静 退一步海阔天空 '
07-24 1408
Join Methods：        Since the days of Oracle 6, the optimizer has used three different ways to join row sources together: the NESTED LOOPS join, the SORT-MERGE join, and the CLUSTER join. (There is
Oracle表连接操作——Merge Sort Join（合并排序连接）
天元喜羊羊的博客
03-31 1312
http://blog.itpub.net/17203031/viewspace-697012 关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。     1、Merge Sort Jo
NESTED LOOPS
最新发布
07-16
<think>我们正在讨论嵌套循环（Nested Loops）在编程和数据库查询优化中的应用。 在编程中，嵌套循环通常用于处理多维数据，例如遍历二维数组。在数据库查询优化中，嵌套循环连接（Nested Loops Join）是一种基本的连接算法。 在数据库查询优化中，嵌套循环连接的工作方式如下： 对于外层表（驱动表）的每一行，遍历内层表（被驱动表）的所有行，并检查连接条件是否满足。 它的时间复杂度为O(n*m)，其中n和m分别是两个表的大小。当其中一个表很小（例如，可以放入内存）时，这种连接方式效率较高。 然而，当两个表都很大时，嵌套循环连接可能效率低下。此时，数据库优化器可能会选择其他连接算法，如哈希连接（Hash Join）或排序合并连接（Sort Merge Join）。 在编程中，嵌套循环也常用于各种算法，例如冒泡排序（Bubble Sort）等。 根据引用[2]中提到成本模型（cost model）会考虑磁盘访问次数和网络成本等，我们可以推断在数据库查询优化中，优化器会根据成本模型选择连接算法。 另外，引用[3]提到了BRIN索引优化，虽然与嵌套循环没有直接关系，但索引的使用可以显著提高嵌套循环连接的效率，因为内层表如果有索引，则可以通过索引快速定位匹配行，而不需要全表扫描。 因此，我们可以从以下几个方面展开： 1. 嵌套循环在编程中的基本应用。 2. 嵌套循环在数据库连接操作中的原理。 3. 如何优化嵌套循环连接（例如，使用索引、小表驱动大表等）。 4. 替代嵌套循环连接的其他连接算法。 下面我们分别进行说明。 注意：根据指令，数学表达式和独立公式要使用正确的LaTeX格式。</think>### 嵌套循环在编程与数据库查询优化中的应用 #### 1. **编程中的嵌套循环** 在编程中，嵌套循环指循环结构的多层嵌套，常用于处理多维数据（如矩阵运算）或组合问题： ```python # 二维数组遍历示例 for i in range(len(matrix)): for j in range(len(matrix[i])): # 嵌套循环层 print(matrix[i][j]) ``` 时间复杂度为 $O(n^m)$（$m$ 为嵌套层数），需警惕性能瓶颈[^1]。 #### 2. **数据库查询优化中的嵌套循环连接** 在数据库（如 PostgreSQL/Oracle）中，嵌套循环连接（Nested Loops Join）是基础连接算法： - **工作原理**： 1. 外层循环遍历驱动表（小表） 2. 内层循环遍历被驱动表，通过索引匹配连接键 $$ \text{总成本} \approx C_{\text{outer}} + N_{\text{outer}} \times C_{\text{inner}} $$ 其中 $C$ 为单次扫描成本，$N$ 为行数[^2] - **适用场景**： - 驱动表行数少（< 1% 总数据量） - 内层表连接列有高效索引（B-tree） - OLTP 短查询场景 - **优化策略**： ```sql -- 强制使用索引（PostgreSQL示例） SET enable_hashjoin = off; SET enable_mergejoin = off; EXPLAIN SELECT * FROM orders JOIN customers ON orders.cust_id = customers.id -- 确保cust_id有索引 ``` #### 3. **性能对比与其他连接算法** | **算法** | 时间复杂度 | 适用场景 | |-------------------|------------------|------------------------------| | 嵌套循环连接 | $O(N \times M)$ | 小表驱动大表，索引高效 | | 哈希连接 | $O(N + M)$ | 无索引中等表 | | 排序合并连接 | $O(N \log N)$ | 数据预排序，无索引大表 | > 优化器根据成本模型（磁盘 I/O + CPU + 内存）自动选择算法[^2] #### 4. **实战优化技巧** - **索引优化**：为内层表连接键创建索引，降低单次查找成本 - **驱动表选择**：用 `/*+ LEADING(t1) USE_NL(t2) */` 提示指定驱动表 - **避免全表扫描**：确保 WHERE 条件能利用索引 - **BRIN 索引**：对按时间排序的大表，BRIN 索引可加速范围查询[^3] --- ### 相关问题 1. 嵌套循环连接在哪些场景下会退化为 $O(n^2)$ 性能？如何避免？ 2. 如何通过执行计划（EXPLAIN）识别数据库是否使用了嵌套循环连接？ 3. 对比哈希连接与嵌套循环连接的优缺点及适用场景 4. 在 NoSQL 数据库中嵌套循环的设计有哪些变化？ --- [^1]: 嵌套循环的高阶复杂度在数据量大时导致性能显著下降，需谨慎使用 [^2]: 数据库成本模型会综合磁盘访问、网络传输等因素选择连接算法 [^3]: BRIN 索引对时序数据的存储优化可提升嵌套循环效率
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值