資料倉庫的性能問題及解決之道

随着資料量不斷增長和業務複雜度逐漸攀升，資料處理效率面臨巨大挑戰。最典型的表現是面向分析型場景的資料倉庫性能問題越來越突出，壓力大、性能低，查詢時間長甚至查不出來，跑批跑不完造成生産事故等問題時有發生。當資料倉庫出現性能問題時便不能很好服務業務了。

傳統資料倉庫的性能解決方案

叢集，也就是采用分布式技術，依賴擴充硬體來提升性能，是最常見的手段。将一個大的任務拆分到各個叢集節點上同時計算自然可以獲得比單機更好的性能，即使不進行分布式計算而簡單分擔并發任務也可以減輕單節點的計算壓力。叢集解決性能問題的思路簡單粗暴，隻要資料倉庫支援叢集并且任務能夠拆分就可以通過堆硬體來解決性能問題，雖然可能做不到線性提升但基本都會有效果。

叢集的缺點在于高成本。現在的大資料時代大家言必提叢集，經常不管單機性能是不是得到充分發揮，反正隻要能叢集往裡擴容就行了，叢集似乎是很多人眼中的“萬能藥”。但我們要注意，叢集需要更多硬體支撐成本自然高，叢集運維也需要投入更多資源。另外有些複雜多步驟計算任務由于無法拆分壓根就沒法用叢集，比如大多數的多步驟大資料量跑批任務還隻能用單機（單體資料庫存儲過程）完成。叢集雖好，但并不萬能，即使财大氣粗架設叢集也并不能解決所有性能問題。

對于一些耗時較長的查詢還可以采用預計算，采用空間換時間的辦法将要用到的資料事先加工好，這樣就可以将計算複雜度降到 O(1) 大幅提升效率。預計算同樣可以解決很多性能問題，通過預彙總将要用到的資料事先加工好，用空間換時間，對多元分析場景尤其有效。

預計算的問題在于靈活性太差。我們仍然以多元分析場景為例，雖然理論上可以将所有次元組合都預計算好（這樣就可以滿足所有查詢需求），但真這樣做會發現不現實，這需要天量的存儲空間才能滿足。是以隻能通過梳理業務進行部分預計算，這就大大限制了查詢範圍降低了靈活性。

其實即使能全部預計算，仍然解決不了諸如非正常聚合（如算中位數、方差）、組合聚合（如算月平均銷售額）、條件測度（如算交易金額大于 100 元以上的訂單銷售額合計）、時間段彙總（自由選擇時間段内的彙總）等情況。實際業務中的查詢需求五花八門，靈活性極強，預彙總隻能解決其中的一部分甚至僅僅一小部分問題，要更大範圍、更高效率地滿足多樣的線上查詢需求還需要更有效的計算手段。

更有效的手段是優化引擎，讓資料倉庫在同樣的硬體資源下跑出更好的性能。這是許多廠商的工作重點，有大量工程性手段已為業界熟知，比如提供列存、向量化執行、編碼壓縮、記憶體利用等（叢集也可以算是一種工程手段），通過這些技術在一定資料規模内可以提升幾倍的計算性能，在某些場景下足夠用。但工程些手段并不能改變計算的複雜度，在資料量大和複雜度特别大的場景時，性能提升仍然常常不能滿足需求。

優化引擎更有效的手段是算法層面上的（複雜度層面的提升），一個優秀的資料倉庫優化引擎可以猜出一個查詢語句的真正目标，進而采用更高效的算法執行（而不以字面表達的邏輯去執行），算法層面的改善經常可以獲得更高的性能提升。目前大部分資料倉庫仍以 SQL 作為主要查詢語言，基于 SQL 的優化已經做得足夠優秀。但由于 SQL 描述能力的局限性，複雜查詢會采取非常迂回的方法，一旦 SQL 複雜度上來優化引擎就很難發揮作用了（猜不出目标隻能按照字面表達去執行，性能就不會提升），即優化引擎僅對簡單查詢有效。

舉個例子，TopN 運算時：

SELECT TOP 10 x FROM T ORDER BY x DESC           

大部分資料倉庫都會優化，不會真排序。但是改成組内 TopN 以後：

select * from
 (select y,*,row_number() over (partition by y order by x desc) rn from T)
where rn<=10           

複雜度雖然沒有提升很多，但優化引擎會犯暈，猜不出這句 SQL 的真正目的，隻能按照 SQL 表達的意思進行大排序而性能低下。是以，有些場景我們會事先把資料加工成寬表，這樣就可以簡化查詢進而發揮優化引擎的作用。雖然會付出很多代價，但為了有效利用優化引擎有時也隻能不得已而為之。

目前幾乎所有資料倉庫技術都在競争 SQL 能力，提供更完善的 SQL 支援、提供更強的優化能力、支援更大規模的叢集等等，雖然這可以最大限度地“讨好”閱聽人很廣的 SQL 使用者，但在面向複雜計算場景時使用前面的方法往往不夠有效，性能問題仍然存在。再在 SQL 的基礎上努力（工程上的優化）獲得的效用也并不高，并不能從根本上解決問題。而這類問題在實際業務中并不少見，下面是幾個例子。

複雜有序計算。比如使用者行為轉換漏鬥分析：使用者頁面浏覽商品、搜尋、加購物車、下單、付款等多個有序事件，現在要統計每個步驟的使用者流失率，就需要遵循多個事件在指定時間視窗内完成、按指定次序發生才有效的原則來實作。這使用 SQL 就很難實作了，需要借助多個子查詢（與步驟數量一緻）和反複關聯完成，有些資料倉庫甚至不能執行這種複雜語句，即使能執行，性能也很低，更難以優化。

多步驟大資料量跑批。複雜的跑批任務使用 SQL 效果也不好，經常需要在存儲過程中借助遊标逐漸讀取資料處理，但遊标性能很低又無法并行，最後不僅資源消耗大性能也低。同時，幾十步運算在存儲過程中需要幾千行代碼，過程中會伴随中間結果反複落地，性能很差，月末年終資料量大任務多的時候就會出現在規定時間内跑不完的情況。

大資料上多名額計算。很多行業都有名額計算的需要，比如銀行的貸款業務中就包括多級分類次元、多種擔保類型，再加上客戶種類、放款方式、币種、分支機構、日期、客戶年齡段、學曆等名額會衍生出極其龐大的名額數量，彙總這些名額時要基于大量的明細資料完成，計算時會涉及大表關聯、條件過濾、分組彙總、去重計數等多種混合運算，靈活、量大、計算複雜，同時伴随高并發導緻使用 SQL 來做很吃力，預計算不靈活，實時算又太慢。

這些問題用 SQL 很難解決，于是擴充 SQL 能力成為繼叢集、預計算和優化引擎外的第四方案。現在很多資料倉庫支援使用者自定義函數（UDF）來擴充計算能力，使用者可以根據實際需求編寫 UDF 以滿足自身的需要。但 UDF 的開發難度較高，這對使用者的能力有很高要求。更重要的是 UDF 仍然無法解決資料倉庫的計算性能問題，因為仍然受限于資料庫的存儲，無法根據計算特點設計更高效的資料存儲（組織）形式，很多高性能算法就無法實施，自然無法獲得高性能。

是以，要解決這些問題就需要采用非 SQL 的方案手段，在資料庫外由程式員控制執行邏輯，以便更好地采用低複雜度算法和充分利用工程手段。

于是我們看到諸如 Spark 等大資料計算引擎應運而生。Spark 提供的是一個分布式計算架構，本意還是希望通過大規模叢集來滿足算力的需要，由于基于全記憶體的設計對于超出記憶體的計算不夠友好，而且 RDD 采用的 immutable 機制，在每個計算步驟後都會複制出新的 RDD，造成記憶體和 CPU 的大量占用和浪費，性能很低，工程手段利用的并不夠好。此外，Spark 的計算類庫也不夠豐富，缺少足夠的高性能算法，很難實作“低複雜度算法”的目标。加之 Scala 的使用難度很大，導緻面對前面提到的那些複雜計算問題編碼難度極高，既不好寫性能也不高，這可能也是 Spark 又要擁抱 SQL 的原因之一。

傳統資料倉庫不行，外部程式設計（Spark）又難又慢，那還有什麼選擇？

從前面的内容我們不難得出這樣的結論，要解決資料倉庫的性能問題确實需要獨立于 SQL 的計算體系（像 Spark），但這個計算體系要具備既簡單又快的特點。描述複雜計算邏輯不能像 Spark 那麼複雜，甚至要比 SQL 更簡單；在計算性能上不能僅靠叢集能力，還要提供豐富的高性能算法和工程能力進而能夠充分利用硬體資源将單機性能發揮到極緻。既有快速描述低複雜度算法的能力，又具備足夠多的工程手段。同時，如果在部署運維方面還很友善就更理想了。

esProc SPL 的解決之道

esProc SPL 是一個專門處理（半）結構化資料的計算引擎，與目前資料倉庫的能力一緻。但與傳統 SQL 型資料倉庫不同，esProc 沒有繼續采用關系代數而是設計了全新的計算體系，在此基礎上提供了 SPL（Structured Process Language）文法。SPL 相對 SQL 提供了更多的資料類型和運算、更豐富的計算類庫，描述能力更強，在過程計算的加持下可以按照自然思維編寫算法，不必繞，也更低代碼，可以很好應對前面場景中的多步驟複雜計算，相對其他寫死以及 SQL 的實作方式更簡單。

在性能方面，esProc SPL 提供了很多“更低複雜度”的高性能算法來保證計算性能。我們知道，軟體改變不了硬體的性能，想要在同等硬體條件下獲得更高的計算性能隻能設計更低複雜度的算法讓計算機少執行一些基本運算，這樣自然就變快了。但是算法不僅要想出來，還要能實作，寫得越簡單越好，是以說寫得簡單和跑得快其實是一回事。

SPL 提供的部分高性能算法，其中很多都是 SPL 的獨創發明

當然，高性能算法還離不開良好的資料組織，即資料存儲。像有序歸并、單邊分堆都要求資料有序才能實施。但是資料庫的存儲相對封閉，外界無法幹預，無法根據計算特征設計存儲。基于這樣的原因，SPL 提供了自有的二進制檔案存儲，将資料存儲在庫外的檔案系統中，以便充分利用列存、有序、壓縮、并行分段等資料存儲優勢，實作根據計算特性來靈活組織資料，充分發揮高性能算法效力。

除了這些高性能算法，esProc 還提供了衆多工程手段來提升計算性能，列存、壓縮編碼、大記憶體以及向量式計算等等。如前所述，這些工程手段雖然無法改變計算的複雜度，但使用後經常能獲得數倍的性能提升，再疊加 SPL 内置的衆多低複雜度算法，性能提升一兩個數量級是常态。

前面說過，基于非 SQL 體系要獲得高性能需要由程式員控制執行邏輯，采用低複雜度算法，并且充分利用工程手段。SPL 理論體系的不同帶來了描述能力強的效果，編碼簡單不必繞；豐富的高性能算法庫及相應存儲機制可以直接使用，實作采用低複雜度算法的同時充分工程優化手段的目标，達到既簡單又快的效果。

像 TopN 在 SPL 中被看成普通的聚合運算，無論對全集和分組都是一樣的，都不需要大排序，這樣就實作了“采用更低複雜度算法”的目标，進而獲得了高性能。

我們再來看一下前面提到的電商漏鬥例子實作，來感受 SQL 和 SPL 的不同。

SQL 實作：

with e1 as (
 select uid,1 as step1,min(etime) as t1
 from event
 where etime>= to_date('2021-01-10') and etime<to_date('2021-01-25')
 and eventtype='eventtype1' and …
 group by 1),
e2 as (
 select uid,1 as step2,min(e1.t1) as t1,min(e2.etime) as t2
 from event as e2
 inner join e1 on e2.uid = e1.uid
 where e2.etime>= to_date('2021-01-10') and e2.etime<to_date('2021-01-25')
 and e2.etime > t1 and e2.etime < t1 + 7
 and eventtype='eventtype2' and …
 group by 1),
e3 as (
 select uid,1 as step3,min(e2.t1) as t1,min(e3.etime) as t3
 from event as e3
 inner join e2 on e3.uid = e2.uid
 where e3.etime>= to_date('2021-01-10') and e3.etime<to_date('2021-01-25')
 and e3.etime > t2 and e3.etime < t1 + 7
 and eventtype='eventtype3' and …
 group by 1)
select
 sum(step1) as step1,
 sum(step2) as step2,
 sum(step3) as step3
from
 e1
 left join e2 on e1.uid = e2.uid
 left join e3 on e2.uid = e3.uid           

SPL 實作：

A
1	=["etype1","etype2","etype3"]
2	=file("event.ctx").open()
3	=A2.cursor(id,etime,etype;etime>=date("2021-01-10") && etime<date("2021-01-25") && A1.contain(etype) && …)
4	=A3.group(uid).(~.sort(etime))
5	=A4.new(~.select@1(etype==A1(1)):first,~:all).select(first)
6	=A5.(A1.(t=if(#==1,t1=first.etime,if(t,all.select@1(etype==A1.~ && etime>t && etime<t1+7).etime, null))))
7	=A6.groups(;count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)

SPL 在有序計算的支援下實作代碼更加簡短，可以根據自然思維分步（過程化支援）編寫代碼，而且這段代碼可以處理任意步驟的漏鬥分析（這裡是 3 步，更多步隻需要改變參數即可），相對 SQL 每增加一步漏鬥就要增加一個子查詢顯然更有優勢，這就是 SPL 的簡單帶來的效果。

性能上，這個例子其實是實際案例的簡化版（原 SQL 有近 200 行），使用者使用 Snowflake 的 Medium 伺服器（相當于 4*8=32 核）3 分鐘沒有跑出來；而 esProc SPL 代碼在一個 12 核 1.7G 的低端伺服器上僅用不到10 秒就跑出來了，這是 SPL 高性能算法和相應工程手段造就的高性能。

有了這些機制以後，esProc SPL 就可以充分利用硬體資源，将單機性能發揮到極緻，不僅原來很多單機性能問題可以得到有效解決，甚至很多原來使用叢集的計算現在也可以用單機搞定（可能更快），達到單機頂級群的效果。當然，單機有極限，SPL 也提供了分布式叢集功能，當單機性能無論如何也無法滿足需要時可以通過叢集橫向擴充算力。這也是 SPL 的高性能計算理念：先把單機性能提升到極緻，不夠用再叢集。

當然，任何技術都有不足，SPL 也不例外。SQL 經過幾十年的積累發展，很多資料庫都擁有很強的優化引擎。對于适合用 SQL 完成的簡單場景運算，可以将普通程式員寫出來的慢語句優化出較好的性能，從這個意義上講，對程式員的要求相對較低。某些場景（比如多元分析）已經被優化多年，某些 SQL 引擎也可以跑出相當好的極緻性能。相比之下，SPL 沒有做多少自動優化的功能，要跑出高性能，幾乎全靠程式員寫出低複雜度的代碼。程式員需要經過一定的訓練來熟悉 SPL 的理念和庫函數，會多一個上手的門檻，不過獲得數量級的性能提升和成倍的成本下降，這些付出通常也還是值得的。