這些 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 …;
這里的 T 是個有數(shù)億行的巨大表，要分別按三種方式分組，分組的結(jié)果集都不大。
from T -- 數(shù)據(jù)來自 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 …; -- 遍歷中的第三種分組
能一次返回多個結(jié)果集，那就可以大幅提高性能了。
可惜， SQL 沒有這種語法，寫不出這樣的語句，只能用個變通的辦法，就是用 group a,b,c,d 的寫法先算出更細致的分組結(jié)果集，但要先存成一個臨時表，才能進一步用 SQL 計算出目標(biāo)結(jié)果。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) minzgroup 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 …;
這樣只要遍歷一次了，但要把不同的 WHERE 條件轉(zhuǎn)到前面的 case when 里，代碼復(fù)雜很多，也會加大計算量。而且，計算臨時表時分組字段的個數(shù)變得很多，結(jié)果集就有可能很大，最后還對這個臨時表做多次遍歷，計算性能也快不了。大結(jié)果集分組計算還要硬盤緩存，本身性能也很差。
TopN 運算同樣會遇到這種無奈。舉個例子，用 Oracle 的 SQL 寫 top5 大致是這樣的：
select * from (select x from T order by x desc) where rownum<=5表 T 有 10 億條數(shù)據(jù)，從 SQL 語句來看，是將全部數(shù)據(jù)大排序后取出前 5 名，剩下的排序結(jié)果就沒用了！大排序成本很高，數(shù)據(jù)量很大內(nèi)存裝不下，會出現(xiàn)多次硬盤數(shù)據(jù)倒換，計算性能會非常差！
select * from(select y,x,row_number() over (partition by y order by x desc) rn from T)where rn<=5
這時候，數(shù)據(jù)庫的優(yōu)化引擎就暈了，不會再采用上面說的把 TopN 理解成聚合運算的辦法。只能去做排序了，結(jié)果運算速度陡降！
select y,top(x,5) from T group by yselect o.oid,o.orderdate,o.amountfrom orders oleft join city ci on o.cityid = ci.cityidleft join shipper sh on o.shid=sh.shidleft join employee e on o.eid=e.eidleft join supplier su on o.suid=su.suidwhere ci.state='New York'and e.title = 'manager'and ...
訂單表有幾千萬數(shù)據(jù)，城市、運貨商、雇員、供應(yīng)商等表數(shù)據(jù)量都不大。過濾條件字段可能會來自于這些表，而且是前端傳參數(shù)到后臺的，會動態(tài)變化。
select o.oid,o.orderdate,o.amountfrom orders oleft join city c on o.cid = c.# -- 訂單表的城市編號通過序號 #關(guān)聯(lián)城市表left join shipper sh on o.shid=sh.# -- 訂單表運貨商號通過序號 #關(guān)聯(lián)運貨商表left join employee e on o.eid=e.# -- 訂單表的雇員編號通過序號 #關(guān)聯(lián)雇員表left join supplier su on o.suid=su.# -- 訂單表供應(yīng)商號通過序號 #關(guān)聯(lián)供應(yīng)商表where ci.state='New York'and e.title = 'manager'and ...
可惜的是，SQL 使用了無序集合概念，即使這些編號已經(jīng)序號化了，數(shù)據(jù)庫也無法利用這個特點，不能在對應(yīng)的關(guān)聯(lián)表這些無序集合上使用序號快速定位的機制，只能使用索引查找，而且數(shù)據(jù)庫并不知道編號被序號化了，仍然會去計算 HASH 值和比對，性能還是很差！
select id,amt,tdate,… from Twhere id='10100'and tdate>= to_date('2021-01-10', 'yyyy-MM-dd')and tdate<to_date('2021-01-25', 'yyyy-MM-dd')and …
在 T 表的幾億條歷史數(shù)據(jù)中，快速找到某個帳戶的幾條到幾千條明細，SQL 寫出來并不復(fù)雜，難點是大并發(fā)時響應(yīng)速度要達到秒級甚至更快。為了提高查詢響應(yīng)速度，一般都會對 T 表的 id 字段建索引：
create index index_T_1 on T(id)在數(shù)據(jù)庫中，用索引查找單個帳戶的速度很快，但并發(fā)很多時就會明顯變慢。原因還是上面提到的 SQL 無序理論基礎(chǔ)，總數(shù)據(jù)量很大，無法全讀入內(nèi)存，而數(shù)據(jù)庫不能保證同一帳戶的數(shù)據(jù)在物理上是連續(xù)存放的。硬盤有最小讀取單位，在讀不連續(xù)數(shù)據(jù)時，會取出很多無關(guān)內(nèi)容，查詢就會變慢。高并發(fā)訪問的每個查詢都慢一點，總體性能就會很差了。在非常重視體驗的當(dāng)下，誰敢讓用戶等待十秒以上？！
| A | B | |
| 1 | A1=file("T.ctx").open().cursor(a,b,c,d,x,y,z) | |
| 2 | cursor A1 | =A2.select(…).groups(a,b;sum(x)) |
| 3 | //定義遍歷中的第一種過濾、分組 | |
| 4 | cursor | =A4.select(…).groups(c,d;max(y)) |
| 5 | //定義遍歷中的第二種過濾、分組 | |
| 6 | cursor | =A6.select(…).groupx(a,c;avg(y),min(z)) |
| 7 | //定義遍歷中的第三種過濾、分組 | |
| 8 | … | //定義結(jié)束，開始計算三種方式的過濾、分組 |
用聚合的方式計算 Top5
| A | |
| 1 | =file("T.ctx").open() |
| 2 | =A1.cursor@m(x).total(top(-5,x), ? top(5,x)) |
| 3 | // top(-5,x)計算出 x 最大的前 5 名，top(5,x) 是 x 最小的前 5 名。 |
分組 Top5（多線程并行計算）
| A | |
| 1 | =file("T.ctx").open() |
| 2 | =A1.cursor@m(x,y).groups(y;top(-5,x), ? top(5,x)) |
用序號做關(guān)聯(lián)的 SPL 代碼：
| A | |
| 2 | >env(city,file("city.btx").import@b()),env(employee,file("employee.btx").import@b()),... |
| 3 | //系統(tǒng)初始化時，幾個小表讀入內(nèi)存 |
查詢
| A | |
| 1 | =file("orders.ctx").open().cursor(cid,eid,…).switch(cid,city:#;eid,employee:#;…) |
| 2 | =A1.select(cid.state='New ? York' && eid.title=="manager"…) |
| 3 | //先序號關(guān)聯(lián)，再引用關(guān)聯(lián)表字段寫過濾條件 |
高并發(fā)帳戶查詢的 SPL 代碼：
| A | B | |
| 1 | =file("T-original.ctx").open().cursor(id,tdate,amt,…) | |
| 2 | =A1.sortx(id) | =file("T.ctx") |
| 3 | =B2.create@r(#id,tdate,amt,…).append@i(A2) | |
| 4 | =B2.open().index(index_id;id) | |
| 5 | //將原數(shù)據(jù)排序后，另存為新表，并為帳號建立索引 | |
帳戶查詢
| A | B | |
| 1 | =T.icursor(;id==10100 ? && tdate>=date("2021-01-10") && tdate | |
| 2 | //查詢代碼非常簡單 | |
除了這些簡單例子，SPL 還能實現(xiàn)更多高性能算法，比如有序歸并實現(xiàn)訂單和明細之間的關(guān)聯(lián)、預(yù)關(guān)聯(lián)技術(shù)實現(xiàn)多維分析中的多層維表關(guān)聯(lián)、位存儲技術(shù)實現(xiàn)上千個標(biāo)簽統(tǒng)計、布爾集合技術(shù)實現(xiàn)多個枚舉值過濾條件的查詢提速、時序分組技術(shù)實現(xiàn)復(fù)雜的漏斗分析等等。