欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

PostgreSQL中文学习手册(索引)

程序员文章站 2024-02-08 12:09:28
...

PostgreSQL中文学习手册(索引) ,PostgreSQL提供了多种索引类型:B-Tree、Hash、GiST和GIN,由于它们使用了不同的算法,因此每种

一、索引的类型:

PostgreSQL提供了多种索引类型:B-Tree、Hash、GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引。

1. B-Tree:
CREATE TABLE test1 (
id integer,
content varchar
);
CREATE INDEX test1_id_index ON test1 (id);
B-Tree索引主要用于等于和范围查询,特别是当索引列包含操作符" =和>"作为查询条件时,PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中,PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询,如LIKE、ILIKE、~和 ~*,仅当模式存在一个常量,且该常量位于模式字符串的开头时,如col LIKE 'foo%'或col ~ '^foo',索引才会生效,否则将会执行全表扫描,如:col LIKE '%bar'。

2. Hash:
CREATE INDEX name ON table USING hash (column);
散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时,查询规划器会考虑使用散列索引。
这里需要额外说明的是,PostgreSQL散列索引的性能不比B-Tree索引强,但是散列索引的尺寸和构造时间则更差。另外,由于散列索引操作目前没有记录WAL日志,因此一旦发生了数据库崩溃,我们将不得不用REINDEX重建散列索引。

3. GiST:
GiST索引不是一种单独的索引类型,而是一种架构,可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略,而使用特定的操作符类型。

4. GIN:
GIN索引是反转索引,它可以处理包含多个键的值(比如数组)。与GiST类似,GIN同样支持用户定义的索引策略,从而可以使GIN索引根据不同的索引策略,而使用特定的操作符类型。作为示例,PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型,如:、=、&&等。

二、复合索引:

PostgreSQL中的索引可以定义在数据表的多个字段上,如:
CREATE TABLE test2 (
major int,
minor int,
name varchar
}
CREATE INDEX test2_mm_idx ON test2 (major, minor);
在当前的版本中,只有B-tree、GiST和GIN支持复合索引,其中最多可以声明32个字段。
1. B-Tree类型的复合索引:
在B-Tree类型的复合索引中,该索引字段的任意子集均可用于查询条件,不过,只有当复合索引中的第一个索引字段(最左边)被包含其中时,才可以获得最高效率。

2. GiST类型的复合索引:
在GiST类型的复合索引中,只有当第一个索引字段被包含在查询条件中时,才能决定该查询会扫描多少索引数据,而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值,那么此时应用GiST索引就会比较低效。

3. GIN类型的复合索引:
与B-Tree和GiST索引不同的是,GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响,,无论是哪种组合,都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下,单一字段上的索引就已经足够了,并且还节约时间和空间。除非表的使用模式非常固定,否则超过三个字段的索引几乎没什么用处。

PostgreSQL中文学习手册(索引)