Rumah >pangkalan data >tutorial mysql >PostgreSQL中文学习手册(索引)
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复合索引不会受到查询条件中使用了哪些索引字段子集的影响,,无论是哪种组合,都会得到相同的效率。
使用复合索引应该谨慎。在大多数情况下,单一字段上的索引就已经足够了,并且还节约时间和空间。除非表的使用模式非常固定,否则超过三个字段的索引几乎没什么用处。