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

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

1、有數(shù) BI 介紹

有數(shù) BI 最大的特點是使用 PPT 制作的方式來制作報表。平臺的使用方式為：

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

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

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

2、BI 性能痛點

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

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

因為數(shù)據(jù)模型是由多表關(guān)聯(lián)構(gòu)成的，復雜的關(guān)聯(lián)會帶來巨大的性能開銷，導致報告查詢遲遲無法響應。

（2）痛點 2-只查部分卻掃全部

這里基本可分為兩類場景。

場景一：行的數(shù)據(jù)。常常出現(xiàn)的場景是僅需要近期的數(shù)據(jù)計算，但是因為模型是多表關(guān)聯(lián)查詢的，每次落庫查詢都會導致全表掃描。

場景二：列的數(shù)據(jù)。很多時候報表展示僅需要部分列信息，但是因為模型的關(guān)聯(lián)查詢，導致需要全字段關(guān)聯(lián)落庫，也會帶來很大的性能損耗。

（3）痛點 3-篩選需求因人而異

有些報表會提供多個維度的查詢篩選器，篩選器的默認值無法滿足需求，不同用戶的查詢條件不同，導致預加載緩存無法命中，使得落庫查詢性能降低。

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

基于以上痛點，我們開始嘗試通過物化視圖的方式解決問題。

1、什么是物化視圖

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

常見分類：

① 關(guān)聯(lián)表數(shù)量：單表物化視圖、多表物化視圖。

② 是否聚合：明細物化視圖、聚合物化視圖。

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

接下來給出一個簡單的物化視圖的例子。

2、物化視圖示例

上圖中可以看到：

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

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

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

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

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

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

圖右邊是用戶開始查詢 SQL，該 SQL 進入到數(shù)據(jù)庫后，通過命中校驗模塊判別是否存在匹配的物化視圖，如果存在則通過改寫模塊對 SQL 進行改寫。

4、BI 場景下直接使用數(shù)據(jù)庫物化視圖的問題

在 BI 場景下直接按照數(shù)據(jù)庫的邏輯使用物化視圖是會存在一些問題的。

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

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

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

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

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

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

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

接下來先來看一下我們的產(chǎn)品設計。

1、物化視圖配置入口

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

2、物化視圖配置頁面

從頁面上我們可以看到：

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

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

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

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

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

在這個頁面中：

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

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

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

④ 支持配置定時計劃。

4、物化視圖管理頁面

在這個頁面中：

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

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

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

④ 提供任務的告警設置。

⑤ 提供物化狀態(tài)的日報定時推送功能。

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

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

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

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

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

2、物化視圖 ETL 生成

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

上圖的下半部分給出了一個例子。

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

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

接下來會從 ETL 配置自動推導出元信息，圖中示例主要展示了字段的信息。

3、物化視圖查詢流程

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

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

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

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

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

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

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

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

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

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

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

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

5、物化視圖命中校驗

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

（1）字段校驗：主要校驗目標為查詢字段是否存在于物化視圖

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

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

③ 第三點需要考慮二次聚合的等價性：查詢較復雜時，可能需要在已經(jīng)聚合了一次的物化視圖基礎上，在查詢中進行第二次聚合；但是很明顯，這種二次聚合是需要考慮等價性的，例如不能通過對物化視圖中的平均值聚合結(jié)果進行第二次平均值聚合，來得到整體的平均值，因為這從數(shù)學邏輯上是不等價的。

（2）謂詞校驗：查詢謂詞是否包含于物化視圖

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

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

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

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

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

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

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

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

6、物化查詢改寫

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

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

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

② 第二點需要考慮子查詢遞歸替換。

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

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

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

③ 考慮二次聚合的等價性。

（3）等價優(yōu)化：對等價場景進行優(yōu)化

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

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

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

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

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

接下來詳細表述下“數(shù)據(jù)抽取”這個階段：

① 支持通過 Spark 引擎進行高性能傳輸。

② 支持錯誤的監(jiān)控報警。

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

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

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

⑥ 容量管理：對于整個 MPP 的容量管理，或者物化視圖自己的容量管理。

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

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

8、物化視圖的智能推薦

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

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

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

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

對于有數(shù) BI 物化視圖的未來展望，主要包括以下四點：

① 更強大的物化視圖配置能力：例如將物化字段擴大到任意計算字段，讓物化視圖更接近于實際的查詢。

② 更精準的物化視圖命中校驗：包括范圍更廣的謂詞校驗，更強的等價命中校驗能力等。

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

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