Skip to main content
 首页 » 数据库

PostgreSql 索引简明教程

2022年07月19日144lonelyxmas

PostgreSql 索引简明教程

索引是数据库引擎加速获取数据的查找表。简言之,索引是指向表数据的指针,类似书的目录。

PostgreSql 提供了Btree、Hash、GiST、SP-GiST、GIN、BRIN等多种索引类型,每种索引类型使用不同的算法来适应不同类型的查询。在默认情况下,创建的索引类型为B-tree索引。

1. 索引概述

索引可以加速查询,但会降低插入或更新速度。创建或删除索引不会影响数据。

创建索引

创建索引语法:

CREATE INDEX index_name ON table_name [USING method] 
( 
    column_name [ASC | DESC] [NULLS {
  FIRST | LAST }], 
    ... 
); 
  • 首先在 CREATE INDEX 后面指定索引名称,名称应该有意义方便记忆和理解。
  • 其次指定表名,表示在那个表上建索引。
  • 第三,指定索引方法,比如:Btree、Hash、GiST、SP-GiST、GIN、BRIN等,默认为Btree。
  • 第四,指定一个或多个列。后面是排序方式及null值排序。缺省为DESC 和 NULLS LAST。

删除索引

DROP INDEX index_name; 

使用drop 命令删除索引。删除索引时要小心,会对性能有负面或正面影响。

2. 索引类型

单列索引

CREATE INDEX index_name 
ON table_name (column_name); 

多列索引

CREATE INDEX index_name 
ON table_name (column1_name, column2_name); 

无论是单列索引或多列索引,应考虑这些列应该在where条件子句中作为过滤条件频繁使用。

唯一索引

CREATE UNIQUE INDEX index_name 
on table_name (column_name); 

唯一索引不仅提升性能,也是为实现数据完整性,它不允许插入重复值。

局部索引

CREATE INDEX idx_customer_inactive 
ON customer(active)               -- 指定表和字段 
WHERE active = 0;                 -- 指定过滤条件 

可以参考这篇博文,上面示例我们仅对 active=0 的记录感兴趣。

表达式索引

CREATE INDEX index_name  
ON table_name (expression); 

当对应 expression 在where或order by子句中出现时,数据库会使用该索引。注意,维护表达式上的索引是相当昂贵的,因为当插入或更新每一行时,数据库系统必须对表达式求值,并使用结果进行索引。因此,当检索速度比插入和更新速度更重要时,才使用表达式上索引。

举例:

CREATE INDEX idx_ic_last_name 
ON customer(LOWER(last_name)); 

对 last_name 的 值转为小写的索引。

隐式索引

是数据库自动创建的索引,当我们创建主键和约束和唯一约束时,数据库系统自动帮我们创建的索引。

3. 索引方法

Btree索引

Btree索引使用Btree数据结构来存储索引数据,可用于处理等值查询和范围查询,包括<、<=、=、>=、>等运算符,以及BETWEEN、IN、IS NULL、IS NOT NULL 等条件。

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

Btree索引可用于模式匹配查询,如“col LIKE′foo%′”或“col~′^foo′”,但是不能用于“col LIKE′%bar′”之类的后缀模糊匹配查询。Btree索引还可以用于查询结果集排序,如ORDER BY排序。

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

Hash索引

Hash索引根据每一行数据的索引字段计算哈希码,并维护哈希码、记录指针对应关系。对于哈希码相同的数据来说,可以采用链表来解决冲突。Hash索引的查询速度很快。

CREATE INDEX index_name  
ON table_name USING HASH (indexed_column); 

GiST索引

GiST(Generalized Search Tree)是一种平衡的树型结构访问方法,可作为一种基础模板来实现任意索引模式。B-tree索引和许多其他的索引模式都可以用GiST索引来实现。GiST 索引适用于多维数据类型和集合数据类型。GiST 多列索引支持在查询条件中包含索引字段的子集。PostgreSQL包含了全文检索、几何数据类型等多个用GiST实现的索引方法。

SP-GiST索引

SP-GiST索引与GiST索引类似,可作为一种基础模板来实现多种搜索方法。SP-GiST索引主要实现非平衡的基于硬盘的数据结构,如四叉树、k-d树和radix树。

GIN索引

GIN索引是一种通用倒排索引(GIN stands for generalized inverted indexes),可以处理包含多个键值,如 hstore, array,jsonb, 和 range 类型。用它来全文搜索或JSON键值的效率很高。GIN 允许用户开发自定义访问方法的数据类型索引,可以支持多种不同用户定义的索引策略。

BRIN索引

BRIN索引BRIN表示块范围索引。BRIN索引存储连续相邻的数据块统计信息,可以大大缩小索引占用空间。对于数据量比较大的表来说,BRIN索引比B-Tree索引插入数据的速度要快,两者的范围查询效率相当。BRIN索引通常用于线性顺序排列的列,如订单表的创建日期。

布隆过滤索引

Bloom Filter是一种空间效率很高的随机数据结构,它利用位数组很简洁地表示一个集合,并能判断一个元素是否属于这个集合。Bloom Filter的这种高效是有一定代价的:在判断一个元素是否属于某个集合时,有可能会把不属于这个集合的元素误认为属于这个集合(false positive)。因此,Bloom Filter不适合那些“零错误”的应用场合。而在能容忍低错误率的应用场合下,Bloom Filter通过极少的错误换取了存储空间的极大节省。

读者可以查找相关资源更深入了解,这里简单示例如何使用布隆过滤索引:

CREATE EXTENSION bloom; 
CREATE INDEX SomeIndex ON SomeTable USING bloom (c1, c2, c3, c4); 
-- a query which may take advantage of the Bloom index 
SELECT * FROM SomeTable WHERE c2 = 5432 AND c4 = 1234; 

4. 总结

虽然索引用于提升数据库性能,但有时我们应该避免使用索引。下面列举几条供参考:

  • 数据量小的表不要使用索引
  • 频繁大批量插入、更新操作的表不要索引
  • 列上有大量null值,不应该建索引
  • 列值被频繁维护,不应该建索引

本文参考链接:https://blog.csdn.net/neweastsun/article/details/119205148
阅读延展