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