本文將展示在處理分層數(shù)據(jù)結(jié)構(gòu)時(shí)，列傳播如何代表一種提高查詢(xún)性能的簡(jiǎn)單方法。
我們將通過(guò)一個(gè)基于數(shù)據(jù)驅(qū)動(dòng)的項(xiàng)目的真實(shí)場(chǎng)景來(lái)實(shí)現(xiàn)這一點(diǎn)，該項(xiàng)目涉及為體育行業(yè)的初創(chuàng)企業(yè)開(kāi)發(fā)的實(shí)時(shí)數(shù)據(jù)網(wǎng)站。作為分層SQL表結(jié)構(gòu)固有性能問(wèn)題的解決方案，您將了解關(guān)于列傳播的所有需要了解的內(nèi)容。讓我們開(kāi)始吧。

發(fā)生情境

我和我的團(tuán)隊(duì)最近為擁有數(shù)百萬(wàn)頁(yè)面的足球迷開(kāi)發(fā)了一個(gè)網(wǎng)站。該網(wǎng)站的想法是成為足球支持者的權(quán)威資源。數(shù)據(jù)庫(kù)和應(yīng)用程序架構(gòu)并不特別復(fù)雜。這是因?yàn)檎{(diào)度器負(fù)責(zé)定期重新計(jì)算復(fù)雜數(shù)據(jù)并將其存儲(chǔ)在表中，以便查詢(xún)不必涉及SQL聚合。因此，真正的挑戰(zhàn)在于非功能性需求，例如性能和頁(yè)面加載時(shí)間。

應(yīng)用領(lǐng)域

體育行業(yè)中有多家數(shù)據(jù)提供商，每一家都為其客戶提供不同的數(shù)據(jù)集。具體來(lái)說(shuō)，足球行業(yè)有四種類(lèi)型的數(shù)據(jù)：
1.傳記數(shù)據(jù)：身高、寬度、年齡、他們效力的球隊(duì)、獲得的獎(jiǎng)杯、獲得的個(gè)人獎(jiǎng)項(xiàng)以及足球運(yùn)動(dòng)員和教練。

2.歷史數(shù)據(jù)：過(guò)去比賽的結(jié)果以及這些比賽中的事件，例如進(jìn)球、助攻、黃牌、紅牌、傳球等。

3.當(dāng)前和未來(lái)數(shù)據(jù)：本賽季比賽的結(jié)果和這些比賽中發(fā)生的事件，以及未來(lái)比賽的表格。

4.實(shí)時(shí)數(shù)據(jù)：正在進(jìn)行的游戲的實(shí)時(shí)結(jié)果和實(shí)時(shí)事件。

我們的網(wǎng)站涉及所有這些類(lèi)型的數(shù)據(jù)，特別注意歷史數(shù)據(jù)的SEO原因和現(xiàn)場(chǎng)數(shù)據(jù)用以支持投注。

分層表結(jié)構(gòu)

由于我簽署的保密協(xié)議，我無(wú)法與您共享整個(gè)數(shù)據(jù)結(jié)構(gòu)。同時(shí)，了解足球賽季的結(jié)構(gòu)就足以理解這一真實(shí)場(chǎng)景。
具體而言，足球供應(yīng)商通常按以下方式組織一個(gè)賽季的比賽數(shù)據(jù):

季節(jié)：有開(kāi)始和結(jié)束日期，通常持續(xù)一個(gè)日歷年

競(jìng)爭(zhēng)：比賽所屬的聯(lián)賽。一個(gè)賽季內(nèi)有一場(chǎng)比賽。在這里了解更多關(guān)于足球比賽如何運(yùn)作的信息。

階段：與比賽相關(guān)的階段（例如資格賽階段、淘汰賽階段、決賽階段）。每個(gè)比賽都有自己的規(guī)則，許多比賽只有一個(gè)階段。

組：與相位相關(guān)的組（例如，A組、B組、C組……）。有些比賽，如世界杯，涉及不同的小組，每個(gè)小組都有自己的球隊(duì)。大多數(shù)比賽對(duì)所有球隊(duì)只有一個(gè)普通組。

回合：從邏輯角度來(lái)看，相當(dāng)于一天的比賽。它通常持續(xù)一周，涵蓋屬于一個(gè)小組的所有球隊(duì)的比賽（例如，大聯(lián)盟有17場(chǎng)主場(chǎng)比賽和17場(chǎng)客場(chǎng)比賽；因此，它有34個(gè)回合）。

比賽：兩隊(duì)之間的比賽。

如ER模式中所示，這5個(gè)表表示分層數(shù)據(jù)結(jié)構(gòu)：

技術(shù)、規(guī)格和性能要求

我們?cè)诠?jié)點(diǎn)中開(kāi)發(fā)了后端。js 和TypeScript 與 Express 4.17.2和 Sequelize 6.10 一起作為 ORM（對(duì)象關(guān)系映射）。前端是下一個(gè)。js 12應(yīng)用程序是用 TypeScript 開(kāi)發(fā)的。至于數(shù)據(jù)庫(kù)，我們決定選擇由AWS托管的 Postgres 服務(wù)器。

該網(wǎng)站運(yùn)行在 AWS Elastic Beanstalk 上，前端有12個(gè)實(shí)例，后端有8個(gè)實(shí)例，目前每天有1k到5k的觀眾。我們客戶的目標(biāo)是在一年內(nèi)達(dá)到每天6萬(wàn)次的瀏覽量。因此，該網(wǎng)站必須準(zhǔn)備好每月接待數(shù)百萬(wàn)用戶，而不會(huì)出現(xiàn)性能下降。

在谷歌燈塔測(cè)試中，該網(wǎng)站的性能、搜索引擎優(yōu)化和可訪問(wèn)性得分應(yīng)達(dá)到80分以上。此外，加載時(shí)間應(yīng)始終小于2秒，理想情況下為數(shù)百毫秒。真正的挑戰(zhàn)在于此，因?yàn)樵摼W(wǎng)站由200多萬(wàn)頁(yè)組成，預(yù)渲染將需要數(shù)周時(shí)間。此外，大多數(shù)頁(yè)面上顯示的內(nèi)容不是靜態(tài)的。因此，我們選擇了增量靜態(tài)再生方法。當(dāng)一個(gè)訪問(wèn)者點(diǎn)擊了一個(gè)從來(lái)沒(méi)有人訪問(wèn)過(guò)的頁(yè)面，接下來(lái)。js 使用從后端公開(kāi)的API檢索的數(shù)據(jù)生成它。然后，下一步。js 根據(jù)頁(yè)面的重要性將頁(yè)面緩存30秒或60秒。

因此，后端必須快速提供服務(wù)器端生成過(guò)程所需的數(shù)據(jù)。

為什么查詢(xún)分層表很慢

現(xiàn)在，讓我們看看為什么層次表結(jié)構(gòu)可以代表性能挑戰(zhàn)。

連接查詢(xún)速度慢

層次數(shù)據(jù)結(jié)構(gòu)中的一種常見(jiàn)場(chǎng)景是，您希望根據(jù)與層次中更高層次的對(duì)象相關(guān)聯(lián)的參數(shù)來(lái)過(guò)濾葉子。例如，您可能希望檢索在特定季節(jié)中玩的所有游戲。由于葉表游戲沒(méi)有直接連接到季節(jié)，因此必須執(zhí)行一個(gè)查詢(xún)，該查詢(xún)涉及的連接數(shù)與層次結(jié)構(gòu)中的元素?cái)?shù)相同。因此，您可能會(huì)編寫(xiě)以下查詢(xún)：

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

這樣的查詢(xún)速度很慢。每個(gè)連接執(zhí)行笛卡爾乘積操作，這需要時(shí)間，可能會(huì)產(chǎn)生數(shù)千條記錄。因此，層次數(shù)據(jù)結(jié)構(gòu)越長(zhǎng)，性能就越差。

此外，如果您想檢索所有數(shù)據(jù)，而不僅僅是游戲表中的列，那么由于笛卡爾乘積的性質(zhì)，您將不得不處理數(shù)千行數(shù)百列。這可能會(huì)變得混亂，但這就是ORM發(fā)揮作用的地方。

ORM數(shù)據(jù)解耦和轉(zhuǎn)換需要時(shí)間

當(dāng)通過(guò)ORM查詢(xún)數(shù)據(jù)庫(kù)時(shí)，您通常感興趣的是在其應(yīng)用程序級(jí)表示中檢索數(shù)據(jù)。原始數(shù)據(jù)庫(kù)級(jí)表示在應(yīng)用程序級(jí)可能沒(méi)有用處。因此，當(dāng)大多數(shù)高級(jí)orm執(zhí)行查詢(xún)時(shí)，它們從數(shù)據(jù)庫(kù)中檢索所需的數(shù)據(jù)，并將其轉(zhuǎn)換為應(yīng)用程序級(jí)表示。這個(gè)過(guò)程包括兩個(gè)步驟：數(shù)據(jù)解耦和數(shù)據(jù)轉(zhuǎn)換。

在后臺(tái)，來(lái)自連接查詢(xún)的原始數(shù)據(jù)首先被解耦，然后在應(yīng)用程序級(jí)別轉(zhuǎn)換為各自的表示。因此，在處理所有數(shù)據(jù)時(shí)，具有數(shù)百列的數(shù)千條記錄成為一個(gè)小數(shù)據(jù)集，每個(gè)記錄都具有在數(shù)據(jù)模型類(lèi)中定義的屬性。因此，包含從數(shù)據(jù)庫(kù)中提取的原始數(shù)據(jù)的數(shù)組將成為一組游戲?qū)ο蟆Ｃ總€(gè)游戲?qū)ο蠖加幸粋€(gè)包含其各自回合實(shí)例的回合場(chǎng)。然后，轉(zhuǎn)彎對(duì)象將有一個(gè)組字段，存儲(chǔ)其各自的組對(duì)象等。

生成這種轉(zhuǎn)換后的數(shù)據(jù)是您愿意接受的開(kāi)銷(xiāo)。處理雜亂的原始數(shù)據(jù)具有挑戰(zhàn)性，并會(huì)導(dǎo)致代碼警報(bào)。另一方面，這一過(guò)程發(fā)生在幕后需要時(shí)間，你不能忽視它。當(dāng)原始記錄有數(shù)千行時(shí)，情況尤其如此，因?yàn)樘幚泶鎯?chǔ)數(shù)千個(gè)元素的數(shù)組總是很棘手。

換句話說(shuō)，在數(shù)據(jù)庫(kù)層和應(yīng)用程序?qū)?，?duì)分層表結(jié)構(gòu)的常見(jiàn)連接查詢(xún)都很慢。

作為解決方案的列傳播

解決方案是在層次結(jié)構(gòu)中將列從父級(jí)傳播到其子級(jí)，以避免此性能問(wèn)題。讓我們了解原因。

為什么應(yīng)該在分層數(shù)據(jù)庫(kù)上傳播列

在分析上述連接查詢(xún)時(shí)，很明顯問(wèn)題在于對(duì)葉表游戲應(yīng)用過(guò)濾器。你必須遍歷整個(gè)層次結(jié)構(gòu)。但是，既然游戲是層次結(jié)構(gòu)中最重要的元素，為什么不直接添加季節(jié)ID、競(jìng)爭(zhēng)ID、階段ID和組ID列呢？這就是列傳播的意義！

通過(guò)將外部鍵列直接傳播到子級(jí)，可以避免所有連接?，F(xiàn)在，您可以將上面的查詢(xún)替換為以下查詢(xún)：

SELECT * FROM `Game` GA
WHERE GA.seasonId = 5

可以想象，此查詢(xún)比原始查詢(xún)快得多。此外，它直接返回您感興趣的內(nèi)容。因此，您現(xiàn)在可以忽略O(shè)RM數(shù)據(jù)解耦和轉(zhuǎn)換過(guò)程。

請(qǐng)注意，列傳播涉及數(shù)據(jù)復(fù)制，您應(yīng)該謹(jǐn)慎而明智地使用它。但是在深入研究如何熟練地實(shí)現(xiàn)它之前，讓我們看看應(yīng)該傳播哪些列。

如何選擇要傳播的列

如果您向下傳播層次結(jié)構(gòu)中較高的實(shí)體的每一列，這將有所幫助；這在過(guò)濾方面可能很有用。例如，這涉及到外部鍵。此外，您可能希望傳播用于過(guò)濾數(shù)據(jù)的枚舉列，或使用來(lái)自父級(jí)的聚合數(shù)據(jù)生成列，以避免連接。

列傳播的前三種方法

當(dāng)我的團(tuán)隊(duì)選擇列傳播方法時(shí)，我們考慮了三種不同的實(shí)現(xiàn)方法。讓我們分析一下。

1、創(chuàng)建物化視圖

我們必須在層次表結(jié)構(gòu)中實(shí)現(xiàn)列傳播的第一個(gè)想法是創(chuàng)建具有所需列的物化視圖。物化視圖存儲(chǔ)查詢(xún)結(jié)果，通常表示復(fù)雜查詢(xún)（如上面的連接查詢(xún)）的行和/或列的子集。

對(duì)于物化查詢(xún)，您可以定義何時(shí)生成視圖。然后，您的數(shù)據(jù)庫(kù)負(fù)責(zé)將其存儲(chǔ)在磁盤(pán)上，并使其像普通表一樣可用。即使生成查詢(xún)可能很慢，您也只能謹(jǐn)慎地啟動(dòng)它。因此，物化視圖代表了一種快速解決方案。

另一方面，物化視圖不是處理實(shí)時(shí)數(shù)據(jù)的最佳方法。這是因?yàn)槲锘晥D可能不是最新的。它存儲(chǔ)的數(shù)據(jù)取決于您決定何時(shí)生成視圖或刷新視圖。此外，涉及大數(shù)據(jù)的物化視圖會(huì)占用大量磁盤(pán)空間，這可能是一個(gè)問(wèn)題，并會(huì)耗費(fèi)您的存儲(chǔ)成本。

2、定義虛擬視圖

另一種可能的解決方案是使用虛擬視圖。同樣，虛擬視圖是存儲(chǔ)查詢(xún)結(jié)果的表。與物化視圖的不同之處在于，這次數(shù)據(jù)庫(kù)不會(huì)將查詢(xún)結(jié)果存儲(chǔ)在磁盤(pán)上，而是將其保存在內(nèi)存中。因此，虛擬視圖總是最新的，可以解決實(shí)時(shí)數(shù)據(jù)的問(wèn)題。

另一方面，每次訪問(wèn)視圖時(shí)，數(shù)據(jù)庫(kù)都必須執(zhí)行生成查詢(xún)。因此，如果生成查詢(xún)需要時(shí)間，那么涉及視圖的整個(gè)過(guò)程只能很慢。虛擬視圖是一個(gè)強(qiáng)大的工具，但考慮到我們的性能目標(biāo)，我們不得不尋找另一種解決方案。

3、使用觸發(fā)器

SQL觸發(fā)器允許您在數(shù)據(jù)庫(kù)中發(fā)生特定事件時(shí)自動(dòng)啟動(dòng)查詢(xún)。換句話說(shuō)，觸發(fā)器使您能夠跨數(shù)據(jù)庫(kù)同步數(shù)據(jù)。因此，通過(guò)在層次結(jié)構(gòu)表中定義所需的列并讓自定義觸發(fā)器更新它們，您可以輕松實(shí)現(xiàn)列傳播。

可以想象，觸發(fā)器會(huì)增加性能開(kāi)銷(xiāo)。這是因?yàn)槊看嗡鼈兊却氖录l(fā)生時(shí)，數(shù)據(jù)庫(kù)都會(huì)執(zhí)行它們。但是執(zhí)行查詢(xún)需要時(shí)間和內(nèi)存。因此，觸發(fā)器是有成本的。另一方面，這種成本通?？梢院雎圆挥?jì)，尤其是與虛擬或物化視圖的缺點(diǎn)相比。

觸發(fā)器的問(wèn)題是，定義它們可能需要一些時(shí)間。同時(shí)，您只能處理此任務(wù)一次，并在需要時(shí)進(jìn)行更新。因此，觸發(fā)器允許您輕松地實(shí)現(xiàn)列傳播。此外，由于我們采用了列傳播并使用觸發(fā)器實(shí)現(xiàn)了它，因此我們?cè)诤艽蟪潭壬蠞M足了客戶定義的性能要求。

層次結(jié)構(gòu)在數(shù)據(jù)庫(kù)中很常見(jiàn)，如果處理不當(dāng)，可能會(huì)導(dǎo)致應(yīng)用程序中的性能問(wèn)題和效率低下。這是因?yàn)樗鼈冃枰L(zhǎng)連接查詢(xún)和ORM數(shù)據(jù)處理，速度慢且耗時(shí)。幸運(yùn)的是，您可以通過(guò)將列從父級(jí)傳播到層次結(jié)構(gòu)中的子級(jí)來(lái)避免這一切。我希望這個(gè)真實(shí)的案例研究可以幫助您構(gòu)建更好更快的應(yīng)用程序！

原文標(biāo)題：Improving Performance in a Hierarchical SQL Structure
原文作者：Antonello Zanini
原文地址：https://dzone.com/articles/improving-performance-in-a-hierarchical-sql-struct