PostgreSQL掃描方法是什么

169次閱讀

共計 4347 個字符，預計需要花費 11 分鐘才能閱讀完成。

這篇文章主要講解了“PostgreSQL 掃描方法是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“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 中看到這個特性。

感謝各位的閱讀，以上就是“PostgreSQL 掃描方法是什么”的內容了，經過本文的學習后，相信大家對 PostgreSQL 掃描方法是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完