【Java開源數據庫語言】基于SPL如何提高SQL優化效率

很多大數據計算都是用SQL實現的，跑得慢時就要去優化SQL，但常常碰到讓人干瞪眼的情況。

比如，存儲過程中有三條大概形如這樣的語句執行得很慢：

select a,b,sum(x) from T group by a,b where …; select c,d,max(y) from T group by c,d where …; select a,c,avg(y),min(z) from T group by a,c where …;

1

2

3

這里的T是個有數億行的巨大表，要分別按三種方式分組，分組的結果集都不大。

分組運算要遍歷數據表，這三句SQL就要把這個大表遍歷三次，對數億行數據遍歷一次的時間就不短，何況三遍。

這種分組運算中，相對于遍歷硬盤的時間，CPU計算時間幾乎可以忽略。如果可以在一次遍歷中把多種分組匯總都計算出來，雖然CPU計算量并沒有變少，但能大幅減少硬盤讀取數據量，就能成倍提速了。

如果SQL支持類似這樣的語法：

from T --數據來自T表 select a,b,sum(x) group by a,b where … --遍歷中的第一種分組 select c,d,max(y) group by c,d where … --遍歷中的第二種分組 select a,c,avg(y),min(z) group by a,c where …; --遍歷中的第三種分組

1

2

3

4

能一次返回多個結果集，那就可以大幅提高性能了。

可惜， SQL沒有這種語法，寫不出這樣的語句，只能用個變通的辦法，就是用group a,b,c,d的寫法先算出更細致的分組結果集，但要先存成一個臨時表，才能進一步用SQL計算出目標結果。SQL大致如下：

create table T\_temp as select a,b,c,d, sum(case when … then x else 0 end) sumx, max(case when … then y else null end) maxy, sum(case when … then y else 0 end) sumy, count(case when … then 1 else null end) county, min(case when … then z else null end) minz group by a,b,c,d; select a,b,sum(sumx) from T\_temp group by a,b where …; select c,d,max(maxy) from T\_temp group by c,d where …; select a,c,sum(sumy)/sum(county),min(minz) from T\_temp group by a,c where …;

1

2

3

4

5

6

7

8

9

10

這樣只要遍歷一次了，但要把不同的WHERE條件轉到前面的case when里，代碼復雜很多，也會加大計算量。而且，計算臨時表時分組字段的個數變得很多，結果集就有可能很大，最后還對這個臨時表做多次遍歷，計算性能也快不了。大結果集分組計算還要硬盤緩存，本身性能也很差。

還可以用存儲過程的數據庫游標把數據一條一條fetch出來計算，但這要全自己實現一遍WHERE和GROUP的動作了，寫起來太繁瑣不說，數據庫游標遍歷數據的性能只會更差！

只能干瞪眼！

TopN運算同樣會遇到這種無奈。舉個例子，用Oracle的SQL寫top5大致是這樣的：

select \* from (select x from T order by x desc) where rownum<=5

1

表T有10億條數據，從SQL語句來看，是將全部數據大排序后取出前5名，剩下的排序結果就沒用了！大排序成本很高，數據量很大內存裝不下，會出現多次硬盤數據倒換，計算性能會非常差！

避免大排序并不難，在內存中保持一個5條記錄的小集合，遍歷數據時，將已經計算過的數據前5名保存在這個小集合中，取到的新數據如果比當前的第5名大，則插入進去并丟掉現在的第5名，如果比當前的第5名要小，則不做動作。這樣做，只要對10億條數據遍歷一次即可，而且內存占用很小，運算性能會大幅提升。

這種算法本質上是把TopN也看作與求和、計數一樣的聚合運算了，只不過返回的是集合而不是單值。SQL要是能寫成這樣,就能避免大排序了：

select top(x,5) from T

1

然而非常遺憾，SQL沒有顯式的集合數據類型，聚合函數只能返回單值，寫不出這種語句！

不過好在全集的TopN比較簡單，雖然SQL寫成那樣，數據庫卻通常會在工程上做優化，采用上述方法而避免大排序。所以Oracle算那條SQL并不慢。

但是，如果TopN的情況復雜了，用到子查詢中或者和JOIN混到一起的時候，優化引擎通常就不管用了。比如要在分組后計算每組的TopN，用SQL寫出來都有點困難。Oracle的SQL寫出來是這樣：

select \* from (select y,x,row\_number() over (partition by y order by x desc) rn from T) where rn<=5

1

這時候，數據庫的優化引擎就暈了，不會再采用上面說的把TopN理解成聚合運算的辦法。只能去做排序了，結果運算速度陡降！

假如SQL的分組TopN能這樣寫：

select y,top(x,5) from T group by y

1

把top看成和sum一樣的聚合函數，這不僅更易讀，而且也很容易高速運算。

可惜，不行。

還是干瞪眼！

關聯計算也是很常見的情況。以訂單和多個表關聯后做過濾計算為例，SQL大體是這個樣子：

select o.oid,o.orderdate,o.amount from orders o left join city ci on o.cityid = ci.cityid left join shipper sh on o.shid=sh.shid left join employee e on o.eid=e.eid left join supplier su on o.suid=su.suid where ci.state='New York' and e.title='manager' and ...

1

2

3

4

5

6

7

8

9

訂單表有幾千萬數據，城市、運貨商、雇員、供應商等表數據量都不大。過濾條件字段可能會來自于這些表，而且是前端傳參數到后臺的，會動態變化。

SQL一般采用HASH JOIN算法實現這些關聯，要計算 HASH 值并做比較。每次只能解析一個JOIN，有N個JOIN要執行N遍動作，每次關聯后都需要保持中間結果供下一輪使用，計算過程復雜，數據也會被遍歷多次，計算性能不好。

通常，這些關聯的代碼表都很小，可以先讀入內存。如果將訂單表中的各個關聯字段預先做序號化處理，比如將雇員編號字段值轉換為對應雇員表記錄的序號。那么計算時，就可以用雇員編號字段值（也就是雇員表序號），直接取內存中雇員表對應位置的記錄，性能比HASH JOIN快很多，而且只需將訂單表遍歷一次即可，速度提升會非常明顯！

也就是能把SQL寫成下面的樣子：

select o.oid,o.orderdate,o.amount from orders o left join city c on o.cid = c.# --訂單表的城市編號通過序號#關聯城市表 left join shipper sh on o.shid=sh.# --訂單表運貨商號通過序號#關聯運貨商表 left join employee e on o.eid=e.# --訂單表的雇員編號通過序號#關聯雇員表 left join supplier su on o.suid=su.#--訂單表供應商號通過序號#關聯供應商表 where ci.state='New York' and e.title='manager' and ...

1

2

3

4

5

6

7

8

9

可惜的是，SQL 使用了無序集合概念，即使這些編號已經序號化了，數據庫也無法利用這個特點，不能在對應的關聯表這些無序集合上使用序號快速定位的機制，只能使用索引查找，而且數據庫并不知道編號被序號化了，仍然會去計算 HASH 值和比對，性能還是很差！

有好辦法也實施不了，只能再次干瞪眼！

還有高并發帳戶查詢，這個運算倒是很簡單：

select id,amt,tdate,… from T where id='10100' and tdate>= to\_date('2021-01-10','yyyy-MM-dd') and tdate

1

2

3

4

5

在T表的幾億條歷史數據中，快速找到某個帳戶的幾條到幾千條明細，SQL寫出來并不復雜，難點是大并發時響應速度要達到秒級甚至更快。為了提高查詢響應速度，一般都會對 T 表的 id 字段建索引：

create index index_T_1 on T(id)

1

在數據庫中，用索引查找單個帳戶的速度很快，但并發很多時就會明顯變慢。原因還是上面提到的SQL無序理論基礎，總數據量很大，無法全讀入內存，而數據庫不能保證同一帳戶的數據在物理上是連續存放的。硬盤有最小讀取單位，在讀不連續數據時，會取出很多無關內容，查詢就會變慢。高并發訪問的每個查詢都慢一點，總體性能就會很差了。在非常重視體驗的當下，誰敢讓用戶等待十秒以上？！

容易想到的辦法是，把幾億數據預先按照帳戶排序，保證同一帳戶的數據連續存儲，查詢時從硬盤上讀出的數據塊幾乎都是目標值，性能就會得到大幅提升。

但是，采用SQL體系的關系數據庫并沒有這個意識，不會強制保證數據存儲的物理次序！這個問題不是SQL語法造成的，但也和SQL的理論基礎相關，在關系數據庫中還是沒法實現這些算法。

那咋辦？只能干瞪眼嗎？

不能再用SQL和關系數據庫了，要使用別的計算引擎。

開源的集算器SPL基于創新的理論基礎，支持更多的數據類型和運算，能夠描述上述場景中的新算法。用簡單便捷的SPL寫代碼，在短時間內能大幅提高計算性能！

上面這些問題用SPL寫出來的代碼樣例如下：

一次遍歷計算多種分組

用聚合的方式計算Top5

全集Top5（多線程并行計算）

分組Top5（多線程并行計算）

用序號做關聯的SPL代碼：

系統初始化

查詢

高并發帳戶查詢的SPL代碼：

數據預處理，有序存儲

帳戶查詢

除了這些簡單例子，SPL還能實現更多高性能算法，比如有序歸并實現訂單和明細之間的關聯、預關聯技術實現多維分析中的多層維表關聯、位存儲技術實現上千個標簽統計、布爾集合技術實現多個枚舉值過濾條件的查詢提速、時序分組技術實現復雜的漏斗分析等等。

正在為SQL性能優化頭疼的小伙伴們，可以和我們一起探討：

http://www.raqsoft.com.cn/wx/Query-run-batch-ad.html

SPL資料

SPL官網

SPL下載

SPL源代碼

Java SQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。