PostgreSQL 索引类型
本文介绍在 PostgreSQL 中的索引类型以及如何正确的使用它们。
PostgreSQL 支持几种不同类型的索引:B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN。每种索引类型使用不同的存储结构和算法来应对不同类型的查询。不同的数据可能要采用不同的索引类型。
PostgreSQL 默认使用 B-tree 索引类型,因为它最适合最常见的查询。 你在使用 CREATE INDEX
语句创建索引时,如果不知道索引类型,默认采用 B-tree。
B-tree 索引
B-tree 是一种自平衡树,它维护已排序的数据。
每当索引列涉及使用以下运算符之一的比较时,PostgreSQL 查询规划器将考虑使用 B-tree 索引:
<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL
此外, 如果模式是一个常量并且在模式的开头是锚点, 查询规划器可以对涉及模式匹配运算符 LIKE
和 ~
的查询使用 B-tree 索引, 例如:
column_name LIKE 'foo%'
column_name LKE 'bar%'
column_name ~ '^foo'
而 col LIKE '%bar'
则不会使用 B-tree 索引。
对于模式匹配运算符 ILIKE
和 ~*
,如果模式以以非字母字符(不受大小写影响的字符)开头,查询规划器将考虑使用 B-tree 索引。
如果您已经开始使用索引来优化 PostgreSQL 数据库,那么 B-tree 可能就是您想要的。
哈希索引
哈希索引只能处理简单的相等比较 (=
)。这意味着每当索引列使用 =
运算符进行比较时,查询计划器将考虑使用哈希索引。
要创建哈希索引,请在子句中使用 CREATE INDEX
带有 HASH
索引类型的 USING
语句,如下所示:
CREATE INDEX index_name
ON table_name USING HASH (indexed_column);
GIN 索引
GIN 索引是“倒排索引”。它很适合索引那些复杂的值(比如 array
, hstore
, json
和 range
)。
倒排索引中为每一个组成值都包含一个单独的项,它可以高效地处理测试指定组成值是否存在的查询。
当您将多个值存储在单个列中时,GIN 索引最有用。
BRIN 索引
BRIN 表示块范围索引(Block Range Indexes),存储有关存放在一个表的连续物理块范围上的值摘要信息。BRIN 是 PostgreSQL 9.5 版本新增的索引类型。与 B-tree 索引相比,BRIN 更小,维护成本更低。
BRIN 允许在非常大的表上使用索引,这在以前使用没有水平分区的 B-tree 是不切实际的。
BRIN 常用于具有线性排序顺序的列,例如,销售订单表的创建日期列。
GiST 索引
GiST 索引不是单独一种索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略。
GiST 代表广义搜索树。GiST 索引允许构建通用的树结构。GiST 索引可用于索引几何数据类型和全文搜索。
SP-GiST 索引
SP-GiST 代表空间分区的 GiST。SP-GiST 支持分区搜索树,有助于开发各种不同的非平衡数据结构。
SP-GiST 索引最适用于具有自然聚类元素但也不是均衡树的数据,例如 GIS、多媒体、电话路由和 IP 路由。
结论
本文介绍了各种 PostgreSQL 索引类型,包括 B-tree、Hash、BRIN、GiST 和 SP-GiST。