Postgresql - Index 介绍

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly. --转自官网。

索引是提高数据库性能的常用方式。索引允许数据库服务器查找和检索特定行的速度比没有索引的情况下要快得多。但是索引也增加了整个数据库系统的开销，所以它们应该被合理地使用。

 

如果没有索引，查询一张大表中少数的数据，不得不逐行扫描，直到扫描所有行之后，将查询结果返回。而如果表中有索引的时候，查询大表中的少量数据，只需查询索引中极少量的数据后，就能根据索引的“指引”直接定位到指定的行。这对查询的速度有大大的提升。那是不是在应该在每个列中都创建索引，当然不是，创建索引之后，索引必须保持与表同步。这增加了数据操作操作的开销。因此，查询中很少或从未使用的索引应该被删除。

 

Index types：

 B-tree, Hash, GiST, SP-GiST, GIN and BRIN.每个索引都使用不同算法。B-tree索引是最常用的。

B-tree处理某种排序的数据。当使用操作符（ < > <= >= = ）进行范围查询或者等于查询时，跟这些操作符相等意义的between、in、is null、is not null，或是like 'foo%' 或col ~ '^foo',但是 like '%foo' 是不走索引的。 B-树索引也可以用于按排序顺序检索数据。

 

hash索引只能处理简单的相等性比较。一般当只使用 = 运算时，才会使用hash索引。

CREATE INDEX name ON table USING HASH (column);

 

GiST索引不是单一类型的索引，而是一种可以实现许多不同索引策略的基础结构。依据特定的运算使用GiST索引。主要计算平面图形。

<<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=, &&

 

SP-GiST索引，如GiST索引，提供支持各种搜索的基础结构。SP-GiST允许实现各种不同的基于非平衡磁盘的数据结构，例如四叉树、K-D树和基数树（tries）。标准分布包括用于二维点的SP-GiST算子类，支持使用运算符的索引查询

<<, >>, ~=, <@, <^, >^

 

GIN索引是“倒数索引”，适用于包含多个组件值（如数组）的数据值。倒排索引包含每个组件值的单独条目，并且可以有效地处理测试特定组件值的存在的查询。支持使用运算符的索引查询

<@, @>, =, &&

 

BRIN索引（块范围索引的缩写）存储关于存储在表的连续物理块范围内的值的摘要。与GiST、SP-GiST和GIN一样，BRIN可以支持许多不同的索引策略，使用BRIN索引的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，索引数据对应于每个块范围的列中的值的最小值和最大值。

<, <=, =, >=, >

 

Multicolumn Indexes 组合索引：

一个索引中可以包含多个列，只有 B-tree, GiST, GIN, and BRIN index types 支持 multicolumn indexes.

 

多列B-tree索引可以与涉及索引列的任何子集的查询条件一起使用，但是当对最前面（最左边）列有约束时，索引是最有效的。确切的规则是，对先行列的等式约束，加上没有相等等式约束的第一列上的任何不等式约束，将用于限制被扫描的索引的一部分。在索引中检查这些列右边的列的约束，因此它们节省了访问表的正确性，但是它们不减少必须扫描的索引的部分。

一个多列的GIST索引可以与包含索引列的任何子集的查询条件一起使用。附加列的条件限制索引返回的条目，但在第一列上的条件是确定需要扫描多少索引的最重要的条件。如果第一列只有几个不同的值，即使在附加列中有许多不同的值，GIST索引也会相对无效。

 

可以使用涉及索引列的任何子集的查询条件的多列GIN索引。与B树或GIST不同，索引搜索有效性是相同的，而不管查询条件使用的索引列是什么。

 

多列BRIN索引可用于涉及索引列的任何子集的查询条件。与GIN不同，B搜索树和GIST不同，无论查询条件使用哪个索引列，索引搜索效率都是相同的。在单个表上有多个BRIN索引而不是多个BRIN BRIN索引的唯一原因是具有不同的pages_per_range存储参数。

 

Unique indexes：

索引还可用于强制列值的唯一性，或多个列的组合值的唯一性。目前，只有B-tree索引可以使用唯一索引。

当索引被声明为唯一时，不允许具有相等索引值的多个表行。空值不会被认为是相等的。多列唯一索引只会拒绝多行中所有索引列相等的情况。

在为表定义唯一约束或主键时自动创建唯一索引。该索引覆盖构成主键或唯一约束的列（如果适当的话，为多栏索引），并且是强制约束的机制。

 

Partial Indexes：

部分索引是在表的子集上建立的索引；子集是由条件表达式定义的（称为部分索引的谓词）。该索引仅包含满足谓词的那些表行的条目。部分索引是一个专门的特性，但在一些情况下它们是有用的。

使用部分索引的一个主要原因是避免对公共值进行索引。因为查询一个公共值（一个占所有表行数的百分之几）不会使用索引，所以在索引中保留这些行是没有意义的。这减少了索引的大小，这将加快使用索引的查询。它也将加快许多表更新操作，因为索引不需要在所有情况下都更新。

 

Indexes 和 collations

索引只能支持每个索引列的一个排序规则。如果想要多个排序，则可能需要多个索引。

 

Index-Only Scan:

PostgreSQL中的所有索引都是辅助索引，这意味着每个索引都与表的主数据区域（在PostgreSQL术语中称为表堆）分开存储。这意味着在普通索引扫描中，每行检索都需要从索引和堆中获取数据。此外，当索引项与给定索引项匹配时，条件通常在索引中紧密地结合在一起，它们引用的表行可能在堆中的任意位置。索引扫描的堆访问部分因此涉及到堆的大量随机访问，这将是非常慢的.

索引类型必须支持仅索引扫描。B树索引总是如此。GIST和SP GIST索引仅支持一些操作符类的索引扫描，而不支持其他操作符类。其他索引类型没有支持。基本要求是，索引必须物理存储或能够重构每个索引条目的原始数据值。