PostgreSQL 教程: 索引类型

九月 11, 2023

摘要：在本教程中，您将了解 PostgreSQL 的各种索引类型以及如何正确使用它们。

PostgreSQL 有多种索引类型：B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN。每种索引类型使用不同的存储结构和算法来应对不同类型的查询。

当您使用CREATE INDEX语句而不指定索引类型时，PostgreSQL 默认使用 B 树索引类型，因为它最适合最常见的查询。

B 树索引

B 树是一种自平衡树，它维护排序的数据，并允许在对数时间内进行搜索、插入、删除和顺序访问。

每当索引列涉及使用以下运算符之一的比较时，PostgreSQL 查询计划器将考虑使用 B 树索引：

<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL

此外，如果模式是常量并且锚定在模式的开头，则查询规划器可以对涉及模式匹配运算符LIKE和~的查询使用 B 树索引，例如：

column_name LIKE 'foo%' 
column_name LKE 'bar%' 
column_name  ~ '^foo'

此外，如果模式以非字母字符开头（即不受大小写转换影响的字符），则查询规划器将考虑对ILIKE和~*使用 B 树索引。

如果您已开始使用索引来优化 PostgreSQL 数据库，B 树可能就是您想要的。

哈希索引只能处理简单的相等比较 (=) 。这意味着每当索引列涉及使用等于 (=) 运算符的比较时，查询规划器都会考虑使用哈希索引。

要创建哈希索引，请使用CREATE INDEX语句，并在USING子句中指定HASH索引类型，如下所示：

CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

GIN 代表通用倒排索引。它通常被称为 GIN。

当您在单个列中存储多个值时，例如 hstore、array、jsonb 和 range 类型，GIN 索引最有用。

BRIN 代表块范围索引。与 B 树索引相比，BRIN 更小，维护成本也更低。

BRIN 允许在非常大的表上使用索引，这在以前使用没有水平分区的 B 树是不切实际的。

BRIN 通常用于具有线性排序顺序的列，例如销售订单表的创建日期列。

GiST 代表广义搜索树。GiST 索引允许构建通用树结构。GiST 索引在索引几何数据类型和全文搜索时非常有用。

SP-GiST 代表空间划分的 GiST。SP-GiST 支持分区搜索树，有助于开发各种不同的非平衡数据结构。

SP-GiST 索引对于具有自然聚类元素且不是同等平衡树的数据最有用，例如 GIS、多媒体、电话路由和 IP 路由。

在本教程中，您学习了各种 PostgreSQL 索引类型，包括 B 树、哈希、BRIN、GiST 和 SP-GiST。