postgresql 一次插入多条记录_PostgreSQL中的JOIN方法概述

作者：Kumar Rajeev Rastogi拉杰夫·拉斯托吉(Rajeev Rastogi)是Severalnines的客座作家。他目前是一名高级系统架构师，在从基于磁盘的关系数据库(如PostgreSQL)到分布式内存中的NoSQL数据库(如RAMCloud)等平台上的核心数据库开发方面拥有近15年的经验。Rajeev拥有计算机科学学位，并拥有十多项专利，他是开源社区的活跃成员，并定期在行业活动中发表演讲。他大部分的业余时间都花在户外运动上，他最喜欢的是羽毛球。 翻译：李冉   中国PostgreSQL分会志愿者 研发工程师

     我在之前的博客中曾经讨论过从单个表中选择或扫描数据的方法。实际上，仅从单个表中获取数据是不够的。我们需要从多个表中选择数据，然后在它们之间进行关联。表之间数据的关联称为表连接，可以通过多种方式进行关联。由于表的连接需要输入数据(例如，来自表扫描的数据)，因此它永远不能成为计划的叶节点。

     例如，一个简单的查询为SELECT * FROM TBL1, TBL2 where TBL1.ID > TBL2.ID;假设生成的计划如下：

     这里首先扫描两个表，然后根据相关条件将它们连接在一起，如TBL.ID> TBL2.ID。

除了连接方法外，连接顺序也很重要。例如以下示例：

    SELECT * FROM TBL1, TBL2, TBL3 WHERE TBL1.ID=TBL2.ID AND TBL2.ID=TBL3.ID;

TBL1，TBL2和TBL3分别具有10、100和1000条记录。符合TBL1.ID=TBL2.ID条件的只返回5条记录，符合TBL2.ID=TBL3.ID 条件的返回100条记录，那么最好先使用join连接TBL1和TBL2，这样，会有更少的记录与TBL3合并。该计划将如下所示：

PostgreSQL支持以下类型的连接：

嵌套循环连接

哈希连接

合并连接

     这些Join方法中的每一个都是有用的，它们的作用取决于查询和其他参数，例如：查询、表数据、连接子句、选择性、内存等。大多数关系数据库都能实现这些连接方法。      让我们创建一些预设置表并填充一些数据，这些数据可以帮助我们更好地说明这些扫描方法。

postgres=# create table blogtable1(id1 int, id2 int);CREATE TABLEpostgres=# create table blogtable2(id1 int, id2 int);CREATE TABLEpostgres=# insert into blogtable1 values(generate_series(1,10000),3);INSERT 0 10000postgres=# insert into blogtable2 values(generate_series(1,1000),3);INSERT 0 1000postgres=# analyze;ANALYZE

     在所有后续示例中，除非特别说明，我们将考虑使用默认的配置参数。

嵌套循环连接

     嵌套循环连接(NLJ)是最简单的连接算法，其中外部表的每个记录都与内部表的每个记录匹配。符合条件A.ID < B.ID的关系A和B之间的连接可以表示如下：

For each tuple r in A        For each tuple s in B                If (r.ID < s.ID)                    Emit output tuple (r,s)

     嵌套循环连接(NLJ)是最常见的连接方法，几乎可以在具有任何类型的连接子句的任何数据集上使用。由于此算法扫描内部和外部表的所有记录，因此被认为是成本最高的连接操作。

根据上表和数据，以下查询将导致嵌套循环连接，如下所示：

postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 < bt2.id1;                               QUERY PLAN------------------------------------------------------------------------------ Nested Loop  (cost=0.00..150162.50 rows=3333333 width=16)   Join Filter: (bt1.id1 < bt2.id1)   ->  Seq Scan on blogtable1 bt1  (cost=0.00..145.00 rows=10000 width=8)   ->  Materialize  (cost=0.00..20.00 rows=1000 width=8)         ->  Seq Scan on blogtable2 bt2  (cost=0.00..15.00 rows=1000 width=8)(5 rows)

     由于join子句为“

     在这里注意有一种新的节点，即Materialize；该节点充当中间结果缓存，例如：将第一次获取的结果存储在内存中，而不是多次获取表的所有记录，并且下一次请求将从内存中获取记录而不是再次从关系(表)页中获取。如果所有记录都无法容纳在内存中，则溢出的记录会转到临时文件中。Materialize节点在使用嵌套循环连接时最有用，而在使用合并连接时则在某种程度上起作用，因为它们都依赖于内部表的重新扫描。Materialize节点不仅可以缓存关系的结果，而且还可以缓存计划树下任何节点的结果。

     提示：如果join子句为“ =”并且在表之间使用嵌套循环连接，那么研究是否可以通过调整配置(例如work_mem，但不仅限于此)来选择更有效的连接方法(例如哈希或合并连接)或添加索引等是非常重要的。

     一些查询可能没有join子句，在这种情况下，连接的唯一选择是嵌套循环连接。例如，根据预设置数据考虑以下查询：

postgres=# explain select * from blogtable1, blogtable2;                             QUERY PLAN -------------------------------------------------------------------------- Nested Loop  (cost=0.00..125162.50 rows=10000000 width=16)   ->  Seq Scan on blogtable1  (cost=0.00..145.00 rows=10000 width=8)   ->  Materialize  (cost=0.00..20.00 rows=1000 width=8)      ->  Seq Scan on blogtable2  (cost=0.00..15.00 rows=1000 width=8)(4 rows)

上例中的连接只是两个表的笛卡尔乘积。

哈希连接

该算法分为两个阶段：

构建阶段：使用内部表记录构建哈希表。哈希键是基于join子句键计算的。

探测阶段：根据join子句键对外部表记录进行哈希处理，以在哈希表中找到匹配的条目。

符合条件A.ID = B.ID的关系A和B之间的连接可以表示如下：

1)构建阶段

对于内部表B中的每个记录r

将r与键r.ID插入哈希表HashTab

2)探测阶段

对于外部表A中的每个记录s

对于bucker HashTab [s.ID]中的每个记录r

   如果(s.ID = r.ID)

   发出输出记录(r，s)

根据上面的预设置表和数据，以下查询将导致哈希连接：

postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 = bt2.id1;                               QUERY PLAN------------------------------------------------------------------------------ Hash Join  (cost=27.50..220.00 rows=1000 width=16)   Hash Cond: (bt1.id1 = bt2.id1)   ->  Seq Scan on blogtable1 bt1  (cost=0.00..145.00 rows=10000 width=8)   ->  Hash  (cost=15.00..15.00 rows=1000 width=8)         ->  Seq Scan on blogtable2 bt2  (cost=0.00..15.00 rows=1000 width=8)(5 rows)

     哈希表是在表blogtable2上创建的，因为它是较小的表。因此哈希表和整个哈希表所需的最小内存不会造成内存溢出。

合并连接

     合并连接是一种匹配算法，其中外部关系的每个记录与内部关系的每个记录匹配，直到存在连接子句匹配的可能性为止。仅当两个关系(表)都已排序且join子句运算符为“ =”时，才使用此算法。

     符合条件A.ID = B.ID的关系A和B之间的连接可以表示如下：

For each tuple r in A    For each tuple s in B         If (r.ID = s.ID)              Emit output tuple (r,s)              Break;         If (r.ID > s.ID)              Continue;         Else              Break;

     如上所示，使用哈希连接的示例查询可以在两个表上都创建索引时导致合并连接。这是因为，索引可以按排序的顺序检索表数据，而索引是合并连接方法的主要规则之一：

postgres=# create index idx1 on blogtable1(id1); CREATE INDEXpostgres=# create index idx2 on blogtable2(id1);CREATE INDEXpostgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 = bt2.id1;                                   QUERY PLAN--------------------------------------------------------------------------------------- Merge Join  (cost=0.56..90.36 rows=1000 width=16)   Merge Cond: (bt1.id1 = bt2.id1)   ->  Index Scan using idx1 on blogtable1 bt1  (cost=0.29..318.29 rows=10000 width=8)   ->  Index Scan using idx2 on blogtable2 bt2  (cost=0.28..43.27 rows=1000 width=8)(4 rows)

     因此，正如我们所看到的，两个表都使用索引扫描而不是顺序扫描，这是因为这两个表都会涉及排序的记录。

配置

     PostgreSQL支持多种与计划器相关的配置，这些配置可用于提示查询优化器不选择某些特定种类的连接方法。如果优化器选择的连接方法不是最佳的，则可以关闭这些配置参数以强制查询优化器选择其他类型的连接方法。默认情况下，所有这些配置参数都设置为“on”。以下是特定于连接方法的计划器配置参数。

enable_nestloop: 嵌套循环连接相关

enable_hashjoin: 哈希连接相关

enable_mergejoin:合并连接相关

     有许多与执行计划相关的配置参数可用于各种目的。在此博客中，仅介绍与连接方法有关的参数。

     可以从特定会话中修改这些参数。因此，万一我们想在特定会话中尝试该计划，则可以配置这些参数，而其他会话仍将不受影响继续工作。

     现在，考虑以上合并连接和哈希连接的示例。没有索引，查询优化器为以下查询选择了哈希连接，如下所示，但是在使用配置后，即使没有索引，它也会切换为合并连接：

postgres=# explain select * from blogtable1, blogtable2 where blogtable1.id1 = blogtable2.id1;                             QUERY PLAN -------------------------------------------------------------------------- Hash Join  (cost=27.50..220.00 rows=1000 width=16)   Hash Cond: (blogtable1.id1 = blogtable2.id1)   ->  Seq Scan on blogtable1  (cost=0.00..145.00 rows=10000 width=8)    ->  Hash  (cost=15.00..15.00 rows=1000 width=8)       ->  Seq Scan on blogtable2  (cost=0.00..15.00 rows=1000 width=8)(5 rows)postgres=# set enable_hashjoin to off;SETpostgres=# explain select * from blogtable1, blogtable2 where blogtable1.id1 = blogtable2.id1;                             QUERY PLAN---------------------------------------------------------------------------- Merge Join  (cost=874.21..894.21 rows=1000 width=16)   Merge Cond: (blogtable1.id1 = blogtable2.id1)   ->  Sort  (cost=809.39..834.39 rows=10000 width=8)      Sort Key: blogtable1.id1      ->  Seq Scan on blogtable1  (cost=0.00..145.00 rows=10000 width=8)      ->  Sort  (cost=64.83..67.33 rows=1000 width=8)      Sort Key: blogtable2.id1      ->  Seq Scan on blogtable2  (cost=0.00..15.00 rows=1000 width=8)(8 rows)

     最初选择哈希连接是因为表中的数据未排序。为了选择合并连接计划，需要首先对从两个表中检索到的所有记录进行排序，然后应用合并连接。因此，排序会产生额外的成本，总成本将增加。在这种情况下，总的(包括增加的)成本可能大于哈希连接的总成本，因此选择了哈希连接。

     一旦将配置参数enable_hashjoin更改为“ off”，查询优化器直接将哈希连接的成本分配为禁用的成本(= 1.0e10，即10000000000.00)。任何可能的连接的成本都将低于此成本。因此，在enable_hashjoin更改为“ off”后，相同查询中，使用合并连接甚至包括排序成本的总成本小于禁用成本。

现在考虑以下示例：

postgres=# explain select * from blogtable1, blogtable2 where blogtable1.id1 < blogtable2.id1;                             QUERY PLAN-------------------------------------------------------------------------- Nested Loop  (cost=0.00..150162.50 rows=3333333 width=16)   Join Filter: (blogtable1.id1 < blogtable2.id1)   ->  Seq Scan on blogtable1  (cost=0.00..145.00 rows=10000 width=8)   ->  Materialize  (cost=0.00..20.00 rows=1000 width=8)      ->  Seq Scan on blogtable2  (cost=0.00..15.00 rows=1000 width=8)(5 rows)postgres=# set enable_nestloop to off;SETpostgres=# explain select * from blogtable1, blogtable2 where blogtable1.id1 < blogtable2.id1;                             QUERY PLAN-------------------------------------------------------------------------- Nested Loop  (cost=10000000000.00..10000150162.50 rows=3333333 width=16)   Join Filter: (blogtable1.id1 < blogtable2.id1)   ->  Seq Scan on blogtable1  (cost=0.00..145.00 rows=10000 width=8)   ->  Materialize  (cost=0.00..20.00 rows=1000 width=8)      ->  Seq Scan on blogtable2  (cost=0.00..15.00 rows=1000 width=8)(5 rows)

     正如我们在上面看到的那样，即使将嵌套循环连接相关的配置参数更改为“off”，它仍会选择“嵌套循环连接”，因为没有选择其他任何联接方法的可能性。简而言之，由于嵌套循环连接是唯一可以使用的连接，因此无论付出什么代价，它总是赢家(这与我以前独自跑100m比赛获胜是一样的....:-))。另外，请注意第一个计划和第二个计划的成本差异。第一个计划显示了嵌套循环连接的实际成本，而第二个计划显示了相同情况下的禁用成本。

总结

     PostgreSQL的连接方法都是有用的，并且可以根据查询、数据、连接子句等进行选择。如果查询未按预期执行，即未按预期选择连接方法，则用户可以尝试使用不同的计划配置参数，并查看是否缺少某些内容。

I Love PG

关于我们

中国开源软件推进联盟PostgreSQL分会(简称：PG分会)于2017年成立，由国内多家PG生态企业所共同发起，业务上接受工信部产业发展研究院指导。PG分会致力于构建PG产业生态，推动PG产学研用发展，是国内一家PG行业协会组织。

欢迎投稿

做你的舞台，show出自己的才华 。

投稿邮箱：partner@postgresqlchina.com

                                 ——愿能安放你不羁的灵魂

技术文章精彩回顾PostgreSQL学习的九层宝塔PostgreSQL职业发展与学习攻略搞懂PostgreSQL数据库透明数据加密之加密算法介绍一文读懂PostgreSQL-12分区表一文搞懂PostgreSQL物化视图PostgreSQL源码学习之：RegularLockPostgresql源码学习之词法和语法分析2019，年度数据库舍 PostgreSQL 其谁?Postgres是最好的开源软件PostgreSQL是世界上最好的数据库从Oracle迁移到PostgreSQL的十大理由PG活动精彩回顾创建PG全球生态！PostgresConf.CN2019大会盛大召开首站起航！2019“让PG‘象’前行”上海站成功举行走进蓉城丨2019“让PG‘象’前行”成都站成功举行中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行群英论道聚北京，共话PostgreSQL相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报相知巴厘岛| PG Conf.Asia 2019 DAY2简报独家|硅谷Postgres大会简报PostgreSQL线上沙龙第一期精彩回顾PostgreSQL线上沙龙第二期精彩回顾PostgreSQL线上沙龙第三期精彩回顾PostgreSQL线上沙龙第四期精彩回顾PostgreSQL线上沙龙第五期精彩回顾PG培训认证精彩回顾关于中国PostgreSQL培训认证，你想知道的都在这里！首批中国PGCA培训圆满结束，首批认证考试将于10月18日和20日举行！中国首批PGCA认证考试圆满结束，203位考生成功获得认证！中国第二批PGCA认证考试圆满结束，115位考生喜获认证！请查收：中国首批PGCA证书！重要通知：三方共建，中国PostgreSQL认证权威升级！一场考试迎新年 | 12月28日，首次PGCE中级认证考试开考！近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！通知：PostgreSQL技术能力电子证书上线！