聊聊PostgreSQL的索引


索引是关系型数据库应用系统优化的关键,在早期的应用开发上,我们对索引的认知不足,因此应用系统在索引设计上总是存在很多问题。在2000年以前我给用户做的优化项目中,一般来说通过优化索引就可以解决几乎所有的问题。随着这些年我们在Oracle数据库上应用经验越来越丰富,Oracle数据库的索引优化也越来越纯熟了。不过如果我们把应用系统从Oracle迁移到PostgreSQL上的时候,我们很可能会沿着Oracle数据库索引使用的惯性,那样我们就可能会遇到一些问题了。因为PostgreSQL的索引体系与Oracle有了很大的不同,如果我们还是按照Oracle数据库索引的应用习惯去优化应用,可能使用的不是最佳的方案。

实际上索引优化是应用驱动的,索引的优化一定要根据应用的特点来做专业的设计,否则索引有可能会成为应用系统出现问题的隐患。数据库表上的索引也不是多多益善,特别是对于经常变更的表。对于PostgreSQL数据库来说,因为M
VCC
带来的死元组问题,如果某张大表上的U
PDATE
十分频繁,那么这张表上的索引数量越大,存在性能隐患的机会也越大。因此针对这些核心数据表的索引设计尤为关键。如何使用尽可能少的索引来完成对绝大多数S
QL
的优化是一个十分重要的课题。

PostgreSQL的索引比Oracle更为复杂,因此对于从Oracle数据库迁移到PostgreSQL的应用来说,重新优化索引是保障系统高效运行的十分重要的步骤。为了做好PostgreSQL索引优化,我们首先来看看Postg
reSQL
的索引有什么不同。

PostgreSQL的索引种类很多,我们今天找一些比较常见的索引来进行讨论。

B-TREE索引

Hash索引(仅用于等于操作,不支持复合索引,PG 10之前不建议使用)

BRIN索引(9.5以上,对时序数据有效)

Partial索引(带where条件的索引)

函数索引

GIN/GIST索引(全文/空间索引)

自定义索引

G
IN/GIST
索引比较特殊,今天我们暂时不讨论,今天重点讨论普通的关系型数据上的索引。
B
-TREE
是我们使用的最多的传统索引,这里就不需要再浪费笔墨来讨论了。Hash索引是PostgreSQL上的一种特殊的索引,针对键值使用一个H
ASH
函数,因此H
ASH
索引只支持等于操作,不支持范围扫描,而且目前H
ASH
索引也不支持复合索引。在Hash索引中,将键值转换为一个
HASH
值,然后再进行定位,H
ASH
索引不需要枝节点,因此索引的大小会比普通的B
-TREE
索引要小。其逻辑结构图如下。

聊聊PostgreSQL的索引




B
RIN
是另外一种体积十分小的索引,这种索引可以用于海量的时序数据,针对物联网应用将会十分有效。B
RIN
的含义是Block

Range
Index
,顾名思义,是针对块中的值范围进行索引。如下图

聊聊PostgreSQL的索引

如果某个键值是按照规律增长的,比如时序数据,那么使用B
RIN
将会十分有效。B
RIN
中存储了某个P
AGE
中的键值的最小值与最大值。如果键值存在某种单边增长的趋势,那么创建B
RIN
索引后,根据这个键值做范围扫描的时候,可以根据B
RIN
索引找到所需要扫描的数据块,跳过其他的所有数据块,快速的将所需的数据扫描出来。
BRIN 在索引数据自然倾向于在表的

中分组或排序的情况
,并且数据量十分巨大的情况下
很有用。
PostgreSQL的函数索引与Oracle的函数索引十分类似,我们这里就不做过多的讨论了。

部分索引(Partial

Index)我们可以看作是一种特殊的函数索引,其存储结构也是B
-TREE
的。部分索引也称为过滤索引,它只覆盖表数据的一个子集。
它是一个带有 WHERE 子句的索引。
部分索引有助于加快查询速度,同时减少索引的大小,这些索引需要更少的存储空间,它们更易于维护,扫描速度更快。如果您通常使用具有常量值的
WHERE 条件,则部分索引很有用。
在使用Oracle数据库的时候,我们也经常会遇到在某张表中,我们经常要根据一个独立值很少的S
TATUS
字段来扫描数据的情况。比如在一张表上,S
TATUS=001
的数据是我们要S
ELECT
出来进行处理的,处理后S
TATUS
就变成了002,因此这张表上的S
TATUS
字段值域是倾斜的,001的记录可能只有几百条,而002的记录有上千万条。早期我们认为这种情况使用位图索引比较好,后来发现如果这张表的变化十分大,位图索引会引起十分严重的T
X
锁冲突。于是我们只能对此字段使用普通的B
-TREE
索引。而Oracle的优化器会根据b
ind peeking
时不同的值生成不同的执行计划。在Oracle中,优化器虽然比较好的解决了这个问题,不过如果表很大,实际上这个索引中大量的数据(S
TATUS=002)
是没用的。在PostgreSQL中,我们可以通过Partial索引获得更好的效果。

create index
idx
_partial_status on t_order (status) where status
=
’001’;
这个索引中只有statu
s=’001’
的数据,因此索引十分小。访问的效率也十分高。再复杂一些,我们可以创建类似这样的Partial

Index。

create index
idx
_partial_status on t_order (status) where status
in
(’001’,’002’);
如果我们的w
here
条件是
status
in
(’001’,’002’)
,那么这个索引就能够发挥作用了。



今天时间关系,我们先讨论这么多。对于自定义索引,是PostgreSQL的一个更为强大的功能,我们可以通过编写插件的方式来实现自己的索引,根据自己的业务逻辑去对数据创建个性化的索引,从而提升应用访问的效率。这个话题比较复杂,我们下一次找一个专题来讨论。正是因为PG的索引十分复杂,因此十分容易出现索引滥用的情况。比如某个字段上原本就有B
-TREE
索引,为了某个S
QL
,我们可能又去创建了其他的索引。如果这样,应用系统经过一段时间的运维后,索引会成为一个大问题。索引导致的行锁问题,写应用变慢问题,会让我们更难去解决。因此在做PostgreSQL数据库索引优化的时候,一定要统筹考虑,密切结合应用的特点去做索引设计与优化。



聊聊PostgreSQL的索引》来自互联网,仅为收藏学习,如侵权请联系删除。本文URL:https://www.hashtobe.com/377.html