PostgreSQL-如何创建并发索引

索引简介

索引是数据库中一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系，就好比一本书前面的目录，通过目录中页码就能快速定位到我们需要查询的内容。

建立索引的好处是加快对表中记录的查找或排序，但建索引需要付出以下代价：

  • 增加了数据库的存储空间
  • 在插入和修改数据时要花费较多的时间，因为索引也要随之更新

除了加快查询的作用外，索引还有一些其他的用途，如唯一索引还可以起到唯一约束的作用。

索引的分类

PG中支持以下几类索引：

  • BTree:最常用的索引，BTree适用于处理等值查询和范围查询
  • HASH:只能处理简单的等值查询
  • GiST:不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略
  • SP-GiST:“Space-Partitioned GiST”的缩写，即空间分区GiST索引。
  • GIN:反转索引，可以处理包含多个健的值，如数组等，它支持用户定义的索引策略，可通过定义GIN索引的特定操作符类型实现不同的功能。PG的标准发布中包含了用于一维数组的GIN操作符类，比如，它支持包含操作符“@>”、被包含操作符“@<”、相等操作符“=”、重叠操作符“&&”等等

创建索引

CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING method]
( { column_name | (expression)} [COLLATE collation] [opclass] [ASC | DESC] [ NULLS {FIRST | LAST}] [,...])
[WITH (storage_parameter = value [,...])]
[TABLESPACE tablespace_name]
[WHERE predicate]

一般，在创建索引的过程中会把表中的数据全部读一遍，该过程所用时间由表的大小决定，对于较大的表，可能会花费很久的时间。在创建索引的过程中，对表的查询可以正常运行，但对表的增、删、改等操作需要等索引建完后才能进行。对此PG提供了并发创建索引的方法。

假设由一张联系人的表，命令如下：

CREATE TABLE contacts(
    id int primary key,
    name varchar(40),
    phone varchar(32)[],
    address text
);

在该表中，由于一个人可能有多个电话号码，所以把“phone”定义为一个数组

为了实现按name快速查找，可以在字段name上建一个简单的BTree索引，命令如下：

CREATE INDEX idx_contacts_name on contacts(name);

如果像按电话号码phone字段快速查询，比如查询某个电话号码是谁的，由于此字段是一个数组，前面所建的BTree索引将不再起作用，这时可以建一个GIN索引，命令如下：

CREATE INDEX idx_contacts_phone on contacts using gin(phone);

如果想要查询号码“15873135680”是谁的，可以使用下面的查询语句：

SELECT * FROM contacts WHERE phone @> array['15873135680'::varchar(32)];

HASH索引的更新不会记录到WAL日志中，所以实际使用场景很少

创建索引可以指定存储参数“WITH(storage_paramter = value)”，常用的存储参数为FILLFACTOR，比如，可以这样创建索引：

CREATE INDEX idx_contacts_name on contacts(name) WITH (FILLFACTOR = 50);

也可以按降序创建索引：

CREATE INDEX idx_contacts_name on contacts(name desc);

如果字段name中有空值，则可以在创建索引时指定空值排在非空值前面：

CREATE INDEX idx_contacts_name on contacts(name desc NULLS FIRST);

也可以指定空值排在非空值后面：

CREATE INDEX idx_contacts_name on contacts(name desc NULLS LAST);

并发创建索引

通常情况下，在创建索引的时候PG会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引的操作。在此过程中，其他用户仍然可以读取表，但是插入、更新、删除等操作将一直被阻塞，直到索引创建完毕。

如果这张表示更新较频繁且比较大的表，那么创建索引可能需要几十分钟，甚至数个小时，这段时间内都不能做任何插入、删除、更新操作，这在大多数的在线数据库中都是不可接受的。

所以，PG支持在长时间阻塞更新的情况下建索引，通过在CREATE INDEX中加CONCURRENTLY选项来实现。

该选项PG会执行表的两次扫描，因此会需要更长的时间来建索引，但是它还是很有用的。

并发创建索引测试

image

create table jxx_test(id int primary key,note text);

insert into jxx_test select generate_series(1,5000000),generate_series(1,5000000);

同时开两个窗口，一个窗口执行创建索引，另一个窗口删除数据：
image
image

删除操作在创建完索引之后才会执行（图片上因为执行删除数据操作在创建索引发生之后才触发，有时间差）

继续同时开两个窗口，一个窗口执行创建索引（使用concurrently关键字），另一个窗口删除数：
image
image

创建索引时间虽然变长了，但是删除数据的操作不受阻塞，直接完成

并发重建索引

PG中，重建索引不支持CONCURRENTLY选项，但是PG中一个字段可以创建两个索引，所以并发重建索引可以执行以下步骤：

  1. 使用CONCURRENTLY选项建一个新的索引
  2. 删除旧索引

无效索引

创建索引过程需要注意，如果在创建索引过程中强行取消操作，会留下一个无效的索引：

  1. 仍然会导致更新速度变慢
  2. 如果是唯一索引，这个无效索引还会导致插入重复值失败

image
上述图片，可以通过shell窗口输入d+table的命令查看。

需要手动删除该索引：

drop index idx_jxx_test_note;

修改索引

alter index idx_jxx_test_note rename to idx_jxx_test_note_new;

image