提高 Postgres JOIN 性能的策略

Neon lights representing two Postgres tables joining each other.

作者：Timescale 团队

作为工具的构建者，我们使您能够扩展 PostgreSQL 以成功处理 TB 级数据，我们了解管理大型（且不断增长）PostgreSQL 表的挑战。在我们与用户、其他开发人员以及像我们自己一样的 PostgreSQL 爱好者的对话中，经常会提到在大型 PostgreSQL 表的上下文中使用 JOIN 的主题。

作为我们 PostgreSQL 提示库的一部分（如我们关于使用分区和减少 PostgreSQL 中的表膨胀导航大型 PostgreSQL 表的文章），本文将介绍提高 JOIN 性能的策略。我们将研究用户常见问题带来的三种策略

  1. 缓存 JOIN 结果：我定期连接多个 PostgreSQL 表。我可以将此 JOIN 结果缓存到某个位置以避免不断计算吗？

  2. JOIN 与重复列：处理大型数据集时，是通过具有多个 JOIN 的高度规范化架构运行查询更好，还是通过复制列来反规范化以避免 JOIN 更好？

  3. 大型表 JOIN：如何加速涉及两个大型表的 JOIN？

首先，我们将回顾 PostgreSQL 连接的基础知识，然后直接回答这些问题。 

什么是关系数据库中的 JOIN？

JOIN 是一种基于多个表之间相关列中的匹配值组合来自两个或多个表的行的方法，它提供了一种以关系方式查询分布在多个表中的数据的方法。

常见的 JOIN 类型

  • 内连接：返回两个表中具有匹配值的记录。连接表中在其他表中没有匹配行的任何行都不会包含在结果中。 

  • 左连接（或左外连接）：返回左表中的所有记录以及右表中的匹配记录。如果没有匹配项，则右表中的所有列都将返回 NULL 值。

  • 右连接（或右外连接）：返回右表中的所有记录以及左表中的匹配记录。如果没有匹配项，则左表中的所有列都将返回 NULL 值。

  • 全连接（或全外连接）：组合左外连接和右外连接的结果。还包括两个表中不匹配的记录。如果没有匹配项，则不匹配表中的所有列都将返回 NULL 值。

PostgreSQL 数据库中 JOIN 的策略 

策略 1：使用物化视图缓存 JOIN 结果

此策略的灵感来自以下问题：“我定期将多个 PostgreSQL 表连接在一起。我可以在某个地方缓存此 JOIN 结果以避免不断计算它吗？”

在 PostgreSQL 中，系统级缓存已经发生，减少了对用户级缓存的需求。但是，如果您希望提高常规 JOIN 操作的性能，请考虑使用物化视图。

创建物化视图涉及计算和存储查询结果。因此，当您查询物化视图时，它会访问这些已计算的数据，从而绕过执行 JOIN 操作的需要。但是，请务必注意，存储这些预先计算的数据需要磁盘空间。

例如，考虑一个大型交易表和一个存储每个交易中产品的表。您想构建一个表来记录每个用户购买次数超过一次的产品。此查询涉及的内容很多，并且所讨论的表很大，因此预先计算需要很长时间。但是，如果您将此查询物化，则会存储生成的表，并且可以在不重新计算大型连接的情况下对其进行查询。 

物化视图的唯一限制是，当基础数据发生更改时，它们不会自动更新。要使视图保持最新，请手动刷新它或使该过程自动化。例如，如果您有一个跟踪销售额的表，并且它是物化视图的一部分，则此表中的任何更改（如插入/更新/删除）都需要刷新视图

REFRESH MATERIALIZED VIEW sales;

对于自动化，您可以使用调度工具，例如 Linux crontab、pg_cron 或 pg_timetable 扩展，它们可以安排定期刷新。

如果您使用的是 TimescaleDB，则可以使用用户定义操作来运行函数和过程。例如，您可以使用 add_job() 函数来调度重新计算物化视图的函数

add_job(‘materialized_view_refresh’, ‘1h’);

您还可以考虑使用Timescale 连续聚合，这有利于缓存简单的时间分组聚合查询并与小表联接。但是，除此之外，连续聚合目前对联接的支持不多。

尽管 PostgreSQL 的 shared_buffers 和操作系统的缓存机制可用，但使用物化视图或连续聚合可以改进重复 JOIN 操作。 

策略 2：评估规范化

此策略回答了以下问题：“在处理大型数据集时，是通过具有多个 JOIN 的高度规范化架构运行查询更好，还是通过复制列以避免 JOIN 来反规范化更好？”

对于大型数据集，规范化架构和反规范化架构之间的选择并非一刀切；它取决于您的应用程序的特定要求和约束。

反规范化涉及向数据库架构添加冗余以提高应用程序性能。此策略通常会减少表的总数并简化连接表的复杂性，这是有利的，因为过多的联接会阻碍查询处理速度。

但是，反规范化也有其自身的一系列挑战。它通常会导致表具有更宽的行，这意味着每页的行数更少，可能需要更多磁盘提取，并且内存占用更大。它还使插入/更新/删除操作更复杂，以保持冗余数据的一致性。 

这种方法对于数据不经常更新的读密集型数据库特别有利，因为在冗余数据之间维护数据一致性的开销不再是问题。

规范化通过将复杂表分解为更小、更易于管理的表并在它们之间建立关系来组织数据。这种方法有效地减少了数据冗余并最大限度地减少了磁盘空间的使用。 

如果 JOIN 操作中涉及的数据被缓存，则与规范化架构中的 JOIN 相关的开销将大大减少。这是因为重复的磁盘 I/O 操作被最小化了。但是，如果规范化架构经常需要磁盘访问才能进行联接操作，则反规范化可以通过减少 JOIN 的数量来提高性能，但代价是数据冗余（仅当它减少磁盘提取时）。由于冗余较少，因此规范化架构通常更适合写密集型操作。

在实践中，连接大型数据集的成本可能很高，但很少需要连接两个大型表的全部内容。查询通常设计为仅使用每个表中的必要行，从而保持结果集较小。对于具有多个联接的大型查询，有效的方法是从联接较小的表开始，逐步移动到较大的表，以使内存中的数据集保持最小。使用真实数据和查询进行性能测试通常是有益的，以便做出明智的决策。

策略 3：优化大型表 JOIN

此策略回答了以下问题：“如何加快涉及两个大型表的 JOIN 的速度？”

在处理两个大型表之间的 JOIN 操作时，可以采用几种策略来提高性能

  • 使用较小的数据类型来减少内存和磁盘空间的使用，从而加快 JOIN 的速度。选择最适合您数据性质的类型。确保用于联接的键具有相同的数据类型，从而避免在联接过程中进行类型转换。

  • 使用 WHERE 简化您的查询并限制 JOIN 中处理的数据量，以获得更好的性能。在两个表上创建适当的索引，尤其是在 JOIN 条件中使用的列上。仅获取您需要的列，因为这可以最大限度地减少 JOIN 期间的数据负载。指定列可以让您使用仅索引扫描，这避免了从索引表中加载所有数据。

  • 确保有足够的 RAM 以减少磁盘访问的需求。调整 PostgreSQL 设置，例如用于缓存的 shared_buffers 和用于排序和 PostgreSQL 使用的 JOIN 算法等内存密集型操作的 work_mem 。此外，使用更快的存储（如 SSD）可以显着提高磁盘密集型 JOIN 的性能。充足的 CPU 功率还可用于对大型表启用并行扫描和并行索引扫描，这可以显着加快 JOIN 的速度。

  • 利用 PostgreSQL 的声明式分区。使用分区化联接检查查询性能，该联接可以通过专注于相关分区来更有效地处理数据，这些分区可能适合内存并减少资源使用。请注意，分区化联接仅在联接条件包含所有分区键时适用。分区键必须具有相同的数据类型以及对应于一对一关系的子分区集。

  •  调整自动清理设置，例如 autovacuum_vacuum_scale_factor 和 autovacuum_freeze_max_age，以最大限度地减少表膨胀。 

通过应用这些策略，您可以显着提高 PostgreSQL 中大型表之间 JOIN 操作的效率。请记住，每种方法的有效性可能会因您的特定数据集和用例而异，因此测试不同的方法以找到适合您情况的最佳解决方案通常是有益的。