POSTGRESQL執行計劃詳解分析SQL的基礎（30頁）.pdf

《POSTGRESQL執行計劃詳解分析SQL的基礎（30頁）.pdf》由會員分享，可在線閱讀，更多相關《POSTGRESQL執行計劃詳解分析SQL的基礎（30頁）.pdf（30頁珍藏版）》請在三個皮匠報告上搜索。

1、POSTGRESQL執行計劃詳解分析SQL的基礎何敏，曾就職于成都文武、人大金倉，參與開發PG高可用系統、RDS、數據庫接口。精通數據庫遷移、高可用、系統方案設計，有豐富的開發和運維經驗。PG中文社區首屆 PG MVP中國首期PG ACE 伙伴中國POSTGRESQL分會官方認證講師盤古云課堂高級講師PostgreSQL在執行任何查詢時，都會先經過語法、語義解析，生成查詢表達式樹；然后根據規則系統對查詢樹進行轉換，視圖也在這里展開，最終生成邏輯查詢樹；最后根據系統統計信息、物理存儲的情況對查詢進行優化，生成物理查詢計劃；最后根據查詢計劃進行執行，最終得到結果。因此，選擇正確的查詢結構和數據屬性

2、的規劃，對執行效率至關重要。我們可以通過explain命令來查看執行計劃，進而對不合理的地方進行調整，提高SQL的執行效率。解析器分析器重寫器計劃器執行器語法解析：語法解析樹語義分析：查詢樹重寫：查詢樹執行計劃樹1.執行預處理2.找出代價最小的路徑3.創建計劃樹初初識識執執行行計計劃劃初初識識執執行行計計劃劃通過在SQL語句前面加上explain操作，就可以獲取到該SQL的執行計劃，該SQL并沒有實際執行。輸出的執行計劃有如下特點：1.查詢規劃是以規劃為節點的樹形結構。樹的最底節點是掃描節點：他返回表中的原數據行。2.不同的表有不同的掃描節點類型：順序掃描，索引掃描和位圖索引掃描。3.也有非表

3、列源，如VALUES子句并設置FROM返回，他們有自己的掃描類型。4.如果查詢需要關聯，聚合，排序或其他操作，會在掃描節點之上增加節點執行這些操作。5.EXPLAIN的輸出是每個樹節點顯示一行，內容是基本節點類型和執行節點的消耗評估?？赡軙霈F同級別的節點，從匯總行節點縮進顯示節點的其他屬性。第一行（最上節點的匯總行）是評估執行計劃的總消耗，這個值越小越好。初初識識執執行行計計劃劃因為這個查詢沒有WHERE子句，所以必須掃描表中的所有行，所以規劃器選擇使用簡單的順序掃描規劃。括號中的數字從左到右依次是：1.評估開始消耗：這是可以開始輸出前的時間，比如排序節點的排序的時間。2.評估總消耗：假設查

4、詢從執行到結束的時間。有時父節點可能停止這個過程，比如LIMIT子句。3.評估查詢節點的輸出行數，假設該節點執行結束。4.評估查詢節點的輸出行的平均字節數。初初識識執執行行計計劃劃需要知道的是：上級節點的消耗包括其子節點的消耗。這個消耗值只反映規劃器關心的內容，一般這個消耗不包括將數據傳輸到客戶端的時間。評估的行數不是執行和掃描查詢節點的數量，而是節點返回的數量。它通常會少于掃描數量，因為有WHERE條件會過濾掉一些數據。理想情況頂級行數評估近似于實際返回的數量。這個消耗的計算依賴于規劃器的設置參數，這里的例子都是在默認參數下運行。cost描述一個SQL執行的代價是多少，而不是具體的時間。下面

5、是默認情況下，對數據操作的消耗評估基礎：初初識識執執行行計計劃劃回到剛才的例子，表test有10000條數據分布在94個磁盤頁，評估時間是（磁盤頁*seq_page_cost）+（掃描行*cpu_tuple_cost）。默認seq_page_cost是1.0，cpu_tuple_cost是0.01，所以評估值是(94*1.0)+(10000*0.01)=194。什么時候更新的pg_class、pg_stat_user_tables等統計信息初初識識執執行行計計劃劃查詢節點增加了“filter”條件。這意味著查詢節點為掃描的每一行數據增加條件檢查，只輸入符合條件數據。評估的輸出記錄數因為wher

6、e子句變少了，但是掃描的數據還是10000條，所以消耗沒有減少，反而增加了一點CPU的計算時間。這個查詢實際輸出的記錄數是1000，但是評估是個近似值，多次運行可能略有差別，這中情況可以通過ANALYZE命令改善。幾幾種種掃掃描描方方式式全全表表掃掃描描：Seq Scan全表掃描，當數據表中沒有索引，或者滿足條件的數據集較大，索引掃描的成本高于全表掃描，這時規劃器會選擇使用全表掃描。至于全表掃描的過程和索引掃描的過程在這里不詳細說，后續可以再開一個主題。幾幾種種掃掃描描方方式式索索引引掃掃描描：index scan如果查詢的列創建有索引，則直接掃描索引，不再進行全表掃描，耗費時間小于順序掃描。

7、多了篩選條件后，會打開每條記錄，進行篩選記錄，花費時間變多了。但是，將篩選條件放到掃描里面是有好處的，尤其是在多表join時，構造自然選擇的塊有很大的區別 幾幾種種掃掃描描方方式式全全索索引引掃掃描描：index only scan當查詢的條件都在索引中，也會走該掃描方式，不會讀取表文件。幾幾種種掃掃描描方方式式位圖掃描：Bitmap Index Scan 位圖掃描也是一種走索引的方式，方法是掃描索引，把滿足條件的行或者塊在內存中建一個位圖，掃描完索引后，再跟進位圖中記錄的指針到表的數據文件讀取相應的數據。在or、and、in子句和有多個條件都可以同時走不同的索引時，都可能走Bitmap In

8、dex Scan：幾幾種種掃掃描描方方式式位圖掃描：Bitmap Index Scan Bitmap Heap Scan的啟動時間就是兩個子Bitmap Index Scan的總和，可以看出在進行組合時花費了大量時間。Bitmap Index Scan和Index Scan掃描的區別很明顯：Index scan:輸出的是tuple，它先掃描索引塊，然后得到rowid掃描數據塊得到目標記錄。一次只讀一條索引項，那么一個 PAGE面有可能被多次訪問.Bitmap index scan;輸出的是索引條目，并不是行的數據，輸出索引條目后，交給上一個節點 bitmap heap scan(之間可能將索引

9、條目根據物理排列順序進行排序)。一次性將滿足條件的索引項全部取出，然后交給bitmap heap scan節點，并在內存中進行排序,根據取出的索引項訪問表數據。幾幾種種掃掃描描方方式式limit對執行計劃的影響 這條查詢的where條件和上面的一樣，只是增加了limit，所以不是所有數據都需要返回，規劃器在發現limit后改變了規劃。雖然在索引掃描節點總消耗和返回記錄數是244，但查詢是從上往下拉取數據的，當掃描到滿足要求的2行結果后，達到了Limit 2 取兩條的要求，就直接返回了，預期時間消耗是26，所以總時間消耗是26。幾種連接方式 Hash Join 這個計劃是使用hash join的

10、方式來進行表連接的，首先確定兩個表的大小，使用小表建立hash map，然后掃描大表，比較hash值，最終獲取查詢結果。幾種連接方式 連連接接方方式式說說明明執執行行步步驟驟適適用用場場景景時時間間消消耗耗hash join散列連接是做大數據集連接時的常用方式，優化器使用兩個表中較小的表（或數據源）利用連接鍵在內存中建立散列表，然后掃描較大的表并探測散列表，找出與散列表匹配的行。這種方式適用于較小的表完全可以放于內存中的情況，這樣總成本就是訪問兩個表的成本之和。但是在表很大的情況下并不能完全放入內存，這時優化器會將它分割成若干不同的分區，不能放入內存的部分就把該分區寫入磁盤的臨時段，此時要有較

11、大的臨時段從而盡量提高I/O 的性能。將兩個表中較小的一個在內存中構造一個HASH表（對JOIN KEY），掃描另一個表，同樣對JOIN KEY進行HASH后探測是否可以JOIN。適用于記錄集比較大的情況。需要注意的是：如果HASH表太大，無法一次構造在內存中，則分成若干個partition，寫入磁盤的temporary segment，則會多一個寫的代價，會降低效率 兩個大表關聯查詢cost=(outer access cost*#of hash partitions)+inner access cost幾種連接方式 Nested Loop Join 這個規劃中有一個內連接的節點，它有兩個子

12、節點。節點摘要行的縮進反映了規劃樹的結構。最外層是一個連接節點，子節點是一個Seq Scan掃描。外部節點為t1.info=3756da242bee7967edcd3041769f8f96的結果。接下來為每一個從外部節點得到的記錄運行內部查詢節點（t.id 10）。外部節點的消耗加上循環內部節點的消耗（2.26+1*8.44）再加一點CPU時間就得到規劃的總消耗10.82。幾種連接方式 連連接接方方式式說說明明執執行行步步驟驟適適用用場場景景時時間間消消耗耗nested loop join對于被連接的數據子集較小的情況，嵌套循環連接是個較好的選擇。在嵌套循環中，內表被外表驅動，外表返回的每一行

13、都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大（大于1 萬不適合），要把返回子集較小表的作為外表（默認外表是驅動表），而且在內表的連接字段上一定要有索引。確定一個驅動表(outer table)，另一個表為inner table，驅動表中的每一行與inner表中的相應記錄JOIN。類似一個嵌套的循環。Nested loop一般用在連接的表中有索引，并且索引選擇性較好的時候。適用于驅動表的記錄集比較?。ㄅ?copy從SQL入手：CTE減少嵌套減少子查詢物化視圖、臨時表Exits使用拆分SQL改寫SQL類型轉換失真 從從數數據據庫庫參參數數入入手手：精確統計信息干涉執行計劃調整性能參數pg_hint_plan其其他他：連接池操作系統參數硬件性能THANKS謝謝觀看