PostgreSQL掃描方法綜述

PostgreSQL 掃描方法綜述

 

關係型資料庫都需要產生一個最佳的執行計劃從而在查詢時耗費的時間和資源最少。通常情況下，所有的資料庫都會產生一個以樹形式的執行計劃：計劃樹的葉子節點被稱為表掃描節點。查詢節點對應於從基表獲取資料。

例如，這一個查詢：SELECT *FROM TAB1,TAB2 where TAB2.ID>1000 。假設計劃樹如下：

 

 

 

上面的計劃樹：“ TBL1 上的順序掃描”和“ TBL2 上的索引掃描”分別對應於表 TBL1 和 TBL2 上的表掃描方法。 TBL1 上的順序掃描：從對應頁中順序獲取資料；索引掃描：使用索引掃描訪問表 2 。選擇一個正確的掃描方法作為計劃的一部分對於查詢效能非常重要。

深入理解PG 的掃描方法之前，先介紹幾個重要的概念。

 

HEAP ：儲存表整個行的儲存域。如上所示，整個域被分割為多個頁，每個頁大小預設是8K 。每個頁中， item 指標（例如上述頁中的 1,2 ）指向頁內的資料。

Index Storage ：只儲存KEY 值，即索引中包含的列值。也是分割成多個頁，每個索引頁預設 8K 。

Tuple Identifier(TID ) ： TID 為 6 個位元組，包含兩部分。前 4 個位元組為頁號，後 2 個位元組為頁內 tuple 索引。 TID 可以定位到特定記錄。

當前版本，PG 支援以下掃描方法：順序掃描、索引掃描、索引覆蓋掃描、 bitmap 掃描、 TID 掃描。依賴於表基數、選擇的表、磁碟 IO 、隨機 IO 、順序 IO 等，每種掃描方法都非常有用。我們先建立一個表並預製資料，並解釋這些掃描方法。

postgres=# CREATE TABLE demotable (num numeric, id int);
CREATE TABLE
postgres=# CREATE INDEX demoidx ON demotable(num);
CREATE INDEX
postgres=# INSERT INTO demotable SELECT random() * 1000,  generate_series(1, 1000000);
INSERT 0 1000000
postgres=# analyze;
ANALYZE

這個例子中，預製1 億條記錄並執行 analyze 更新統計資訊。

順序掃描

顧名思義，表的順序掃描就是順序掃描對應表所有頁的item 指標。如果一個表有 100 頁，每頁有 1000 條記錄，順序掃描就會獲取 100*1000 條記錄並檢查是否匹配隔離級別以及 where 條件。因此，即使只有 1 條記錄滿足條件，他也會掃描 100K 條記錄。針對上表的資料，下面的查詢會進行順序掃描，因為有大部分的資料需要被 selected 。

postgres=# explain SELECT * FROM demotable WHERE num < 21000;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on demotable  (cost=0.00..17989.00 rows=1000000 width=15)
   Filter: (num < '21000'::numeric)
(2 rows)

注意，不計算和比較計劃耗費，幾乎不可能直到選用哪個掃描方法。但是為了使用順序掃描，至少需要滿足以下關鍵點：謂詞部分沒有可用的索引鍵；或者SQL 查詢獲取的行記錄佔表的大部分。如果只有少數行資料被獲取，並且謂詞在一個或多個列上，那麼久會嘗試使用或者不使用索引來評估效能。

索引掃描

和順序掃描不同，索引掃描不會順序獲取所有表記錄。相反，依賴於不同索引型別並和查詢中涉及的索引相對應使用不同的資料結構。然後索引掃描獲取的條目直接指向heap 域中的資料，然後根據隔離級別判斷可見性。因此索引掃描分兩步：

從索引資料結構中獲取資料，返回heap 中資料對應的 TID ；然後定位到對應的 heap 頁直接訪問資料。由於以下原因需要執行額外的步驟：查詢可能請求可用索引更多的列；索引資料中不維護可見資訊，為了判斷可見性，需要訪問 heap 資料。

此時可能會迷惑，索引掃描如此高效，為什麼有時不用呢？原因在於cost 。這裡的 cost 涉及 IO 的型別。索引掃描中，為了獲取 heap 中的對應資料，涉及隨機 IO ；而順序掃描涉及順序 IO ，只有隨機 IO 耗時的 1/4 。

因此只有當順序IO 的代價大於隨機 IO 時，才會選擇索引掃描。

針對上表和資料，執行下面查詢時會使用索引掃描。隨機IO 代價小，從而查詢標記快。

postgres=# explain SELECT * FROM demotable WHERE num = 21000;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using demoidx on demotable  (cost=0.42..8.44 rows=1 width=15)
   Index Cond: (num = '21000'::numeric)
(2 rows)

Index Only Scan

僅索引掃描和索引掃描類似，區別在於第二步，僅僅涉及到掃描索引資料。有兩個條件：查詢獲取的資料只有key 列，且該列是索引的一部分；所有獲取的資料都是可見的。如下所示：

postgres=# explain SELECT num FROM demotable WHERE num = 21000;
                                  QUERY PLAN
-----------------------------------------------------------------------------
Index Only Scan using demoidx on demotable  (cost=0.42..8.44 rows=1 Width=11)
   Index Cond: (num = '21000'::numeric)
(2 rows)

Bitmap Scan

是索引掃描和順序掃描的混合體。為了解決索引掃描的缺點並充分利用其優點。正如上面所說，對於索引資料結構中的資料，需要找到heap 頁中對應的資料。因此需要獲取一次索引頁，然後獲取 heap 頁，從而造成大量隨機 IO 。 Bitmap 掃描方法平衡了不使用隨機 IO 的索引掃描優點。

Bitmap index scan ：首先獲取索引資料併為所有 TID 建立 bitmap 。為了理解方法，可以認為 bitmap 包含所有頁的雜湊（基於頁號），每個頁的 entry 包含頁內所有偏移的陣列。

Bitmap heap scan ：從頁的 bitmap 中讀取值，然後針對頁和偏移掃描資料。最後檢查可見性和條件並返回 tuple 。

下面查詢使用bitmap 掃描，因為他選擇的記錄很多（比如 too much for index scan ）但不是大量（ too little for sequential scan ）。

postgres=# explain SELECT * FROM demotable WHERE num < 210;
                                  QUERY PLAN
--------------------------------------------------------------------------
 Bitmap Heap Scan on demotable  (cost=5883.50..14035.53 rows=213042 width=15)
   Recheck Cond: (num < '210'::numeric)
   ->  Bitmap Index Scan on demoidx  (cost=0.00..5830.24 rows=213042 width=0)
      Index Cond: (num < '210'::numeric)
(4 rows)

再看另一個查詢，選擇同樣多的記錄但是僅僅索引列。不需要heap 頁因次沒有隨機 IO ，因此這個查詢選擇 index only scan 而不是 bitmap scan 。

postgres=# explain SELECT num FROM demotable WHERE num < 210;
                                   QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using demoidx on demotable  (cost=0.42..7784.87 rows=208254 width=11)
   Index Cond: (num < '210'::numeric)
(2 rows)

TID Scan

TID 掃描是 PG 中非常特殊的一種方式 , 和 Oracle 中的基於 ROWID 查詢類似：

postgres=# select ctid from demotable where id=21000;
   ctid
----------
 (115,42)
(1 row)
postgres=# explain select * from demotable where ctid='(115,42)';
                        QUERY PLAN
----------------------------------------------------------
 Tid Scan on demotable  (cost=0.00..4.01 rows=1 width=15)
   TID Cond: (ctid = '(115,42)'::tid)
(2 rows)

此外，PG 社群還在討論其他的掃描方法： MySQL 中的“ Loose Index Scan ”、 Oracle 中的“ index skip scan ”、 DB2 中的“ jump scan ”。這個掃描方法用在指定場景：選擇的 B-tree 索引的 key 列值都不同。避免遍歷所有相等的 key 值，而只遍歷第一個唯一值然後跳到下一個大值。這項工作 PG 正在開發，同樣被叫做“ Index skip scan ”，未來可以在 release 中看到這個特性。

原文

https://severalnines.com/database-blog/overview-various-scan-methods-postgresql

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31493717/viewspace-2657875/，如需轉載，請註明出處，否則將追究法律責任。