六种索引结构

Postgres支持六种索引结构：B-tree、Hash、GiST、SP-GiST、GIN和BRIN。

  1. B-tree可以在可排序数据上处理：等值、范围查询。
  2. Hash索引只能处理简单等值比较。
  3. GiST索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施，可以用来优化“最近邻”搜索。
  4. SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST允许实现众多不同的非平衡的基于磁盘的数据结构，例如四叉树、k-d树和radix树。
  5. GIN 索引是“倒排索引”，它适用于包含多个组成值的数据值，例如数组。倒排索引中为每一个值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。
  6. BRIN 索引(块范围索引的缩写)会将一张大表分为多个物理上连续分布的“块”，索引会存储每一个“块”的摘要信息。与 GiST、SP-GiST 和 GIN 相似，BRIN可以支持很多种不同的索引策略，

索引的使用原理

当SQL语句中谓词涉及的列上有索引时，Postgres会扫描各个索引，每个索引的扫描结果组成一个位图，最后所有的位图根据谓词组合求并或者求或，生成最终结果。

需要注意的地方

当在同一列中存在多个查询条件时，例如WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样一个谓词条件，会被Postgres分解成为四次独立的在x列上索引的扫描，每一次扫描使用其中一个条件。这些查询的结果将被“或”起来形成最后的结果。

关于多列索引

多列索引，顾名思义能够将多个列的值绑定在一起，组成一个索引。

在成员列筛选查询时，多列索引的效果较好，因为只需要扫描一躺索引结构。

多列索引在设计时，需要将选择性较高的列放在左侧，较低的放在右侧。

多列索引理论上也能为单列查询提供性能改观，但是相比于单列索引，性能提升会稍微差一些，因为多列索引的体积偏大。

索引顺序

B-tree索引较为适合范围查询和数据排序。

B-tree索引默认使用升序来索引数据，但也支持以降序的方式索引数据。

理论上讲，对于单列索引，顺序其实对于查询的关系并不大。但对于多列索引，如果能够业务场景，选择合适的索引顺序，能够为查询带来明显的性能提升。

表达式索引

Postgres支持对某一列的值按某表达式求值后的结果建立索引。

每一次行数据发生更新时，表达式都要更新一次，维护成本有点高。。。

部分索引

Postgres支持在某一列对某一部分值建立索引，能够有效降低索引的大小，提高查询效率。

CREATE INDEX
  access_log_client_ip_ix
ON access_log (client_ip)
WHERE
  NOT (client_ip > inet '192.168.100.0'
  AND client_ip < inet '192.168.100.255');

也可以利用部分索引，创建某一范围内的唯一约束。

关于索引维护

  1. 定期地运行ANALYZE命令来更新统计信息可以帮助查询规划器能做出正确的决定。
  2. 周期性使用REINDEX命令重建索引。索引多次更新后，可能导致磁盘页上空间利用率低下，进而导致索引查询时产生更多的磁盘IO操作，造成查询效率低下。新创建的索引总能够保证高效的利用磁盘页面空间。

参考资料

延伸阅读