PostgreSQL Explain 複雜執行計劃怎麼看 --- 逐個分解 PG 執行計劃的那些操作

開頭還是介紹一下羣，如果感興趣 polardb ,mongodb ,mysql ,postgresql ,redis 等有問題，有需求都可以加羣羣內有各大數據庫行業大咖，CTO，可以解決你的問題。加羣請聯繫 liuaustin3 ，在新加的朋友會分到 2 羣（共 700 人左右 1 + 2）。

每個數據中都希望自己的 SQL 的執行計劃是最好的，最快的，最妙的，但是在你使用 EXPLAIN 的命令的情況下，你看的懂裏面的那些東西嗎？如果你看不懂，你怎麼知道你的執行計劃是最好的，最棒的，最妙的。

1 數據掃描獲取方式

Sequential Scan
Index Scan
Index Only Scan
Bitmap Scan
TID Scan

在 PG 的數據庫獲取數據的方式中，有以上的一些方式來獲取數據的方式的名字展示。

Sequential Scan

1 順序掃描，順序掃描是一種數據獲取的方式，舉例如果您想獲取 1000 萬行數據中的一條數據，使用這樣的方式，即使你的數據可能就在 1000 萬上數據的第一條，那麼他還是會將 1000 萬數據全部進行掃描，並匹配後，在給出結果。數據在數據庫庫存儲是通過頁面來存儲的，假設 1000 個頁面存儲了 1000 萬行的數據每個頁面，那麼掃描的方式就是 1000 （頁）* 10000（行）的方式進行數據的掃描。

順序掃描的方式的使用可能會符合以下的幾個條件

1 沒有合適的索引使用

2 獲取的數據佔比整體表是佔大多數的

2 INDEX SCAN

INDEX SCAN 雖然也有 SCAN ，掃描，但是這個掃描的 COST 相對於上面的掃描，不是一個意義，INDEX SCAN 並不是順序掃描的方式，而是按照索引查找定位數據的方式，通過非常低廉的索引 SEARCH 的方式，並通過索引存儲的指針指向具體的數據部分，獲取數據。

考量是否用索引來解決問題

1 有合適的對應的索引

2 數據提取的量佔據整體的數據量是少數，或極少數

3 隨機數據獲取相對於順序範圍數據獲取，更容易使用索引獲取數據

4 在成本估算後，索引掃描優於順序掃描

在以上的情況下，索引掃描纔可以啓用

3 INDEX Only Scan

INDEX only Scan 與上面的 index scan 在原理上是一致的，但是在操作的步驟上是不一樣的，INDEX only Scan 在獲取數據時，並不會在返回到原表中獲取數據，而是我們的數據已經在索引中了，所以直接在索引獲取數據後就返回了，節省了相關的第二次操作的消耗。

滿足使用 INDEX ONLY SCAN 方式，需要

1 有合適的索引

2 提取的數據佔據整體表的數據量是少數或極少數

3 獲取數據的字段在索引中，不需要回表找到索引中未包含的數據

4 Bitmap Scan

Bitmap Scan 掃描的出現是基於獲取的數據在 INDEX SCAN 中的問題點而產生的一個數據的獲取的方式，在 INDEX SCAN 中獲取到數據的位置後，還是需要到對應的數據頁面中，在掃描到對應的數據，而 BITMAP SCAN 就是要解決數據通過索引定位後，在去原數據頁面定位的問題，解決最後一公里的問題。

位圖索引掃描: 首先它從索引數據結構中獲取所有索引數據，並創建所有 TID 的位圖。爲了簡單理解，您可以認爲這個位圖包含所有頁面的哈希 (基於 page no 進行哈希)，並且每個頁面條目包含該頁內所有偏移量的數組。

所以通過位圖來獲取數據的方式，速度更快，當然相對的付出的成本也更多一些。BITMAP 如果用一個粗略的方式來評價爲什麼會選擇這樣的方式來獲取數據。

數據量多少

index_scan (index_only_scan)  <  bitmap scan < Sequential Scan

5 TID Scan

TID 數據掃描的方式，是一種特殊的數據掃描的方式，在常見的數據獲取中，是沒有選擇這樣的數據獲取的方式，但他可以解決一些特殊場景的問題。

實際上這樣的數據獲取的模式就是在 PG 的數據查詢中直接使用 CTID 的方式來獲取數據的物理位置上的數據。

postgres=# explain select * from table where ctid='(116,42)'

                      QUERY PLAN 
---------------------------------------------------------- 
Tid Scan on demotable  (cost=0.00..4.01 rows=1 width=15)   TID Cond: (ctid = '(116,42)'::tid)

在我們清楚了相關的數據搜索的幾種方式後，那麼數據集合和集合之間的關係如何處理是我們下一個需要理解的部分，在 POSTGRESQL 中我們可以將數據集合和數據集合之間的關係處理放方式。

1 Nested Loop join

2 Hash join

3 Merge join

1 NLJ Nested loop join ，嵌套循環，嵌套循環是將集合和集合之間的關係進行比較，在操作中，是兩個集合比較的關係，將外部數據逐條的與內部的集合的數據進行匹配.

這種數據集合比較的方式，是比較消耗數據庫運算性能的, 在使用這樣的方式進行數據庫的連接的情況下應該採用驅動表儘量小的方式來進行數據的處理。

2 Hash join

hash join 適用於集合和集合之間的等值比較，使用 HASH JOIN 方式中的集合和集合之間的關係應該是等於的方式。下面的這個例子中，選擇了 bt2 作爲 hash 的表，將相關的數據先進行 HASH 存儲到內存中，在將 bt1 中的數據 hash 後與 hash 表中的數據進行對比。顯然這樣的方式比第一個剛纔提到的 NLJ 的方式要更快，但也有相關的限制條件，就是被 HASH 的部分可以放入到內存當中。

select * from table1 bt1, table2 bt2 where bt1.id1 = bt2.id1;

Hash Join  (cost=27.50..220.00 rows=1000 width=16)   Hash Cond: (bt1.id1 = bt2.id1)   ->  Seq Scan on table1 bt1  (cost=0.00..145.00 rows=10000 width=8)   ->  Hash  (cost=15.00..15.00 rows=1000 width=8)         ->  Seq Scan on table2 bt2  (cost=0.00..15.00 rows=1000 width=8) (5 rows)

3 Merge join

Merge join 也是一種集合和集合之間的進行數據挑選的方式，在進行 Merge join 的方式中，需要注意的是集合和集合之間的數據是需要進行排序的，也就是說如果要進行 Merge join 則在除了必須是 = 號運算的基礎上，等號兩邊的的所在的列，必須是帶有索引的，有序的。

postgres=# explain select * from table1 bt1, table2 bt2 where bt1.id1 = bt2.id1;
QUERY PLAN 
------------------------------------------------------------------------ 
Merge Join  (cost=0.56..90.36 rows=1000 width=16)   Merge Cond: (bt1.id1 = bt2.id1)   ->  Index Scan using idx1 on table1 bt1  (cost=0.29..318.29 rows=10000 width=8)   ->  Index Scan using idx2 on table2 bt2  (cost=0.28..43.27 rows=1000 width=8) (4 rows)

在說完這些後，細緻的同學可能在執行計劃中發現過如下的一些工作的項目

Sort
Aggregate
Group By Aggregate
Limit
Unique
LockRows
SetOp

1 sort , sort 有的時候有，有的時候沒有，可能有些同學會發現這個問題，比如昨天我還發現我的執行計劃裏面有這個，今天我在用就沒有了，這是什麼原因，我們看下面兩個部分，同樣的語句，同樣的配方，但是執行計劃不同，因爲在語句中都有 order by 但不同的是，因爲後面的表創建了對應 ORDER BY 字段的索引，所以不在需要進行顯示的排序。

postgres=# explain select * from table order by num;
                   QUERY PLAN 
---------------------------------------------------------------------- 
Sort  (cost=819.39..844.39 rows=10000 width=15)   Sort Key: num   ->  Seq Scan on table  (cost=0.00..155.00 rows=10000 width=15) (3 rows)

postgres=# CREATE INDEX demoidx ON table(num); 
CREATE INDEX postgres=# explain select * from table order by num;
                     QUERY PLAN 
---------------------------------------------------------------------- 
Index Scan using demoidx on demotable  (cost=0.29..534.28 rows=10000 width=15) (1 row)

2 Aggregate 聚合操作是我們在 SQL 執行計劃中經常碰到的，這個操作的意味着你在語句的執行中有使用聚合函數，或對整體的結果進行了 count 計算等等，一般對於 SQL 執行中出現使用進行彙總，分析的函數時都會出現這個聚合測操作。

3 GroupAggregate 和 hashAggregate 出現的情況下，意味着 SQL 的操作中出現了 GROUP BY, 而如果是 groupAggregate 並且在此位置的 cost 較大的情況下，則說明進行 group by 的字段沒有索引的可能性較大.

postgres=# explain select count(*) from demo2 group by id2;
                            QUERY PLAN 
------------------------------------------------------------------------- 
GroupAggregate  (cost =9747.82..11497.82 rows=100000 width=12)   Group Key: id2   ->  Sort  (cost=9747.82..9997.82 rows=100000 width=4)      Sort Key: id2      ->  Seq Scan on demo2  (cost=0.00..1443.00 rows=100000 width=4) (5 rows)

postgres=# create index idx1 on demo1(id); 
CREATE INDEX postgres=# explain select sum(id2), id from demo1 where id=1 group by id;
                            QUERY PLAN 
------------------------------------------------------------------------ 
GroupAggregate  (cost=0.28..8.31 rows=1 width=12)   Group Key: id   ->  Index Scan using idx1 on demo1  (cost=0.28..8.29 rows=1 width=8)      Index Cond: (id = 1) (4 rows)

而 hashAggregate 一般是結果較小，並且需要分組進行數據的展示的情況下，使用 hashAggregate 的方式進行處理。

postgres=# explain select count(*) from demo1 group by id2;
                       QUERY PLAN 
--------------------------------------------------------------- 
HashAggregate  (cost=20.00..30.00 rows=1000 width=12)   Group Key: id2   ->  Seq Scan on demo1  (cost=0.00..15.00 rows=1000 width=4) (3 rows)

除以上的部分，還有如 subquery scan ,setOp, lockrows, Unique 等這裏就不在多說了。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/QbAydak4bOSLc5tICK3ggw

1 數據掃描獲取方式

2 INDEX SCAN

3 INDEX Only Scan

4 Bitmap Scan

5 TID Scan

猜你喜歡