10分鐘掌握PostgreSQL 5種索引的應用場景

索引是增強數據庫性能的常用方法。索引使得數據庫在查找和檢索數據庫的特定行的時候比沒有索引快的多。但索引也增加了整個數據庫系統的開銷，所以應該合理使用。

PostgreSQL提供了幾種索引類型：B-tree，Hash，GiST，SP-GiST，GIN和BRIN。每個索引類型使用不同的算法，適合不同種類的查詢。默認情況下，CREATE INDEX命令創建B-tree索引，這符合最常見的情況。

在本文中，筆者會介紹PostgreSQL的5種索引類型。

1 BTree

1.1 應用場景

b-tree適合所有的數據類型，支持排序，支持大於、小於、等於、大於或等於、小於或等於的搜索。索引與遞歸查詢結合，還能實現快速的稀疏檢索。

1.2 示例

2 HASH

2.1 應用場景

hash索引存儲的是被索引字段VALUE的哈希值，只支持等值查詢。

hash索引特別適用於字段VALUE非常長（不適合b-tree索引，因為b-tree一個PAGE至少要存儲3個ENTRY，所以不支持特別長的VALUE）的場景，例如很長的字符串，並且用戶只需要等值搜索，建議使用hash index。

2.2 示例

3 gin

3.1 原理

gin是倒排索引，存儲被索引字段的VALUE或VALUE的元素，以及行號的list或tree。

（ col_val:(tid_list or tid_tree) ， col_val_elements:(tid_list or tid_tree) ）

3.2 應用場景

1、當需要搜索多值類型內的value時，適合多值類型。例如數組、全文檢索、TOKEN。（根據不同的類型，支持相交、包含、大於、在左邊、在右邊等搜索）

2、當用戶的數據比較稀疏時，如果要搜索某個value的值，可以適應btree_gin支持普通btree支持的類型。（支持btree的操作符）

3、當用戶需要按任意列進行搜索時，gin支持多列展開單獨建立索引域，同時支持內部多域索引的bitmapAnd, bitmapOr合併，快速的返回按任意列搜索請求的數據。

3.3 示例

3.3.1 多值類型搜索

3.3.2 單值稀疏數據搜索

3.3.3 多列任意搜索

4 gist

4.1 應用場景

GiST是一個通用的索引接口，可以使用GiST實現b-tree, r-tree等索引結構。

不同的類型，支持的索引檢索也各不一樣。例如：

1、幾何類型，支持位置搜索（包含、相交、在上下左右等），按距離排序。

2、範圍類型，支持位置搜索（包含、相交、在左右等）。

3、IP類型，支持位置搜索（包含、相交、在左右等）。

4、空間類型（PostGIS），支持位置搜索（包含、相交、在上下左右等），按距離排序。

5、標量類型，支持按距離排序。

4.2 示例

4.2.1 幾何類型檢索

4.2.2 標量類型排序

5 sp-gist

5.1 應用場景

1、幾何類型，支持位置搜索（包含、相交、在上下左右等），按距離排序。

2、範圍類型，支持位置搜索（包含、相交、在左右等）。

3、IP類型，支持位置搜索（包含、相交、在左右等）。

5.2 示例

範圍類型搜索

今天筆者就只介紹這5種索引的場景和基礎示例，後續會繼續介紹其他索引的使用場景及示例。

歡迎關注筆者，每天分享架構乾貨。