導讀:
本文将介紹有數 BI 在圖表查詢性能方面的優化和實踐，包括有數 BI 圖表的資料查詢原理，以及對圖表查詢過程進行的相關優化。
主要内容分為以下幾大部分：
1. 有數 BI 圖表的資料查詢原理
2. 有數 BI 智能緩存的設計與實踐
3. 圖表查詢的合并和優化
4. 圖表查詢的其他優化
5. 圖表的性能分析與診斷
6. 總結
分享嘉賓｜張佃鵬 網易 資深服務端開發工程師
編輯整理｜崔沈鵬 多來點
出品社群｜DataFun
01
有數 BI 圖表的資料查詢原理
1. 可視化的資料分析流程
可視化的資料分析流程，主要分成三部分：資料接入、資料模組化和報告制作。
（1）資料接入
目前有數已經支援 30 多種資料源，且支援多種資料源類型，包含關系型資料庫、分布式資料庫、restAPI、資料表單等。
（2）資料模組化
資料接入後，我們就可以進行資料模組化。
目前我們支援通過拖拽式的 Join/Union 模組化，如果使用者有複雜需求，也可以通過自定義 SQL 方式模組化。另外我們也具備計算字段的能力，可以進行一些計算字段的擴充。同時，我們也支援字段元資訊定義，比如字段類型、名額口徑等等。
（3）報告制作
資料模型建好了，就可以進行拖拽式的報告制作。
目前我們支援豐富的圖表庫以及強大的分析能力，讓使用者做報告就像制作 PPT 一樣簡單。同時，使用者也可以把做好的報告通過移動端、微信小程式、分享連結等形式分享出去，進行資料的分發和共享。
2. 圖表背後的資料查詢和處理技術
（1）Query DSL：圖表資料查詢語言
首先圖表配置好以後會先生成 Query DSL（圖表資料查詢語言），它包含模型依賴的資料源連接配接資訊、寬表資訊以及使用者拖上去的次元、度量、篩選、排序等等。
（2）所有操作都可以轉換成 Query DSL
使用者配置的圖表最終都能轉換成一套 Query DSL。
（3）抽象文法樹的生成
有了 Query DSL，就可以生成對應的 AST（抽象文法樹），它包含 Table 節點、Select 節點、From 節點、Group By 節點和一些計算節點等。
（4）SQL 文法适配：屏蔽資料源差異
有了抽象文法樹，我們就可以針對不同的資料源進行 SQL 的适配，然後從不同的資料源将資料查詢回來并傳回給使用者。這樣，對于使用者來說是不需要感覺到底層資料源的類型的。
3. 圖表查詢核心能力
首先，最基礎的部分，支援排序、篩選、聚合、資料字典、計算字段和分組字段等等。
另外，支援圖表關聯、上卷下鑽、地圖計算等強大的分析能力。
同時，還支援行列權限（同一個圖表不同使用者看到不同資料）以及跨視圖粒度分析（同一張圖表可以看到不同聚合粒度的資料）的能力。
最後總結下，前面講到的圖表提供的一些能力，背後的核心其實都是對資料字段行為的處理，比如我們常用的聚焦下鑽，當點選地區 = “東北”這個柱子下鑽下去看下東北下面各個省份求和銷售額的分布，其實背後的處理行為就是，我們會把 x 軸的次元字段從地區轉換為省份，然後再加一個地區 = “東北”的字段篩選器，是以這裡聚焦下鑽其實背後被轉換成了兩個對字段行為的處理，最後這些字段都會轉換成統一的文法樹。
02
有數 BI 智能緩存的設計和實作
前面我們講到，針對使用者配置的圖表會對應生成 SQL，然後去對應的落庫查詢。整個查詢過程有以下幾個特點：
① 高并發：早上看數高峰期；
② 大資料量：很多千萬、億級資料查詢場景；
③ 離線資料：大部分都是 T + 1 的資料。
針對以上特點最有效的手段就是通過緩存去解決，可以看到下圖是我們線上最近一個月的查詢耗時對比（綠色線表示落庫查詢、紅色線表示緩存查詢），很明顯命中緩存以後查詢效率可以有上百倍的提升。
那麼，圖表緩存的是什麼資料呢？
答案是圖表資料。
這裡可能很多同學會問，為什麼不緩存模型資料？
首先，模型資料可能較大；其次，當緩存模型資料時，圖表查詢時依然需要進行二次聚合運算，是以也存在一定的計算成本。
可以看到我們緩存的對象由圖表和使用者組成，這裡的主要原因是由于前面講到的同一圖表不同使用者可以看到不同資料，是以一個圖表會存在多份緩存。
除此之外，我們還引入了二級緩存的概念。
一級緩存，主要緩存前端圖表通過 queryData 查詢出來的資料。
二級緩存，主要緩存生成的 SQL 查詢出來的資料，同時，二級緩存可以實作緩存複用，例如：同一套資料展示成折線圖和柱狀圖，雖然他們的展示形式不同，但底層生成的 SQL 是一樣的，此時就可以複用二級緩存。
下圖展示了智能緩存的整體架構。
第一層是排程器：包括緩存的重新整理計劃、OpenAPI、表産出訂閱、MPP 抽取和手動觸發。
第二層是運算器：包括資料産出驅動、圖表血緣計算、使用者查詢行為分析和基于 ROI 的優先級計算。
第三層是執行器：通過運算器會生産一個緩存對象，緩存對象就會被放入隊列進行排隊執行，最終生成緩存的資料。
另外，我們我們還會實時地監控緩存資料，進行一些緩存的監控和報警。
接下來，我們看一下緩存運算器中的基于資料産出驅動緩存的原理。
因為大部分資料是 T+1 離線資料，是以這裡也是以天為機關進行實作。它的輸入是離線表的産出消息，當消息推送過來時，我們可以根據圖表的血緣關系找到該表關聯的圖表，然後對圖表進行緩存。
當然，有些表不是每天都有産出任務的，是以這裡我們引入了産出預測子產品。它可以根據資料任務的排程資訊計算出某個表今天有沒有産出，另外還可以根據表産出曆史推測出今天該表有沒有産出。
該方式的優點是可以提升緩存效率，進行錯峰緩存，進而保障緩存的實時性和有效性。
它的應用場景有三點：
① 一是與有數資料中台的資料服務打通，天然就具備了這個能力。
② 二是内置 MPP 抽取完進行資料驅動，例如物化視圖完或資料抽取完以後可以實時觸發緩存。
③ 三是使用者可以通過調用 API 的方式，主動觸發緩存。
下面介紹緩存運算器的原理。
這裡主要講四點：
第一點是使用者行為分析：使用者在打開報告後，會預設進行一些篩選，然後使用者可以進行篩選器切換、圖表關聯、圖表跳轉等行為，此時随着圖表的變換，緩存資料也會随之變動。是以，使用者行為分析是希望從中找出規律，提高使用者行為分析時的緩存命中率。
第二點是 QueryData：QueryData 是圖表的查詢請求輸入，它主要分為兩類，一類是圖表預設的 QueryData；另一類是根據圖表曆史分析行為采集器生成的 QueryData，它可能有很多份，我們會選取 TopN 進行緩存。
第三點是權限判斷：因為使用者權限每天可能發生變化，是以我們會結合使用者的資源權限和資料權限，最終選取 TopN 的使用者進行緩存。
第四點是優先級計算：可以看到我們這裡是根據 ROI 算法進行緩存的優先級判斷的，計算因子包括 PV、UV、産出頻率等等，最終計算出每一個使用者和 queryData 的優先級。
除此之外，我們可以計算出：緩存總數量 = 使用者數量 * queryData 數量。
下面是我們在雲音樂環境下緩存實踐效果統計：
① 首次緩存命中率從 35% 提高到 93%+；
② 整體緩存命中率從 70% 提高到 92%+；
③ 5 秒内頁面響應占比從 70% 提高到 90%+。
下面是使用者行為分析緩存的統計，可看到使用者行為分析緩存命中率從 35% 提高到 80%+，效果比較明顯。
03
圖表查詢的合并和優化
下圖是一個報告的示例，可以看到該報告可能包含上百個圖表，且每個圖表都會産生一到多個 SQL 查詢，而整體并發 SQL 查詢越多，整體查詢也就越慢。通過對比 SQL 分析，可以發現大部分 SQL 隻是 select 字段不一樣，where 和 group by 等條件可能都一樣，那麼對于這種查詢就可以進行查詢合并。
圖表查詢的合并和優化是通過查詢視圖的建構來實作的。
首先我們以報告為粒度進行查詢視圖的建構，一份報告對應多個查詢視圖，一個查詢視圖可以承載多個圖表的查詢，查詢視圖的聚合規則包含：Filter、Dimensions、Sorters 等。
假設一個報告内 Query DSL 有 N 個，Query DSL View 有 M 個，那麼 N/M 越大，查詢視圖的價值就越大。
接下來我們介紹一下查詢視圖的查詢流程優化：
首先，前端輸入一個 Query Data，我們會找到對應的原始的 Query DSL，然後生成對應的查詢視圖，然後進行重組生成一個新的 Query DSL，新的 Query DSL 傳回資料的時候，也會對查詢結果進行反向還原。
然後 Query DSL 會經過緩存子產品進行處理，這裡有一個好處就是，多個相似圖表的緩存可以進行複用，進而減少緩存資料大小和預緩存次數。
如果沒有命中緩存，請求會經過我們的查詢攔截器，攔截器可以保證同一個查詢視圖的請求隻會下發一次。
以上的整個過程對前端來說是透明的，前端無需感覺整個查詢過程。
下面是圖表查詢合并後的實踐效果：
其中 13000 多個查詢視圖可以承載 84000 多個查詢，也就是 N/M≈1:7；
其中每日落庫查詢量從 7000 次下降到 3000 次左右，效果比較明顯。
04
圖表查詢的其他優化
1. 維值加速
下圖是一家客戶的查詢報告，可以看到其中篩選器數量非常多，并且是根據明細資料進行去重聚合的，其查詢也相對頻繁，查詢出來的成員較少且是固定的，此時我們可以通過維值加速進行優化。
我們可以通過以下幾種方式進行維值加速：
動态值：可以把被查詢的字段綁定到單獨的維表字段上，最終查詢篩選器資料時實際上查的是對應維表的資料。
靜态值：對于靜态值，可以直接對查詢字段設定枚舉，例如：性别的男、女等。
總體思路是減少對于明細表高頻且耗時的聚合查詢。
2. 分區篩選器優化
因為大部分資料源都具備分區概念，如果能命中分區索引，就可以減少全表掃描，進而提升查詢速度。
這裡的分區查詢篩選器優化，是結合了有數資料中台中繼資料中心，去擷取對應表的分區字段，進而生成分區篩選器。當圖表在查詢的時候，我們會動态的擷取分區篩選器的最近分區，同時會進行分區篩選的強制下推，進而提升查詢性能。
分區篩選器有以下兩個特點：
第一是由模組化人員決定是否使用分區篩選器，這樣可以規範分析師的使用流程。
第二是報告必須繼承模型分區篩選器，同時不能進行删除。
3. 查詢分級優化
查詢分級優化主要從四個方面介紹：
（1）查詢分流
針對不同場景使用不同查詢引擎，比如使用 Impala 資料源時支援使用 Spark 進行抽取和導出 Excel 相關查詢。
（2）高低優先級查詢隊列
控制同一個資料源的并發 SQL；
優先保障重點使用者（例如老闆等）的看數需求等。
（3）重點報告/普通報告
重點報告優先緩存；
重點報告支援單獨切換資料連接配接，比如 Impala 資料源可以走不同的分組。
（4）VIP 查詢服務
部分報告的查詢走單獨的後端服務；
資源隔離，保障可用性。
4. 前端渲染優化
主要包括以下三個方面：
① 查詢元件分級：例如圖表及 topN 篩選器可以進行高優先級加載，圖檔和普通篩選器做低優先級加載。
② 局部渲染優化：優先渲染視窗内的元件，非可視區域進行懶加載。
③ 請求隊列控制：前端做了請求隊列的控制，減少查詢并發，進而避免一個使用者就把資料源資源吃光。
5. SQL 生成優化
第五個就是我們會對第一章的講到的 Query DSL 去生成 SQL 這個過程也做了一些通用的優化，比如篩選條件等價下推，Join 條件篩選傳遞，動态模型剪枝等優化。這些優化都會對特定場景的查詢有較大的優化效果，右邊這個圖是我們字段類型轉換做篩選的優化效果圖，雖然該字段做了類型轉換，其實我們也可以背後讓他用原始的類型進行篩選，提高查詢效率。
6. MPP 查詢加速
對于一些異構資料源，我們可以通過資料抽取、資料準備、物化視圖等方式，将資料抽取到 MPP 數倉，達到查詢加速的目的。
05
圖表的性能查詢分析和診斷
前面我們介紹了很多圖表查詢性能優化的方式和方法，但是并不能解決所有的性能問題，新的性能問題總是會不斷的産生。同時，有數 BI 的圖表查詢鍊路也較長，性能排查過程耗時耗力。雖然我們提供了很多圖表性能的優化手段，分析師具體該如何選擇也是問題。
我們做性能分析和診斷的目标是：
① 幫助使用者快速定位問題；
② 輸出準确的方案；
③ 直接賦能使用者自我解決問題。
這個功能在我們産品功能上叫做“資料醫生”，下面是我們資料醫生實作的方案：
① 第一步是根據性能統計，發現到底是哪個圖表慢；
② 第二步是找到某個具體圖表以後我們可以進行全鍊路 timeline 計算和分析；
③ 第三步結合我們診斷的規則庫，推斷具體問題原因；
④ 第四步針對不同的問題，給出不同的解決方案。
另外我們還專門做了一層統一的針對 SQL profile 的性能解析層，會把不同資料源的 profile 進行統一的抽象，比如統計 SQL 的資源消耗，分區、存儲格式等表的元資訊，另外會對 Join 或者 Scan 算子的性能進行定義和解析，目前我們已經适配了 Impala 和 Clickhouse 兩種資料源，右邊是我們針對這個抽象的 SQL 粒度的性能解析在産品上的診斷結果的展現，我們會告知使用者哪些表的掃描資料比較慢，哪些 Join 節點的計算比較慢，這樣使用者就很友善的發現具體的問題。
資料醫生自助幫助使用者解決了基本的性能問題，釋放了技術支援和開發的人力。同時我們的診斷規則和經驗也在不斷持續積累。
06
總結
最後來做一個簡單的總結。
（1）性能是 BI 産品的有理保障和核心競争力，性能是保障使用者體驗的根基。
（2）解決性能問題一定要從使用者使用場景出發，具體問題具體分析。
（3）提效和易用：盡可能地賦能使用者自助解決問題的能力，提升産品效率和易用性。
07
問答環節
Q1：緩存是行列級别的嗎？
A1：我們的緩存是緩存一份圖表的資料，是以一個圖表看能有多份緩存，不同使用者不同權限看到圖表資料可能不一樣，同時我們也會對相同權限的使用者進行去重和合并。
Q2：MPP 查詢負載打滿後怎麼處理？
A2：MPP 這邊我們有專人進行維護，會對 MPP 資料庫的查詢進行監控和告警，同時我們也會對 MPP 進行一些治理，例如：發現一些大查詢我們會進行攔截等。
Q3：查詢合并隻針對名額卡嗎？還是所有圖表類型都會進行合并？
A3：查詢合并目前最主要還是名額卡的應用場景，其他場景我們後面也會慢慢支援。
Q4：請問資料存儲最終是存儲到 MPP 裡面的嗎？還是說支援外部？
A4：對于有數 BI 來說，我們的圖表查詢是落到 MPP 數倉裡面。對于外部數倉來說，我們支援 ETL 資料準備能力，進行一個資料清洗，可以把我們的資料準備清洗到外部資料庫，目前 ETL 外部資料庫已經支援 MySQL、Doris 等，然後使用者可以通過建立資料連接配接在有數 BI 上進行查詢和分析。
Q5：目前的性能診斷的資料醫生，對于優化性能最大的是哪種手段？
A5：對于資料醫生來說，隻是診斷工具，并不是用來提升性能的。具體性能的提升，還是要通過物化視圖、維值加速、分區篩選等手段進行，物化視圖目前的性能提升相對較大。
Q6：查詢視圖與物化視圖如何區分與使用？
A6：查詢視圖其實是物化視圖前面的部分，為了建構 Query DSL 的輸入，請求的鍊路到達查詢器，才會命中後面的物化視圖。
今天的分享就到這裡，謝謝大家。
▌2023資料智能創新與實踐大會
4大體系，專業解構資料智能
16個主題論壇，覆寫當下熱點與趨勢
70+演講，兼具創新與最佳實踐
1000+專業觀衆，内行人的技術盛會
點選下方連結直達線下大會官網
DataFunCon2023（北京站）：資料智能創新與實踐大會 �-�百格活動