SQL連線查詢優化[姊妹篇.第五彈]

桑小榆的堅定學說 發表於 2022-05-16
SQL

上篇的sql優化篇章,更多偏向於優化的思想概念,先前丟擲的4個優化問題中,篇幅過長,只對前兩個問題進行了解析。

 

接下來我們一起來談談sql的連線查詢優化,更偏向於實際運用,並對如下兩個問題進行探討。篇幅過長,請耐心看完。

 

1.巢狀查詢、HASH連線、排序合併連線、笛卡爾連線等怎樣玩能達到最優?

 

2. IN  EXISTS 誰快誰慢?

 

圖片

巢狀迴圈(NESTED LOOPS)

 

巢狀迴圈的演算法:驅動表返回一行資料,通過連線列傳值給被驅動表,驅動表返回多少行,被驅動表就要被掃描多少次。

 

這裡我補充一下驅動表和被驅動表:理解驅動表和被驅動表的本質,需要理解順序讀取和隨機讀取的差異,記憶體適合隨機讀取,硬碟則順序讀取的效率比較好。

 

驅動表,作為外層迴圈,若進行一次IO將所有資料讀取,則適合順序讀取,一次性批量的把資料讀取出來,不考慮快取情況下。

 

被驅動表,即裡層迴圈,由於需要不斷的拿外層迴圈傳進來的每條記錄去匹配,所以如果是適合隨機讀取的,那麼效率就會比較高。如果表上有索引,實際上就意味著這個表是適合隨機讀取的。

 

圖片

 

1.巢狀迴圈可以快速返回兩表關聯的前幾條資料,如果SQL中新增了HINT:FIRST_ROWS,在兩表關聯的時候,優化器更傾向於巢狀迴圈。

 

2.巢狀迴圈驅動表應該返回少量資料。如果驅動表返回了100萬行,那麼被驅動表 就會被掃描100萬次。這個時候SQL會執行很久,被驅動表會被誤認為熱點表,被驅動表連線列的索引也會被誤認為熱點索引。

 

3.巢狀迴圈被驅動表必須走索引。如果巢狀迴圈被驅動表的連線列沒包含在索引中,那麼被驅動表就只能走全表掃描,而且是反覆多次全表掃描。當被驅動表很大的 時候,SQL就執行不出結果。

 

4.巢狀迴圈被驅動表的連線列基數應該很高。如果被驅動表連線列的基數很低,那麼被驅動表就不應該走索引,這樣一來被驅動表就只能進行全表掃描了,但是被驅動表也不能走全表掃描。

 

 

5.兩表關聯返回少量資料才能走巢狀迴圈。前面提到,巢狀迴圈被驅動表必須走索引,如果兩表關聯,返回100萬行資料,那麼被驅動表走索引就會產生100萬次回表。回表一般是單塊讀,這個時候SQL效能極差,所以兩表關聯返回少量資料才能走巢狀 迴圈。

 

圖片

 

兩表關聯走不走NL(巢狀迴圈)是看兩個表關聯之後返回的資料量多少?還是看驅動表返回的資料量多少

 

如果兩個表是1∶N關係,驅動表為1,被驅動表為N並且N很大,這時即使驅動表返回資料量很少,也不能走巢狀迴圈,因為兩表關聯之後返回的資料量會很多。

 

所以判斷兩表關聯是否應該走NL應該直接檢視兩表關聯之後返回的資料量,如果 兩表關聯之後返回的資料量少,可以走NL;返回的資料量多,應該走HASH連線。 

 

SELECT * FROM t1,t2 WHERE t1.id = t2.id; 如果t1有200條資料,t2有200萬行資料,t1與t2是1∶N關係,N很低,應該怎麼優化SQL?

 

 

因為t1與t2是1∶N關係,N很低,我們可以在b的連線列(id)上建立索引,讓 t1與t2走巢狀迴圈(t1 nl t2),這樣t2表會被掃描100次,但是每次掃描表的時候走的 是id列的索引(範圍掃描)。

 

如果讓t1和t2進行HASH連線,t2表會被全表掃描(因為沒有過濾條件),需要查詢表中100萬行資料,而如果讓t1和t2進行巢狀迴圈,t2表只需要 查詢出表中最多幾百行資料(100*N)。

 

 

一般情況下,一個小表與一個大表關聯,我們可以考慮讓小表NL大表,大表走連線列索引(如果大表有過濾條件,需要將過濾條件與連線列組合起來建立組合索引),從而避免大表被全表掃描。

 

圖片

HASH連線(HASH JOIN )

 

HASH連線的演算法:兩表等值關聯,返回大量資料,將較小的表選為驅動表,將驅動表的“select列和join列”讀入PGA中的work area,然後對驅動表的連線列進行hash 運算生成hash table,當驅動表的所有資料完全讀入PGA中的work area之後,再讀取被驅動表(被驅動表不需要讀入PGA中的work area),對被驅動表的連線列也進行 hash運算,然後到PGA中的work area去探測hash table,找到資料就關聯上,沒找到 資料就沒關聯上。雜湊連線只支援等值連線。

 

圖片

 

如何優化HASH連線?

 

因為HASH連線需要將驅動表的select列和join列放入PGA中,所以,我們 應該儘量避免書寫select * from....語句,將需要的列放在select list中,這樣可以減少驅動表對PGA的佔用,避免驅動表被溢位到臨時表空間,從而提升查詢效能。

 

如果無法避免驅動表被溢位到臨時表空間,我們可以將臨時表空間建立在SSD上或者RAID 0上,加快臨時資料的交換速度。

 

當PGA採用自動管理,單個程式的work area被限制在1G以內,如果PGA採用手動管理,單個程式的work area不能超過2GB。如果驅動表比較大,比如驅動表有 4GB,可以開啟並行查詢至少parallel(4),將表拆分為至少4份,這樣每個並行程式中 的work area能夠容納1GB資料,從而避免驅動表被溢位到臨時表空間。

 

圖片

排序合併連線(SORT MERGE JOIN)

 

前文提到HASH連線主要用於處理兩表等值關聯返回大量資料。排序合併連線主要用於處理兩表非等值關聯,比如>,>=,<,<=,<>,但是不能用於instr、substr、like、regexp_like關聯,instr、substr、like、regexp_like關聯只能走巢狀迴圈。


排序合併連線的演算法:兩表關聯,先對兩個表根據連線列進行排序,將較小的表作為驅動表,然後從驅動表中取出連線列的值,到已經排好序的被驅動表中匹配資料,如果匹配上資料,就關聯成功。驅動表返回多少行,被驅動表就要被匹配多少次,這個匹配的過程類似巢狀迴圈,但是巢狀迴圈是從被驅動表的索引中匹配資料,而排序合併連線是在記憶體中(PGA中的work area)匹配資料。 

 

怎麼優化排序合併連線?

 

如果兩表關聯是等值關聯,走的是排序合併連線,我們可以將表連線方式改為HASH連線。如果兩表關聯是非等值關聯,比如>,>=,<,<=,<>,這時我們應該先從業務上入手,嘗試將非等值關聯改寫為等值關聯,因為非等值關聯返回的結果 集“類似”於笛卡兒積,當兩個表都比較大的時候,非等值關聯返回的資料量相當大。如果沒有辦法將非等值關聯改寫為等值關聯,我們可以考慮增加兩表的限制條件,將兩個表資料量縮小,最後可以考慮開啟並行查詢加快SQL執行速度。 

圖片

笛卡爾連線(CARTESIAN JOIN)

 

兩個表關聯沒有連線條件的時候會產生笛卡兒積,這種表連線方式就叫笛卡兒連線。在多表關聯的時候,兩個表沒有直接關聯條件,但是優化器錯誤地把某個表返回的Rows算為1行(注意必是1行),這個時候也可能發生笛卡兒連線。

圖片

標量子查詢(SCALAR SUBQURY)

 

當一個子查詢介於select與from之間,這種子查詢就叫標量子查詢。

 

標量子查詢類似一個天然的巢狀迴圈,而且驅動表固定為主表。大家是否還記得:巢狀迴圈被驅動表的連線列必須包含在索引中。同理,標量子查詢中子查詢的表連線列也必須包含在索引中。

 

我們建議在工作中,儘量避免使用標量子查詢,假如主表返回大量資料,主表的連線列基數很高,那麼子查詢中的表會被多次掃描,從而嚴重影響SQL效能。如果主表資料量小,或者主表的連線列基數很低,那麼這個時候我們也可以使用標量子查詢,但是記得要給子查詢中表的連線列建立索引。

 

當SQL裡面有標量子查詢,我們可以將標量子查詢等價改寫為外連線,從而使它 們可以進行HASH連線。

 

為什麼要將標量子查詢改寫為外連線而不是內連線呢?因為標量子查詢是一個傳值的過程,如果主表傳值給子查詢,子查詢沒有查詢到資料,這個時候會顯示NULL。如果將標量子查詢改寫為內連線,會丟失沒有關聯上的資料。

圖片

半連線與反連線
 

半連線:兩表關聯只返回一個表的資料就叫半連線。半連線一般就是指的in和exists。在 SQL優化實戰中,半連線的優化是最為複雜的。in和exists一般情況下都可以進行等價改寫。 

 

反連線:兩表關聯只返回主表的資料,而且只返回主表與子表沒關聯上的資料,這種連線就叫反連線。反連線一般就是指的not in和not exists。

 

圖片

 

需要注意的是,not in裡面如果有null,整個查詢會返回空,而in裡面有null,查詢不受null影響。所以在將not exists等價改寫為not in的時候,要注意null。一般情況下,如果反連線採用not in寫法,我們需要在where條件中剔除null。

 

FILTER:如果子查詢(in/exists/not in/not exists)沒能展開(unnest),在執行計劃中就會產生FILTER,FILTER類似巢狀迴圈,FILTER的演算法與標量子查詢一模一樣。

 

圖片

 

IN 與EXISTS 誰快誰慢?

 

如果執行計劃中沒有產生FILTER,那麼我們可以參考以下思路:in與exists是半連線,半連線也屬於表連線,那麼既然是表連線,我們需要關心兩表的大小以及兩表之間究竟走什麼連線方式,還要控制兩表的連線方式,才能隨心所欲優化SQL,而不是去記什麼時候in跑得快,什麼時候exists跑得快。

 

SQL 語句的本質:標量子查詢可以改寫為外連線(需要注意表與表之間關係,去重),半連線可以改寫為內連線(需要注意表與表之間關係,去重),反連線可以改寫為外連線(不需要注意表與表之間關係,也不需要去重)。

 

SQL語句中幾乎所有的子查詢都能改寫為表連線的方式,所以我們提出這個觀點:SQL語句其本質就是表連線(內連線與外連線),以及表與表之間是幾比幾 關係再加上GROPU BY。