SQL 建表查詢最佳化有哪些小技巧？

作者：由匿名使用者發表于書法時間：2021-09-13

石臻臻的雜貨鋪2022-03-21 11:03:03

很多大資料計算都是用 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 支援類似這樣的語法：

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 大致如下：

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 大致是這樣的：

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 要是能寫成這樣：select top（x，5） from T 就能避免大排序了。然而非常遺憾，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

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

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

select y，top（x，5） from T group by y

把 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 。。。

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

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

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

有好辦法也實施不了，只能再次乾瞪眼！還有高併發帳戶查詢，這個運算倒是很簡單：

select id，amt，tdate，… from T

where id=‘10100’

and tdate>= to_date（‘2021-01-10’， ‘yyyy-MM-dd’）

and tdate

and …

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

create index index_T_1 on T（id）

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

容易想到的辦法是，把幾億資料預先按照帳戶排序，保證同一帳戶的資料連續儲存，查詢時從硬碟上讀出的資料塊幾乎都是目標值，效能就會得到大幅提升。但是，採用 SQL 體系的關係資料庫並沒有這個意識，不會強制保證資料儲存的物理次序！這個問題不是 SQL 語法造成的，但也和 SQL 的理論基礎相關，在關係資料庫中還是沒法實現這些演算法。那咋辦？只能乾瞪眼嗎？不能再用 SQL 和關係資料庫了，要使用別的計算引擎。開源的集算器 SPL 基於創新的理論基礎，支援更多的資料型別和運算，能夠描述上述場景中的新演算法。用簡單便捷的 SPL 寫程式碼，在短時間內能大幅提高計算效能！上面這些問題用 SPL 寫出來的程式碼樣例如下：

一次遍歷計算多種分組