資料庫 vs 應用程式：揭開 JOIN 策略的神秘面紗

簡介

為什麼 Prisma ORM 最初只提供應用程式層級聯結？

Prisma ORM 最初僅提供應用程式層級聯結策略。此選擇有幾個原因

• 能夠跨資料庫引擎使用相同的聯結策略，確保可移植性。
• 透過將昂貴的操作移至應用程式層（與資料庫相比，應用程式層更容易且更便宜擴展），提高整體系統的可擴展性。
• 雲原生和無伺服器用例，其中應用程式和資料庫位於同一雲端區域是常態，額外往返資料庫的開銷變得微不足道。
• 高效能用例，處理數百萬行資料和深度巢狀查詢，並使用額外功能（如篩選和分頁）。
• 查詢除錯的簡易性，因為每個查詢僅針對單個表格（無需理解和除錯複雜的查詢計畫）。
• 可預測的效能，透過將資料庫責任限制為直接的操作，並防止查詢效能的顯著變化，這些變化取決於資料庫的查詢規劃器和執行階段最佳化。

在 2024 年 2 月，Prisma ORM 新增了智慧型資料庫層級聯結作為替代策略，使用現代資料庫功能，例如 LATERAL 聯結和 JSON 聚合。當應用程式和資料庫伺服器相距遙遠，且額外的網路往返成本顯著影響查詢的整體延遲時，這種方法是有利的。

最終，這些方法中的每一種都有其自身的權衡，我們將在本文的其餘部分闡明這些權衡，以幫助您為關聯查詢選擇最佳策略。

巢狀物件 vs 外鍵關聯

在深入探討聯結的複雜性之前，讓我們先快速縮放視野，了解「聯結資料」這個主題的全部內容。

作為開發人員，您可能習慣於使用巢狀物件，它們看起來類似於這樣

在此範例中，「物件階層」如下：post → author → profile。

這種巢狀結構是大多數具有物件概念的程式語言中資料的表示方式。

但是，如果您之前使用過 SQL 資料庫，您可能意識到相關資料在那裡以不同的方式表示，即以扁平（或 正規化）的方式表示。透過這種方法，實體之間的關聯透過外鍵表示，外鍵指定跨表格的參照。

這是兩種方法的視覺表示

這是一個巨大的差異，不僅在資料物理上在磁碟和記憶體中的佈局方式，而且在心智模型和資料推理方面也是如此。

「聯結」資料是什麼意思？

聯結資料的過程是指將 SQL 資料庫中扁平佈局的資料轉換為應用程式開發人員可以在其應用程式中使用的巢狀結構。

這可能在以下兩個位置之一發生

• 在資料庫中：單個 SQL 查詢被發送到資料庫。查詢使用 JOIN 關鍵字（或可能是 相關子查詢）讓資料庫執行跨多個表格的聯結並傳回巢狀結構。我們將在下一節中查看執行此聯結的多種方法。
• 在應用程式中：多個查詢被發送到資料庫。每個查詢僅存取單個表格，然後在應用程式層聯結查詢結果。

資料庫層級聯結有其優點，但如果它們變得太複雜，也會有一些缺點。因此，根據架構、資料集和查詢複雜性等因素，任一種方法可能比另一種更適合特定的用例。請繼續閱讀以了解詳細資訊！

三種 JOIN 策略：Naive、Smart 和應用程式層級 JOIN

在高層次上，可以應用三種不同的聯結策略，資料庫層級的「Naive」和「Smart」 JOIN，以及「應用程式層級」聯結。讓我們使用以下架構逐一檢查這些策略

Naive 資料庫層級 JOIN 會導致資料冗餘

Naive 資料庫層級 JOIN 是指未採取任何額外措施進行最佳化的 JOIN 操作。由於幾個原因，這些 JOIN 通常對效能不利，讓我們來探索一下！

例如，這是一個簡單的 LEFT JOIN 操作，開發人員可能會天真地編寫它來聯結 users 和 post 表格中的資料

資料庫傳回的結果可能看起來類似於這樣

您注意到什麼了嗎？在 user_name 欄位中有很多重複資料。

現在，讓我們將 comments 新增到查詢中

現在情況更糟了！不僅 user_name 重複，而且 post_title 也重複了

資料的冗餘具有幾個負面影響

• 透過網路傳輸的（不必要的）資料量增加，導致網路頻寬成本增加並增加整體查詢延遲。
• 應用程式層需要做更多的工作才能獲得所需的巢狀物件
  • 對冗餘資料進行重複資料刪除
  • 重新建構資料記錄之間的關係

此外，這種操作會在資料庫上產生很高的 CPU 成本，因為它會查詢所有三個表格並執行自己的記憶體內對應以將資料聯結到一個結果集中。

以上仍然是一個相對簡單的範例。想像一下，您使用更多 JOIN 和更多巢狀結構來執行此操作。在達到一定程度後，資料庫將放棄最佳化查詢計畫，而只是對每個表格執行表格掃描，然後使用自己的 CPU 在記憶體中將資料縫合在一起。這很快就會變得非常昂貴！

與應用程式層級的 CPU 和記憶體相比，資料庫 CPU 和記憶體的擴展要複雜得多（且成本更高）。因此，改善情況的一種方法是使用應用程式伺服器的 CPU 來完成聯結資料的工作，這將我們引導至下一個方法：「應用程式層級聯結」。

應用程式層級聯結簡單高效，但有網路成本

執行這些 Naive 資料庫層級聯結的另一種替代方法是在應用程式層聯結資料。在這種情況下，開發人員制定三個不同的查詢，這些查詢會個別發送到資料庫。一旦資料庫傳回查詢的結果，開發人員就可以應用自己的業務邏輯來自行聯結資料。

在 TypeScript 中，一個範例可能如下所示（使用像 node-postgres 這樣的純 Postgres 驅動程式）

這種方法有幾個好處

• 資料庫將為每個查詢產生高度最佳的執行計畫，並且幾乎不執行 CPU 工作，因為它只是從單個表格傳回資料。
• 透過網路傳輸的資料針對應用程式的資料需求進行了最佳化（並且不會遇到與 Naive 資料庫層級聯結策略相同的冗餘問題）。
• 由於大部分對應和聯結工作現在都在應用程式本身中完成，因此資料庫伺服器有更多資源來處理更複雜的查詢。

透過將 CPU 成本從資料庫轉移到應用程式層，這種方法增強了整個系統的水平可擴展性。

在 O'Reilly 的書 High Performance MySQL 中，這種應用程式層級聯結技術稱為聯結分解：「許多高效能網站使用聯結分解。您可以透過執行多個單表格查詢而不是多表格聯結來分解聯結，然後在應用程式中執行聯結。」

然而，一個主要的缺點是它需要多次往返資料庫。如果應用程式伺服器和資料庫相距遙遠，這是一個相當大的因素，會對效能產生嚴重的影響，並可能使此策略變得不可行。如果資料庫和應用程式託管在同一區域，則網路開銷通常可以忽略不計，並且此方法可能在整體上更有效能。

Smart 資料庫層級聯結解決了冗餘問題

Naive 資料庫層級聯結幾乎永遠不是從資料庫檢索相關資料的最佳方法，但這是否意味著您的資料庫永遠不應負責聯結資料？當然不是！

資料庫引擎在過去幾年中變得非常強大，並且不斷改進它們最佳化查詢的方式。為了使資料庫能夠產生最佳的查詢計畫，最重要的是它可以理解查詢的意圖。

這有兩個不同的因素

• 使用像 JSON 聚合這樣的技術來減少冗餘
• 在 PostgreSQL 中使用像 LATERAL 聯結（或 MySQL 中的相關子查詢）這樣的現代資料庫功能，這些功能包含查詢複雜性

使用與上面相同的架構範例，表示此內容的一個好方法是

這樣的查詢產生以下結果

此資料與關於 Naive 資料庫層級聯結的部分中的資料相似，但

• 它不再包含冗餘
• 貼文已經以 JSON 結構格式化

雖然此查詢可能會產生比 Naive 策略格式更好的結果，但它也變得冗長而複雜。請記住，我們仍然在談論一個相對簡單的整體情境：聯結三個表格，沒有大多數真實世界應用程式正在處理的其他因素（例如篩選和分頁）。

Prisma ORM 中 JOIN 策略的演進

當 Prisma ORM 在 2021 年最初發布時，它為其所有關聯查詢實作了應用程式層級聯結策略。

當應用程式伺服器和資料庫彼此靠近時，此策略效果非常好，有助於跨資料庫引擎的可移植性，並提高整體系統的可擴展性（因為應用程式層級 CPU 比資料庫層級 CPU 更容易且更便宜擴展）。

雖然應用程式層級聯結的方法已為大多數開發人員提供了良好的服務，但有時當應用程式伺服器和資料庫無法緊密託管在一起，並且額外的往返對整體查詢效能產生負面影響時，就會引起問題。

這就是為什麼 我們在一年前新增了智慧型資料庫層級聯結作為替代方案，以便開發人員可以選擇始終為其個別用例選擇效能最佳的聯結策略。

能夠使用資料庫層級聯結一直是 Prisma ORM 最受歡迎的功能請求之一，自預覽版發布以來，已受到我們社群的好評。一旦此功能普遍可用，資料庫層級聯結將成為 Prisma ORM 為其關聯查詢應用的預設聯結策略。

社群回饋是幫助我們確定優先順序的主要驅動力之一，我們正在努力改進 Prisma ORM。

結論

找出從資料庫中多個表格聯結資料的最佳效能方法是一個複雜的主題。在本文中，我們研究了三種不同的方法，資料庫層級的 Naive 和 Smart 聯結，以及 應用程式層級 聯結。

Naive 資料庫層級聯結會在資料庫伺服器上產生高 CPU 成本，並由於不必要地傳輸冗餘資料而導致網路開銷。

由於應用程式層級聯結的簡單性和在資料庫層級上的低成本執行，它們可能更適合許多情境。使用此策略的系統通常也更容易且更便宜擴展。

最後，Smart 資料庫層級聯結解決了冗餘問題，可以傳回針對應用程式開發人員需求量身定制的巢狀結構中的資料，並且總體而言，更有可能被資料庫引擎更好地最佳化。