HASH JOIN是大資料量表連線中最為常用的方式，與最為常用的NESTED LOOP相比，其應用場景不同。對於兩張表的連線，NESTED LOOP適合於類似查電話號碼的應用場景，如果領導給你一張清單，讓你去查一下幾家企業的電話號碼，那麼你要做的是找到一本電話號碼本，根據公司名稱的索引，挨個查一遍，很快就可以完成了。這種方式就是著名的NESTED LOOP，透過數個快速的迴圈，完成兩個行源的關聯操作（待查清單，電話號碼簿）。

如果這個任務改一下，領導給你的清單上有幾萬家企業，那麼我們還這麼一條條的去查，那不傻了。這時候，就不適合用NESTED LOOP迴圈了，HASH JOIN是比較快速的解決方法。很多SQL的執行計劃出現錯誤，有很大一部分就是選擇錯誤使用了NESTED LOOP和HASH JOIN。因此現在一些CBO的最佳化器中，都有針對NESTED LOOP和HASH JOIN的主動糾偏技術。Oracle 19C的可調節執行計劃主要就是在執行NESTED LOOP的過程中一旦發現迴圈數量超出評估預期，則可以動態改為HASH JOIN。

剛開始就有點扯遠了，今天我們的重點不是討論NL和HASH JOIN的差異，而是帶大家看看PG資料庫的HASH JOIN執行計劃中的一些容易被忽略的點，在檢視執行計劃的時候，如果能夠比較好的抓住這些關注點，對於SQL最佳化來說很有幫助。

可能有朋友要說了，反正都是HASH JOIN,執行計劃都差不多，有啥可看的。那麼我們來看看上面的執行計劃裡的紅框裡的內容吧，Batches :32,這個是啥意思？如果你以前是Oracle DBA，那麼最佳化排序、one-pass 排序，multi-pass排序的概念應該還有印象吧。當需要做排序或者HASH TABLE的資料量太大，超出了SORT AREA SIZE的限制，那麼這次排序/HASH join就無法一次完成，必須切分為多個分割槽，一個個的完成。在PG的HASH JOIN裡，就是把HASH JOIN切分為多個BATCHES。因為某個BATCH完成後需要暫存在臨時檔案中，因此遇到這種情況我們一般都可以看到temp written這個內容，這部分內容我也用紅框標註出來了。

這種排序區不足導致的問題會帶來什麼樣的效能問題呢？我們來看這個例子，BATCHES：1,也就是無需透過分割槽完成，此時使用了4540KB的WORK_MEM。實際上我給大家演示這個案例的時候，第一個例子用了256KB的work_mem設定，當然無法滿足4M多的記憶體需求了。而第二個例子我使用了一個極大的work_mem(256MB),當然實際上的記憶體使用以執行計劃中的為準。一次性在記憶體中完成HASH JOIN的好處是什麼呢?當然是執行效率，我們可以看出第二個執行只用了90毫秒，而分裂為32個BATCH的執行花了239毫秒。

看到這裡可能有朋友要說了，既然效果那麼好，那麼我們把WORK_MEM引數設的足夠大不就行了。實際上設定過大的WORK_MEM也是存在隱患的。如果我們的實體記憶體不是很大，那麼設定過大的WORK_MEM可能導致極端情況下，實體記憶體過度消耗而導致更嚴重的問題。

WORK_MEM引數是可會話級動態設定的，如果我們的某些要做大型排序或者HASH JOIN的SQL能夠在應用層面做設定，執行大型SQL的時候設定一個較大的值，SQL執行完畢RESET一下引數，這樣WORK_MEM的使用效率是最高的。否則我們為了滿足大型SQL的需求，就需要設定一個做大值。當然雖然我們設定了WORK_MEM並不一定就會消耗那麼多的記憶體，不過活躍會話數*WORK_MEM這個數字還是需要關注的，確保我們的實體記憶體有那麼多的空閒可用（參考可用記憶體，而不是FREE記憶體）是十分必要的。如果我們不確定系統最大的記憶體使用量，並且實體記憶體比較緊張，那麼設定大一點的SWAP是十分必要的，在極端情況下可以確保系統不會因為OOM而出大問題。

上面的這個執行計劃也是我們經常看到的，PG資料庫支援並行HASH JOIN，並且預設是開啟的。如果我們的系統中的CPU資源是充足的，那麼enable_parallel_hash引數確保開啟狀態就行了。並行HASH JOIN可以透過過parallel seq scan和parallel hash join兩種機制來進一步提高HASH JOIN的效能。我們可以看到，透過併發，這個SQL的執行效率進一步的提升了。

不過任何事情都是有利有弊，如果你的伺服器的CPU資源十分緊張，那麼過多的並行HASH JOIN可能會導致你的CPU資源經常出現不足，引發其他問題。如果存在這種情況，那麼關閉並行HASH JOIN，讓每個HASH JOIN變得略微慢一點，但是確保CPU資源不過載，也是一種策略。

從Hash Join的執行計劃的細節中能看到點啥

相關文章