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