在辦公筆記本玩轉(zhuǎn)基于星型模型的自助探索分析

　　概要：

　　在DELL燃7000筆記本電腦上(i5-7200u)，對(duì)億級(jí)事實(shí)表&維度表的探索式分析，平均響應(yīng)性能從11.9秒優(yōu)化到8.9秒，提升程度約25%，這一切歸功于Smartbi+Vertica的高性能自助分析解決方案!

　　難點(diǎn)：

　　星型模型又稱(chēng)Star-schema，是一種數(shù)據(jù)庫(kù)的建模(組織數(shù)據(jù))的方式，它與三范模型3-NF的知名度等高。由于這類(lèi)模型都是以“事實(shí)表”為核心，圍繞幾個(gè)維度表，所以非常形象的被稱(chēng)為“星型”。

　　在沒(méi)有犧牲空間換時(shí)間(OLAP)的數(shù)據(jù)分析場(chǎng)景下，這樣的建模方式非常有利于數(shù)據(jù)更新，因?yàn)榫S護(hù)事實(shí)表的增量以及事實(shí)表和維度表的數(shù)據(jù)一致性比較快速，或者說(shuō)ETL的時(shí)間窗口比較小。但其對(duì)于查詢類(lèi)型的分析應(yīng)用，卻需要消耗大量的“關(guān)聯(lián)”運(yùn)算，對(duì)CPU來(lái)說(shuō)是比較操作，因此在很多大數(shù)據(jù)量的數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)中，往往其查詢性能并不好。

　　更具挑戰(zhàn)的是，在需要提供自助探索的分析平臺(tái)上(比如Smartbi的透視分析以及Tableau等)，業(yè)務(wù)人員無(wú)法預(yù)料的會(huì)動(dòng)態(tài)生成各種查詢請(qǐng)求，從技術(shù)的角度說(shuō)就是SQL沒(méi)有規(guī)律，任何字段都可能是where條件、group分組以及計(jì)算字段，這就導(dǎo)致索引等傳統(tǒng)DBA的手段毫無(wú)用武之地。

　　干貨

　　關(guān)注過(guò)Smartbi公眾號(hào)的同學(xué)可能知道，Smartbi在7月份與Vertica進(jìn)行了戰(zhàn)略合作，基于這個(gè)新一代列式MPP數(shù)據(jù)庫(kù)發(fā)布了“高性能自助分析解決方案“，在隨后9月的workshop中提供了1個(gè)億級(jí)的星型數(shù)據(jù)模型和22個(gè)性能測(cè)試案例。

　　我在本人筆記本電腦對(duì)V201709版星型模型做了性能測(cè)試，平均響應(yīng)時(shí)間為11.9秒，個(gè)人感受只能是差強(qiáng)人意。22個(gè)測(cè)試案例的結(jié)果如下，單位為秒：

　　筆記本電腦型號(hào)燃7000，配置如下，只不過(guò)操作系統(tǒng)為了安裝Vertica改成了linux：

　　這個(gè)配置和價(jià)位是非常親民的吧，尤其這顆CPU在牙膏廠(Intel)的產(chǎn)品里根本排不上號(hào)。

　　言歸正傳，最近本人研究了一下Smartbi的這個(gè)星型數(shù)據(jù)模型，對(duì)其做了2項(xiàng)調(diào)整工作，第一是將3個(gè)維度表的關(guān)聯(lián)字段改成了整型(當(dāng)然首先是在維度表增加了車(chē)型、姓名、城市的整數(shù)編號(hào)，其次是在事實(shí)表增加這3個(gè)字段)，第二是對(duì)事實(shí)表按年份進(jìn)行了分區(qū)。

　　同樣按照22個(gè)案例進(jìn)行了測(cè)試，就得到了25%的性能提升，達(dá)到8.9秒，結(jié)果棒棒的!

　　具體來(lái)說(shuō)，前3個(gè)測(cè)試案例是對(duì)事實(shí)表3個(gè)字段的分組求和，不涉及任何優(yōu)化的內(nèi)容，所以沒(méi)有什么改變，甚至由于隨機(jī)性的誤差還有一些下降。從第四個(gè)開(kāi)始，2個(gè)優(yōu)化手段開(kāi)始發(fā)揮作用，平均提升更大(30%)。

　　既然此次優(yōu)化用了2個(gè)手段，那么它們各自有多大貢獻(xiàn)呢?(原諒本人懶得重新測(cè))

　　將測(cè)試案例的三類(lèi)對(duì)比來(lái)看，因?yàn)?rdquo;同比計(jì)算“和”條件匯總“都用到年份作為條件，我們暫且可以認(rèn)為它們更能體現(xiàn)按年做分區(qū)的優(yōu)化作用，這里它們分別提升了27%和32%，比普通的全表匯總提升的21%更有效果，就認(rèn)為有5%-10%的提升吧。

　　另外從這個(gè)圖可以看到，以前同比計(jì)算的平均性能比全表匯總明顯要慢，但優(yōu)化后基本差不多了，都在11秒左右。而按年條件匯總的平均性能從6秒提升到4秒，真的是非常優(yōu)秀了!

　　總結(jié)

　　只有用列式數(shù)據(jù)庫(kù)，才可能降低大數(shù)據(jù)量分析對(duì)IO的硬性要求，使得采用筆記本做數(shù)據(jù)分析成為可能。但能把1億數(shù)據(jù)量的星型模型玩轉(zhuǎn)自助分析的，目前也就是Smartbi+Vertica，最后給Smartbi透視分析的同環(huán)比計(jì)算、分組字段和自由鉆取點(diǎn)個(gè)贊，以后有空再繼續(xù)挑戰(zhàn)這個(gè)任務(wù)!

　　寫(xiě)在最后：該優(yōu)化的模型已經(jīng)被Smartbi采納，用于后續(xù)的workshop活動(dòng)!