?譯者 | 萬望琳
審校 | 孫淑娟 梁策
本文將展示在處理分層數據結構時，列傳播這一直接提高查詢性能的方法。本文將使用基于數據驅動項目的真實場景來講解，其中項目為某體育行業初創公司開發的實時數據網站。本文將帶你了解有關列傳播的相關知識，以解決分層 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 的查詢。
因此你可能會編寫以下查詢：
SELECT GA.* FROM `Game` GA
LEFT JOIN `Turn` T on GA.`turnId` = T.`id`
LEFT JOIN `Group` G on T.`groupId` = G.`id`
LEFT JOIN `Phase` P on G.`phaseId` = P.`id`
LEFT JOIN `Competition` C on P.`competitionId` = C.`id`
LEFT JOIN `Season` S on C.`seasonId` = S.`id`
WHERE S.id = 5
這樣的查詢就會很慢。每個 JOIN 都會執行一次笛卡爾積運算，這需要時間并且可能會產生數千條記錄。因此，分層數據結構越長，性能就越差。
此外，如果你想檢索所有數據而不僅僅是表中的Game列，由于笛卡爾積的性質，你必須處理數千行和數百列。這個過程可能會變得混亂，但這正是 ORM 發揮作用的地方。
ORM數據解耦和轉換需要時間
通過 ORM 查詢數據庫時，你可能會對檢索基于應用程序級別的表中的數據感興趣。原始數據庫級別表示在應用程序級別可能沒有用。因此，當大多數高級 ORM 執行查詢時，它們會從數據庫中檢索所需數據并將其轉換為應用程序級表示。這個過程包括兩個步驟：數據解耦和數據轉換。
在后臺，來自 JOIN 查詢的原始數據首先被解耦，然后在應用程序級別轉換為相應的表示。因此，在處理所有數據時，具有數百列的數千條記錄成為一個小組數據，每個數據都具有數據模型類中定義的屬性。因此，包含從數據庫中提取的原始數據的數組將成為一組Game對象。每個Game對象都有一個包含其各自Turn實例的turn字段。然后，該Turn對象將有一個Group字段存儲其各自的Group對象等。
生成這種轉換后的數據是無法擺脫的負擔。處理凌亂的原始數據具有挑戰，并且會導致代碼異味。另一方面，這個后臺發生的過程需要時間。因為處理存儲數千個元素的數組總是非常棘手，當原始記錄有數千行時尤其如此。
換句話說，分層表結構的常見 JOIN 查詢在數據庫和應用程序層都很慢。
列傳播作為一種解決方案
針對這一性能問題，在分層結構將列從父級傳播到其子級可以作為一種解決方案。
為什么應該在分層數據庫上傳播列
在分析上面的 JOIN 查詢時，很明顯問題在于在葉子表Game應用了過濾器。你必須遍歷整個層次結構。但是既然 Game 是層次結構中最重要的元素，為什么不直接在其中添加seasonId、competitionId、phaseId和groupId列呢？這就是列傳播的意義所在。
將外部鍵列直接傳播給子項可以避免所有的 JOIN。現在你可以將上面的查詢替換為以下查詢：
SELECT * FROM `Game` GA
WHERE GA.seasonId = 5
可以想見，這個查詢會比原來的查詢快得多。此外，它會直接返回你感興趣的內容。因此，ORM 數據解耦和轉換過程現在也可以忽略了。
請注意，列傳播涉及數據重復，需要少用、慎用。在深入研究如何優雅實現之前，讓我們看看應該傳播哪些列。
如何選擇要傳播的列
如果向下傳播層次結構中較高的實體的每一列，這在過濾方面可能很有用（例如外部密鑰）。此外，你也可用傳播用于過濾數據的枚舉列，或生成包含來自父級的聚合數據的列來避免 JOIN。
Top 3- 列傳播方法
在選擇列傳播方法時，我們的團隊考慮了三種不同的實現方法。
1. 創建物化視圖
要在層次表結構中實現列傳播，我們首先是想創建具有所需列的物化視圖。物化視圖存儲查詢的結果，它通常表示復雜查詢的行和/或列的子集，例如上面介紹的 JOIN 查詢。
當涉及到具體化查詢時，你可以定義何時生成視圖。然后數據庫會將其存儲在磁盤上并使其像普通表一樣可用。即使生成查詢可能很慢，你也只能一點點地啟動它。因此，物化視圖代表了一種快速的解決方案。
另一方面，物化視圖對處理實時數據可能并非最佳方法，因為物化視圖可能不是最新的。它存儲的數據取決于你決定生成視圖或刷新它的時間。此外，涉及大數據的物化視圖會占用大量磁盤空間，這可能會帶來問題并增加存儲成本。
2. 定義虛擬視圖
另一種可能的解決方案是使用虛擬視圖。同樣，虛擬視圖是存儲查詢結果的表。與物化視圖的不同之處在于，這一次數據庫不會將查詢結果存儲在磁盤上，而是將其保存在內存中。因此，虛擬視圖始終是最新的，從而解決了實時數據的問題。
此外，每次訪問視圖時，數據庫都必須執行生成查詢。所以，如果生成查詢需要時間，那么涉及到視圖的整個過程必然很慢。虛擬視圖是一個強大的工具，但考慮到我們的性能目標，還需尋找其他解決方案。
3. 使用觸發器
SQL 觸發器可以讓你在數據庫中發生特定事件時自動啟動查詢。換句話說，觸發器使你能夠跨數據庫同步數據。因此，在層次結構表中定義所需的列，并讓自定義觸發器更新它們，這樣就可輕松實現列傳播。
因為每次觸發器等待的事件發生時，數據庫都會執行它們，所以可以想見，觸發器會增加性能開銷。執行查詢需要時間和內存，所以會有成本，但與虛擬或物化視圖帶來的缺點相比，這種成本通常可以忽略不計。
觸發器的問題是，定義它們可能需要一些時間。同時，你只能處理此任務一次，并在需要時要對其更新。通過觸發器可以讓你輕松實現列傳播。此外，通過這種方式，我們也極大滿足了客戶定義的性能要求。
層次結構在數據庫中很常見。因為需要長時間的 JOIN 查詢和 ORM 數據處理，過程緩慢且耗時。如果處理不當，可能會導致應用程序出現性能和效率低下的問題。不過，你可以在層次結構中將列從父級傳播到的子級來避免這些問題。
譯者介紹
萬望琳，51CTO社區編輯，資深DBA工程師，具有十余年DBA以及系統運維經驗，曾就職于南網/合生創展等，目前就職于某大型跨國銀行。擁有豐富的系統、Oracle數據庫等維護經驗，IT基礎架構背景，獲得阿里云ACE，CKA，RHCE以及Oracle OCP等認證。擅長領域有Oracle，Ansible，Linux，系統架構，云原生等。
原文標題：??Improving Performance in a Hierarchical SQL Structure???，作者：Antonello Zanini?
