大資料計算,如何優化SQL?

語言: CN / TW / HK

theme: awesome-green

持續創作,加速成長!這是我參與「掘金日新計劃 · 10 月更文挑戰」的第1天,點選檢視活動詳情

📢📢📢📣📣📣 哈嘍!大家好,我是【IT邦德】,江湖人稱jeames007,10年DBA工作經驗 一位上進心十足的【大資料領域博主】!😜😜😜 中國DBA聯盟(ACDU)成員,目前從事DBA及程式程式設計 擅長主流資料Oracle、MySQL、PG 運維開發,備份恢復,安裝遷移,效能優化、故障應急處理等。 ✨ 如果有對【資料庫】感興趣的【小可愛】,歡迎關注【IT邦德】💞💞💞 ❤️❤️❤️感謝各位大可愛小可愛!❤️❤️❤️

前言

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


很多大資料計算都是用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 …;

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

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

這種分組運算中,相對於遍歷硬碟的時間,CPU計算時間幾乎可以忽略。如果可以在一次遍歷中把多種分組彙總都計算出來,雖然CPU計算量並沒有變少,但能大幅減少硬碟讀取資料量,就能成倍提速了。

如果SQL支援類似這樣的語法:

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 …; --遍歷中的第三種分組

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

可惜, SQL沒有這種語法,寫不出這樣的語句,只能用個變通的辦法,就是用group a,b,c,d的寫法先算出更細緻的分組結果集,但要先存成一個臨時表,才能進一步用SQL計算出目標結果。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 …;

這樣只要遍歷一次了,但要把不同的WHERE條件轉到前面的case when裡,程式碼複雜很多,也會加大計算量。而且,計算臨時表時分組欄位的個數變得很多,結果集就有可能很大,最後還對這個臨時表做多次遍歷,計算效能也快不了。大結果集分組計算還要硬碟快取,本身效能也很差。

還可以用儲存過程的資料庫遊標把資料一條一條fetch出來計算,但這要全自己實現一遍WHERE和GROUP的動作了,寫起來太繁瑣不說,資料庫遊標遍歷資料的效能只會更差!

只能乾瞪眼!

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

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

表T有10億條資料,從SQL語句來看,是將全部資料大排序後取出前5名,剩下的排序結果就沒用了!大排序成本很高,資料量很大記憶體裝不下,會出現多次硬碟資料倒換,計算效能會非常差!

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

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

sql select top(x,5) from T

然而非常遺憾,SQL沒有顯式的集合資料型別,聚合函式只能返回單值,寫不出這種語句!

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

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

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

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

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

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

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

可惜,不行。

還是乾瞪眼!

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

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 ...

訂單表有幾千萬資料,城市、運貨商、僱員、供應商等表資料量都不大。過濾條件欄位可能會來自於這些表,而且是前端傳引數到後臺的,會動態變化。

SQL一般採用HASH JOIN演算法實現這些關聯,要計算 HASH 值並做比較。每次只能解析一個JOIN,有N個JOIN要執行N遍動作,每次關聯後都需要保持中間結果供下一輪使用,計算過程複雜,資料也會被遍歷多次,計算效能不好。

通常,這些關聯的程式碼表都很小,可以先讀入記憶體。如果將訂單表中的各個關聯欄位預先做序號化處理,比如將僱員編號欄位值轉換為對應僱員表記錄的序號。那麼計算時,就可以用僱員編號欄位值(也就是僱員表序號),直接取記憶體中僱員表對應位置的記錄,效能比HASH JOIN快很多,而且只需將訂單表遍歷一次即可,速度提升會非常明顯!

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

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 ...

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

有好辦法也實施不了,只能再次乾瞪眼!

還有高併發帳戶查詢,這個運算倒是很簡單:

sql select id,amt,tdate,… from T where id='10100' and tdate>= to\_date('2021-01-10','yyyy-MM-dd') and tdate<to_date('2021-01-25','yyyy-mm-dd') and="" …="" <p="">

在T表的幾億條歷史資料中,快速找到某個帳戶的幾條到幾千條明細,SQL寫出來並不複雜,難點是大併發時響應速度要達到秒級甚至更快。為了提高查詢響應速度,一般都會對 T 表的 id 欄位建索引:

sql create index index_T_1 on T(id)

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

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

但是,採用SQL體系的關係資料庫並沒有這個意識,不會強制保證資料儲存的物理次序!這個問題不是SQL語法造成的,但也和SQL的理論基礎相關,在關係資料庫中還是沒法實現這些演算法。

那咋辦?只能乾瞪眼嗎?

不能再用SQL和關係資料庫了,要使用別的計算引擎。

開源的集算器SPL基於創新的理論基礎,支援更多的資料型別和運算,能夠描述上述場景中的新演算法。用簡單便捷的SPL寫程式碼,在短時間內能大幅提高計算效能!

上面這些問題用SPL寫出來的程式碼樣例如下:

  • 一次遍歷計算多種分組

| | A | B | | ---- | ------------------------------------------ | ---------------------------------------- | | 1 | =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 | … | //定義結束,開始計算三種方式的過濾、分組 |

  • 用聚合的方式計算Top5

全集Top5(多執行緒平行計算)

| | A | | ---- | ------------------------------------------------------------ | | 1 | =file("T.ctx").open() | | 2 | [email protected](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 | [email protected](x,y).groups(y;top(-5,x),top(5,x)) |

  • 用序號做關聯的SPL程式碼:

系統初始化

| | A | | ---- | ------------------------------------------------------------ | | 1 | >env(city,file("city.btx")[email protected]()),env(employee,file("employee.btx")[email protected]()),... | | 2 | //系統初始化時,幾個小表讀入記憶體 |

查詢

| | 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 | //先序號關聯,再引用關聯表字段寫過濾條件 |

  • 高併發帳戶查詢的SPL程式碼:

資料預處理,有序儲存

| | A | B | | ---- | ----------------------------------------------------- | -------------- | | 1 | =file("T-original.ctx").open().cursor(id,tdate,amt,…) | | | 2 | =A1.sortx(id) | =file("T.ctx") | | 3 | [email protected](#id,tdate,amt,…)[email protected](A2) | | | 4 | =B2.open().index(index_id;id) | | | 5 | //將原資料排序後,另存為新表,併為帳號建立索引 | |

帳戶查詢

| | A | | ---- | ------------------------------------------------------------ | | 1 | =T.icursor(;id==10100 && tdate>=date("2021-01-10") && tdate<date("2021-01-25") && …,index_id).fetch() | | 2 | //查詢程式碼非常簡單 |

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

正在為SQL效能優化頭疼的小夥伴們,可以和我們一起探討:

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

SPL資料

在這裡插入圖片描述