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