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