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