SQL語義轉換、記憶體帶寬、JOIN算法、FILTER親和力、TSP、HINT、命中率、存儲順序、掃描順序、索引深度

PostgreSQL , 記憶體帶寬 , JOIN算法 , FILTER親和力 , TSP , HINT , 索引掃描順序與命中率 , 語義轉換 , 掃描順序 , 存儲順序 , 命中率 , 索引深度 , partial index

一個OUTER JOIN的SQL優化，引出了一系列的知識點，非常值得深入探讨。

記憶體帶寬 , JOIN算法 , FILTER親和力 , TSP , HINT , 索引掃描順序與命中率 , 語義轉換 , 掃描順序 , 存儲順序 , 命中率 , 索引深度 , partial index 。

這條SQL雖然是OUTER JOIN，但是Join條件隻有一個等值條件，而其他條件都在WHERE中，WHERE條件中同時還包含了可空表的非空查詢條件，是以我們可以認為它的語義與INNER JOIN一緻。

單次查詢響應速度約7毫秒。

掃描了10217個資料塊，這個也是後面重點優化的地方。

測試環境是60核的機器，按單次查詢時間，理論上性能應該可以達到60*1000/7.166=8372的TPS。但是，實際上真實的壓測，并發查詢也隻能達到300多的TPS。

原因是什麼？

實際上我們通過命中率，我們大概可以推算出來，356的TPS時，占用的記憶體帶寬約 29 GB/s。加上其他的損耗，基本上達到了記憶體帶寬的瓶頸。

因為記憶體讀取占用了大量的時間，是以降低CACHE讀取，是重點需要優化的。

PostgreSQL支援三種JOIN算法，嵌套循環、MERGE SORT、哈希JOIN。

詳見：

<a href="https://github.com/digoal/blog/blob/master/201205/20120521_02.md">《PostgreSQL nestloop/hash/merge join講解》</a>

PostgreSQL HINT可以指定執行計劃。

詳見:

<a href="https://github.com/digoal/blog/blob/master/201607/20160723_02.md">《關鍵時刻HINT出彩 - PG優化器的參數優化、執行計劃固化CASE》</a>

比如

在例子中，我們可以看到這樣的執行計劃，實際上在JOIN時，使用了索引，同時使用了索引過濾，而非查詢過濾。

索引過濾的好處是不需要額外的CPU運算。

那麼JOIN時，怎麼樣的條件能過濾呢？

對于NESTLOOP JOIN：

對于外表，除了JOIN都可以作為索引過濾條件。因為外表是需要周遊的，周遊時不知道它的JOIN KEY的VALUE到底是多少，是以作為複合索引沒有必要把外表的JOIN字段加進來。

作為内表，所有條件都可以作為索引過濾條件。包括JOIN字段，因為内表不是周遊的，而是通過外表傳入的JOIN值來查詢的，是以複合索引可以加速JOIN字段。

那麼應該如何建立索引呢？

外表：WHERE條件中除了JOIN字段的其他字段，建構BTREE複合索引，等值條件作為驅動列，如果有多個等值條件，那麼将稀疏（選擇性不好的）列排在前面。

内表，WHERE條件中，所有字段建構BTREE複合索引，等值條件作為驅動列，如果有多個等值條件，那麼将稀疏（選擇性不好的）列排在前面。

在前面的執行計劃中，雖然是limit 10 offset 20，最多實際上需要掃描30條外表即可，但是實際上，從LOOP值來看，外表掃描了2513次。

這也是前面提到的問題所在，LOOP 2513次，直接帶來了10095個資料塊的通路。

實際上每次通路了4個資料塊

這平均4個資料塊是内表每一次LOOP時，通路每一條記錄的 "索引通路+HEAP通路 (有一些ID不存在是以不需要通路heap)" 命中到的BLOCK，實際上對于單次掃描來說已經很小了。

但是由于内表通路了2000多次，是以造成了IO放大。

那麼為什麼30次的LIMIT，内表确LOOP了2513次？

這說明外表提供的記錄，掃了2513條，在内表中隻有30條是滿足JOIN和WHERE條件的記錄，為了減少内表的掃描次數（最多降低到30次），我們可以通過調整外表的掃描順序來實作。

做法，隻要預先把滿足條件的資料，在外表重排一下即可。

當然這個做法不具備通用性，需要預先處理。我隻是借機講解背後的原理。

這樣做之後，隻需要掃描30次内表了。

内表掃描30次，掃描151個BLOCK。(每次通路5個PAGE，root+branch1+branch2+leaf+heap)

響應也從7毫秒，降低到了0.7毫秒。

重新壓測，性能達到 2萬tps。

性能：

接着第七個問題，内表每次通路，都是索引通路，那麼每次的索引通路需要通路多少個資料塊呢？和索引深度有關：

這裡索引的深度決定了要通路多少個索引頁，有命中時，則需要通路到heap page。

索引深度由索引頁内記錄數以及整個HEAP表被索引的記錄的記錄數決定。

原理詳見：

<a href="https://github.com/digoal/blog/blob/master/201605/20160528_01.md">《深入淺出PostgreSQL B-Tree索引結構》</a>

<a href="https://github.com/digoal/blog/blob/master/201706/20170627_01.md">《PostgreSQL 9種索引的原理和應用場景》</a>

<a href="https://github.com/digoal/blog/blob/master/201706/20170617_01.md">《自動選擇正确索引通路接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》</a>

partial index實際上是部分索引，可用來降低索引深度，例如1000萬條記錄，如果每個索引 BLOCK 可以存放260個ITEM (ctid即行号固定8位元組，加上字段本身内容的長度，占一個IDX ITEM的長度)，那麼需要3層。

3層樹剛好滿足1千多萬條記錄。

通過降低索引樹的層級，在大量LOOP時，可以減少掃描的BLOCK數量。怎麼降低層級呢？partial index是一種方法，另一種方法是減少索引字段個數。

原來是多字段複合索引，改成partial index

這樣的話被索引的記錄變少了，可以直接影響索引層級。

同時索引也從4個字段降到了1個字段，每個IDX ITEM也變短了，一個PAGE可以存儲更多的ITEM，是以索引層級再次壓縮。

性能再次提升：

因為記錄數降了，索引層級直接變2級，每次通路3個PAGE(root, leaf, heap)，循環30次僅僅通路了90個BLOCK，達到了極限。

本文通過一個OUTER JOIN的例子，展示了JOIN相關的知識點，涉及記憶體帶寬 , JOIN算法 , FILTER親和力 , TSP , HINT , 索引掃描順序與命中率 , 語義轉換 , 掃描順序 , 存儲順序 , 命中率 , 索引深度 , partial index 。

最後通過提高内表命中率，降低索引層級等方法，減少記憶體通路消耗，性能從300多TPS，提升到了2萬多TPS。

更智能的方法是關聯分區、TSP算法與資料分區的結合。解決JOIN過濾與親和, limit的問題。