一、有數(shù) BI 圖表的數(shù)據(jù)查詢原理

1、可視化的數(shù)據(jù)分析流程

可視化的數(shù)據(jù)分析流程，主要分成三部分：數(shù)據(jù)接入、數(shù)據(jù)建模和報(bào)告制作。

（1）數(shù)據(jù)接入

目前有數(shù)已經(jīng)支持 30 多種數(shù)據(jù)源，且支持多種數(shù)據(jù)源類型，包含關(guān)系型數(shù)據(jù)庫、分布式數(shù)據(jù)庫、restAPI、數(shù)據(jù)表單等。

（2）數(shù)據(jù)建模

數(shù)據(jù)接入后，我們就可以進(jìn)行數(shù)據(jù)建模。

目前我們支持通過拖拽式的 Join/Union 建模，如果用戶有復(fù)雜需求，也可以通過自定義 SQL 方式建模。另外我們也具備計(jì)算字段的能力，可以進(jìn)行一些計(jì)算字段的擴(kuò)展。同時(shí)，我們也支持字段元信息定義，比如字段類型、指標(biāo)口徑等等。

（3）報(bào)告制作

數(shù)據(jù)模型建好了，就可以進(jìn)行拖拽式的報(bào)告制作。

目前我們支持豐富的圖表庫以及強(qiáng)大的分析能力，讓用戶做報(bào)告就像制作 PPT 一樣簡單。同時(shí)，用戶也可以把做好的報(bào)告通過移動端、微信小程序、分享鏈接等形式分享出去，進(jìn)行數(shù)據(jù)的分發(fā)和共享。

2、圖表背后的數(shù)據(jù)查詢和處理技術(shù)

（1）Query DSL：圖表數(shù)據(jù)查詢語言

首先圖表配置好以后會先生成 Query DSL（圖表數(shù)據(jù)查詢語言），它包含模型依賴的數(shù)據(jù)源連接信息、寬表信息以及用戶拖上去的維度、度量、篩選、排序等等。

（2）所有操作都可以轉(zhuǎn)換成 Query DSL

用戶配置的圖表最終都能轉(zhuǎn)換成一套 Query DSL。

（3）抽象語法樹的生成

有了 Query DSL，就可以生成對應(yīng)的 AST（抽象語法樹），它包含 Table 節(jié)點(diǎn)、Select 節(jié)點(diǎn)、From 節(jié)點(diǎn)、Group By 節(jié)點(diǎn)和一些計(jì)算節(jié)點(diǎn)等。

（4）SQL 語法適配：屏蔽數(shù)據(jù)源差異

有了抽象語法樹，我們就可以針對不同的數(shù)據(jù)源進(jìn)行 SQL 的適配，然后從不同的數(shù)據(jù)源將數(shù)據(jù)查詢回來并返回給用戶。這樣，對于用戶來說是不需要感知到底層數(shù)據(jù)源的類型的。

3、圖表查詢核心能力

首先，最基礎(chǔ)的部分，支持排序、篩選、聚合、數(shù)據(jù)字典、計(jì)算字段和分組字段等等。

另外，支持圖表聯(lián)動、上卷下鉆、地圖計(jì)算等強(qiáng)大的分析能力。

同時(shí)，還支持行列權(quán)限（同一個(gè)圖表不同用戶看到不同數(shù)據(jù)）以及跨視圖粒度分析（同一張圖表可以看到不同聚合粒度的數(shù)據(jù)）的能力。

最后總結(jié)下，前面講到的圖表提供的一些能力，背后的核心其實(shí)都是對數(shù)據(jù)字段行為的處理，比如我們常用的聚焦下鉆，當(dāng)點(diǎn)擊地區(qū) = “東北”這個(gè)柱子下鉆下去看下東北下面各個(gè)省份求和銷售額的分布，其實(shí)背后的處理行為就是，我們會把 x 軸的維度字段從地區(qū)轉(zhuǎn)換為省份，然后再加一個(gè)地區(qū) = “東北”的字段篩選器，所以這里聚焦下鉆其實(shí)背后被轉(zhuǎn)換成了兩個(gè)對字段行為的處理，最后這些字段都會轉(zhuǎn)換成統(tǒng)一的語法樹。

二、有數(shù) BI 智能緩存的設(shè)計(jì)和實(shí)現(xiàn)

前面我們講到，針對用戶配置的圖表會對應(yīng)生成 SQL，然后去對應(yīng)的落庫查詢。整個(gè)查詢過程有以下幾個(gè)特點(diǎn)：

① 高并發(fā)：早上看數(shù)高峰期。

② 大數(shù)據(jù)量：很多千萬、億級數(shù)據(jù)查詢場景。

③ 離線數(shù)據(jù)：大部分都是 T + 1 的數(shù)據(jù)。

針對以上特點(diǎn)最有效的手段就是通過緩存去解決，可以看到下圖是我們線上最近一個(gè)月的查詢耗時(shí)對比（綠色線表示落庫查詢、紅色線表示緩存查詢），很明顯命中緩存以后查詢效率可以有上百倍的提升。

那么，圖表緩存的是什么數(shù)據(jù)呢？

答案是圖表數(shù)據(jù)。

這里可能很多同學(xué)會問，為什么不緩存模型數(shù)據(jù)？

首先，模型數(shù)據(jù)可能較大；其次，當(dāng)緩存模型數(shù)據(jù)時(shí)，圖表查詢時(shí)依然需要進(jìn)行二次聚合運(yùn)算，因此也存在一定的計(jì)算成本。

可以看到我們緩存的對象由圖表和用戶組成，這里的主要原因是由于前面講到的同一圖表不同用戶可以看到不同數(shù)據(jù)，因此一個(gè)圖表會存在多份緩存。

除此之外，我們還引入了二級緩存的概念。

一級緩存，主要緩存前端圖表通過 queryData 查詢出來的數(shù)據(jù)。

二級緩存，主要緩存生成的 SQL 查詢出來的數(shù)據(jù)，同時(shí)，二級緩存可以實(shí)現(xiàn)緩存復(fù)用，例如：同一套數(shù)據(jù)展示成折線圖和柱狀圖，雖然他們的展示形式不同，但底層生成的 SQL 是一樣的，此時(shí)就可以復(fù)用二級緩存。

下圖展示了智能緩存的整體架構(gòu)。

第一層是調(diào)度器：包括緩存的刷新計(jì)劃、OpenAPI、表產(chǎn)出訂閱、MPP 抽取和手動觸發(fā)。

第二層是運(yùn)算器：包括數(shù)據(jù)產(chǎn)出驅(qū)動、圖表血緣計(jì)算、用戶查詢行為分析和基于 ROI 的優(yōu)先級計(jì)算。

第三層是執(zhí)行器：通過運(yùn)算器會生產(chǎn)一個(gè)緩存對象，緩存對象就會被放入隊(duì)列進(jìn)行排隊(duì)執(zhí)行，最終生成緩存的數(shù)據(jù)。 

另外，我們我們還會實(shí)時(shí)地監(jiān)控緩存數(shù)據(jù)，進(jìn)行一些緩存的監(jiān)控和報(bào)警。

接下來，我們看一下緩存運(yùn)算器中的基于數(shù)據(jù)產(chǎn)出驅(qū)動緩存的原理。

因?yàn)榇蟛糠謹(jǐn)?shù)據(jù)是 T+1 離線數(shù)據(jù)，因此這里也是以天為單位進(jìn)行實(shí)現(xiàn)。它的輸入是離線表的產(chǎn)出消息，當(dāng)消息推送過來時(shí)，我們可以根據(jù)圖表的血緣關(guān)系找到該表關(guān)聯(lián)的圖表，然后對圖表進(jìn)行緩存。

當(dāng)然，有些表不是每天都有產(chǎn)出任務(wù)的，因此這里我們引入了產(chǎn)出預(yù)測模塊。它可以根據(jù)數(shù)據(jù)任務(wù)的調(diào)度信息計(jì)算出某個(gè)表今天有沒有產(chǎn)出，另外還可以根據(jù)表產(chǎn)出歷史推測出今天該表有沒有產(chǎn)出。

該方式的優(yōu)點(diǎn)是可以提升緩存效率，進(jìn)行錯(cuò)峰緩存，從而保障緩存的實(shí)時(shí)性和有效性。

它的應(yīng)用場景有三點(diǎn)：

① 一是與有數(shù)數(shù)據(jù)中臺的數(shù)據(jù)服務(wù)打通，天然就具備了這個(gè)能力。

② 二是內(nèi)置 MPP 抽取完進(jìn)行數(shù)據(jù)驅(qū)動，例如物化視圖完或數(shù)據(jù)抽取完以后可以實(shí)時(shí)觸發(fā)緩存。

③ 三是用戶可以通過調(diào)用 API 的方式，主動觸發(fā)緩存。

下面介紹緩存運(yùn)算器的原理。

這里主要講四點(diǎn)：

第一點(diǎn)是用戶行為分析：用戶在打開報(bào)告后，會默認(rèn)進(jìn)行一些篩選，然后用戶可以進(jìn)行篩選器切換、圖表聯(lián)動、圖表跳轉(zhuǎn)等行為，此時(shí)隨著圖表的變換，緩存數(shù)據(jù)也會隨之變動。因此，用戶行為分析是希望從中找出規(guī)律，提高用戶行為分析時(shí)的緩存命中率。

第二點(diǎn)是 QueryData：QueryData 是圖表的查詢請求輸入，它主要分為兩類，一類是圖表默認(rèn)的 QueryData；另一類是根據(jù)圖表歷史分析行為采集器生成的 QueryData，它可能有很多份，我們會選取 TopN 進(jìn)行緩存。

第三點(diǎn)是權(quán)限判斷：因?yàn)橛脩魴?quán)限每天可能發(fā)生變化，因此我們會結(jié)合用戶的資源權(quán)限和數(shù)據(jù)權(quán)限，最終選取 TopN 的用戶進(jìn)行緩存。

第四點(diǎn)是優(yōu)先級計(jì)算：可以看到我們這里是根據(jù) ROI 算法進(jìn)行緩存的優(yōu)先級判斷的，計(jì)算因子包括 PV、UV、產(chǎn)出頻率等等，最終計(jì)算出每一個(gè)用戶和 queryData 的優(yōu)先級。

除此之外，我們可以計(jì)算出：緩存總數(shù)量 = 用戶數(shù)量 * queryData 數(shù)量。

下面是我們在云音樂環(huán)境下緩存實(shí)踐效果統(tǒng)計(jì)：

① 首次緩存命中率從 35% 提高到 93%+。

② 整體緩存命中率從 70% 提高到 92%+。

③ 5 秒內(nèi)頁面響應(yīng)占比從 70% 提高到 90%+。

下面是用戶行為分析緩存的統(tǒng)計(jì)，可看到用戶行為分析緩存命中率從 35% 提高到 80%+，效果比較明顯。

三、圖表查詢的合并和優(yōu)化

下圖是一個(gè)報(bào)告的示例，可以看到該報(bào)告可能包含上百個(gè)圖表，且每個(gè)圖表都會產(chǎn)生一到多個(gè) SQL 查詢，而整體并發(fā) SQL 查詢越多，整體查詢也就越慢。通過對比 SQL 分析，可以發(fā)現(xiàn)大部分 SQL 只是 select 字段不一樣，where 和 group by 等條件可能都一樣，那么對于這種查詢就可以進(jìn)行查詢合并。

圖表查詢的合并和優(yōu)化是通過查詢視圖的構(gòu)建來實(shí)現(xiàn)的。

首先我們以報(bào)告為粒度進(jìn)行查詢視圖的構(gòu)建，一份報(bào)告對應(yīng)多個(gè)查詢視圖，一個(gè)查詢視圖可以承載多個(gè)圖表的查詢，查詢視圖的聚合規(guī)則包含：Filter、Dimensions、Sorters 等。

假設(shè)一個(gè)報(bào)告內(nèi) Query DSL 有 N 個(gè)，Query DSL View 有 M 個(gè)，那么 N/M 越大，查詢視圖的價(jià)值就越大。

接下來我們介紹一下查詢視圖的查詢流程優(yōu)化：

首先，前端輸入一個(gè) Query Data，我們會找到對應(yīng)的原始的 Query DSL，然后生成對應(yīng)的查詢視圖，然后進(jìn)行重組生成一個(gè)新的 Query DSL，新的 Query DSL 返回?cái)?shù)據(jù)的時(shí)候，也會對查詢結(jié)果進(jìn)行反向還原。

然后 Query DSL 會經(jīng)過緩存模塊進(jìn)行處理，這里有一個(gè)好處就是，多個(gè)相似圖表的緩存可以進(jìn)行復(fù)用，從而減少緩存數(shù)據(jù)大小和預(yù)緩存次數(shù)。

如果沒有命中緩存，請求會經(jīng)過我們的查詢攔截器，攔截器可以保證同一個(gè)查詢視圖的請求只會下發(fā)一次。

以上的整個(gè)過程對前端來說是透明的，前端無需感知整個(gè)查詢過程。

下面是圖表查詢合并后的實(shí)踐效果：

其中 13000 多個(gè)查詢視圖可以承載 84000 多個(gè)查詢，也就是 N/M≈1:7。

其中每日落庫查詢量從 7000 次下降到 3000 次左右，效果比較明顯。 

四、圖表查詢的其他優(yōu)化

1、維值加速

下圖是一家客戶的查詢報(bào)告，可以看到其中篩選器數(shù)量非常多，并且是根據(jù)明細(xì)數(shù)據(jù)進(jìn)行去重聚合的，其查詢也相對頻繁，查詢出來的成員較少且是固定的，此時(shí)我們可以通過維值加速進(jìn)行優(yōu)化。

我們可以通過以下幾種方式進(jìn)行維值加速：

動態(tài)值：可以把被查詢的字段綁定到單獨(dú)的維表字段上，最終查詢篩選器數(shù)據(jù)時(shí)實(shí)際上查的是對應(yīng)維表的數(shù)據(jù)。

靜態(tài)值：對于靜態(tài)值，可以直接對查詢字段設(shè)置枚舉，例如：性別的男、女等。

總體思路是減少對于明細(xì)表高頻且耗時(shí)的聚合查詢。

2、分區(qū)篩選器優(yōu)化

因?yàn)榇蟛糠謹(jǐn)?shù)據(jù)源都具備分區(qū)概念，如果能命中分區(qū)索引，就可以減少全表掃描，從而提升查詢速度。

這里的分區(qū)查詢篩選器優(yōu)化，是結(jié)合了有數(shù)數(shù)據(jù)中臺元數(shù)據(jù)中心，去獲取對應(yīng)表的分區(qū)字段，從而生成分區(qū)篩選器。當(dāng)圖表在查詢的時(shí)候，我們會動態(tài)的獲取分區(qū)篩選器的最近分區(qū)，同時(shí)會進(jìn)行分區(qū)篩選的強(qiáng)制下推，從而提升查詢性能。

分區(qū)篩選器有以下兩個(gè)特點(diǎn)：

第一是由建模人員決定是否使用分區(qū)篩選器，這樣可以規(guī)范分析師的使用流程。

第二是報(bào)告必須繼承模型分區(qū)篩選器，同時(shí)不能進(jìn)行刪除。

3、查詢分級優(yōu)化

查詢分級優(yōu)化主要從四個(gè)方面介紹：

（1）查詢分流

針對不同場景使用不同查詢引擎，比如使用 Impala 數(shù)據(jù)源時(shí)支持使用 Spark 進(jìn)行抽取和導(dǎo)出 Excel 相關(guān)查詢。

（2）高低優(yōu)先級查詢隊(duì)列

控制同一個(gè)數(shù)據(jù)源的并發(fā) SQL。

優(yōu)先保障重點(diǎn)用戶（例如老板等）的看數(shù)需求等。

（3）重點(diǎn)報(bào)告/普通報(bào)告

重點(diǎn)報(bào)告優(yōu)先緩存。

重點(diǎn)報(bào)告支持單獨(dú)切換數(shù)據(jù)連接，比如 Impala 數(shù)據(jù)源可以走不同的分組。

（4）VIP 查詢服務(wù)

部分報(bào)告的查詢走單獨(dú)的后端服務(wù)；

資源隔離，保障可用性。

4、前端渲染優(yōu)化

主要包括以下三個(gè)方面：

① 查詢組件分級：例如圖表及 topN 篩選器可以進(jìn)行高優(yōu)先級加載，圖片和普通篩選器做低優(yōu)先級加載。

② 局部渲染優(yōu)化：優(yōu)先渲染視窗內(nèi)的組件，非可視區(qū)域進(jìn)行懶加載。

③ 請求隊(duì)列控制：前端做了請求隊(duì)列的控制，減少查詢并發(fā)，從而避免一個(gè)用戶就把數(shù)據(jù)源資源吃光。

5、SQL 生成優(yōu)化

第五個(gè)就是我們會對第一章的講到的 Query DSL 去生成 SQL 這個(gè)過程也做了一些通用的優(yōu)化，比如篩選條件等價(jià)下推，Join 條件篩選傳遞，動態(tài)模型剪枝等優(yōu)化。這些優(yōu)化都會對特定場景的查詢有較大的優(yōu)化效果，右邊這個(gè)圖是我們字段類型轉(zhuǎn)換做篩選的優(yōu)化效果圖，雖然該字段做了類型轉(zhuǎn)換，其實(shí)我們也可以背后讓他用原始的類型進(jìn)行篩選，提高查詢效率。

6、MPP 查詢加速

對于一些異構(gòu)數(shù)據(jù)源，我們可以通過數(shù)據(jù)抽取、數(shù)據(jù)準(zhǔn)備、物化視圖等方式，將數(shù)據(jù)抽取到 MPP 數(shù)倉，達(dá)到查詢加速的目的。

五、圖表的性能查詢分析和診斷

前面我們介紹了很多圖表查詢性能優(yōu)化的方式和方法，但是并不能解決所有的性能問題，新的性能問題總是會不斷的產(chǎn)生。同時(shí)，有數(shù) BI 的圖表查詢鏈路也較長，性能排查過程耗時(shí)耗力。雖然我們提供了很多圖表性能的優(yōu)化手段，分析師具體該如何選擇也是問題。

我們做性能分析和診斷的目標(biāo)是：

① 幫助用戶快速定位問題。

② 輸出準(zhǔn)確的方案。

③ 直接賦能用戶自我解決問題。

這個(gè)功能在我們產(chǎn)品功能上叫做“數(shù)據(jù)醫(yī)生”，下面是我們數(shù)據(jù)醫(yī)生實(shí)現(xiàn)的方案：

① 第一步是根據(jù)性能統(tǒng)計(jì)，發(fā)現(xiàn)到底是哪個(gè)圖表慢。

② 第二步是找到某個(gè)具體圖表以后我們可以進(jìn)行全鏈路 timeline 計(jì)算和分析。

③ 第三步結(jié)合我們診斷的規(guī)則庫，推斷具體問題原因。

④ 第四步針對不同的問題，給出不同的解決方案。

另外我們還專門做了一層統(tǒng)一的針對 SQL profile 的性能解析層，會把不同數(shù)據(jù)源的 profile 進(jìn)行統(tǒng)一的抽象，比如統(tǒng)計(jì) SQL 的資源消耗，分區(qū)、存儲格式等表的元信息，另外會對 Join 或者 Scan 算子的性能進(jìn)行定義和解析，目前我們已經(jīng)適配了 Impala 和 Clickhouse 兩種數(shù)據(jù)源，右邊是我們針對這個(gè)抽象的 SQL 粒度的性能解析在產(chǎn)品上的診斷結(jié)果的體現(xiàn)，我們會告知用戶哪些表的掃描數(shù)據(jù)比較慢，哪些 Join 節(jié)點(diǎn)的計(jì)算比較慢，這樣用戶就很方便的發(fā)現(xiàn)具體的問題。

數(shù)據(jù)醫(yī)生自助幫助用戶解決了基本的性能問題，釋放了技術(shù)支持和開發(fā)的人力。同時(shí)我們的診斷規(guī)則和經(jīng)驗(yàn)也在不斷持續(xù)積累。

六、總結(jié)

最后來做一個(gè)簡單的總結(jié)。

（1）性能是 BI 產(chǎn)品的有理保障和核心競爭力，性能是保障用戶體驗(yàn)的根基。

（2）解決性能問題一定要從用戶使用場景出發(fā)，具體問題具體分析。

（3）提效和易用：盡可能地賦能用戶自助解決問題的能力，提升產(chǎn)品效率和易用性。

七、問答環(huán)節(jié)

Q1：緩存是行列級別的嗎？

A1：我們的緩存是緩存一份圖表的數(shù)據(jù)，所以一個(gè)圖表看能有多份緩存，不同用戶不同權(quán)限看到圖表數(shù)據(jù)可能不一樣，同時(shí)我們也會對相同權(quán)限的用戶進(jìn)行去重和合并。

Q2：MPP 查詢負(fù)載打滿后怎么處理？

A2：MPP 這邊我們有專人進(jìn)行維護(hù)，會對 MPP 數(shù)據(jù)庫的查詢進(jìn)行監(jiān)控和告警，同時(shí)我們也會對 MPP 進(jìn)行一些治理，例如：發(fā)現(xiàn)一些大查詢我們會進(jìn)行攔截等。

Q3：查詢合并只針對指標(biāo)卡嗎？還是所有圖表類型都會進(jìn)行合并？

A3：查詢合并目前最主要還是指標(biāo)卡的應(yīng)用場景，其他場景我們后面也會慢慢支持。

Q4：請問數(shù)據(jù)存儲最終是存儲到 MPP 里面的嗎？還是說支持外部？

A4：對于有數(shù) BI 來說，我們的圖表查詢是落到 MPP 數(shù)倉里面。對于外部數(shù)倉來說，我們支持 ETL 數(shù)據(jù)準(zhǔn)備能力，進(jìn)行一個(gè)數(shù)據(jù)清洗，可以把我們的數(shù)據(jù)準(zhǔn)備清洗到外部數(shù)據(jù)庫，目前 ETL 外部數(shù)據(jù)庫已經(jīng)支持 MySQL、Doris 等，然后用戶可以通過建立數(shù)據(jù)連接在有數(shù) BI 上進(jìn)行查詢和分析。

Q5：目前的性能診斷的數(shù)據(jù)醫(yī)生，對于優(yōu)化性能最大的是哪種手段？

A5：對于數(shù)據(jù)醫(yī)生來說，只是診斷工具，并不是用來提升性能的。具體性能的提升，還是要通過物化視圖、維值加速、分區(qū)篩選等手段進(jìn)行，物化視圖目前的性能提升相對較大。

Q6：查詢視圖與物化視圖如何區(qū)分與使用？

A6：查詢視圖其實(shí)是物化視圖前面的部分，為了構(gòu)建 Query DSL 的輸入，請求的鏈路到達(dá)查詢器，才會命中后面的物化視圖。