聊聊寬表為什么橫行?你學(xué)會了嗎?
寬表在BI業(yè)務(wù)中比比皆是,每次建設(shè)BI系統(tǒng)時(shí)首先要做的就是準(zhǔn)備寬表。有時(shí)系統(tǒng)中的寬表可能會有上千個(gè)字段,經(jīng)常因?yàn)椤斑^寬”超過了數(shù)據(jù)庫表字段數(shù)量限制還要再拆分。
為什么大家樂此不疲地造寬表呢?主要原因有兩個(gè)。
一是為了提高查詢性能?,F(xiàn)代BI通常使用關(guān)系數(shù)據(jù)庫作為后臺,而SQL通常使用的HASH JOIN算法,在關(guān)聯(lián)表數(shù)量和關(guān)聯(lián)層級變多的時(shí)候,計(jì)算性能會急劇下降,有七八個(gè)表三四層級關(guān)聯(lián)時(shí)就能觀察到這個(gè)現(xiàn)象,而BI業(yè)務(wù)中的關(guān)聯(lián)復(fù)雜度遠(yuǎn)遠(yuǎn)超過這個(gè)規(guī)模,直接使用SQL的JOIN就無法達(dá)到前端立等可取的查詢需要了。為了避免關(guān)聯(lián)帶來的性能問題,就要先將關(guān)聯(lián)消除,即將多表事先關(guān)聯(lián)好采用單表存儲(也就是寬表),再查詢的時(shí)候就可以不用再關(guān)聯(lián),從而達(dá)到提升查詢性能的目的。
二是為了降低業(yè)務(wù)難度。因?yàn)槎啾黻P(guān)聯(lián)尤其是復(fù)雜關(guān)聯(lián)在BI前端很難表達(dá)和使用。如果采用自動關(guān)聯(lián)(根據(jù)字段類型等信息匹配)當(dāng)遇到同維字段(如一個(gè)表有2個(gè)以上地區(qū)字段)時(shí)會“暈掉”不知道該關(guān)聯(lián)哪個(gè),表間循環(huán)關(guān)聯(lián)或自關(guān)聯(lián)的情況也無法處理;如果將眾多表開放給用戶來自行選擇關(guān)聯(lián),由于業(yè)務(wù)用戶無法理解表間關(guān)系而幾乎沒有可用性;分步關(guān)聯(lián)可以描述復(fù)雜的關(guān)聯(lián)需求,但一旦前一步出錯(cuò)就要推倒重來。所以,無論采用何種方式,工程實(shí)現(xiàn)和用戶使用都很麻煩。但是基于單表來做就會簡單很多,業(yè)務(wù)用戶使用時(shí)沒有什么障礙,因此將多表組織成寬表就成了“自然而然”的事情。
不過,凡事都有兩面性,我們看到寬表好處而大量應(yīng)用的同時(shí),其缺點(diǎn)也不容忽視,有些缺點(diǎn)會對應(yīng)用產(chǎn)生極大影響。下面來看一下。
寬表的缺點(diǎn)
數(shù)據(jù)冗余容量大
寬表不符合范式要求,將多個(gè)表合并成一個(gè)表會存在大量冗余數(shù)據(jù),冗余程度跟原表數(shù)據(jù)量和表間關(guān)系有關(guān),通常如果存在多層外鍵表,其冗余程度會呈指數(shù)級上升。大量數(shù)據(jù)冗余不僅會帶來存儲上的壓力(多個(gè)表組合出來的寬表數(shù)量可能非常多)造成數(shù)據(jù)庫容量問題,在查詢計(jì)算時(shí)由于大量冗余數(shù)據(jù)參與運(yùn)算還會影響計(jì)算性能,導(dǎo)致雖然用了寬表但仍然查詢很慢。
數(shù)據(jù)錯(cuò)誤
由于寬表不符合三范式要求,數(shù)據(jù)存儲時(shí)可能出現(xiàn)一致性錯(cuò)誤(臟寫)。比如同一個(gè)銷售員在不同記錄中可能存儲了不同的性別,同一個(gè)供應(yīng)商在不同記錄中的所在地可能出現(xiàn)矛盾?;谶@樣的數(shù)據(jù)做分析結(jié)果顯然不對,而這種錯(cuò)誤非常隱蔽很難被發(fā)現(xiàn)。
另外,如果構(gòu)建的寬表不合理還會出現(xiàn)匯總錯(cuò)誤。比如基于一對多的A表和B表構(gòu)建寬表,如果A中有計(jì)算指標(biāo)(如金額),在寬表中就會重復(fù),基于重復(fù)的指標(biāo)再匯總就會出現(xiàn)錯(cuò)誤。
靈活性差
寬表本質(zhì)上是一種按需建模的手段,根據(jù)業(yè)務(wù)需求來構(gòu)建寬表(雖然理論上可以把所有表的組合都形成寬表,但這只存在于理論上,如果要實(shí)際操作會發(fā)現(xiàn)需要的存儲空間大到完全無法接受的程度),這就出現(xiàn)了一個(gè)矛盾:BI系統(tǒng)建設(shè)的初衷主要是為了滿足業(yè)務(wù)靈活查詢的需要,即事先并不知道業(yè)務(wù)需求,有些查詢是在業(yè)務(wù)開展過程中逐漸催生出來的,有些是業(yè)務(wù)用戶臨時(shí)起意的查詢,這種靈活多變的需求采用寬表這種要事先加工的解決辦法極為矛盾,想要獲得寬表的好就得犧牲靈活性,可謂魚與熊掌不可兼得。
可用性問題
除了以上問題,寬表由于字段過多還會引起可用性低的問題。一個(gè)事實(shí)表會對應(yīng)多個(gè)維表,維表又有維表,而且表之間還可能存在自關(guān)聯(lián)/循環(huán)關(guān)聯(lián)的情況,這種結(jié)構(gòu)在數(shù)據(jù)庫系統(tǒng)中很常見,基于這些結(jié)構(gòu)的表構(gòu)建寬表,尤其要表達(dá)多個(gè)層級的時(shí)候,寬表字段數(shù)量會急劇增加,經(jīng)??赡苓_(dá)到成百上千個(gè)(有的數(shù)據(jù)庫表有字段數(shù)量限制,這時(shí)又要橫向分表),試想一下,在用戶接入界面如果出現(xiàn)上千個(gè)字段要怎么用?這就是寬表帶來的可用性差的問題。
總體來看,寬表的壞處在很多場景中經(jīng)常要大于好處,那為什么寬表還大量橫行呢?
因?yàn)闆]辦法。一直沒有比寬表更好的方案來解決前面提到的查詢性能和業(yè)務(wù)難度的問題。其實(shí)只要解決這兩個(gè)問題,寬表就可以不用,由寬表產(chǎn)生的各類問題也就解決了。
SPL+DQL消滅寬表
借助開源集算器SPL可以完成這個(gè)目標(biāo)。
SPL(Structured Process Language)是一個(gè)開源結(jié)構(gòu)化數(shù)據(jù)計(jì)算引擎,本身提供了不依賴數(shù)據(jù)庫的強(qiáng)大計(jì)算能力,SPL內(nèi)置了很多高性能算法,尤其是對關(guān)聯(lián)運(yùn)算做了優(yōu)化,對不同的關(guān)聯(lián)場景采用不同的手段,可以大幅提升關(guān)聯(lián)性能,從而不用寬表也能實(shí)時(shí)關(guān)聯(lián)以滿足多維分析時(shí)效性的需要。同時(shí),SPL還提供了高性能存儲,配合高效算法可以進(jìn)一步發(fā)揮性能優(yōu)勢。
只有高性能還不夠,SPL原生的計(jì)算語法不適合多維分析應(yīng)用接入(生成SPL語句對BI系統(tǒng)改造較大)。目前大部分多維分析前端都是基于SQL開發(fā)的,但SQL體系(不用寬表時(shí))在描述復(fù)雜關(guān)聯(lián)計(jì)算上又很困難,基于這樣的原因,SPL設(shè)計(jì)了專門的類SQL查詢語法DQL(Dimensional Query Language)用于構(gòu)建語義層。前端生成DQL語句,DQL Server將其轉(zhuǎn)換成SPL語句,再基于SPL計(jì)算引擎和存儲引擎完成查詢返回給前端,實(shí)現(xiàn)全鏈路BI查詢。需要注意的是,SPL只作為計(jì)算引擎存在,前端界面仍要由用戶自行實(shí)現(xiàn)(或選用相應(yīng)產(chǎn)品)。
SPL:關(guān)聯(lián)實(shí)現(xiàn)技術(shù)
SPL如何不用寬表也能實(shí)現(xiàn)實(shí)時(shí)關(guān)聯(lián)以滿足性能要求的目標(biāo)?
在BI業(yè)務(wù)中絕大部分的JOIN都是等值JOIN,也就是關(guān)聯(lián)條件為等式的 JOIN。SPL把等值關(guān)聯(lián)分為外鍵關(guān)聯(lián)和主鍵關(guān)聯(lián)。外鍵關(guān)聯(lián)是指用一個(gè)表的非主鍵字段,去關(guān)聯(lián)另一個(gè)表的主鍵,前者稱為事實(shí)表,后者稱為維表,兩個(gè)表是多對一的關(guān)系,比如訂單表和客戶表。主鍵關(guān)聯(lián)是指用一個(gè)表的主鍵關(guān)聯(lián)另一個(gè)表的主鍵或部分主鍵,比如客戶表和 VIP 客戶表(一對一)、訂單表和訂單明細(xì)表(一對多)。
這兩類 JOIN 都涉及到主鍵,如果充分利用這個(gè)特征采用不同的算法,就可以實(shí)現(xiàn)高性能的實(shí)時(shí)關(guān)聯(lián)了。
不過很遺憾,SQL 對 JOIN 的定義并不涉及主鍵,只是兩個(gè)表做笛卡爾積后再按某種條件過濾。這個(gè)定義很簡單也很寬泛,幾乎可以描述一切。但是,如果嚴(yán)格按這個(gè)定義去實(shí)現(xiàn) JOIN,理論上沒辦法在計(jì)算時(shí)利用主鍵的特征來提高性能,只能是工程上做些有限的優(yōu)化,在情況較復(fù)雜時(shí)(表多且層次多)經(jīng)常無效。
SPL 改變了 JOIN 的定義,針對這兩類 JOIN 分別處理,就可以利用主鍵的特征來減少運(yùn)算量,從而提高計(jì)算性能。
外鍵關(guān)聯(lián)
和SQL不同,SPL中明確地區(qū)分了維表和事實(shí)表。BI系統(tǒng)中的維表都通常不大,可以事先讀入內(nèi)存建立索引,這樣在關(guān)聯(lián)時(shí)可以少計(jì)算一半的HASH值。
對于多層維表(維表還有維表的情況)還可以用外鍵地址化的技術(shù)做好預(yù)關(guān)聯(lián)。即將維表(本表)的外鍵字段值轉(zhuǎn)換成對應(yīng)維表(外鍵表)記錄的地址。這樣被關(guān)聯(lián)的維表數(shù)據(jù)可以直接用地址取出而不必再進(jìn)行HASH值計(jì)算和比對,多層維表僅僅是多個(gè)按地址取值的時(shí)間,和單層維表時(shí)的關(guān)聯(lián)性能基本相當(dāng)。
類似的,如果事實(shí)表也不大可以全部讀入內(nèi)存時(shí),也可以通過預(yù)關(guān)聯(lián)的方式解決事實(shí)表與維表的關(guān)聯(lián)問題,提升關(guān)聯(lián)效率。
預(yù)關(guān)聯(lián)可以在系統(tǒng)啟動時(shí)一次性讀入并做好,以后直接使用即可。
當(dāng)事實(shí)表較大無法全內(nèi)存時(shí),SPL 提供了外鍵序號化方法:將事實(shí)表中的外鍵字段值轉(zhuǎn)換為維表對應(yīng)記錄的序號。關(guān)聯(lián)計(jì)算時(shí),用序號取出對應(yīng)維表記錄,這樣可以獲得和外鍵地址化類似的效果,同樣能避免HASH值的計(jì)算和比對,大幅提升關(guān)聯(lián)性能。
主鍵關(guān)聯(lián)
有的事實(shí)表還有明細(xì)表,比如訂單和訂單明細(xì),二者通過主鍵和部分主鍵進(jìn)行關(guān)聯(lián),前者作為主表后者作為子表(還有通過全部主鍵關(guān)聯(lián)的稱為同維表,可以看做主子表的特例)。主子表都是事實(shí)表,涉及的數(shù)據(jù)量都比較大。
SPL為此采用了有序歸并方法:預(yù)先將外存表按照主鍵有序存儲,關(guān)聯(lián)時(shí)順序取出數(shù)據(jù)做歸并,不需要產(chǎn)生臨時(shí)緩存,只用很小的內(nèi)存就可以完成計(jì)算。而SQL采用的HASH分堆算法復(fù)雜度較高,不僅要計(jì)算HASH值進(jìn)行對比,還會產(chǎn)生臨時(shí)緩存的讀寫動作,運(yùn)算性能很差。
HASH 分堆技術(shù)實(shí)現(xiàn)并行困難,多線程要同時(shí)向某個(gè)分堆緩存數(shù)據(jù),造成共享資源沖突;某個(gè)分堆關(guān)聯(lián)時(shí)又會消費(fèi)大量內(nèi)存,無法實(shí)施較大的并行數(shù)量。而有序歸則易于分段并行。數(shù)據(jù)有序時(shí),子表就可以根據(jù)主表鍵值進(jìn)行同步對齊分段以保證正確性,無需緩存,且因?yàn)檎加脙?nèi)存很少可以采用較大的并行數(shù),從而獲得更高性能。
預(yù)先排序的成本雖高,但是一次性做好即可,以后就總能使用歸并算法實(shí)現(xiàn) JOIN,性能可以提高很多。同時(shí),SPL 也提供了在有追加數(shù)據(jù)時(shí)仍然保持?jǐn)?shù)據(jù)整體有序的方案。
對于主子表關(guān)聯(lián)SPL還可以采用更有效的存儲形式將主子表一體化存儲,子表作為主表的集合字段,其取值是由與該主表數(shù)據(jù)相關(guān)的多條子表記錄構(gòu)成。這相當(dāng)于預(yù)先實(shí)現(xiàn)了關(guān)聯(lián),再計(jì)算時(shí)直接取數(shù)計(jì)算即可,不需要比對,存儲量也更少,性能更高。
存儲機(jī)制
高性能離不開有效的存儲。SPL也提供了列式存儲,在BI計(jì)算中可以大幅降低數(shù)據(jù)讀取量以提升讀取效率。SPL列存采用了獨(dú)有的倍增分段技術(shù),相對傳統(tǒng)列存分塊并行方案要在很大數(shù)據(jù)量時(shí)(否則并行會受到限制)才會發(fā)揮優(yōu)勢不同,這個(gè)技術(shù)可以使SPL列存在數(shù)據(jù)量不很大時(shí)也能獲得良好的并行分段效果,充分發(fā)揮并行優(yōu)勢。
SPL還提供了針對數(shù)據(jù)類型的優(yōu)化機(jī)制,可以顯著提升多維分析中的切片運(yùn)算性能。比如將枚舉型維度轉(zhuǎn)換成整數(shù),在查詢時(shí)將切片條件轉(zhuǎn)換成布爾值構(gòu)成的對位序列,在比較時(shí)就可以直接從序列指定位置取出切片判斷結(jié)果。還有將多個(gè)標(biāo)簽維度(取值是或否的維度,這種維度在多維分析中大量存在)存儲在一個(gè)整數(shù)字段中的標(biāo)簽位維度技術(shù)(一個(gè)整數(shù)字段可以存儲16個(gè)標(biāo)簽),不僅大幅減少存儲量,在計(jì)算時(shí)還可以針對多個(gè)標(biāo)簽同時(shí)做按位計(jì)算從而大幅提升計(jì)算性能。
有了這些高效機(jī)制以后,我們就可以在BI分析中不再使用寬表,轉(zhuǎn)而基于SPL存儲和算法做實(shí)時(shí)關(guān)聯(lián),性能比寬表還更高(沒有冗余數(shù)據(jù)讀取量更小,更快)。
不過,只有這些還不夠,SPL原生語法還不適合BI前端直接訪問,這就需要適合的語義轉(zhuǎn)換技術(shù),通過適合的方式將用戶操作轉(zhuǎn)換成SPL語法進(jìn)行查詢。
這就需要DQL了。
DQL:關(guān)聯(lián)描述技術(shù)
DQL是SPL之上的語義層構(gòu)建工具,在這一層完成對于SPL數(shù)據(jù)關(guān)聯(lián)關(guān)系的描述(建模)再為上層應(yīng)用服務(wù)。即將SPL存儲映射成DQL表,再基于表來描述數(shù)據(jù)關(guān)聯(lián)關(guān)系。
通過對數(shù)據(jù)表關(guān)系描述以后形成了一種以維度為中心的總線式結(jié)構(gòu)(不同于E-R圖中的網(wǎng)狀結(jié)構(gòu)),中間是維度,表與表之間不直接相關(guān)都通過維度過渡。
基于這種結(jié)構(gòu)下的關(guān)聯(lián)查詢(DQL語句)會很好表達(dá)。比如要根據(jù)訂單表(orders)、客戶表(customer)、銷售員表(employee)以及城市表(city)查詢:本年度華東的銷售人員,在全國各銷售區(qū)的銷售額。
用SQL寫起來是這樣的:
多個(gè)表關(guān)聯(lián)要JOIN多次,同一個(gè)地區(qū)表要反復(fù)關(guān)聯(lián)兩次才能查到銷售員和客戶的所在區(qū)域,對于這種情況BI前端表達(dá)起來會很吃力,如果將關(guān)聯(lián)開放出來,用戶又很難理解。
那么DQL是怎么處理的呢?
DQL寫法:
DQL不需要JOIN多個(gè)表,只基于orders單表查詢就可以了,外鍵指向表的字段當(dāng)成屬性直接使用,有多少層都可以引用下去,很好表達(dá)。像查詢客戶所在地區(qū)通過cus_id.city_id.area一直寫下去就可以了,這樣就消除了關(guān)聯(lián),將多表關(guān)聯(lián)查詢轉(zhuǎn)化成單表查詢。
更進(jìn)一步,我們再基于DQL開發(fā)BI前端界面就很容易,比如可以做成這樣:
用樹結(jié)構(gòu)分多級表達(dá)多層維表關(guān)聯(lián),這樣的多維分析頁面不僅容易開發(fā),普通業(yè)務(wù)用戶使用時(shí)也很容易理解,這就是DQL的效力。
總結(jié)一下,寬表的目的是為了解決BI查詢性能和前端工程實(shí)現(xiàn)問題,而寬表會帶來數(shù)據(jù)冗余和靈活性差等問題。通過SPL的實(shí)時(shí)關(guān)聯(lián)技術(shù)與高效存儲可以解決性能問題,而且性能比寬表更高,同時(shí)不存在數(shù)據(jù)冗余,存儲空間也更?。▔嚎s);DQL構(gòu)建的語義層解決了多維分析前端工程的實(shí)現(xiàn)問題,讓實(shí)時(shí)關(guān)聯(lián)成為可能,,靈活性更高(不再局限于寬表的按需建模),界面也更容易實(shí)現(xiàn),應(yīng)用范圍更廣。
SPL+DQL繼承(超越)寬表的優(yōu)點(diǎn)同時(shí)改善其缺點(diǎn),這才是BI該有的樣子。