Nested loop（PostgreSQL 14 Internals翻译版）

已于 2023-10-17 18:40:50 修改
阅读量1.2k 收藏 1
点赞数
分类专栏： PostgreSQL 数据库 文章标签： postgresql 数据库
于 2023-10-17 16:48:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Post_Yuan/article/details/133881545
版权
本文详细解释了SQL连接的不同类型，如内连接、外连接、嵌套循环、外连接方法（如PostgreSQL的nestedloopjoin、hashjoin、mergejoin），以及如何进行基数预估、选择性计算和成本估算。还讨论了并行模式在连接操作中的应用。

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

连接类型和方法

连接是SQL语言的一个关键特性;它们是其力量和灵活性的基础。行集(要么直接从表中检索，要么作为某些其他操作的结果接收)总是成对连接。

有几种类型的连接：

  • 内连接。 内连接(指定为“INNER JOIN”或简称为“JOIN”)由满足特定连接条件的两个集合的行对组成。连接条件将一组行的一些列与另一组行的一些列结合起来；所有涉及的列都构成连接键。
    如果连接条件要求两个集合的连接键相等，这样的连接称为等连接（equi-join） ；这是最常见的连接类型。
    两个集合的笛卡尔积（CROSS JOIN） 包含了这些集合的所有可能的行对——它是具有真条件的内连接的一种特殊情况。

  • 外连接。 左外部连接(指定为“LEFT OUTER”或简称“LEFT JION”)通过左集中没有与右集中匹配的行(相应的右侧列填充为NULL值)扩展内部连接的结果。
    对于右连接(RIGHT JOIN)也是如此，直到集合的排列。
    完整的外部连接(指定为FULL JOIN)包括左外部连接和右外部连接，添加没有找到匹配的右侧和左侧行。

  • 反连接(ANTI-JOIN)及半连接(SEMI-JOIN)。 半连接看起来很像内部连接，但它只包括左集中与右集中有匹配的行(即使有几个匹配，也只包括一行)。
    反连接包括一个集合中在另一个集合中没有匹配的行。
    SQL语言没有显式的半连接和反连接，但是使用像“EXISTS”和“NOT EXISTS”这样的谓词也可以达到同样的结果。

所有这些连接都是逻辑操作。例如，内部连接通常被描述为已经清除了不满足连接条件的行的笛卡尔积。但是在物理级别上，内连接通常是通过成本较低的方式实现的。

PostgreSQL提供几种连接方法：

  • nested loop join
  • hash join
  • merge join

连接方法是实现SQL连接的逻辑操作的算法。这些基本算法通常具有为特定连接类型量身定制的特殊风格，即使它们可能只支持其中的一些。例如，嵌套循环支持内部连接(在计划中由嵌套循环节点表示)和左外部连接(由嵌套循环左连接节点表示)，但它不能用于完全连接。
相同算法的某些风格也可以用于其他操作，例如聚合。
不同的连接方式在不同的条件下表现最佳；优化器的工作是选择最具成本效益的一种。

嵌套循环连接（Nested loop Joins）

嵌套循环连接函数的基本算法如下：外部循环遍历第一个集合(称为外部集合)的所有行。对于这些行中的每一行，将遍历第二组(称为内部集合)的行，以查找满足连接条件的行。 每个找到的对都会作为查询结果的一部分立即返回。
该算法访问内部集合的次数与外部集合的行数相同。因此，嵌套循环连接的效率取决于以下几个因素:

  • 行外部集合的基数
  • 可用的访问方法，可以有效地获取内部集所需的行
  • 循环访问内部集合的同一行

笛卡尔积

不管集合中有多少行，嵌套循环连接是找到笛卡尔积的最有效方法:
在这里插入图片描述
嵌套循环节点使用上述算法执行连接。它总是有两个子节点：在计划中显示较高的一个对应于外部行集，而较低的一个代表内部行集。

在本例中，内部集合由Materialize节点表示。该节点返回从其子节点接收到的行，并保存它们以备将来使用(这些行在内存中累积，直到它们的总大小达到work_mem；然后PostgreSQL开始将它们溢出到磁盘上的临时文件中)。如果再次访问，该节点将读取累积的行，而不调用子节点。因此，执行器可以避免再次扫描整个表，而只读

最低0.47元/天 解锁文章
数据库领域DBA的数据库数据架构设计
2502_91592937的博客
05-18 449
数据库架构设计是DBA工作的核心内容之一，它直接关系到数据库系统的性能、可靠性和可维护性。本文旨在为数据库专业人员提供一套系统化的数据库架构设计方法论，涵盖从概念设计到物理实现的全过程。文章首先介绍数据库架构设计的基本概念和原则，然后深入探讨各个设计环节，包括数据模型、性能优化、高可用性等。最后通过实际案例展示如何应用这些原则解决现实问题。OLTP(联机事务处理)：面向事务的高并发数据处理系统OLAP(联机分析处理)：面向分析的大规模数据处理系统ACID。
从数据库内核开发的角度读《PostgreSQL 14 Internals》|文档简介及相关资源汇总(从这里开始阅读)
孤傲小二~阿沐的博客
10-09 908
《PostgreSQL 14 Internals》|文档简介及相关资源汇总
B-tree（PostgreSQL 14 Internals翻译版）
数据源的港湾
10-22 741
在这种情况下，节点被分成两个，旧节点的一些元素被移动到新节点中，指向新子节点的指针被添加到父节点中。此外，可能有太多的副本，以至于它们不适合单个节点，因此相邻的叶节点也必须处理。如果搜索是通过条件“索引-列 ⩽expression”(或“索引-列⩾expression”)执行的，我们必须首先搜索满足相等条件的值的索引，然后在所需的方向遍历其叶节点，直到到达树的末端。您可以注意到，树的内部节点中最左边的键是冗余的:要选择根的子节点，只要满足条件KJA < OVB就足够了。生成的新节点在下面的图中突出显示。
postgresql 数据库执行计划 Nested Loop
一名数据库爱好者的专栏
05-28 4430
os: centos 7.4 db: postgresql 10.11 Nested Loop 比较适合两个表的数据量都比较少的情况(最简单的 table join 方式) 外层驱动表过滤后的数据量较少。 内层被驱动表的关联列上最好有高效索引(主键或者唯一性索引)。 版本 # cat /etc/centos-release CentOS Linux release 7.4.1708 (Core) # # su - postgres $ $ psql -c "select version();"
PostgreSQL连接方式
m0_61738548的博客
03-18 256
PostgreSQL连接方式
Nested Loops Join(嵌套连接)
wangyihbu的专栏
02-26 2211
Nested Loops Join(嵌套连接)Sql Server支持三种物理连接：nested loops join,merge join和hash join.这篇文章,我将描述nested loops join(或者简称为NL)。基本算法最简单的情况是,nested loop会以连接谓词为条件取出一张表里的每一行(称为外部表)与另外一张表(称为内部表)的每一行进行比较来寻找符合条件的行。(注意这里的"内部"和"外部"是具有多层含义的,必须从上下文中来理解它们。"内部表"和"外部表"是指连接的输入,"内连
对PostgreSQL Nested Loop 执行计划的初步学习
weixin_34235371的博客
11-09 206
开始 伪代码如下： for (i = 0; i &lt; length(outer); i++) 　　for (j = 0; j &lt; length(inner); j++) 　　　　if (outer[i] == inner[j]) 　　　　　　output(outer[i], inner[j]); 就是个双层循环。 实际运行一下： 建表： postgres=#...
postgresql_internals-14 学习笔记（六）—— 统计信息
Hehuyi_In的博客
06-24 7333
不完全来自这本书，把查到的和之前的文章重新汇总整理了一把。
Nested Loops(嵌套循环)
weixin_30472035的博客
01-01 843
先扫描驱动表的（外表），外表的每一行驱动内表，然后匹配，所以nest loops不是主要依赖于内表有多少行，而是非常依赖于驱动表到底有多少行参与nested loops,因为驱动表（或者准确的说是驱动的cardinality)越多，那么nested loops的效率会越低，因为内表是索引效率，成本不会变化的，变化的是驱动结果集的行数导致整个查询成本的升高。一般来说，驱动表的基数不要超过200到30...
postgresql_internals-14 学习笔记（一）
Hehuyi_In的博客
12-05 2291
梳理一下之前理解不太清楚的知识点，重点内容可能会再拆出来单独研究。原书链接：Index of /命名空间，在逻辑上相当于DB中的一个目录。pg自带以下schema： search_path变量用于设置搜索路径，pg_catalog和pg_temp 这两个schema总是包含在其中（因此所有库中都能查到系统表和临时表），但默认不显示。未完待续...
postgresql_internals-14 学习笔记（二）常规vacuum
Hehuyi_In的博客
12-11 3274
page pruning执行速度很快，但它们的作用范围毕竟只有单页、且不包含索引，因此，我们还需要更有效的清理机制。常规vacuum是最常用的一种，作用范围可以是整张表，清理过期元组及索引项，并且不阻塞读和写。为提高效率，vacuum会结合前面提到的.vm文件，跳过不需清理的页。清理之后，还会更新前面提到的fsm（空闲空间映射）文件。另外还可以用vacuum analyze顺便收集统计信息。
嵌套循环连接(Nested Loops), 合并联接(Merge), 哈希联接(Hash)的适用情况
weixin_33720078的博客
11-21 488
1.嵌套循环连接(Nested Loops)适用范围两个表, 一个叫外部表, 一个叫内部表. 如果外部输入非常小，而内部输入非常大并且已预先建立索引，那么嵌套循环联接将特别有效率。 关于连接时哪个表为outer表，哪个为inner表，我发现sql server会自动给你安排，和你写的位置无关，它自动选择数据量小的表为outer表， 数据量大的表为inner表。 2.合并联接(Merge)指两...
NESTED LOOPS
Sallery的博客
04-05 1058
table get 主键 select * 不用回表 table scan 范围扫描 当range(MIN,MIN, MIN ; MAX, MAX, MAX)代表全表扫描 table lookup 全局索引回表 以下读sql优化核心思想进行提炼 全局索引和局部索引 全局索引：索引表中一个分区对应多个分区数据，TABLE LOOKUP 算子中就会包含一次 RPC 操作到远端机器上去获取主表数据。因此全局索引相比局部索引有更高的维护代价 NESTED LOOPS ： 1、驱动表返回一行数据，通过连..
从数据库内核开发的角度读《PostgreSQL 14 Internals》|Data Organization
孤傲小二~阿沐的博客
10-09 722
《PostgreSQL 14 Internals》|Data Organization
Nested loops
Gangelwn
03-27 752
<br />Straightforward nested loop <br />Runtime: 8,987 microseconds <br /><br />* Entries: 100 (ITAB1), 1000 (ITAB2)<br />* Line width: 100<br />* Both tables sorted by key K<br />LOOP AT ITAB1 INTO WA1.<br />  LOOP AT ITAB2 INTO WA2<br />                W
Nested loops、Hash join、Sort merge join（三种连接类型原理、使用要点）
张冲andy的博客
04-28 2412
nested loop 嵌套循环（原理）： oracle从较小结果集（驱动表、也可以被称为outer）中读取一行，然后和较大结果集（被侦查表，也可以叫做inner）中的所有数据逐条进行比较（也是等值连接，也可以是非等值连接），如果符合规则，就放在结果集中，然后取驱动表的下一条数据继续循环，直到结束。 简单的理解就是：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个
oracle 表连接 - nested loop 嵌套循环连接
热门推荐
dataminer 的博客
12-09 1万+
一. nested loop 原理 nested loop 连接(循环嵌套连接)指的是两个表连接时, 通过两层嵌套循环来进行依次的匹配, 最后得到返回结果集的表连接方法.  假如下面的 sql 语句中表 T1 和 T2 的连接方式是循环嵌套连接, T1 是驱动表 select * from T1, T2 where T1.id = T2.id and T1.nam
PostgreSQL表连接 nestloop/hash/merge join详解
weixin_43084715的博客
01-13 2778
PostgreSQL中如果查询需要连接两个或更多表，在所有扫描单个表的可能计划都被找到后，连接计划将会被考虑。和很多数据库一样，可供选择的三种表连接方式为：nested loop join、merge join、hash join。 nested loop join： 对左表中找到的每一行都要扫描右表一次。这种策略最容易实现但是可能非常耗时(不过，如果右表可以通过索引扫描，这将是一个不错的策略。因为可以用左表当前行的值来作为右表上索引扫描的键)。 merge join： 在连接开始之前，每一个表都按照连
PostgreSQL14 Internals 中文版 持续修正...
xuejianxinokok的专栏
12-27 996
为了方便自己快速学习，整理了翻译版本，目前翻译的还不完善，后续会边学习边完善。
nestedloopjoin
最新发布
04-03
### 嵌套循环连接算法及其数据库中的应用 嵌套循环连接（Nested Loop Join, NLJ）是一种常见的关系型数据库查询优化器使用的物理实现方法之一。它通过遍历两个数据集来查找匹配的记录组合，通常用于处理较小的数据表或者当外层表非常小时。 #### 工作原理 NLJ 的基本工作流程如下：对于外部输入表中的每一行，内部输入表都会被完全扫描一次以寻找满足条件的匹配项。如果存在索引，则可以利用该索引来加速访问过程[^1]。这种方法特别适合于那些具有高度选择性的连接谓词场景下运行良好，因为这样能够减少不必要的 I/O 开销。 #### 使用情况分析 在实际操作过程中，SQL Server 和其他主流的关系型数据库管理系统可能会自动决定何时采用 Nested Loops Joins 来执行特定类型的 JOIN 查询语句。例如，在某些情况下，如果两张待关联表格之间存在着良好的分区对齐特性(pre-joining aligned tables)，那么系统就更倾向于选用这种高效的联接方式来进行多阶段并行化计算。 另外需要注意的是，并不是所有的数据库都支持相同的功能集合；比如当我们对比 SQL Server、MySQL 及 PostgreSQL 这三种不同平台所提供的特性和功能差异时就会发现它们各自有所侧重之处[^3]。因此，在具体开发项目之前应该充分考虑目标环境的支持程度以及性能表现等因素后再做最终抉择。 下面给出一段 Python 中模拟简单版 nested loop join 功能的小例子： ```python def simple_nested_loop_join(table_a, table_b, condition_func): result = [] for row_a in table_a: for row_b in table_b: if condition_func(row_a, row_b): # Apply the given condition function. combined_row = {**row_a, **row_b} # Combine rows into one dictionary. result.append(combined_row) return result ``` 此函数接受三个参数分别是代表左表`table_a`,右表 `table_b`还有用来判断两行是否符合条件的一个回调函数`condition_func`. 它会逐一遍历每一对可能来自这两个列表内的元素并对他们调用传入的方法去检测是否存在对应关系; 如果有则将其合并成一个新的字典形式加入到结果集中最后返回整个列表.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

数据源的港湾

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值