sql - 何时将 JOIN 操作从数据库层移动到服务层？

Question

考虑到我们有 2 个表的情况。需求是实现一个函数，从TABLE_A和TABLE_B中选择前10条记录(按一定规则排序)，其中table_a.id == table_b.a_id == X。有两种选择:

1. 使用JOIN查询SQL；

2. 从数据库中进行 2 个选择查询:SELECT * FROM table_a WHERE id = X 和 SELECT * FROM table_b WHERE a_id = X，从每个查询中获取 10 条记录在内存中查询(假设在这种情况下排序是正确的)，然后将它们加入代码(使用 for 循环和哈希表或类似的东西)。

我听说 JOIN 可能会降低系统 性能(这里是“数据库性能”，但这是错误的)(请参阅下面的跟进以供引用)。此外，在这种情况下，我们最多只查询 10 个结果，将它们全部加载到内存中然后在那里连接它们是可以接受的。

我的问题是，业界是否有通用指南，说明在什么情况下我们建议在数据库层使用 JOIN 而不是在内存中执行，以及何时执行相反的操作？

============

跟进:

所以这是我读过的“将 JOIN 从数据库层移动到 服务层”:

1. 如果我们连接多个表，它们将同时被锁定。如果操作需要时间并且服务需要低响应时间，它可能会阻止其他执行；
2. 在大系统中难以维护。 JOIN 中涉及的表的更改可能会使查询中断。
3. 这些复杂的系统可能有一些历史原因，数据可能在不同的数据库(或数据库系统，比如 DynamoDB 中的一个表和 Postgres 中的另一个表)中迁移/创建，这使得 JOIN 在数据库层是不可能的。

Answer 1

简单回答，视情况而定

• 一般来说，最好在离数据更近的地方进行数据操作，而不是将它们带到更高的层级并处理数据操作。您可以看到许多基于 PL/SQL 的实现，它们在其中执行更接近数据的操作。 PL/SQL(ORACLE) 或 TSQL(SQL Server) 等语言旨在执行复杂的数据操作。

• 但是，如果您有一个应用程序，它从不同的系统中获取数据并且必须在它们之间进行连接，那么您必须在内存中进行这些操作。

  1. If we are joining multiple tables, they will all be locked at once. And if the operation take times and the service requires low response time, it might block other executions;

读者不会阻止其他读者。他们有一种叫做共享锁的东西。另外，一旦读操作结束，共享锁就会被释放。作为@TimBiegeleisen，您可以根据需要创建索引来加速读取操作。最好只读取需要的列(投影)、需要的行(过滤)。

2. Hard to maintain in the big system. Changes of the tables that are involved in JOIN might make the query broken.

只要您只选择需要的列，而不是 SELECT *，您就不会遇到问题。如果即将发生许多更改，您可以考虑创建 SCHEMA BINDING View，以避免对基础表进行架构更改。

3. There might be some historical reason for those complicated systems, that data might be migrated/created in different db (or db systems, say one table in DynamoDB and the other one in Postgres), which makes JOIN in the database layer impossible.

根据当前需要设计应用程序。不要假设将来会发生类似的事情并为此进行设计并妥协当前的应用程序性能。如果有明确的 future 需求，请进行内存操作。否则，最好使用数据库 JOIN。