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会执行表的两次扫描，因此会需要更长的时间来建索引，但是它还是很有用的。
并发创建索引测试
create table jxx_test(id int primary key,note text);insert into jxx_test select generate_series(1,5000000),generate_series(1,5000000);
同时开两个窗口，一个窗口执行创建索引，另一个窗口删除数据：
删除操作在创建完索引之后才会执行（图片上因为执行删除数据操作在创建索引发生之后才触发，有时间差）
继续同时开两个窗口，一个窗口执行创建索引（使用concurrently关键字），另一个窗口删除数：
创建索引时间虽然变长了，但是删除数据的操作不受阻塞，直接完成
并发重建索引
PG中，重建索引不支持CONCURRENTLY选项，但是PG中一个字段可以创建两个索引，所以并发重建索引可以执行以下步骤：
- 使用CONCURRENTLY选项建一个新的索引
- 删除旧索引
无效索引
创建索引过程需要注意，如果在创建索引过程中强行取消操作，会留下一个无效的索引：
- 仍然会导致更新速度变慢
- 如果是唯一索引，这个无效索引还会导致插入重复值失败
上述图片，可以通过shell窗口输入\d+table的命令查看。
需要手动删除该索引：
drop index idx_jxx_test_note;
修改索引
alter index idx_jxx_test_note rename to idx_jxx_test_note_new;
