一、有數 BI 介紹與性能痛點

首先給大家介紹下有數 BI。

1、有數 BI 介紹

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

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

② 制作數據模型。可以通過多張底層數據表，或者是自定義 SQL 將它們關聯在一起就形成了數據模型。

③ 在數據模型之上可以構建數據應用。例如數據報表、數據大屏、數據門戶等。

2、BI 性能痛點

在用戶使用過程中，有數 BI 發現了一些性能痛點：

（1）痛點 1-多表關聯查詢慢

因為數據模型是由多表關聯構成的，復雜的關聯會帶來巨大的性能開銷，導致報告查詢遲遲無法響應。

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

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

場景一：行的數據。常常出現的場景是僅需要近期的數據計算，但是因為模型是多表關聯查詢的，每次落庫查詢都會導致全表掃描。

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

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

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

二、數據庫物化視圖基本原理

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

1、什么是物化視圖

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

常見分類：

① 關聯表數量：單表物化視圖、多表物化視圖。

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

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

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

2、物化視圖示例

上圖中可以看到：

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

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

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

3、數據庫物化視圖基本架構

上圖是一般情況下數據庫的物化視圖基本架構。

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

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

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

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

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

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

③ BI 產品支持很多種類的數據庫，它們的物化視圖特點不同，無法統一支持，需要分別適配和管理。甚至有的數據庫不支持物化視圖。

④ 無法跨數據庫種類進行關聯后再創建物化視圖。

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

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

三、有數 BI 物化視圖產品設計

接下來先來看一下我們的產品設計。

1、物化視圖配置入口

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

2、物化視圖配置頁面

從頁面上我們可以看到：

① 對于物化視圖，系統默認會物化全部明細數據。

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

③ 系統支持物化聚合數據。

④ 系統支持對物化字段進行數據篩選。

3、物化視圖執行計劃頁面

在這個頁面中：

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

② 支持配置執行計劃。

③ 支持配置依賴執行的物化規則。

④ 支持配置定時計劃。

4、物化視圖管理頁面

在這個頁面中：

① 提供各物化視圖的模型信息、狀態、調度時間、占用空間、成功率、創建人等基本信息。

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

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

④ 提供任務的告警設置。

⑤ 提供物化狀態的日報定時推送功能。

四、有數 BI 物化視圖實現原理

1、有數 BI 物化視圖架構

整個架構從左至右共分為三塊結構。

最左邊是數據源，有數 BI 支持 Excel、MySQL、Spark 等數據源，物化視圖即是將它們內部的數據在關聯和其他操作后寫入到 MPP 中。

中間的是數據模型。通過左邊的數據源關聯構建數據模型。物化視圖的物化配置也在這個結構中，包括維度、度量、聚合信息、篩選范圍等配置項。這些配置項再加上模型配置，可以轉換為有數 BI 的 ETL 任務配置，以進行數據同步和實際的數據物化。通過對該 ETL 任務配置進行元信息推導，可以生成 ETL 元信息，該 ETL 元信息將用于右側數據查詢過程的物化視圖改寫模塊，以進行數據查詢的物化視圖改寫。

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

接下來詳細闡述這些結構和流程。

2、物化視圖 ETL 生成

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

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

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

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

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

3、物化視圖查詢流程

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

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

① 基于結構化的查詢 AST，相比于通過解析 SQL 更可控。

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

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

④ 支持跨數據庫種類關聯，不需要限制單數據庫物化。

4、物化視圖優先級排序

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

① 預聚合：優先使用預聚合的物化視圖。

② 聚合維度數量：優先使用聚合維度個數更少的物化視圖。

③ 謂詞數量：優先使用謂詞個數更多的物化視圖。

④ 謂詞范圍：優先使用謂詞范圍更小的物化視圖。

5、物化視圖命中校驗

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

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

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

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

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

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

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

② 相同種類的謂詞存在包含關系。

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

（3）關聯校驗：確認表關聯是否能滿足等價要求

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

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

③ 子查詢遞歸校驗：當查詢中包含子查詢時，需要遞歸判斷每一層的子查詢全部命中才能確認可以替換為物化視圖。

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

6、物化查詢改寫

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

（1）模型替換：基礎數據模型替換為物化模型表

① 第一點需要考慮關聯表和子查詢。

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

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

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

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

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

（3）等價優化：對等價場景進行優化

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

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

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

7、調度與抽取管理

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

接下來詳細表述下“數據抽取”這個階段：

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

② 支持錯誤的監控報警。

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

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

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

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

⑦ 狀態管理：可以看到當前物化視圖的物化狀態、以及開啟關閉狀態等。

⑧ 調度歷史：歷史調度信息、成功率、物化時長等。

8、物化視圖的智能推薦

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

五、有數 BI 物化視圖內部案例

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

六、有數 BI 物化視圖未來展望

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

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

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

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

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