分層表結構性能速度很慢，本文将學習列傳播，以幫助解決與分層表結構相關的典型性能問題。
本文将展示在處理分層資料結構時，列傳播這一直接提高查詢性能的方法。本文将使用基于資料驅動項目的真實場景來講解，其中項目為某體育行業初創公司開發的實時資料網站。本文将帶你了解有關列傳播的相關知識，以解決分層 SQL 表結構中固有的性能問題。
背景
本文所做項目涉及一個擁有數百萬頁面的足球球迷網站。該網站緻力于成為球迷心中的權威，尤其是在投注方面。因為排程程式負責定期重新計算複雜資料并将其存儲在表中，這樣查詢就不必涉及SQL 聚合，資料庫和應用程式架構也不是特别複雜。是以，真正的挑戰在于非功能性需求，例如性能和頁面加載時間。
應用領域
體育行業的資料來源有很多，每個來源都為其客戶提供不同的資料集。具體來說，足球行業有四種類型的資料：
- 個人檔案資料：身高、體重、年齡、效力球隊、所獲獎杯、個人獎項、球員和教練。
- 曆史資料：過往賽果和技術統計，如進球、助攻、黃牌、紅牌、傳球等。
- 目前和未來資料：目前賽季已完結和将進行的比賽結果與技術統計。
- 實時資料：比賽實時結果與技術統計。
該網站涉及所有這些類型的資料，同時特别關注有利于搜尋引擎優化的曆史資料和支援投注的實時資料。
分層表結構
出于保密要求，部分資料結構無法完全公開。但通過足球賽季的結構也可以了解相關情況。
具體來說，足球提供商通常按如下方式組織賽季中的比賽資料：
- 賽季(Season)：有開始和結束日期，通常持續一個月曆年。
- 賽事(Competition)：比賽所屬的賽事。
- 階段(Phase)：賽事所處的階段（例如，資格賽、淘汰賽、決賽階段）。每個賽事都有自己的規則，很多賽事隻有一個階段。
- 組别(Group)：與階段相關的組（例如，A 組、B 組、C 組……）。像世界杯等賽事會涉及不同的組别，每個組内涵蓋相應球隊。大多數賽事隻有一個通用組适用于所有球隊。
- 回合(Turn)：是從邏輯上相對于進行一天的賽事而言的。通常持續一周，涵蓋屬于一個小組的所有球隊的比賽（例如，MLS 有 17 場主場比賽和 17 場客場比賽，是以它有 34 個回合）。
- 比賽(Game)：兩支足球隊之間的比賽。
如下圖ER 模式所示，這 5 張表代表了一個分層資料結構：
技術、參數和性能要求
我們使用Express 4.17.2和 Sequelize 6.10作為 ORM（對象關系映射）在 Node.js 和 TypeScript 中開發後端。前端是使用 TypeScript 開發的 Next.js 12應用程式。資料庫則選用由 AWS 托管的 Postgres 伺服器。
該網站在AWS Elastic Beanstalk上運作，前端有 12 個執行個體，後端有 8 個執行個體，目前每天有 1000到 5000的通路者。客戶的目标是在一年内達到每天6萬的浏覽量，是以該網站必須準備好在無損性能的情況下托管數百萬月度使用者。
在Google Lighthouse測試中，該網站應性能、SEO 和可通路性方面得分超過了80。此外，加載時間應始終小于 2 秒，理想情況下為幾百毫秒。真正的挑戰在于，該網站包含超過 200 萬個頁面，預渲染它們都需要數周時間。此外，大多數頁面上顯示的内容都不是靜态的。是以，我們選擇了增量靜态再生方法。當通路者點選一個沒有人通路過的頁面時，Next.js 會使用從後端公開的 API 檢索到的資料生成它。然後，Next.js 将頁面緩存 30 或 60 秒，具體取決于頁面的重要性。
是以，後端必須快速為伺服器端生成過程提供所需的資料。
為什麼查詢分層表很慢
現在讓我們看看為什麼分層表結構會帶來性能挑戰。
JOIN 查詢速度很慢
根據與層次結構中較高對象關聯的參數過濾葉子是分層資料結構中的一個常見場景。比如，檢索在特定賽季中進行的所有比賽。由于葉表Game不直接連接配接到Season，是以你必須執行一個與層次結構中的元素一樣多的 JOIN 的查詢。
是以你可能會編寫以下查詢：
這樣的查詢就會很慢。每個 JOIN 都會執行一次笛卡爾積運算，這需要時間并且可能會産生數千條記錄。是以，分層資料結構越長，性能就越差。
此外，如果你想檢索所有資料而不僅僅是表中的Game列，由于笛卡爾積的性質，你必須處理數千行和數百列。這個過程可能會變得混亂，但這正是 ORM 發揮作用的地方。
ORM資料解耦和轉換需要時間
通過 ORM 查詢資料庫時，你可能會對檢索基于應用程式級别的表中的資料感興趣。原始資料庫級别表示在應用程式級别可能沒有用。是以，當大多數進階 ORM 執行查詢時，它們會從資料庫中檢索所需資料并将其轉換為應用程式級表示。這個過程包括兩個步驟：資料解耦和資料轉換。
在背景，來自 JOIN 查詢的原始資料首先被解耦，然後在應用程式級别轉換為相應的表示。是以，在處理所有資料時，具有數百列的數千條記錄成為一個小組資料，每個資料都具有資料模型類中定義的屬性。是以，包含從資料庫中提取的原始資料的數組将成為一組Game對象。每個Game對象都有一個包含其各自Turn執行個體的turn字段。然後，該Turn對象将有一個Group字段存儲其各自的Group對象等。
生成這種轉換後的資料是無法擺脫的負擔。處理淩亂的原始資料具有挑戰，并且會導緻代碼異味。另一方面，這個背景發生的過程需要時間。因為處理存儲數千個元素的數組總是非常棘手，當原始記錄有數千行時尤其如此。
換句話說，分層表結構的常見 JOIN 查詢在資料庫和應用程式層都很慢。
列傳播作為一種解決方案
針對這一性能問題，在分層結構将列從父級傳播到其子級可以作為一種解決方案。
為什麼應該在分層資料庫上傳播列
在分析上面的 JOIN 查詢時，很明顯問題在于在葉子表Game應用了過濾器。你必須周遊整個層次結構。但是既然 Game 是層次結構中最重要的元素，為什麼不直接在其中添加seasonId、competitionId、phaseId和groupId列呢？這就是列傳播的意義所在。
将外部鍵列直接傳播給子項可以避免所有的 JOIN。現在你可以将上面的查詢替換為以下查詢：
可以想見，這個查詢會比原來的查詢快得多。此外，它會直接傳回你感興趣的内容。是以，ORM 資料解耦和轉換過程現在也可以忽略了。
請注意，列傳播涉及資料重複，需要少用、慎用。在深入研究如何優雅實作之前，讓我們看看應該傳播哪些列。
如何選擇要傳播的列
如果向下傳播層次結構中較高的實體的每一列，這在過濾方面可能很有用（例如外部密鑰）。此外，你也可用傳播用于過濾資料的枚舉列，或生成包含來自父級的聚合資料的列來避免 JOIN。
Top 3- 列傳播方法
在選擇列傳播方法時，我們的團隊考慮了三種不同的實作方法。
1. 建立物化視圖
要在層次表結構中實作列傳播，我們首先是想建立具有所需列的物化視圖。物化視圖存儲查詢的結果，它通常表示複雜查詢的行和/或列的子集，例如上面介紹的 JOIN 查詢。
當涉及到具體化查詢時，你可以定義何時生成視圖。然後資料庫會将其存儲在磁盤上并使其像普通表一樣可用。即使生成查詢可能很慢，你也隻能一點點地啟動它。是以，物化視圖代表了一種快速的解決方案。
另一方面，物化視圖對處理實時資料可能并非最佳方法，因為物化視圖可能不是最新的。它存儲的資料取決于你決定生成視圖或重新整理它的時間。此外，涉及大資料的物化視圖會占用大量磁盤空間，這可能會帶來問題并增加存儲成本。
2. 定義虛拟視圖
另一種可能的解決方案是使用虛拟視圖。同樣，虛拟視圖是存儲查詢結果的表。與物化視圖的不同之處在于，這一次資料庫不會将查詢結果存儲在磁盤上，而是将其儲存在記憶體中。是以，虛拟視圖始終是最新的，進而解決了實時資料的問題。
此外，每次通路視圖時，資料庫都必須執行生成查詢。是以，如果生成查詢需要時間，那麼涉及到視圖的整個過程必然很慢。虛拟視圖是一個強大的工具，但考慮到我們的性能目标，還需尋找其他解決方案。
3. 使用觸發器
SQL 觸發器可以讓你在資料庫中發生特定事件時自動啟動查詢。換句話說，觸發器使你能夠跨資料庫同步資料。是以，在層次結構表中定義所需的列，并讓自定義觸發器更新它們，這樣就可輕松實作列傳播。
因為每次觸發器等待的事件發生時，資料庫都會執行它們，是以可以想見，觸發器會增加性能開銷。執行查詢需要時間和記憶體，是以會有成本，但與虛拟或物化視圖帶來的缺點相比，這種成本通常可以忽略不計。
觸發器的問題是，定義它們可能需要一些時間。同時，你隻能處理此任務一次，并在需要時要對其更新。通過觸發器可以讓你輕松實作列傳播。此外，通過這種方式，我們也極大滿足了客戶定義的性能要求。
層次結構在資料庫中很常見。因為需要長時間的 JOIN 查詢和 ORM 資料處理，過程緩慢且耗時。如果處理不當，可能會導緻應用程式出現性能和效率低下的問題。不過，你可以在層次結構中将列從父級傳播到的子級來避免這些問題。