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 代表通用倒排索引。它通常被称为 GIN。

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

BRIN

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

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

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

GiST 索引

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

SP-GiST 索引

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

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

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