?譯者 | 萬望琳
審校 | 孫淑娟 梁策
本文將展示在處理分層數(shù)據(jù)結(jié)構(gòu)時，列傳播這一直接提高查詢性能的方法。本文將使用基于數(shù)據(jù)驅(qū)動項目的真實場景來講解，其中項目為某體育行業(yè)初創(chuàng)公司開發(fā)的實時數(shù)據(jù)網(wǎng)站。本文將帶你了解有關(guān)列傳播的相關(guān)知識，以解決分層 SQL 表結(jié)構(gòu)中固有的性能問題。
背景
本文所做項目涉及一個擁有數(shù)百萬頁面的足球球迷網(wǎng)站。該網(wǎng)站致力于成為球迷心中的權(quán)威，尤其是在投注方面。因為調(diào)度程序負責定期重新計算復雜數(shù)據(jù)并將其存儲在表中，這樣查詢就不必涉及SQL 聚合，數(shù)據(jù)庫和應用程序架構(gòu)也不是特別復雜。因此，真正的挑戰(zhàn)在于非功能性需求，例如性能和頁面加載時間。
應用領(lǐng)域
體育行業(yè)的數(shù)據(jù)來源有很多，每個來源都為其客戶提供不同的數(shù)據(jù)集。具體來說，足球行業(yè)有四種類型的數(shù)據(jù)：
- 個人檔案數(shù)據(jù)：身高、體重、年齡、效力球隊、所獲獎杯、個人獎項、球員和教練。
- 歷史數(shù)據(jù)：過往賽果和技術(shù)統(tǒng)計，如進球、助攻、黃牌、紅牌、傳球等。
- 當前和未來數(shù)據(jù)：當前賽季已完結(jié)和將進行的比賽結(jié)果與技術(shù)統(tǒng)計。
- 實時數(shù)據(jù)：比賽實時結(jié)果與技術(shù)統(tǒng)計。
該網(wǎng)站涉及所有這些類型的數(shù)據(jù)，同時特別關(guān)注有利于搜索引擎優(yōu)化的歷史數(shù)據(jù)和支持投注的實時數(shù)據(jù)。
分層表結(jié)構(gòu)
出于保密要求，部分數(shù)據(jù)結(jié)構(gòu)無法完全公開。但通過足球賽季的結(jié)構(gòu)也可以了解相關(guān)情況。
具體來說，足球提供商通常按如下方式組織賽季中的比賽數(shù)據(jù)：
- 賽季(Season)：有開始和結(jié)束日期，通常持續(xù)一個日歷年。
- 賽事(Competition)：比賽所屬的賽事。
- 階段(Phase)：賽事所處的階段（例如，資格賽、淘汰賽、決賽階段）。每個賽事都有自己的規(guī)則，很多賽事只有一個階段。
- 組別(Group)：與階段相關(guān)的組（例如，A 組、B 組、C 組……）。像世界杯等賽事會涉及不同的組別，每個組內(nèi)涵蓋相應球隊。大多數(shù)賽事只有一個通用組適用于所有球隊。
- 回合(Turn)：是從邏輯上相對于進行一天的賽事而言的。通常持續(xù)一周，涵蓋屬于一個小組的所有球隊的比賽（例如，MLS 有 17 場主場比賽和 17 場客場比賽，因此它有 34 個回合）。
- 比賽(Game)：兩支足球隊之間的比賽。
如下圖ER 模式所示，這 5 張表代表了一個分層數(shù)據(jù)結(jié)構(gòu)：
技術(shù)、參數(shù)和性能要求
我們使用Express 4.17.2和 Sequelize 6.10作為 ORM（對象關(guān)系映射）在 Node.js 和 TypeScript 中開發(fā)后端。前端是使用 TypeScript 開發(fā)的 Next.js 12應用程序。數(shù)據(jù)庫則選用由 AWS 托管的 Postgres 服務器。
該網(wǎng)站在AWS Elastic Beanstalk上運行，前端有 12 個實例，后端有 8 個實例，目前每天有 1000到 5000的訪問者。客戶的目標是在一年內(nèi)達到每天6萬的瀏覽量，因此該網(wǎng)站必須準備好在無損性能的情況下托管數(shù)百萬月度用戶。
在Google Lighthouse測試中，該網(wǎng)站應性能、SEO 和可訪問性方面得分超過了80。此外，加載時間應始終小于 2 秒，理想情況下為幾百毫秒。真正的挑戰(zhàn)在于，該網(wǎng)站包含超過 200 萬個頁面，預渲染它們都需要數(shù)周時間。此外，大多數(shù)頁面上顯示的內(nèi)容都不是靜態(tài)的。因此，我們選擇了增量靜態(tài)再生方法。當訪問者點擊一個沒有人訪問過的頁面時，Next.js 會使用從后端公開的 API 檢索到的數(shù)據(jù)生成它。然后，Next.js 將頁面緩存 30 或 60 秒，具體取決于頁面的重要性。
因此，后端必須快速為服務器端生成過程提供所需的數(shù)據(jù)。
為什么查詢分層表很慢
現(xiàn)在讓我們看看為什么分層表結(jié)構(gòu)會帶來性能挑戰(zhàn)。
JOIN 查詢速度很慢
根據(jù)與層次結(jié)構(gòu)中較高對象關(guān)聯(lián)的參數(shù)過濾葉子是分層數(shù)據(jù)結(jié)構(gòu)中的一個常見場景。比如，檢索在特定賽季中進行的所有比賽。由于葉表Game不直接連接到Season，因此你必須執(zhí)行一個與層次結(jié)構(gòu)中的元素一樣多的 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 都會執(zhí)行一次笛卡爾積運算，這需要時間并且可能會產(chǎn)生數(shù)千條記錄。因此，分層數(shù)據(jù)結(jié)構(gòu)越長，性能就越差。
此外，如果你想檢索所有數(shù)據(jù)而不僅僅是表中的Game列，由于笛卡爾積的性質(zhì)，你必須處理數(shù)千行和數(shù)百列。這個過程可能會變得混亂，但這正是 ORM 發(fā)揮作用的地方。
ORM數(shù)據(jù)解耦和轉(zhuǎn)換需要時間
通過 ORM 查詢數(shù)據(jù)庫時，你可能會對檢索基于應用程序級別的表中的數(shù)據(jù)感興趣。原始數(shù)據(jù)庫級別表示在應用程序級別可能沒有用。因此，當大多數(shù)高級 ORM 執(zhí)行查詢時，它們會從數(shù)據(jù)庫中檢索所需數(shù)據(jù)并將其轉(zhuǎn)換為應用程序級表示。這個過程包括兩個步驟：數(shù)據(jù)解耦和數(shù)據(jù)轉(zhuǎn)換。
在后臺，來自 JOIN 查詢的原始數(shù)據(jù)首先被解耦，然后在應用程序級別轉(zhuǎn)換為相應的表示。因此，在處理所有數(shù)據(jù)時，具有數(shù)百列的數(shù)千條記錄成為一個小組數(shù)據(jù)，每個數(shù)據(jù)都具有數(shù)據(jù)模型類中定義的屬性。因此，包含從數(shù)據(jù)庫中提取的原始數(shù)據(jù)的數(shù)組將成為一組Game對象。每個Game對象都有一個包含其各自Turn實例的turn字段。然后，該Turn對象將有一個Group字段存儲其各自的Group對象等。
生成這種轉(zhuǎn)換后的數(shù)據(jù)是無法擺脫的負擔。處理凌亂的原始數(shù)據(jù)具有挑戰(zhàn)，并且會導致代碼異味。另一方面，這個后臺發(fā)生的過程需要時間。因為處理存儲數(shù)千個元素的數(shù)組總是非常棘手，當原始記錄有數(shù)千行時尤其如此。
換句話說，分層表結(jié)構(gòu)的常見 JOIN 查詢在數(shù)據(jù)庫和應用程序?qū)佣己苈?/p>
列傳播作為一種解決方案
針對這一性能問題，在分層結(jié)構(gòu)將列從父級傳播到其子級可以作為一種解決方案。
為什么應該在分層數(shù)據(jù)庫上傳播列
在分析上面的 JOIN 查詢時，很明顯問題在于在葉子表Game應用了過濾器。你必須遍歷整個層次結(jié)構(gòu)。但是既然 Game 是層次結(jié)構(gòu)中最重要的元素，為什么不直接在其中添加seasonId、competitionId、phaseId和groupId列呢？這就是列傳播的意義所在。
將外部鍵列直接傳播給子項可以避免所有的 JOIN?，F(xiàn)在你可以將上面的查詢替換為以下查詢：
SELECT * FROM `Game` GA
WHERE GA.seasonId = 5
可以想見，這個查詢會比原來的查詢快得多。此外，它會直接返回你感興趣的內(nèi)容。因此，ORM 數(shù)據(jù)解耦和轉(zhuǎn)換過程現(xiàn)在也可以忽略了。
請注意，列傳播涉及數(shù)據(jù)重復，需要少用、慎用。在深入研究如何優(yōu)雅實現(xiàn)之前，讓我們看看應該傳播哪些列。
如何選擇要傳播的列
如果向下傳播層次結(jié)構(gòu)中較高的實體的每一列，這在過濾方面可能很有用（例如外部密鑰）。此外，你也可用傳播用于過濾數(shù)據(jù)的枚舉列，或生成包含來自父級的聚合數(shù)據(jù)的列來避免 JOIN。
Top 3- 列傳播方法
在選擇列傳播方法時，我們的團隊考慮了三種不同的實現(xiàn)方法。
1. 創(chuàng)建物化視圖
要在層次表結(jié)構(gòu)中實現(xiàn)列傳播，我們首先是想創(chuàng)建具有所需列的物化視圖。物化視圖存儲查詢的結(jié)果，它通常表示復雜查詢的行和/或列的子集，例如上面介紹的 JOIN 查詢。
當涉及到具體化查詢時，你可以定義何時生成視圖。然后數(shù)據(jù)庫會將其存儲在磁盤上并使其像普通表一樣可用。即使生成查詢可能很慢，你也只能一點點地啟動它。因此，物化視圖代表了一種快速的解決方案。
另一方面，物化視圖對處理實時數(shù)據(jù)可能并非最佳方法，因為物化視圖可能不是最新的。它存儲的數(shù)據(jù)取決于你決定生成視圖或刷新它的時間。此外，涉及大數(shù)據(jù)的物化視圖會占用大量磁盤空間，這可能會帶來問題并增加存儲成本。
2. 定義虛擬視圖
另一種可能的解決方案是使用虛擬視圖。同樣，虛擬視圖是存儲查詢結(jié)果的表。與物化視圖的不同之處在于，這一次數(shù)據(jù)庫不會將查詢結(jié)果存儲在磁盤上，而是將其保存在內(nèi)存中。因此，虛擬視圖始終是最新的，從而解決了實時數(shù)據(jù)的問題。
此外，每次訪問視圖時，數(shù)據(jù)庫都必須執(zhí)行生成查詢。所以，如果生成查詢需要時間，那么涉及到視圖的整個過程必然很慢。虛擬視圖是一個強大的工具，但考慮到我們的性能目標，還需尋找其他解決方案。
3. 使用觸發(fā)器
SQL 觸發(fā)器可以讓你在數(shù)據(jù)庫中發(fā)生特定事件時自動啟動查詢。換句話說，觸發(fā)器使你能夠跨數(shù)據(jù)庫同步數(shù)據(jù)。因此，在層次結(jié)構(gòu)表中定義所需的列，并讓自定義觸發(fā)器更新它們，這樣就可輕松實現(xiàn)列傳播。
因為每次觸發(fā)器等待的事件發(fā)生時，數(shù)據(jù)庫都會執(zhí)行它們，所以可以想見，觸發(fā)器會增加性能開銷。執(zhí)行查詢需要時間和內(nèi)存，所以會有成本，但與虛擬或物化視圖帶來的缺點相比，這種成本通?？梢院雎圆挥嫛?/p>
觸發(fā)器的問題是，定義它們可能需要一些時間。同時，你只能處理此任務一次，并在需要時要對其更新。通過觸發(fā)器可以讓你輕松實現(xiàn)列傳播。此外，通過這種方式，我們也極大滿足了客戶定義的性能要求。
層次結(jié)構(gòu)在數(shù)據(jù)庫中很常見。因為需要長時間的 JOIN 查詢和 ORM 數(shù)據(jù)處理，過程緩慢且耗時。如果處理不當，可能會導致應用程序出現(xiàn)性能和效率低下的問題。不過，你可以在層次結(jié)構(gòu)中將列從父級傳播到的子級來避免這些問題。
譯者介紹
萬望琳，51CTO社區(qū)編輯，資深DBA工程師，具有十余年DBA以及系統(tǒng)運維經(jīng)驗，曾就職于南網(wǎng)/合生創(chuàng)展等，目前就職于某大型跨國銀行。擁有豐富的系統(tǒng)、Oracle數(shù)據(jù)庫等維護經(jīng)驗，IT基礎(chǔ)架構(gòu)背景，獲得阿里云ACE，CKA，RHCE以及Oracle OCP等認證。擅長領(lǐng)域有Oracle，Ansible，Linux，系統(tǒng)架構(gòu)，云原生等。
原文標題：??Improving Performance in a Hierarchical SQL Structure???，作者：Antonello Zanini?
