PostgreSql索引（B-tree索引 Hash索引 GiST索引 SP-GiST索引 GIN 索引 BRIN 索引）

野性酷女 2022-11-06 11:49 290阅读 0赞

# 索引 #

**语法：**

CREATE INDEX test1_id_index ON test1 (id);

索引的名字test1\_id\_index可以自由选择，但我们最好选择一个能让我们想起该索引用途的名字。

为了移除一个索引，可以使用DROP INDEX命令。索引可以随时被创建或删除。

在一个大表上创建一个索引会耗费很长的时间。默认情况下，PostgreSQL允许在索引创建时并行地进行读（SELECT命令），但写（INSERT、UPDATE和DELETE）则会被阻塞直到索引创建完成。在生产环境中这通常是不可接受的。在创建索引时允许并行的写是可能的， PostgreSQL支持构建索引时不阻塞写入。这种方法通过 指定CREATE INDEX的CONCURRENTLY选项 实现。

一个索引被创建后，系统必须保持它与表同步。这增加了数据操作的负担。因此哪些很少或从不在查询中使用的索引应该被移除。

--------------------

## 索引类型 ##

PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。  
**B-tree索引**

支持的操作符：

<
    <=
    =
    >=
    >
    BETWEEN
    IN
    IS NULL
    IS NOT NULL
    LIKE (字符开头)
    ILIKE(非字母字符开头)

**Hash索引**  
Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时，查询规划器将考虑使用一个Hash索引。下面的命令将创建一个Hash索引：

CREATE INDEX name ON table USING HASH (column);

**GiST索引**  
GiST表示通用搜索树。它是一种平衡的树结构的访问方法，它作为一种模板可用来实现任意索引模式。B 树、R 树和很多其他索引模式都可以在GiST中实现。  
GiST索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施。（需要安装扩展 create extension btree\_gist;）  
多用于支持二维几何数据类型的索引化查询  
支持的操作符：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70]

GiST索引也有能力优化“最近邻”搜索，例如：

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特定操作符类

与Btree索引比较的优缺点：

优点：

Gist索引适用于多维数据类型和集合数据类型，和Btree索引类似，同样适用于其他的数据类型。和Btree索引相比，Gist多字段索引在查询条件中包含索引字段的任何子集都会使用索引扫描。

缺点：

Gist索引创建耗时较长，占用空间也比较大。

**SP-GiST索引**  
和GiST相似，SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现众多不同的非平衡的基于磁盘的数据结构，例如四叉树、k-d树和radix树。  
支持的操作符：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 1]  
**GIN 索引**  
GIN意思是通用倒排索引。GIN被设计为处理被索引项为组合值的情况，并且这种索引所处理的查询需要搜索出现在组合项中的元素值。例如，项可以是文档，并且查询可以是搜索包含指定词的文档。

我们使用词项来表示要被索引的一个组合值，并且用词键来表示一个元素值。GIN总是存储和搜索键，而不是项值本身。

一个GIN存储一个（键，位置列表）对的集合，这里一个posting list是键在其中出现的一个行 ID 的集合。相同的行 ID 可以出现在多个位置列表中，因为一个项可以包含多于一个键。每个键值只被存储一次，因此对于同一个键出现多次的情况，一个GIN索引是非常紧凑的。

支持的操作符：  
![在这里插入图片描述][20210309143251324.png]  
**BRIN 索引**  
BRIN表示块范围索引。 BRIN是为处理这样的表而设计的：表的规模非常大，并且其中某些列与它们在表中的物理位置存在某种自然关联。一个块范围是一组在表中物理上相邻的页面，对于每一个块范围在 索引中存储了一些摘要信息。例如，一个存储商店销售订单的表可能有一个日期 列记录每个订单产生的时间，并且很多时候较早的订单项也将出现在表中较早的 地方。一个存储 ZIP 代码列的表中一个城市的所有代码可能自然地聚在一起。

如果索引中存储的摘要信息与查询条件一致，BRIN 索引可以通过常规的位图索引扫描满足查询，并且将会返回每个范围中所有页面 中的所有元组。查询执行器负责再次检查这些元组并且抛弃掉那些不匹配查询条 件的元组 — 换句话说，这些索引是有损的。由于一个BRIN 索引很小，扫描这种索引虽然比使用顺序扫描多出了一点点开销，但是可能会避 免扫描表中很多已知不包含匹配元组的部分。

一个BRIN索引将存储的特定数据以及该索引将能 满足的特定查询，都依赖于为该索引的每一列所选择的操作符类。具有一种 线性排序顺序的数据类型的操作符类可以存储在每个块范围内的最小和最大 值，例如几何类型可能会存储在块范围内的所有对象的外包盒。

块范围的尺寸在索引创建时由pages\_per\_range存储参数决定。 索引项的数量将等于该关系的尺寸（以页面计）除以为 pages\_per\_range选择的值。因此，该值越小，索引会变得越大 （因为需要存储更多索引项），但是与此同时存储的摘要数据可以更加精确并 且在索引扫描期间可以跳过更多数据块。  
支持的操作符：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 2]

--------------------

## 多列索引 ##

一个索引可以定义在表的多个列上。例如，我们有这样一个表：

CREATE TABLE test2 (
      major int,
      minor int,
      name varchar
    );

（即将我们的/dev目录保存在数据库中）而且我们经常会做如下形式的查询：

SELECT name FROM test2 WHERE major = constant AND minor = constant;

那么我们可以在major和minor上定义一个索引：

CREATE INDEX test2_mm_idx ON test2 (major, minor);

目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件pg\_config\_manual.h中修改，但是修改后需要重新编译PostgreSQL）。

一个B-tree索引可以用于条件中涉及到任意索引列子集的查询，但是当先导列（即最左边的那些列）上有约束条件时索引最为有效。确切的规则是：在先导列上的等值约束，加上第一个无等值约束的列上的不等值约束，将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查，这样它们适当节约了对表的访问，但它们并未减小索引被扫描的部分。例如，在(a, b, c)上有一个索引并且给定一个查询条件WHERE a = 5 AND b >= 42 AND c < 77，对索引的扫描将从第一个具有a = 5和b = 42的项开始向上进行，直到最后一个具有a = 5的项。在扫描过程中，具有c >= 77的索引项将被跳过，但是它们还是会被扫描到。这个索引在原则上可以被用于在b和/或c上有约束而在a上没有约束的查询，但是整个索引都不得不被扫描，因此在大部分情况下规划器宁可使用一个顺序的表扫描来替代索引。

一个多列GiST索引可以用于条件中涉及到任意索引列子集的查询。在其余列上的条件将限制由索引返回的项，但是第一列上的条件是决定索引上扫描量的最重要因素。当第一列中具有很少的可区分值时，一个GiST索引将会相对比较低效，即便在其他列上有很多可区分值。

一个GIN索引可以用于条件中涉及到任意索引列子集的查询。与B-tree和GiST不同，GIN的搜索效率与查询条件中使用哪些索引列无关。

多列 BRIN 索引可以被用于涉及该索引被索引列的任意子集的查询条件。和 GIN 相似且不同于 B-树 或者 GiST，索引搜索效率与查询条件使用哪个索引列无关。在单个表上使用多个 BRIN 索引来取代一个多列 BRIN 索引的唯一原因是为了使用不同的pages\_per\_range存储参数。

当然，要使索引起作用，查询条件中的列必须要使用适合于索引类型的操作符，使用其他操作符的子句将不会被考虑使用索引。

多列索引应该较少地使用。在绝大多数情况下，单列索引就足够了且能节约时间和空间。具有超过三个列的索引不太有用，除非该表的使用是极端程式化的。

--------------------

## 索引和ORDER BY ##

除了简单地查找查询要返回的行外，一个索引可能还需要将它们以指定的顺序传递。这使得查询中的ORDER BY不需要独立的排序步骤。在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序后的输出，其他索引类型会把行以一种没有指定的且与实现相关的顺序返回。

规划器会考虑以两种方式来满足一个ORDER BY说明：扫描一个符合说明的可用索引，或者先以物理顺序扫描表然后再显式排序。对于一个需要扫描表的大部分的查询，一个显式的排序很可能比使用一个索引更快，因为其顺序访问模式使得它所需要的磁盘I/O更少。只有在少数行需要被取出时，索引才会更有用。一种重要的特殊情况是ORDER BY与LIMIT n联合使用：一个显式的排序将会处理所有的数据来确定最前面的n行，但如果有一个符合ORDER BY的索引，前n行将会被直接获取且根本不需要扫描剩下的数据。

默认情况下，B-tree索引将它的项以升序方式存储，并将空值放在最后(表TID被处理为其它相等条目之间的分线器列)。这意味着对列x上索引的一次前向扫描将产生满足ORDER BY x（或者更长的形式：ORDER BY x ASC NULLS LAST）的结果。索引也可以被后向扫描，产生满足ORDER BY x DESC（ORDER BY x DESC NULLS FIRST， NULLS FIRST是ORDER BY DESC的默认情况）。

我们可以在创建B-tree索引时通过ASC、DESC、NULLS FIRST和NULLS LAST选项来改变索引的排序，例如：

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
    CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

一个以升序存储且将空值前置的索引可以根据扫描方向来支持ORDER BY x ASC NULLS FIRST或 ORDER BY x DESC NULLS LAST。

--------------------

## 唯一索引 ##

索引也可以被用来强制列值的唯一性，或者是多个列组合值的唯一性。

CREATE UNIQUE INDEX name ON table (column [, ...]);

当前，只有B-tree能够被声明为唯一。

当一个索引被声明为唯一时，索引中不允许多个表行具有相同的索引值。空值被视为不相同。一个多列唯一索引将会拒绝在所有索引列上具有相同组合值的表行。

PostgreSQL会自动为定义了一个唯一约束或主键的表创建一个唯一索引。该索引包含组成主键或唯一约束的所有列（可能是一个多列索引），它也是用于强制这些约束的机制。  
**注意**  
不需要手工在唯一列上创建索引，如果那样做也只是重复了自动创建的索引而已。

--------------------

## 表达式索引 ##

一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。

例如，一种进行大小写不敏感比较的常用方法是使用lower函数：

SELECT * FROM test1 WHERE lower(col1) = 'value';

这种查询可以利用一个建立在lower(col1)函数结果之上的索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

表达式索引还允许控制唯一索引的范围。例如，此唯一索引可防止在double precision类型列中存储重复的整数值:

CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))

如果我们将该索引声明为UNIQUE，它将阻止创建在col1值上只有大小写不同的行。

另外一个例子，如果我们经常进行如下的查询：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

那么值得创建一个这样的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

正如第二个例子所示，CREATE INDEX命令的语法通常要求在被索引的表达式周围书写圆括号。而如第一个例子所示，当表达式只是一个函数调用时可以省略掉圆括号。

索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。在上面两个例子中，系统将会发现查询的条件是WHERE indexedcolumn = ‘constant’，因此查询的速度将等同于其他简单索引查询。因此，表达式索引对于检索速度远比插入和更新速度重要的情况非常有用。

--------------------

## 部分索引 ##

一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式（被称为部分索引的谓词）定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性，但在很多种情况下它们也很有用。

使用部分索引的一个主要原因是避免索引公值。由于搜索一个公值的查询（一个在所有表行中占比超过一定百分比的值）不会使用索引，所以完全没有理由将这些行保留在索引中。这可以减小索引的尺寸，同时也将加速使用索引的查询。它也将加速很多表更新操作，因为这种索引并不需要在所有情况下都被更新。

如果我们有一个表包含已上账和未上账的订单，其中未上账的订单在整个表中占据一小部分且它们是最经常被访问的行。我们可以通过只在未上账的行上创建一个索引来提高性能。创建索引的命令如下：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
        WHERE billed is not true;

使用该索引的一个可能查询是：

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

然而，索引也可以用于完全不涉及order\_nr的查询，例如：

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

这并不如在amount列上部分索引有效，因为系统必须扫描整个索引。然而，如果有相对较少的未上账订单，使用这个部分索引来查找未上账订单将会更好。

注意这个查询将不会使用该索引：

SELECT * FROM orders WHERE order_nr = 3501;

订单3501可能在已上账订单或未上账订单中。

假设我们有一个描述测试结果的表。我们希望保证其中对于一个给定的主题和目标组合只有一个“成功”项，但其中可能会有任意多个“不成功”项。实现它的方式是：

CREATE TABLE tests (
        subject text,
        target text,
        success boolean,
        ...
    );
    
    CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
        WHERE success;

当有少数成功测试和很多不成功测试时这是一种特别有效的方法。

通过使用部分索引子句只处理空列值，该索引只允许索引列中有一个空值，并且用一个表达式索引子句来索引 true 来替代 null:

CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;

最后，一个部分索引也可以被用来重载系统的查询规划选择。同样，具有特殊分布的数据集可能导致系统在它并不需要索引的时候选择使用索引。在此种情况下可以被建立，这样它将不会被那些无关的查询所用。通常，PostgreSQL会对索引使用做出合理的选择（例如，它会在检索公值时避开索引，这样前面的例子只能节约索引尺寸，它并非是避免索引使用所必需的），非常不正确的规划选择则需要作为故障报告。

记住建立一个部分索引意味着我们知道的至少和查询规划器所知的一样多，尤其是我们知道什么时候一个索引会是有益的。构建这些知识需要经验和对于PostgreSQL中索引工作方式的理解。在大部分情况下，一个部分索引相对于一个普通索引的优势很小。

--------------------

## 索引和排序规则 ##

一个索引在每一个索引列上只能支持一种排序规则。如果需要多种排序规则，你可能需要多个索引。

考虑这些语句：

CREATE TABLE test1c (
        id integer,
        content varchar COLLATE "x"
    );
    
    CREATE INDEX test1c_content_index ON test1c (content);

该索引自动使用下层列的排序规则。因此一个如下形式的查询：

SELECT * FROM test1c WHERE content > constant;

可以使用该索引，因为比较会默认使用列的排序规则。但是，这个索引无法加速涉及到某些其他排序规则的查询。因此对于下面形式的查询：

SELECT * FROM test1c WHERE content > constant COLLATE "y";

可以创建一个额外的支持"y"排序规则的索引，例如：

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70]: /images/20221023/42b237f3a18048ab91326b2422eeda91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 1]: /images/20221023/982868c8cbb44b70958af779f868b557.png
[20210309143251324.png]: /images/20221023/4186f8191cc2410aad39ced2c0987910.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neW9uZ3plX3o_size_16_color_FFFFFF_t_70 2]: /images/20221023/72059929a35443e999ac95969b7dc88f.png