一、有數(shù) BI 介紹與性能痛點(diǎn)

首先給大家介紹下有數(shù) BI。

1、有數(shù) BI 介紹

有數(shù) BI 最大的特點(diǎn)是使用 PPT 制作的方式來(lái)制作報(bào)表。平臺(tái)的使用方式為：

① 首先準(zhǔn)備一個(gè)數(shù)據(jù)源，可以是 excel，也可以 MySQL、Oracle 等，當(dāng)然 Hive 和 Impala 等很多其他數(shù)據(jù)源也是可以的。

② 制作數(shù)據(jù)模型?？梢酝ㄟ^(guò)多張底層數(shù)據(jù)表，或者是自定義 SQL 將它們關(guān)聯(lián)在一起就形成了數(shù)據(jù)模型。

③ 在數(shù)據(jù)模型之上可以構(gòu)建數(shù)據(jù)應(yīng)用。例如數(shù)據(jù)報(bào)表、數(shù)據(jù)大屏、數(shù)據(jù)門戶等。

2、BI 性能痛點(diǎn)

在用戶使用過(guò)程中，有數(shù) BI 發(fā)現(xiàn)了一些性能痛點(diǎn)：

（1）痛點(diǎn) 1-多表關(guān)聯(lián)查詢慢

因?yàn)閿?shù)據(jù)模型是由多表關(guān)聯(lián)構(gòu)成的，復(fù)雜的關(guān)聯(lián)會(huì)帶來(lái)巨大的性能開銷，導(dǎo)致報(bào)告查詢遲遲無(wú)法響應(yīng)。

（2）痛點(diǎn) 2-只查部分卻掃全部

這里基本可分為兩類場(chǎng)景。

場(chǎng)景一：行的數(shù)據(jù)。常常出現(xiàn)的場(chǎng)景是僅需要近期的數(shù)據(jù)計(jì)算，但是因?yàn)槟Ｐ褪嵌啾黻P(guān)聯(lián)查詢的，每次落庫(kù)查詢都會(huì)導(dǎo)致全表掃描。

場(chǎng)景二：列的數(shù)據(jù)。很多時(shí)候報(bào)表展示僅需要部分列信息，但是因?yàn)槟Ｐ偷年P(guān)聯(lián)查詢，導(dǎo)致需要全字段關(guān)聯(lián)落庫(kù)，也會(huì)帶來(lái)很大的性能損耗。

（3）痛點(diǎn) 3-篩選需求因人而異

有些報(bào)表會(huì)提供多個(gè)維度的查詢篩選器，篩選器的默認(rèn)值無(wú)法滿足需求，不同用戶的查詢條件不同，導(dǎo)致預(yù)加載緩存無(wú)法命中，使得落庫(kù)查詢性能降低。

二、數(shù)據(jù)庫(kù)物化視圖基本原理

基于以上痛點(diǎn)，我們開始嘗試通過(guò)物化視圖的方式解決問(wèn)題。

1、什么是物化視圖

物化視圖的概念來(lái)自數(shù)據(jù)庫(kù)，例如 Oracle、Doris 就有這樣的概念（MySQL 沒(méi)有）。它的本質(zhì)是通過(guò)預(yù)計(jì)算保存 SQL 查詢的結(jié)果數(shù)據(jù)，相較于普通視圖僅僅是一段靜態(tài)的 SQL 文本，預(yù)計(jì)算的數(shù)據(jù)一旦命中能更好的加速執(zhí)行性能。

常見分類：

① 關(guān)聯(lián)表數(shù)量：?jiǎn)伪砦锘晥D、多表物化視圖。

② 是否聚合：明細(xì)物化視圖、聚合物化視圖。

③ 更新策略：全量更新、增量更新。

接下來(lái)給出一個(gè)簡(jiǎn)單的物化視圖的例子。

2、物化視圖示例

上圖中可以看到：

① V1 是我們創(chuàng)建的物化視圖，數(shù)據(jù)來(lái)源為 T1 JOIN T2，時(shí)間跨度從 T1 日期的 2022-07-01 到 2022-07-31，視圖包含三個(gè)結(jié)果字段“地區(qū)”、“類別”、“利潤(rùn)”。

② 用戶查詢了一個(gè) SQL，我們可以看到，查詢的數(shù)據(jù)來(lái)源也是 T1 JOIN T2，時(shí)間跨度從 2022-07-10 到 2022-07-20，所查詢的字段也在 V1 范圍內(nèi)。

③ 因此我們可以將上述用戶的 SQL 改寫為直接從 V1 物化視圖讀取數(shù)據(jù)，并使用用戶 SQL 的過(guò)濾條件，形成了物化視圖改寫后的 SQL。

3、數(shù)據(jù)庫(kù)物化視圖基本架構(gòu)

上圖是一般情況下數(shù)據(jù)庫(kù)的物化視圖基本架構(gòu)。

圖左邊是用戶通過(guò)物化視圖的 DDL 進(jìn)行視圖的創(chuàng)建、管理和更新。當(dāng)然數(shù)據(jù)庫(kù)本身也會(huì)進(jìn)行視圖數(shù)據(jù)的同步和元數(shù)據(jù)管理。數(shù)據(jù)同步的流向就是從原始表中抽取同步到物化視圖中。

圖右邊是用戶開始查詢 SQL，該 SQL 進(jìn)入到數(shù)據(jù)庫(kù)后，通過(guò)命中校驗(yàn)?zāi)K判別是否存在匹配的物化視圖，如果存在則通過(guò)改寫模塊對(duì) SQL 進(jìn)行改寫。

4、BI 場(chǎng)景下直接使用數(shù)據(jù)庫(kù)物化視圖的問(wèn)題

在 BI 場(chǎng)景下直接按照數(shù)據(jù)庫(kù)的邏輯使用物化視圖是會(huì)存在一些問(wèn)題的。

① 以 SQL 為粒度，無(wú)法與 BI 模型建立直接綁定，也存在構(gòu)建的人工溝通成本。

② 無(wú)法利用 BI 的模型、報(bào)告、圖表的整體信息及查詢信息創(chuàng)建更好的物化視圖。

③ BI 產(chǎn)品支持很多種類的數(shù)據(jù)庫(kù)，它們的物化視圖特點(diǎn)不同，無(wú)法統(tǒng)一支持，需要分別適配和管理。甚至有的數(shù)據(jù)庫(kù)不支持物化視圖。

④ 無(wú)法跨數(shù)據(jù)庫(kù)種類進(jìn)行關(guān)聯(lián)后再創(chuàng)建物化視圖。

⑤ 通常缺乏物化視圖管理 UI 界面，只能通過(guò) DDL 管理。

因此我們考慮是否可以在 BI 層做物化視圖，該物化視圖以模型為粒度，可以充分利用模型的信息，可以屏蔽底層的數(shù)據(jù)源類型，我們也可以為這個(gè)功能提供 UI 界面方便管理?；谶@個(gè)想法，我們?cè)O(shè)計(jì)了自己的物化視圖。

三、有數(shù) BI 物化視圖產(chǎn)品設(shè)計(jì)

接下來(lái)先來(lái)看一下我們的產(chǎn)品設(shè)計(jì)。

1、物化視圖配置入口

上面提到，有數(shù) BI 的物化視圖是以模型為單位的，所以在模型的右上角可以點(diǎn)擊物化視圖的選項(xiàng)，之后就會(huì)進(jìn)入到物化視圖的配置頁(yè)面。從頁(yè)面中可以看出，一個(gè)模型支持創(chuàng)建多個(gè)物化視圖，我們也提供了一個(gè)統(tǒng)一開關(guān)可以控制物化視圖的開啟和關(guān)閉。在創(chuàng)建物化視圖按鈕或者點(diǎn)擊某個(gè)物化視圖的編輯按鈕后，就跳轉(zhuǎn)到了物化視圖的配置頁(yè)面。

2、物化視圖配置頁(yè)面

從頁(yè)面上我們可以看到：

① 對(duì)于物化視圖，系統(tǒng)默認(rèn)會(huì)物化全部明細(xì)數(shù)據(jù)。

② 用戶可以根據(jù)需求選擇物化部分字段。

③ 系統(tǒng)支持物化聚合數(shù)據(jù)。

④ 系統(tǒng)支持對(duì)物化字段進(jìn)行數(shù)據(jù)篩選。

3、物化視圖執(zhí)行計(jì)劃頁(yè)面

在這個(gè)頁(yè)面中：

① 支持配置建表方式和物化引擎。

② 支持配置執(zhí)行計(jì)劃。

③ 支持配置依賴執(zhí)行的物化規(guī)則。

④ 支持配置定時(shí)計(jì)劃。

4、物化視圖管理頁(yè)面

在這個(gè)頁(yè)面中：

① 提供各物化視圖的模型信息、狀態(tài)、調(diào)度時(shí)間、占用空間、成功率、創(chuàng)建人等基本信息。

② 提供立即執(zhí)行物化視圖任務(wù)的功能。

③ 提供物化視圖的歷史物化記錄。

④ 提供任務(wù)的告警設(shè)置。

⑤ 提供物化狀態(tài)的日?qǐng)?bào)定時(shí)推送功能。

四、有數(shù) BI 物化視圖實(shí)現(xiàn)原理

1、有數(shù) BI 物化視圖架構(gòu)

整個(gè)架構(gòu)從左至右共分為三塊結(jié)構(gòu)。

最左邊是數(shù)據(jù)源，有數(shù) BI 支持 Excel、MySQL、Spark 等數(shù)據(jù)源，物化視圖即是將它們內(nèi)部的數(shù)據(jù)在關(guān)聯(lián)和其他操作后寫入到 MPP 中。

中間的是數(shù)據(jù)模型。通過(guò)左邊的數(shù)據(jù)源關(guān)聯(lián)構(gòu)建數(shù)據(jù)模型。物化視圖的物化配置也在這個(gè)結(jié)構(gòu)中，包括維度、度量、聚合信息、篩選范圍等配置項(xiàng)。這些配置項(xiàng)再加上模型配置，可以轉(zhuǎn)換為有數(shù) BI 的 ETL 任務(wù)配置，以進(jìn)行數(shù)據(jù)同步和實(shí)際的數(shù)據(jù)物化。通過(guò)對(duì)該 ETL 任務(wù)配置進(jìn)行元信息推導(dǎo)，可以生成 ETL 元信息，該 ETL 元信息將用于右側(cè)數(shù)據(jù)查詢過(guò)程的物化視圖改寫模塊，以進(jìn)行數(shù)據(jù)查詢的物化視圖改寫。

右邊的是數(shù)據(jù)應(yīng)用，通過(guò)數(shù)據(jù)模型構(gòu)建而來(lái)，包括可視化報(bào)告、自助取數(shù)、數(shù)據(jù)大屏等。這些應(yīng)用中的查詢會(huì)生成查詢 DSL 給數(shù)據(jù)物化查詢改寫模塊，結(jié)合上述中間結(jié)構(gòu)中傳遞來(lái)的物化視圖配置和 ETL 元信息，進(jìn)行物化查詢的改寫和調(diào)整，最終生成改寫后的物化 SQL，然后將物化 SQL 發(fā)送給 MPP 進(jìn)行數(shù)據(jù)查詢。MPP 中的數(shù)據(jù)就是上述生成的 ETL 任務(wù)從數(shù)據(jù)源中抽取寫入的。

接下來(lái)詳細(xì)闡述這些結(jié)構(gòu)和流程。

2、物化視圖 ETL 生成

首先，將物化視圖的配置和模型的配置轉(zhuǎn)換為 ETL 配置，這里會(huì)用到 ETL 的輸入節(jié)點(diǎn)、關(guān)聯(lián)節(jié)點(diǎn)、清洗節(jié)點(diǎn)、聚合節(jié)點(diǎn)、輸出節(jié)點(diǎn)。之后進(jìn)行 ETL 元信息的推導(dǎo)，生成視圖表信息、字段信息、字段映射關(guān)系和聚合信息。

上圖的下半部分給出了一個(gè)例子。

例子中有一個(gè)具體的模型，從模型配置上看，它是由三張表進(jìn)行關(guān)聯(lián)的。對(duì)于物化配置，指定了 4 個(gè)維度和 3 個(gè)度量；還有一個(gè)篩選范圍，選的是發(fā)貨日期的前 7 天。接下來(lái)，根據(jù)上述兩個(gè)配置可以轉(zhuǎn)化為 ETL 的配置。

在 ETL 的配置中首先有關(guān)聯(lián)節(jié)點(diǎn)，將三張表關(guān)聯(lián)起來(lái)。接下來(lái)是一個(gè)清洗節(jié)點(diǎn)，用于數(shù)據(jù)篩選，例子中物化配置中的發(fā)貨日期的前 7 天的篩選條件就會(huì)置于清洗節(jié)點(diǎn)中。接下來(lái)是聚合節(jié)點(diǎn)，配置了物化配置中的維度和度量。最后是一個(gè)內(nèi)部輸出節(jié)點(diǎn)，表示數(shù)據(jù)將輸出到內(nèi)部的 MPP 中。

接下來(lái)會(huì)從 ETL 配置自動(dòng)推導(dǎo)出元信息，圖中示例主要展示了字段的信息。

3、物化視圖查詢流程

數(shù)據(jù)應(yīng)用查詢的時(shí)候會(huì)生成查詢 DSL，包括了維度、度量、篩選器、排序等一些配置。DSL 解析之后會(huì)生成一組查詢 AST。這些 AST 將依據(jù)物化配置和 ETL 元信息，通過(guò)優(yōu)先級(jí)排序、命中校驗(yàn)等步驟，最終轉(zhuǎn)化為物化 AST。之后經(jīng)過(guò) SQL 生成和優(yōu)化的環(huán)節(jié)，最終到達(dá) MPP 引擎進(jìn)行數(shù)據(jù)查詢。

通過(guò)這個(gè)流程可以看到，在 BI 層做物化視圖相對(duì)數(shù)據(jù)庫(kù)而言是存在一些優(yōu)勢(shì)的：

① 基于結(jié)構(gòu)化的查詢 AST，相比于通過(guò)解析 SQL 更可控。

② 以模型為粒度，減少校驗(yàn)成本，傳統(tǒng)的查詢引擎會(huì)需要匹配所有的物化視圖，效率降低。

③ 任意數(shù)據(jù)庫(kù)類型可用，屏蔽了底層數(shù)據(jù)庫(kù)類型，解決了部分?jǐn)?shù)據(jù)庫(kù)不支持物化視圖的問(wèn)題。

④ 支持跨數(shù)據(jù)庫(kù)種類關(guān)聯(lián)，不需要限制單數(shù)據(jù)庫(kù)物化。

4、物化視圖優(yōu)先級(jí)排序

同一個(gè)模型可能會(huì)匹配到多個(gè)物化視圖，那么通過(guò)優(yōu)先級(jí)排序選擇更合適的視圖就顯得尤為重要了。涉及物化視圖優(yōu)先級(jí)排序的原則如下：

① 預(yù)聚合：優(yōu)先使用預(yù)聚合的物化視圖。

② 聚合維度數(shù)量：優(yōu)先使用聚合維度個(gè)數(shù)更少的物化視圖。

③ 謂詞數(shù)量：優(yōu)先使用謂詞個(gè)數(shù)更多的物化視圖。

④ 謂詞范圍：優(yōu)先使用謂詞范圍更小的物化視圖。

5、物化視圖命中校驗(yàn)

在上圖中我們可以看到，通過(guò)查詢語(yǔ)法樹的配置和物化視圖的配置，可以進(jìn)行物化視圖的命中校驗(yàn)。校驗(yàn)大體分為三個(gè)階段：字段校驗(yàn)、謂詞校驗(yàn)和關(guān)聯(lián)校驗(yàn)。

（1）字段校驗(yàn)：主要校驗(yàn)?zāi)繕?biāo)為查詢字段是否存在于物化視圖

① 第一點(diǎn)需要考慮查詢和物化視圖的聚合狀態(tài)分類：明細(xì)查詢顯然無(wú)法使用聚合類型的物化視圖。

② 第二點(diǎn)需要考慮模型表字段與子查詢字段：模型表的字段是否均在物化視圖中，但如果是子查詢，因?yàn)槭莾?nèi)部生成的，就可以跳過(guò)這層考慮。

③ 第三點(diǎn)需要考慮二次聚合的等價(jià)性：查詢較復(fù)雜時(shí)，可能需要在已經(jīng)聚合了一次的物化視圖基礎(chǔ)上，在查詢中進(jìn)行第二次聚合；但是很明顯，這種二次聚合是需要考慮等價(jià)性的，例如不能通過(guò)對(duì)物化視圖中的平均值聚合結(jié)果進(jìn)行第二次平均值聚合，來(lái)得到整體的平均值，因?yàn)檫@從數(shù)學(xué)邏輯上是不等價(jià)的。

（2）謂詞校驗(yàn)：查詢謂詞是否包含于物化視圖

① 首先以物化視圖的篩選謂詞為基準(zhǔn)：也就是說(shuō)物化視圖中篩選的謂詞都要在查詢中找到同樣的篩選謂詞且查詢范圍相同或更小。

② 相同種類的謂詞存在包含關(guān)系。

③ 等價(jià)謂詞優(yōu)化：例如物化視圖中是日期范圍篩選，但是查詢中是列表篩選，這時(shí)可以判斷列表中的篩選項(xiàng)是否都在日期篩選范圍中。

（3）關(guān)聯(lián)校驗(yàn)：確認(rèn)表關(guān)聯(lián)是否能滿足等價(jià)要求

① 同樣需要考慮查詢和物化視圖的聚合狀態(tài)分類。

② 考慮模型表和子查詢：當(dāng)查詢中是關(guān)聯(lián)表和子查詢關(guān)聯(lián)時(shí)，直接將關(guān)聯(lián)表替換為物化視圖可能存在不等價(jià)的情況。

③ 子查詢遞歸校驗(yàn)：當(dāng)查詢中包含子查詢時(shí)，需要遞歸判斷每一層的子查詢?nèi)棵胁拍艽_認(rèn)可以替換為物化視圖。

經(jīng)歷三個(gè)階段校驗(yàn)后，可以得到校驗(yàn)結(jié)果，其中包括是否命中，命中視圖的信息以及未命中的原因。

6、物化查詢改寫

接下來(lái)到了物化查詢改寫的階段，這個(gè)階段中使用到 ETL 元信息和查詢語(yǔ)法樹。這個(gè)階段主要的操作為三個(gè)方面：模型替換，字段替換，等價(jià)優(yōu)化。

（1）模型替換：基礎(chǔ)數(shù)據(jù)模型替換為物化模型表

① 第一點(diǎn)需要考慮關(guān)聯(lián)表和子查詢。

② 第二點(diǎn)需要考慮子查詢遞歸替換。

（2）字段替換：原模型字段替換為物化字段

① 需要考慮查詢和物化視圖的聚合狀態(tài)分類。

② 考慮維度和度量二者處理邏輯不同。

③ 考慮二次聚合的等價(jià)性。

（3）等價(jià)優(yōu)化：對(duì)等價(jià)場(chǎng)景進(jìn)行優(yōu)化

① 當(dāng)物化聚合粒度等于查詢聚合粒度時(shí)，顯然不需要在查詢時(shí)再次做 group by 的聚合操作，此時(shí)可以將聚合查詢退化為明細(xì)查詢。

② 當(dāng)物化篩選謂詞等于查詢的篩選謂詞時(shí)，在查詢時(shí)無(wú)需繼續(xù)使用該篩選謂詞，可以直接抵消。

經(jīng)歷這個(gè)階段后，就會(huì)生成物化語(yǔ)法樹，下一步生成對(duì)應(yīng)的 SQL，正式進(jìn)入 MPP 進(jìn)行查詢。

7、調(diào)度與抽取管理

如上圖所示，這里其實(shí)復(fù)用了有數(shù) BI 的數(shù)據(jù)準(zhǔn)備即 ETL 的調(diào)度體系。首先我們支持許多種類的數(shù)據(jù)源，接下來(lái)這些數(shù)據(jù)源經(jīng)過(guò)數(shù)據(jù)清洗創(chuàng)建了數(shù)據(jù) ETL 的配置，再通過(guò) ETL 的配置生成抽取的 SQL，最終通過(guò)抽取 SQL 將數(shù)據(jù)抽取到 MPP 數(shù)倉(cāng)中。因?yàn)?ClickHouse 對(duì)于常見的物化視圖形成的大寬表有非常好的性能體現(xiàn)，所以我們現(xiàn)在更多的使用 ClickHouse 作為 MPP 數(shù)倉(cāng)的引擎。

接下來(lái)詳細(xì)表述下“數(shù)據(jù)抽取”這個(gè)階段：

① 支持通過(guò) Spark 引擎進(jìn)行高性能傳輸。

② 支持錯(cuò)誤的監(jiān)控報(bào)警。

③ 支持多樣的調(diào)度方式：手動(dòng)調(diào)度、依賴調(diào)度、定時(shí)調(diào)度等。

④ 支持豐富的抽取方式：例如全量物化、增量物化等。

⑤ 支持執(zhí)行計(jì)劃配置：可以對(duì)依賴表進(jìn)行表信息配置和定時(shí)任務(wù)配置。

⑥ 容量管理：對(duì)于整個(gè) MPP 的容量管理，或者物化視圖自己的容量管理。

⑦ 狀態(tài)管理：可以看到當(dāng)前物化視圖的物化狀態(tài)、以及開啟關(guān)閉狀態(tài)等。

⑧ 調(diào)度歷史：歷史調(diào)度信息、成功率、物化時(shí)長(zhǎng)等。

8、物化視圖的智能推薦

在模型和報(bào)表中使用的字段很多，光靠用戶手動(dòng)進(jìn)行物化視圖配置效率較低。通過(guò)模型信息和物化歷史信息，我們會(huì)給出一些物化配置的推薦?，F(xiàn)在這個(gè)模塊還在不斷推進(jìn)開發(fā)中。

五、有數(shù) BI 物化視圖內(nèi)部案例

在網(wǎng)易嚴(yán)選這個(gè)場(chǎng)景中，存在許多需要高性能查詢的重點(diǎn)報(bào)告，為此我們根據(jù)這些重點(diǎn)報(bào)告，構(gòu)建出了 60+ 個(gè)物化視圖。在實(shí)際使用中發(fā)現(xiàn)，配置了物化視圖的這些重點(diǎn)報(bào)告中，每天有超過(guò) 90% 的 SQL 查詢能命中物化視圖，在命中了物化視圖的查詢組件中，平均查詢時(shí)長(zhǎng)從 5000ms 下降到 500ms。

六、有數(shù) BI 物化視圖未來(lái)展望

對(duì)于有數(shù) BI 物化視圖的未來(lái)展望，主要包括以下四點(diǎn)：

① 更強(qiáng)大的物化視圖配置能力：例如將物化字段擴(kuò)大到任意計(jì)算字段，讓物化視圖更接近于實(shí)際的查詢。

② 更精準(zhǔn)的物化視圖命中校驗(yàn)：包括范圍更廣的謂詞校驗(yàn)，更強(qiáng)的等價(jià)命中校驗(yàn)?zāi)芰Φ取?/span>

③ 更智能的物化視圖推薦配置：根據(jù)模型的使用情況一鍵物化，根據(jù)物化視圖歷史命中情況優(yōu)化配置。

④ 更飛速的物化視圖查詢體驗(yàn)：更快的物化抽取速度，更快的 MPP 引擎查詢速度。