以下內容來自公眾號逆鋒起筆，關注每日干貨及時送達
很多大數(shù)據(jù)計算都是用 SQL 實現(xiàn)的，跑得慢時就要去優(yōu)化 SQL，但常常碰到讓人干瞪眼的情況。
比如，存儲過程中有三條大概形如這樣的語句執(zhí)行得很慢：
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ù)億行的巨大表，要分別按三種方式分組，分組的結果集都不大。分組運算要遍歷數(shù)據(jù)表，這三句 SQL 就要把這個大表遍歷三次，對數(shù)億行數(shù)據(jù)遍歷一次的時間就不短，何況三遍。這種分組運算中，相對于遍歷硬盤的時間，CPU 計算時間幾乎可以忽略。如果可以在一次遍歷中把多種分組匯總都計算出來，雖然 CPU 計算量并沒有變少，但能大幅減少硬盤讀取數(shù)據(jù)量，就能成倍提速了。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 …; -- 遍歷中的第三種分組
可惜， 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) 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 條件轉到前面的 case when 里，代碼復雜很多，也會加大計算量。而且，計算臨時表時分組字段的個數(shù)變得很多，結果集就有可能很大，最后還對這個臨時表做多次遍歷，計算性能也快不了。大結果集分組計算還要硬盤緩存，本身性能也很差。還可以用存儲過程的數(shù)據(jù)庫游標把數(shù)據(jù)一條一條 fetch 出來計算，但這要全自己實現(xiàn)一遍 WHERE 和 GROUP 的動作了，寫起來太繁瑣不說，數(shù)據(jù)庫游標遍歷數(shù)據(jù)的性能只會更差！
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 名，剩下的排序結果就沒用了！大排序成本很高，數(shù)據(jù)量很大內存裝不下，會出現(xiàn)多次硬盤數(shù)據(jù)倒換，計算性能會非常差！避免大排序并不難，在內存中保持一個 5 條記錄的小集合，遍歷數(shù)據(jù)時，將已經計算過的數(shù)據(jù)前 5 名保存在這個小集合中，取到的新數(shù)據(jù)如果比當前的第 5 名大，則插入進去并丟掉現(xiàn)在的第 5 名，如果比當前的第 5 名要小，則不做動作。這樣做，只要對 10 億條數(shù)據(jù)遍歷一次即可，而且內存占用很小，運算性能會大幅提升。這種算法本質上是把 TopN 也看作與求和、計數(shù)一樣的聚合運算了，只不過返回的是集合而不是單值。SQL 要是能寫成這樣：select top(x,5) from T 就能避免大排序了。然而非常遺憾，SQL 沒有顯式的集合數(shù)據(jù)類型，聚合函數(shù)只能返回單值，寫不出這種語句！不過好在全集的 TopN 比較簡單，雖然 SQL 寫成那樣，數(shù)據(jù)庫卻通常會在工程上做優(yōu)化，采用上述方法而避免大排序。所以 Oracle 算那條 SQL 并不慢。但是，如果 TopN 的情況復雜了，用到子查詢中或者和 JOIN 混到一起的時候，優(yōu)化引擎通常就不管用了。比如要在分組后計算每組的 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
這時候，數(shù)據(jù)庫的優(yōu)化引擎就暈了，不會再采用上面說的把 TopN 理解成聚合運算的辦法。只能去做排序了，結果運算速度陡降！select y,top(x,5) from T group by y
把 top 看成和 sum 一樣的聚合函數(shù)，這不僅更易讀，而且也很容易高速運算。關聯(lián)計算也是很常見的情況。以訂單和多個表關聯(lián)后做過濾計算為例，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 ...
訂單表有幾千萬數(shù)據(jù)，城市、運貨商、雇員、供應商等表數(shù)據(jù)量都不大。過濾條件字段可能會來自于這些表，而且是前端傳參數(shù)到后臺的，會動態(tài)變化。
SQL 一般采用 HASH JOIN 算法實現(xiàn)這些關聯(lián)，要計算 HASH 值并做比較。每次只能解析一個 JOIN，有 N 個 JOIN 要執(zhí)行 N 遍動作，每次關聯(lián)后都需要保持中間結果供下一輪使用，計算過程復雜，數(shù)據(jù)也會被遍歷多次，計算性能不好。通常，這些關聯(lián)的代碼表都很小，可以先讀入內存。如果將訂單表中的各個關聯(lián)字段預先做序號化處理，比如將雇員編號字段值轉換為對應雇員表記錄的序號。那么計算時，就可以用雇員編號字段值（也就是雇員表序號），直接取內存中雇員表對應位置的記錄，性能比 HASH JOIN 快很多，而且只需將訂單表遍歷一次即可，速度提升會非常明顯！select o.oid,o.orderdate,o.amount from orders o left join city c on o.cid = c.# -- 訂單表的城市編號通過序號 #關聯(lián)城市表 left join shipper sh on o.shid=sh.# -- 訂單表運貨商號通過序號 #關聯(lián)運貨商表 left join employee e on o.eid=e.# -- 訂單表的雇員編號通過序號 #關聯(lián)雇員表 left join supplier su on o.suid=su.# -- 訂單表供應商號通過序號 #關聯(lián)供應商表 where ci.state='New York' and e.title = 'manager' and ...
可惜的是，SQL 使用了無序集合概念，即使這些編號已經序號化了，數(shù)據(jù)庫也無法利用這個特點，不能在對應的關聯(lián)表這些無序集合上使用序號快速定位的機制，只能使用索引查找，而且數(shù)據(jù)庫并不知道編號被序號化了，仍然會去計算 HASH 值和比對，性能還是很差！select id,amt,tdate,… from T where id='10100' and tdate>= to_date('2021-01-10', 'yyyy-MM-dd') and tdate and …
在 T 表的幾億條歷史數(shù)據(jù)中，快速找到某個帳戶的幾條到幾千條明細，SQL 寫出來并不復雜，難點是大并發(fā)時響應速度要達到秒級甚至更快。為了提高查詢響應速度，一般都會對 T 表的 id 字段建索引：create index index_T_1 on T(id)
在數(shù)據(jù)庫中，用索引查找單個帳戶的速度很快，但并發(fā)很多時就會明顯變慢。原因還是上面提到的 SQL 無序理論基礎，總數(shù)據(jù)量很大，無法全讀入內存，而數(shù)據(jù)庫不能保證同一帳戶的數(shù)據(jù)在物理上是連續(xù)存放的。硬盤有最小讀取單位，在讀不連續(xù)數(shù)據(jù)時，會取出很多無關內容，查詢就會變慢。高并發(fā)訪問的每個查詢都慢一點，總體性能就會很差了。在非常重視體驗的當下，誰敢讓用戶等待十秒以上？！容易想到的辦法是，把幾億數(shù)據(jù)預先按照帳戶排序，保證同一帳戶的數(shù)據(jù)連續(xù)存儲，查詢時從硬盤上讀出的數(shù)據(jù)塊幾乎都是目標值，性能就會得到大幅提升。但是，采用 SQL 體系的關系數(shù)據(jù)庫并沒有這個意識，不會強制保證數(shù)據(jù)存儲的物理次序！這個問題不是 SQL 語法造成的，但也和 SQL 的理論基礎相關，在關系數(shù)據(jù)庫中還是沒法實現(xiàn)這些算法。不能再用 SQL 和關系數(shù)據(jù)庫了，要使用別的計算引擎。開源的集算器 SPL 基于創(chuàng)新的理論基礎，支持更多的數(shù)據(jù)類型和運算，能夠描述上述場景中的新算法。用簡單便捷的 SPL 寫代碼，在短時間內能大幅提高計算性能！微信搜索readdot，關注后回復視頻教程獲取23種精品資料
| | |
| A1=file("T.ctx").open().cursor(a,b,c,d,x,y,z) |
| | =A2.select(…).groups(a,b;sum(x)) |
|
| |
| | =A4.select(…).groups(c,d;max(y)) |
|
| |
| | =A6.select(…).groupx(a,c;avg(y),min(z)) |
|
| |
| | |
| |
| |
| =A1.cursor@m(x).total(top(-5,x), ? top(5,x)) |
| // top(-5,x)計算出 x 最大的前 5 名，top(5,x) 是 x 最小的前 5 名。 |
| |
| |
| =A1.cursor@m(x,y).groups(y;top(-5,x), ? top(5,x)) |
| |
| >env(city,file("city.btx").import@b()),env(employee,file("employee.btx").import@b()),... |
| |
| |
| =file("orders.ctx").open().cursor(cid,eid,…).switch(cid,city:#;eid,employee:#;…) |
| =A1.select(cid.state='New ? York' && eid.title=="manager"…) |
| //先序號關聯(lián)，再引用關聯(lián)表字段寫過濾條件 |
| | |
| =file("T-original.ctx").open().cursor(id,tdate,amt,…) |
| | |
| =B2.create@r(#id,tdate,amt,…).append@i(A2) |
| =B2.open().index(index_id;id) |
|
| //將原數(shù)據(jù)排序后，另存為新表，并為帳號建立索引 |
| | |
| =T.icursor(;id==10100 ? && tdate>=date("2021-01-10") && tdate |
| |
除了這些簡單例子，SPL 還能實現(xiàn)更多高性能算法，比如有序歸并實現(xiàn)訂單和明細之間的關聯(lián)、預關聯(lián)技術實現(xiàn)多維分析中的多層維表關聯(lián)、位存儲技術實現(xiàn)上千個標簽統(tǒng)計、布爾集合技術實現(xiàn)多個枚舉值過濾條件的查詢提速、時序分組技術實現(xiàn)復雜的漏斗分析等等。正在為 SQL 性能優(yōu)化頭疼的小伙伴們，來和我們一起探討吧：逆鋒起筆專注于程序員圈子，你不但可以學習到java、python等主流技術干貨和N多個源碼分享，還可以第一時間獲悉最新技術動態(tài)、內測資格、BAT大佬的經驗、精品視頻教程、副業(yè)賺錢經驗，微信搜索readdot關注！
MySQL 8.0 可以操作 JSON 了?。?/span>
SQL 中如何給指定數(shù)據(jù)庫創(chuàng)建只讀用戶？
HarmonyOS 數(shù)據(jù)庫系列之對象關系映射數(shù)據(jù)庫
MySQL 大批量插入，如何過濾掉重復數(shù)據(jù)？
一文理解 MySQL 中的 page 頁
