原文鏈接：深入解讀SQL優化中的執行計劃 ()
在了解執行計劃之前，需要先知道執行計劃由來。TDSQL 版任何查詢都會經過語法和語義解析，生成查詢表達式樹，也就是常用查詢數，解析器會去解析語法，分析器會把語法對應對象進行展開，通過重寫器對規則進行重寫，最后生成查詢數。
根據查詢樹執行器經過查詢再進行預處理，找出最小代價路徑，最終創建出計劃樹。再把查詢計劃交由執行器進行執行。最終執行完成會把結果返回給前端應用。這些操作都是在每個連接對應進程去進行處理。執行器在執行時，會去訪問共享內存，內存沒有數據，則從磁盤讀取。最終將查詢的結果緩存在數據庫中，逐步輸出給用戶進程。
進程會涉及到例如Work 、temp 等進程級內存，可以通過我們的命令來查看執行計劃，對不合理的資源進行調整，提高SQL執行效率。在SQL前面加上，就可以直接看到執行計劃。不管是在還是其它工具都可以簡單進行查看。
我們的執行計劃有幾個特點：首先查詢規劃是以規劃為節點的樹形結構，以查詢的一些路徑作為樹形結構，樹最底層節點是掃描節點，去掃描表中原始行數。不同表也有不同掃描類型，比如順序掃描或索引掃描、位圖索引掃描。也有非表列源，比如說子句。還有查詢，可能需要關聯、聚合、排序以便操作，同時也會在掃描節點上增加節點進行操作提示以及消耗。輸出總是以每個樹節點顯示一行，內容是基本節點類型和執行節點的消耗評估。可能會出現同級別節點，從匯總行節點縮進顯示其它屬性。第一行一般都是我們匯總的消耗，這個值是越小越好。
在看一個執行計劃，我們創建一個測試表，插入1萬條數據做分析后，可以看到它的執行計劃，這個執行計劃很簡單，全面掃描它只有一行。執行計劃我們從左到右去看，先是評估開始的消耗，這里因為沒有別的步驟，所以這個步驟是從0開始，然后是一個總消耗評估。
Rows是輸出的行數，它是一個評估結果；然后是每一行的平均字節數，這是一個評估結果，這個評估結果依賴于和統計信息。
那么我們怎么去看執行計劃呢？就是上級節點的消耗，其中包含了其子節點的消耗，這個消耗值反映在規劃器評估這個操作需要的代價。一般這個消耗不包括將數據傳輸到客戶端，只是在數據庫后臺的執行代價。評估的行數不是執行和掃描節點查詢的節點數量，而是返回的數量。同時消耗它不是一個秒的，它是我們規劃器的一個參數。Cost是描述一個執行計劃代價是多少，而不是具體時間。
代價評估的一些基準值一般會關注哪幾個參數？，即掃描一個塊需要的消耗，我們默認為是1，而隨機掃描我們默認為是4，這個在優化的環節需要進行優化，比如說現在使用SSD，隨機頁的訪問效率肯定比其它的磁盤更快，而這里值就可以改為1。另外就是，我們CPU去掃描一個塊里具體行數，一行大概0.01的消耗。索引是，0.005的消耗。
舉個例子，新建Test表有一萬行，它分配了94個頁。而根據剛剛執行計劃可以大概估算消耗：磁盤頁乘順序掃描的Cost，加上掃描行數。這個值就是94個頁乘以1，加上1萬行乘以0.01的消耗就是194。
那什么時候去更新以及的統計信息？它分為兩個部分，一部分主要還是通過以及部分DDL語句去觸發更新統計信息。所以執行計劃準確與否和統計信息也很有關聯。這里加上條件，比如說Where Id小于1000，會去增加一個篩選條件。這樣掃描的同時它會去增加損耗，比如掃描的行數不變，但是增加了CPU的計算比較時間，就變為219。
執行計劃最底層是表的掃描，而掃描又分為兩種方式，全表掃描以及索引掃描。全表掃描顧名思義去整個表上掃描。就算是有些表加了索引，它也不一定會走索引掃描，如果說滿足條件的數據集比較大，索引掃描代價比全表掃描更大，它就會走全表掃描。如前面所說，掃描全表，這個時候重新掃描，會先走索引，再走對應的塊，這個代價會比走全表掃描更慢。
另一個問題是索引掃描Index Scan。在上面的測試表對查詢列建一個索引，舉例查詢條件是小于1000這個值，cost減少還不夠直觀，如果條件是小于10之類小數據量查詢，索引效果更好，直接走Index Scan。但如果查詢條件篩選率不夠高，查詢會先走索引掃描，再重新掃描行，掃描后他會去判斷每一個行的條件，Cost可能相應就變更高。在優化的時候，尤其要去關注這一點，一定要關注索引的篩選率。
索引掃描里還有一個Index Only Scan，也就是投影列、查詢條件都在索引里面，它就會走一個Index Only Scan，不會再去讀其它具體的行值，掃描完索引之后就返回，效率非常高。
還有一種掃描方式是位圖掃描，在PG里沒有位圖索引，但是它是有位圖掃描的，一般是在on、and或in子句里面去走。舉個例子，上面查詢ID小于1000，同時ID要大于9000，這時候它會先做兩次索引掃描。掃描時它不會去讀具體數據，會先去做一個 Scan，之后我們的條件是Or，會先做一個聚集后再去做Check，看一下具體實現方式。它是先去啟動時間兩個 Scan總和，因為是具體掃描會有掃描時間，所以這個組合會花費大量時間。同時Index Scan輸出的是Tuple，先掃描索引塊，得到對應ctid再去掃描具體數據。如果一次只讀一條索引項然后去判斷行是否滿足條件，一個PAGE可以多次訪問。
而 Scan會去輸出所有滿足條件的索引項，然后組合到一起做or等操作，最后才交給上一個節點 Heap Scan去掃描具體數據，由于會先去根據索引掃描的物理數據進行排序，一次性將塊中滿足條件索引項數據取出來。這樣可以說一個塊，一次掃描就掃描完了，可以想象這個效率是非常高的。
在底層的數據掃描完之后會去做表連接。連接方式一般在兩表關聯的時候才有連接可能。一般簡單說自然選擇、左連接、右連接等等。但具體的到數據庫的執行計劃里一般主要有hash join、 loop、merge join。
Hash Join，它是以Hash方式來進行表連接，首先它確定是兩個表里的大小，使用小表去建立Hash map，去掃描大表比較Hash值獲取最終查詢結果。我們示例中建立另外一張表Test1，并建一個索引進行兩張表關聯查詢，當他們的T1的ID小于10，它Info相等，做一個關聯查詢。首先開始的時候，因為兩個表大小一樣，一張有索引，一張沒有，會優先選擇有索引的表去做一個Hash桶，另外一張表進行一個循環比較Hash值。如果說變一下條件把Test1表刪除一部分數據，優化器會以Test1去做一個Hash表，Test表在上面去做驅動。
做一個簡單梳理。Hash連接是在做大數據連接時非常有用的方式，就是在兩個大表進行join。那么這里也是為什么PG在和MySQL比的時候，說它的分析能力要強一點的原因，因為我們的Hash join支持非常好。另外現在MySQL已經支持Hash了，但是還不是那么完善。
Hash它有個問題，如果Hash的小表也比較大，Hash表的結果非常大，你的內存放不下，這時就可能會寫到你的磁盤中去，就會導致性能急劇下降。在這個時候就要提高。hash join的時間消耗是什么？我們的外層Cost請求，加上內層一個請求就可以了。
另外一個連接方式就是 Loop循環掃描，在這個掃描上寫了兩個循環去掃描。一般在優化的時候，特別是用PG數據庫，要去重點看 Loop是不是合理。那么什么時候用 Loop呢？就是小表和大表進行關聯的時候，小表作為驅動表，那大表作為下面的內層表會比較合理。
首先它會確定一個驅動表，另外是一個內層的表，驅動表每一行與它里面那張表進行一個查詢，一個嵌套循環查詢比較，代價非常高。就比如每次都是外層的表，乘以外面的條件消耗，這一看就比較大了。
像這種情況，每次掃描時，外層的表每次在驅動時它會去掃描層內層的表，這樣效率非常低。而如果內層的表它結果集是相對固定的，那么就可以掃描一次把它做一個物化，下次再循環比較的時就不用再去查詢里面的表，類似于Hash join。Hash join是做什么的呢？它前面也是一個Loop，只是把內存的表建立一個Hash表，這樣去掃描就會快很多。就是這么一個優化的方向，這個也依賴于我們的。
最后一種連接方式叫Merge join，主要針對于數據量不是特別大的情況下，而且兩個表如果結構相似，做好排序，這時反而會比散列連接會好一點。示例中原來是走了一個 Loop，我們把索引刪除，它就去走了Merge join。一般對于這種數值比較效率還可以，因為排序數值效率是高一點。如果是字符串一類，走Merge join效率會更低。
看一下具體的實現，它是先將兩個表進行一個排序。Id 1等于1先比較完后，再去比較Id等于2時，就不會再去比較Id1等于1的位置塊，會直接從另外一張表的2開始去比較。
做一個簡單比較，Hash join是將一個小表做為一個內存表做Hash運算，將列數據根據hash值放到Hash行列表中，再從另外一張表去抽取記錄做Hash運算找到匹配的值，一般是小表做Hash表。
Loop是一張表讀取數據，訪問另外一張表做匹配。 Loop在關聯表比較小的時候效率最高。小表做驅動，比如這個表只有百來行，而大表很大，循環100次查詢mysql數據庫執行計劃，大表會進行索引掃描，相對會快很多。
Merge join如果數據做好了排序，而且是數字類型排序，Merge join可能反而比Hash要快。但一般來說如果數據量比較大，Hash基本會比Merge join更快。
另外是關聯相關參數一般以開頭。剛剛那幾種連接 Loop、Merge join、Hash join、 Scan都是可以去控制的，參數可以是級別控制。
查看執行計劃首先是看掃描方式和連接方式，不論再怎么復雜，都是通過這兩個進行組合。一般是看它在掃描和關聯是不是合理的。這兩個判斷之后，再去看它的條件是不是合理，或需不需要改寫。有了執行計劃之外，在看具體執行時間，就要加上 來看具體執行時間。這里有一個不一樣的點，在這里有了一個實際執行時間，這個時間是真實時間。可以很精確知道每一步花費時間。
在之外，還有一些其它參數，可以通過\H 的方式去查看詳細的語法，有顯示具體執行日志，還有Cost消耗、顯示特殊設置，內存的一些分配情況。wal、時間，，輸出的格式TXT或者xml、json。如果加上，它的顯示信息會多很多。主要是比較有用，顯示說你申請了多少，現在多少磁盤塊是要命中，多少是進行讀取的。在第二次查詢的時候，它的磁盤讀取會變少，第一次讀取是94塊，第二次50塊塊。
除了上述內容，還有一個日志參數。我們的可以輸出你的執行計劃到日志文件中，的執行計劃是從表里去看，而我們PG是沒有的。那么怎么辦？可以通過一些參數去控制，導到日志里來。就目前這個日志它是輸入到運行日志里的，沒有單獨去進行記錄。當然這個也是我們優化的一個方向。
通過設置這些參數，把這里日志打印出來，顯示出執行計劃，語法分析、語義分析、重寫，這幾個階段它會顯示出來。如果開啟了執行計劃狀態，會把這些進行打印。
最后看執行計劃之外，從執行計劃去反推SQL優化方向。從最底層一個掃描去入手，要盡量走索引掃描。另外索引掃描這里有很多方式，就是看它是否是合理索引，要看類型是不是選擇合理的。比如數字類型、字符串類型，我們選用gin索引，還是一些btree索引。PG默認是btree索引，但btree索引不是所有類型和操作符都會適用。另外還需要減少不必要的索引、避免單條SQL插入，要單條變為批量進行插入。
前面說執行計劃表連接類型是不是正確合理，另外要從SQL本身進行入手，我們目的是為了減少它的消耗。如果SQL語句比較復雜，而掃描類型已經無法改動，那這時只能去改寫SQL語句，盡量減少嵌套，減少子查詢。還可以通過物化視圖臨時表，去做SQL拆分。
盡量把in語法用Exits方式做連接。另外還要注意一些類型的轉換失真，在掃描時，如果它可以走索引掃描，結果走了全面掃描，可能是轉換失真了mysql數據庫執行計劃，比如說一個in類型，結果輸入是一個字符串類型，它有可能會轉換失敗，只能走全面掃描，不能索引。
另外從數據庫參數來入手，就需要精確的統計信息，我們在生成執行計劃時，可能沒有去執行，也可能統計信息落后，那么執行計劃就是錯誤的。這時候就要對應表作為一個。
最后就是干涉執行計劃，干涉執行計劃有兩種方式，除了前面的幾個參數，我們的插件也可以做一些Hint控制。還有一些新參數調整，例如調整、、等參數。還有一些連接池的使用，我們操作系統參數、硬件的性能參數調整等等。
其實往往數據庫優化，除了這些以外，還有我們去看操作系統的一些硬件性能，比如CPU是不是模式，磁盤調度方式是不是最優的，網卡Bond模式等其他參數。
