Postgres 索引類型探索之旅

在 Citus 公司，為讓事情做的更好，我們與客戶一起在數據建模、優化查詢、和增加 索引上花費了許多時間。我的目標是為客戶的需求提供更好的服務，從而創造成功。我們所做的其中一部分工作是持續為你的 Citus 集群保持良好的優化和 高性能；另外一部分是幫你了解關於 Postgres 和 Citus 你所需要知道的一切。畢竟，一個健康和高性能的資料庫意味著 app 執行的更快，並且誰不願意這樣呢？ 今天，我們簡化一些內容，與客戶分享一些關於 Postgres 索引的信息。

Postgres 有幾種索引類型， 並且每個新版本都似乎增加一些新的索引類型。每個索引類型都是有用的，但是具體使用哪種類型取決於（1）數據類型，有時是（2）表中的底層數據和（3）執行的查找類型。接下來的內容我們將介紹在 Postgres 中你可以使用的索引類型，以及你何時該使用何種索引類型。在開始之前，這裡有一個我們將帶你親歷的索引類型列表：

• B-Tree
• 倒排索引 Generalized Inverted Index (GIN)
• 倒排搜索樹 Generalized Inverted Seach Tree (GiST)
• 空間分區的 Space partitioned GiST (SP-GiST)
• 塊範圍索引 Block Range Index (BRIN)
• Hash

現在開始介紹索引。

在 Postgres 中，B-Tree 索引是你使用的最普遍的索引

如果你有一個計算機科學的學位，那麼 B-Tree 索引可能是你學會的第一個索引。B-tree 索引 會創建一個始終保持自身平衡的一棵樹。當它根據索引去查找某個東西時，它會遍歷這棵樹去找到鍵，然後返回你要查找的數據。使用索引是大大快於順序掃描的，因為相對於順序掃描成千上萬的記錄，它可以僅需要讀幾個 頁 (當你僅返回幾個記錄時)。

如果你運行一個標準的 CREATE INDEX 語句，它將為你創建一個 B-tree 索引。 B-tree 索引在大多數的數據類型上是很有價值的，比如文本、數字和時間戳。如果你剛開始在你的資料庫中使用索引，並且不在你的資料庫上使用太多的 Postgres 的高級特性，使用標準的 B-Tree 索引可能是你最好的選擇。

GIN 索引，用於多值列

倒排索引 Generalized Inverted Index ，一般稱為 GIN，大多適用於當單個列中包含多個值的數據類型。

據 Postgres 文檔：

「GIN 設計用於處理被索引的條目是複合值的情況，並且由索引處理的查詢需要搜索在複合條目中出現的值。例如，這個條目可能是文檔，查詢可以搜索文檔中包含的指定字元。」

包含在這個範圍內的最常見的數據類型有：

• hStore
• Array
• Range
• JSONB

關於 GIN 索引中最讓人滿意的一件事是，它們能夠理解存儲在複合值中的數據。但是，因為一個 GIN 索引需要有每個被添加的單獨類型的數據結構的特定知識，因此，GIN 索引並不是支持所有的數據類型。

GiST 索引， 用於有重疊值的行

倒排搜索樹 Generalized Inverted Seach Tree （GiST）索引多適用於當你的數據與同一列的其它行數據重疊時。GiST 索引最好的用處是：如果你聲明一個幾何數據類型，並且你希望知道兩個多邊型是否包含一些點時。在一種情況中一個特定的點可能被包含在一個盒子中，而與此同時，其它的點僅存在於一個多邊形中。使用 GiST 索引的常見數據類型有：

• 幾何類型
• 需要進行全文搜索的文本類型

GiST 索引在大小上有很多的固定限制，否則，GiST 索引可能會變的特別大。作為其代價，GiST 索引是有損的（不精確的）。

據官方文檔：

「GiST 索引是有損的，這意味著索引可能產生虛假匹配，所以需要去檢查真實的錶行去消除虛假匹配。 (當需要時 PostgreSQL 會自動執行這個動作)」

這並不意味著你會得到一個錯誤結果，它只是說明了在 Postgres 給你返回數據之前，會做了一個很小的額外工作來過濾這些虛假結果。

特別提示：同一個數據類型上 GIN 和 GiST 索引往往都可以使用。通常一個有很好的性能表現，但會佔用很大的磁碟空間，反之亦然。說到 GIN 與 GiST 的比較，並沒有某個完美的方案可以適用所有情況，但是，以上規則應用於大部分常見情況。

SP-GiST 索引，用於更大的數據

空間分區 GiST （SP-GiST）索引採用來自 Purdue 研究的空間分區樹。 SP-GiST 索引經常用於當你的數據有一個天然的聚集因素，並且不是一個平衡樹的時候。 電話號碼是一個非常好的例子 (至少 US 的電話號碼是)。 它們有如下的格式：

• 3 位數字的區域號
• 3 位數字的前綴號 (與以前的電話交換機有關)
• 4 位的線路號

這意味著第一組前三位處有一個天然的聚集因素，接著是第二組三位，然後的數字才是一個均勻的分布。但是，在電話號碼的一些區域號中，存在一個比其它區域號更高的飽合狀態。結果可能導致樹非常的不平衡。因為前面有一個天然的聚集因素，並且數據不對等分布，像電話號碼一樣的數據可能會是 SP-GiST 的一個很好的案例。

BRIN 索引， 用於更大的數據

塊範圍索引（BRIN）專註於一些類似 SP-GiST 的情形，它們最好用在當數據有一些自然排序，並且往往數據量很大時。如果有一個以時間為序的 10 億條的記錄，BRIN 也許就能派上用場。如果你正在查詢一組很大的有自然分組的數據，如有幾個郵編的數據，BRIN 能幫你確保相近的郵編存儲在磁碟上相近的地方。

當你有一個非常大的比如以日期或郵編排序的資料庫， BRIN 索引可以讓你非常快的跳過或排除一些不需要的數據。此外，與整體數據量大小相比，BRIN 索引相對較小，因此，當你有一個大的數據集時，BRIN 索引就可以表現出較好的性能。

Hash 索引， 總算不怕崩潰了

Hash 索引在 Postgres 中已經存在多年了，但是，在 Postgres 10 發布之前，對它們的使用一直有個巨大的警告，它不是 WAL-logged 的。這意味著如果你的伺服器崩潰，並且你無法使用如 wal-g 故障轉移到備機或從存檔中恢復，那麼你將丟失那個索引，直到你重建它。 隨著 Postgres 10 發布，它們現在是 WAL-logged 的，因此，你可以再次考慮使用它們 ，但是，真正的問題是，你應該這樣做嗎?

Hash 索引有時會提供比 B-Tree 索引更快的查找，並且創建也很快。最大的問題是它們被限制僅用於「相等」的比較操作，因此你只能用於精確匹配的查找。這使得 hash 索引的靈活性遠不及通常使用的 B-Tree 索引，並且，你不能把它看成是一種替代品，而是一種用於特殊情況的索引。

你該使用哪個？

我們剛才介紹了很多，如果你有點被嚇到，也很正常。 如果在你知道這些之前， CREATE INDEX 將始終為你創建使用 B-Tree 的索引，並且有一個好消息是，對於大多數的資料庫， Postgres 的性能都很好或非常好。 🙂 如果你考慮使用更多的 Postgres 特性，下面是一個當你使用其它 Postgres 索引類型的備忘清單：

• B-Tree - 適用於大多數的數據類型和查詢
• GIN - 適用於 JSONB/hstore/arrays
• GiST - 適用於全文搜索和幾何數據類型
• SP-GiST - 適用於有天然的聚集因素但是分布不均勻的大數據集
• BRIN - 適用於有順序排列的真正的大數據集
• Hash - 適用於相等操作，而且，通常情況下 B-Tree 索引仍然是你所需要的。

如果你有關於這篇文章的任何問題或反饋，歡迎加入我們的 slack channel。

via: https://www.citusdata.com/blog/2017/10/17/tour-of-postgres-index-types/

作者：Craig Kerstiens 譯者：qhwdw 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive