PG索引全知道

我们了解了数据库访问数据的方式后，已经十分清晰，通过优化索引可以调整数据库访问数据的方式。而不同的索引类别，支持不同种类的数据访问方式，因此在给数据建索引的时候，不能盲目的去使用默认的方式创建索引（PostgreSQL数据库和其他大多数数据库一样，默认的模式创建B-TREE结构的索引）。了解索引的种类是做索引优化的基础，因此在本小节中，我们首先来学习一下PostgreSQL数据库索引的种类。与其他数据库相比，PostgreSQL的索引是最为复杂的，索引的种类很多，而且PostgreSQL也允许我们通过自定义索引的方式来创建非标准的索引，以适应特殊的业务需求。我们今天找一些比较常见的索引来进行讨论。在PostgreSQL目前的主流版本中，存在下面的一些索引种类：

• • B-TREE索引（默认的索引）
  • Hash索引（仅用于等于操作，不支持复合索引，PostgreSQL10之前不够成熟，不建议使用）
  • BRIN索引(9.5以上版本，对时序数据有效）
  • Partial索引（带where条件的索引，一种特殊的B-TREE索引）
  • 函数索引（索引字段上带函数的索引，一种特殊的B-TREE索引）
  • GIST/SP-GIST索引(全文/空间索引)
  • GIN索引
  • 自定义索引

BTREE索引：B-TREE是我们使用的最多的传统索引，采用B树结构。

B-TREE索引分为根节点、枝节点与叶节点三种节点，索引扫描是从根节点，通过枝节点找到数据所在的叶节点。和不同B-TREE不同的是，索引中还有一个双向链表-叶节点链，用于按照键值进行升序或者降序扫描。如果SQL语句是范围扫描，可以首先找到符合条件的索引页，然后顺着叶节点链根据大于、小于、等于的条件进行扫描。B-TREE索引分为唯一性索引和非唯一性索引，唯一性索引键值不能重复。通过索引扫描的时候，如果是等于操作，唯一性索引找到第一条记录时就会停止扫描，而针对非唯一性索引，扫描会沿着叶节点链继续扫描，直到发现不符合条件的记录为止。

B-TREE索引支持的操作类型：<、<=、=、>=、BETWEEN、IN、IS NULL、IS NOT NULL。除了标准的操作符之外，B-TREE索引对于部分匹配类的操作有效，比如like ‘abc%‘。如果正则表达式是^开头的，也可以使用B-TREE索引，比如where email ~’^jackson’。

B-TREE索引适用范围：大部分需要通过索引访问的场合都适合使用B-TREE索引，特别是键值数量较多，每次访问返回数据行数少于全表数据行数10%的场合，B-TREE索引都可以大幅度提升数据访问效率。

Hash索引：PostgreSQL上的一种特殊的索引，针对键值使用一个HASH函数，因此HASH索引只支持等于操作，不支持范围扫描，而且目前HASH索引也不支持复合索引。在Hash索引中，将键值转换为一个HASH值，然后再进行定位，HASH索引不需要枝节点，因此索引的大小会比普通的B-TREE索引要小。其逻辑结构图如下。

Hash索引通过HASH函数定位记录，因此对于HASH离散性较好的数据扫描十分高效。对于重复键值很少的情况的扫描效率较高。不过如果某个HASH值对应的数据记录较多，那么这个索引的扫描效率会变得不稳定，可能某些值的扫描特别快，某些值稍微慢一些。

HASH索引创建语法：

CREATE INDEX index_name ON table_name USING HASH (indexed_column);

HASH索引支持的操作类型：仅支持等于操作（=）。

HASH索引适用范围：仅仅做等于比较的单字段场景可以使用HASH 索引。

HASH索引使用限制：

1. PostgreSQL 10之前，Hash索引都不会生成WAL，因此也无法复制；
2. PostgreSQL 10之前，索引创建与重建的性能不佳；
3. 仅支持等于操作，仅支持单字段索引。

支持复合索引：否

BRIN索引：另外一种体积十分小的索引，这种索引可以用于海量的时序数据，针对物联网应用将会十分有效。BRIN的含义是Block Range Index，顾名思义，是针对块中的值范围进行索引。如下图

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

我们来看一个例子，在没有创建索引的情况下我们来看看执行计划

T_BRIN上的o_entry_d是一个时间类型的字段，随着时间单边增长，在上面创建一个索引：

Create index idx_tbrin on t_brin using brin(o_entry_d);

然后再次执行这条SQL：

我们惊喜的发现，执行计划使用了idx_tbrin这个索引，执行时间从681.911ms缩短到44.183ms。如果我们使用普通的B-TREE索引会怎样呢，我们删除这个索引然后再创建新的索引，从创建索引的速度上我们就看出BRIN索引创建的时间比B-TREE索引要快得多，因为BRIN索引的大小要小很多。

最后我们从SQL的执行时间上，使用B-TREE索引，执行时间为63毫秒多，B-TREE索引比BRIN索引要高出20毫秒左右。

BRIN索引创建语法：

CREATE INDEX index_name ON tablename USING brin (column List);

BRIN索引支持的操作类型：针对索引字段的=、>、<、>=、<=等操作。

BRIN索引适用范围：索引字段呈单边增长或者下降的场合。

BRIN索引使用限制：

支持复合索引：是

函数索引：PostgreSQL的函数索引与Oracle的函数索引十分类似，是对某个字段的函数创建索引。比如：

create index function_index_name on table_name (function_name(column));

函数索引也是B-TREE结构的，和普通的B-TREE索引结构完全类似。当我们的SQL语句中存在：

  where to_char(col1)=’123’

这样的语法的时候，如果我们在字段col1上创建一个索引是没用的，我们必须创建一个针对to_char(col1)的索引。

create index idx_func on tab1 (to_char(col1));

这样上面的语句就可以使用索引了。

函数索引创建语法：

CREATE INDEX index_name ON tablename (column List) where <where 条件>;

函数索引支持的操作类型：与B-TREE索引相同。

函数索引适用范围：与B-TREE索引相同。

函数索引使用限制：与B-TREE索引相同。

支持复合索引：是

部分索引：部分索引（Partial Index）我们可以看作是一种特殊的函数索引，其存储结构也是B-TREE的。部分索引也称为过滤索引，它只覆盖表数据的一个子集。 它是一个带有 WHERE 子句的索引，仅仅对WHERE 条件限定的数据行创建索引。

部分索引有助于加快查询速度，同时减少索引的大小，这些索引需要更少的存储空间，它们更易于维护，扫描速度更快，重建维护的速度也更快。如果您通常使用具有常量值的 WHERE 条件，则部分索引很有用。

在使用Oracle数据库的时候，我们也经常会遇到在某张表中，我们经常要根据一个独立值很少的STATUS字段来扫描数据的情况。比如在一张表上，STATUS=001的数据是我们要SELECT出来进行处理的，处理后STATUS就变成了002，因此这张表上的STATUS字段值域是倾斜的，001的记录可能只有几百条，而002的记录有上千万条。早期我们认为这种情况使用位图索引比较好，后来发现如果这张表的变化十分大，位图索引会引起十分严重的TX锁冲突。于是我们只能对此字段使用普通的B-TREE索引。而Oracle的优化器会根据bind peeking时不同的值生成不同的执行计划。在Oracle中，优化器虽然比较好的解决了这个问题，不过如果表很大，实际上这个索引中大量的数据（STATUS=002）是没用的。在PostgreSQL中，我们可以通过Partial索引获得更好的效果。

create index idx_partial_status on t_order (status) where status=’001’;

这个索引中只有status=’001’的数据，因此索引十分小。访问的效率也十分高。再复杂一些，我们可以创建类似这样的Partial Index。

create index idx_partial_status on t_order (status) where status in (’001’,’002’);

如果我们的where 条件是status in (’001’,’002’)，那么这个索引就能够发挥作用了。

PARTIAL索引创建语法：

CREATE INDEX index_name ON tablename (column List) where <where 条件>;

PARTIAL索引支持的操作类型：与B-TREE索引相同。

PARTIAL索引适用范围：与B-TREE索引相同。

PARTIAL索引使用限制：与B-TREE索引相同。

支持复合索引：是

GIN索引：GIN索引是一种通用倒排索引，这种索引是建立某个词与词在数据中所出现的位置信息。GIN索引对于复合型的数据十分有效。比如HSTORE、ARRAY、RANGE、JSON等。GIN索引对于全文检索等操作十分有效。

GIN索引创建语法：

CREATE INDEX index_name ON tablename  USING GIN(column List);

GIN索引适用范围：主要用于全文建索、空间数据等场景。

支持复合索引：是

GiST索引：通用搜索树索引，这是一种十分特殊的索引，是一种基准的平衡树模板，作为一个通用模板，GiST可以用于实现用户自定义的B-TREE和R-TREE等平衡树索引。如果GiST单独作为索引使用，可以用于和GIN索引类似的HSTORE、JSON等复合数据类型。GiST索引使用前必须先安装GiST扩展（create extension btree_gist）。

GiST索引创建语法：

CREATE INDEX index_name ON tablename  USING GIST(column List);

GiST索引适用范围：主要用于全文建索、空间数据等场景。

支持复合索引：是

    PostgreSQL的索引类型十分丰富，并且提供了十分方便的接口，让用户可以开发自己业务逻辑的特殊索引，从而提升应用的性能。正是因为PostgreSQL的索引类型十分丰富，让PostgreSQL索引的使用也变得复杂起来。使用Oracle等数据库的时候，我们基本上都会选择B-TREE索引，而再使用PostgreSQL的时候，我们要考虑好使用哪种索引。对于DBA来说，选择适当的索引类型是比较困难的，因为索引类型的选择与业务特点与业务逻辑有十分强的关联性。因此在PostgreSQL数据库中，DBA要更早、更多的参与到应用开发工作中去，研发部门在专业DBA的协助下，可以设计出更合理的索引，这对于应用系统的长期稳定运行十分关键。