Postgresql学习笔记之——逻辑结构管理之索引_在postgressql的以下索引中,最常见的索引并且适合处理等值查询和范围查询的索引是

一、索引介绍

1.索引是数据库中一种提供快速查询数据的方法。
2.索引缺点是增加了数据库存储空间，并且在插入和修改数据时花费额外的事件来维护索引。
3.特别索引会附带产生约束，如主键索引会附加主键约束，唯一索引会附加唯一约束等。

二、索引的分类：

Postgresql数据库支持以下几种索引：

1.B-tree：最常用的索引类型，适合处理等值查询和范围查询。

2.Hash：处理简单的等值查询

3.GiST：不是一种单独的索引类型，而是一种架构，可以在架构上实现不同的索引策略，GiST索引定义的特定操作符用于特定索引策略。Postgresql的标准发布中包含了二维几何数据类型的Gist操作符，比如，一个图形包含另一个图像的操作符 ”@>“，一个图形在另一个图形的左边且没有重叠的操作符”<<“等等。

4.SP-GiST：SP-GiST是 ”space-partitioned GiST“的缩写，即空间分区GiST索引，他是从PG 9.2开始提供的一种新的索引类型，主要通过一些新的索引算法提高GiST索引在某个情况下的性能。

5.GIN：反转索引，它可以处理包含多个键值，如数组等。与GiST类似，GIN支持用户自定义的索引策略，可以通过定义GIN索引的特定操作符类型实现不同的功能。Postgresql的标准发布中包含了用于一维数组的GIN操作符，比如，它支持包含操作符 ”@>“，被包含操作符 ”<@“，相等操作符 ”=“，重叠操作符 ”&&“等。

三、创建索引

语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
1
2
3
4
5
6

语法解析：

1.UNIQUE
创建唯一索引的关键词，当此索引创建时系统会检查表中索引所在列的值是否重复（已经存在数据），每次数据增加都会检测一次，插入或更新数据出现重复数据时会导致错误。

2.CONCURRENTLY
当此选项被指定时，Postgresql在创建索引时不会再表上加任何锁，防止并发的插入、更新或删除。而标准的索引构建会锁定表上的写操作(而不是读操作)，直到完成为止。

对于临时表，CREATE INDEX始终是非并发的，因为没有其他会话可以访问它们，而且创建非并发索引的成本更低。

3.IF NOT EXISTS
指定了此选项，在创建索引时如果已存在相同名字的索引会提示但不会抛出错误。

4.INCLUDE
可选的INCLUDE子句指定一个列的列表，这些列将作为非键列包含在索引中。非键列不能用于索引扫描搜索限定，并且出于索引强制的唯一性或排除约束的目的，它将被忽略。但是，仅索引扫描可以返回非键列的内容，而不必访问索引表，因为它们可以直接从索引项中获得。因此，添加非键列允许仅对索引扫描用于查询，否则无法使用它们。

It’s wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index’s table and bloat the size of the index, thus potentially slowing searches.

Columns listed in the INCLUDE clause don’t need appropriate operator classes; the clause can include columns whose data types don’t have operator classes defined for a given access method.

Expressions are not supported as included columns since they cannot be used in index-only scans.

Currently, the B-tree and the GiST index access methods support this feature. In B-tree and the GiST indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation.

5.name
要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在 的模式
中。如果索引名称被省略，PostgreSQL将基于基 表名称和被索引列名称选择一个合适的名称。

6.table_name
要被索引的表的名称（可以被模式限定）。

7.method
要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。 默认方法是btree。

8.column_name
一个表列的名称。

9.expression
一个基于一个或者更多个表列的表达式。如语法中所示，表达式通常必须 被写在圆括号中。不过，如果该表达式是一个函数调用的形式，圆括号可 以被省略。

10.collation
要用于该索引的排序规则的名称。默认情况下，该索引使用被索引列 的排序规则或者被索引表达式的结果排序规则。当查询涉及到使用非 默认排序规则的表达式时，使用非默认排序规则的索引就能排上用场。

11.opclass
一个操作符类的名称。

12.ASC
指定上升排序（默认）。

13.DESC
指定下降排序。

14NULLS FIRST
指定把空值排序在非空值前面。在指定DESC时， 这是默认行为。

15.NULLS LAST
指定把空值排序在非空值后面。在没有指定DESC时， 这是默认行为。

16storage_parameter
索引方法相关的存储参数的名称。详见 索引存储参数。

17.tablespace_name
在其中创建索引的表空间。如果没有指定，将会使用 default_tablespace。或者对临时表上的索引使用 temp_tablespaces。

18.predicate
部分索引的约束表达式。

一般索引在创建过程中，会把表全部数据扫描一遍，创建索引的过程中，对表还可以正常查询，但是对表的增、删、改操作需要等索引创建完成后才能进行，对此Postgresql提供了一种并发创建索引的方法。

示例：

数据库中有一张人员表 ”tb_people“ ：

postgres=# \d tb_people
                     Table "public.tb_people"
 Column |          Type           | Collation | Nullable | Default 
--------+-------------------------+-----------+----------+---------
 id     | integer                 |           | not null | 
 name   | text                    |           |          | 
 age    | integer                 |           |          | 
 sex    | boolean                 |           |          | 
 addr   | text                    |           |          | 
 phone  | character varying(12)[] |           |          | 
Indexes:
    "tb_people_pkey" PRIMARY KEY, btree (id)
1
2
3
4
5
6
7
8
9
10
11
12

在表中，由于一个人可以有多个联系方式，所以字段 ”phone“ 定义时一个数组。
为了实现姓名（name）的快速查询，可以在字段 ”name” 上创建一个简单的 B-tree 索引，如下：

postgres=# create index idx_people_name on tb_people (name);
CREATE INDEX

1
2
3
4

如果按照电话号码（phone）字段做快速查询，比如查询一个电话号码是谁的，由于此字段是一个数组，索引不适用B-tree索引，因此可以创建一个GIN索引，如下：

postgres=# create index idx_people_phone on tb_people using gin(phone);
CREATE INDEX

1
2
3
4

使用phone上的gin索引进行快速查询，使用如下方法查询：

select * from tb_people where phone @> arry['13122345678'::varchar(32)];

1
2
3

PS:"@>" 是数组的操作符，表示“包含”的意思，GIN索引能在 “@>" 上起作用。

Hash索引的更新不会记录到WAL日志中，所以在实际场景中应用不多。

创建索引的时候可以指定存储参数 ”WITH ( storage_parameter = value ) “，常用的存储参数为 FILLFACTOR。例如：

create index idx_tb_people_name on tb_people(name) with (FILLFACTOR);

1
2
3

也可以按照降序创建索引（默认为升序 esc）：

create index idx_people_name on tb_people(name desc);

1
2
3

如果字段 name 有空值，则可在创建索引是指定空值排在最前面：

create index idx_people_name on tb_people(name desc nulls first);

1
2
3

或者空值排在最后：

create index idx_people_name on tb_people(name desc nulls last);

1
2
3

四、并发创建索引

通常，在创建索引的时候Postgresql会锁定表以防止写入，然后对表做全表扫描，从而完成索引创建。在索引创建过程中，用户仍然可以读取表数据，但是对表的插入、更新或删除就会被阻塞，直到索引创建完成。如果表的更新频繁，且数据量较大，创建索引的消耗时间会比较长，就会导致一段时间内无法进行DML操作，这会对用户使用影响较大。

鉴于此，Postgresql支持不长时间阻塞更新的情况下创建索引，主要通过 CREATE INDEX 中加 CONCURRENTLY（并发创建索引）选项来实现，当该选项被使用时，Postgresql会执行表的两次扫描，因此会需要更长时间来创建索引。

示例

创建测试表，并插入测试数据：

postgres=# create table tb_index_test(id int primary key,note text);
CREATE TABLE
postgres=# insert into tb_index_test select generate_series(1,5000000),generate_series(1,5000000);
INSERT 0 5000000
1
2
3
4

两外打开两个psql窗口，第一个psql窗口执行创建索引的语句：

[root@local pg_data]# su - postgres
[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# \timing 
Timing is on.
postgres=# create index idx_test_not on tb_index_test (note);
CREATE INDEX
Time: 12040.072 ms (00:12.040)
1
2
3
4
5
6
7
8
9
10

同时在另一个psql窗口中删除表中id为1的记录，可以看到删除操作一直等待创建索引操作完成后才进行完成：

[root@local pg_data]# su - postgres
[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# \timing 
Timing is on.
postgres=# delete from tb_index_test where id=1;
DELETE 1
Time: 14622.906 ms (00:14.623)
1
2
3
4
5
6
7
8
9
10

如果在创建索引时加上参数 CONCURRENTLY ，那么在创建索引未完成时，删除操作仍然正常执行：

psql 1窗口创建索引

postgres=# drop index idx_test_not ;
DROP INDEX
Time: 26.507 ms
postgres=# create index concurrently idx_test_not on tb_index_test (note);
CREATE INDEX
Time: 9320.573 ms (00:09.321)
1
2
3
4
5
6

psql 2删除数据：

postgres=# delete from tb_index_test where id=4;
DELETE 1
Time: 2.618 ms

1
2
3
4

并发索引创建的时候需要注意，如果索引在创建过程中被强行取消，很可能会留下一个无效的索引，这个索引仍然会到值更新变慢。如果创建一个唯一索引，这个无效索引还会导致插入重复值失败。

五、修改索引

语法：

重命名

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
1

修改索引所在表空间

ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
1

使已命名的索引附加到已更改的索引上。指定的索引必须位于包含被更改索引的表的一个分区上，并且具有相同的定义。
附加的索引本身不能被删除，如果父索引被删除，那么它将自动被删除。

ALTER INDEX name ATTACH PARTITION index_name
1

此表单将索引标记为依赖于扩展，这样，如果删除扩展，索引也将自动删除。

ALTER INDEX name DEPENDS ON EXTENSION extension_name
1

此表单更改索引的一个或多个特定于索引方法的存储参数。有关可用参数的详细信息，请参见创建索引，例如索引的填充因子fillfactor。
注意，该命令不会立即修改索引内容;根据参数的不同，您可能需要使用REINDEX重新构建索引以获得所需的效果。

ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
1

将一个或多个特定于索引方法的存储参数重置为其默认值。与SET一样，可能需要重新索引来完全更新索引。

ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
1

该表单为后续的分析操作设置每列的统计信息收集目标，但是只能在定义为表达式的索引列上使用。由于表达式缺少唯一的名称，所以我们使用索引列的序号来引用它们。目标可设置在0 ~ 10000范围内;或者，将它设置为-1，以恢复到使用系统默认统计目标(default_statistics_target)。

ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number
    SET STATISTICS integer
1
2

ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, … ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]

示例：

1.重命名索引：

ALTER INDEX distributors RENAME TO suppliers;
1

2.移动索引到指定的表空间：

ALTER INDEX distributors SET TABLESPACE fasttablespace;
1

3.更改索引的填充因子（更改后需要重建索引）：

ALTER INDEX distributors SET (fillfactor = 75);
REINDEX INDEX distributors;
1
2

4.设置表达式索引的统计信息收集目标：

CREATE INDEX coord_idx ON measured (x, y, (z + t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
1
2

六、重建、删除索引

重建索引：

REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
1

语法解析：

1.VERBOSE
重建索引时打印重建进度条

2.INDEX
重建单个指定的索引：REINDEX INDEX index_name;

3.TABLE
重新创建指定表的所有索引。如果表有一个次要的“TOAST”表，那么也要重新建立索引。（REINDEX TABLE table_name;）

4.SCHEMA
重新创建指定模式的所有索引。如果这个模式的表有第二个“TOAST”表，那么也要重新建立索引。还将处理共享系统编目上的索引。这种形式的重索引不能在事务块中执行。

5.DATABASE
重新创建当前数据库中的所有索引。还将处理共享系统编目上的索引。这种形式的重索引不能在事务块中执行

6.SYSTEM
在当前数据库中重新创建系统编目上的所有索引。包括共享系统目录上的索引。不处理用户表上的索引。这种形式的重索引不能在事务块中执行。

7.CONCURRENTLY
当使用此选项时，PostgreSQL将重新构建索引，而不采取任何锁来防止并发插入、更新或删除表;而标准的索引重新构建将锁定表上的写操作(而不是读操作)，直到完成为止。在使用此选项时需要注意几个注意事项——请同时参阅重新构建索引。

对于临时表，重索引总是非并发的，因为没有其他会话可以访问它们，而且非并发重索引消耗更小。

示例：

1.重建索引：

REINDEX INDEX my_index;
1

2.重建指定表的所有索引：

REINDEX TABLE my_table;
1

3.在不信任系统索引已经是有效的前提下，重建特定数据库所有索引：

$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q
1
2
3
4
5

4.重新建立表的索引，在重新建立索引的过程中不阻塞对相关关系的读写操作:

REINDEX TABLE CONCURRENTLY my_broken_table;
1

注意：

1.REINDEX使用存储在索引表中的数据重新构建索引，替换旧的索引副本。有几种情况下可以使用重索引:

（1）索引已损坏，不再包含有效数据。尽管理论上不应该发生这种情况，但在实践中，索引可能会由于软件错误或硬件故障而损坏。REINDEX提供了一种恢复方法。

（2）一个索引变得“臃肿空虚”，即它包含许多空的或几乎空的页面。在某些不常见的访问模式下，PostgreSQL中的B-tree索引可能会出现这种情况。REINDEX提供了一种方法来减少索引的空间消耗，方法是在没有死页的情况下编写索引的新版本。

（3）您已经更改了索引的存储参数(如fillfactor)，并希望确保更改已完全生效。

（4）如果索引构建失败，则使用 ”CONCURRENTLY“ 选项，这个索引变成了 “invalid”状态，这样的索引是无用的，但是使用REINDEX来重建它们是很方便的。注意，只有REINDEX索引能够在无效索引上执行并发构建。

2.并发重建索引

重建索引可能会干扰数据库的正常操作。通常情况下，PostgreSQL会锁住索引针对写重新构建的表，并通过对表的一次扫描来执行整个索引构建。其他事务仍然可以读取表，但是如果它们试图插入、更新或删除表中的行，它们将阻塞这些行，直到完成索引重建。如果系统是一个实时的生产数据库，这可能会产生严重的影响。非常大的表可能需要花费很多时间来建立索引，甚至对于更小的表，重新构建索引可能会将编写者锁定在生产系统无法接受的长时间内。

PostgreSQL支持使用最小的写锁来重建索引。此方法是通过指定REINDEX的 ”CONCURRENTLY“ 选项来调用的。当使用此选项时，PostgreSQL必须为每个需要重建的索引执行两次表扫描，并等待可能使用该索引的所有现有事务的终止。与标准的索引重建相比，此方法需要更多的工作，而且完成所需的时间要长得多，因为它需要等待可能修改索引的未完成事务。但是，由于它允许在重新构建索引时继续正常的操作，所以此方法对于在生产环境中重新构建索引非常有用。当然，索引重建所带来的额外CPU、内存和I/O负载可能会降低其他操作的速度。

删除索引

语法：

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
1

语法解析：

1.CONCURRENTLY
并发删除索引。

2.IF EXISTS
如果索引不存在，不要抛出错误。在这种情况下会发出通知。

3.CASCADE
自动删除依赖于索引的对象，并依次删除依赖于这些对象的所有对象

4.RESTRICT
如果有任何对象依赖于索引，则拒绝删除它。这是默认设置。

示例：

删除索引：

DROP INDEX title_idx;
1

删除索引支持命令行操作，具体参照官方日志：https://www.postgresql.org/docs/12/app-reindexdb.html