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