PostgreSQL索引 postgresql索引会影响插入速度吗

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

索引分类

在PostgreSQL中，支持以下几类索引：

• B-tree：最常用的索引，B-tree索引适合处理等值查询和范围查询
• Hash：智能处理简单的等值查询
• GiST：不是一种单独的索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。GiST索引定义的特定操作符可以用于特定索引策略。PostgreSQL的标准发布中包含了用于二维几何数据类型的GiST操作符类。比如，一个图形包含另一个图形的操作符"@>",一个图形在另一个图形的左边且没有重叠的操作符"<<",等等。
• SP-GiST: SP-GiST是"space-partitioned GiST"的缩写，即空间分区GiST索引。它是从PostgreSQL9.2版本开始提高了一种新索引类型，主要是通过一些新的索引算法提高GiST索引在某个情况下的性能。
• 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 [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
     [ INCLUDE ( column_name [, ...] ) ]
     [ WITH ( storage_parameter [= value] [, ... ] ) ]
     [ TABLESPACE tablespace_name ]
     [ WHERE predicate ]

一般在创建索引的过程中，会把表的数据都读一遍，这个过程所用时间由表的大小决定，对于比较大的表，可能会花很久的时间。

在创建索引的过程中，对表的查询可以正常运行，但对表的增删改等操作需要等索引建完后才能进行，为此PostgreSQL提供了一种并发创建索引的方法。

如何在不同的情况下创建索引？

 假设有一个联系人的表
 CREATE TABLE contacts(
     id int primary key,
     name varchar(40),
     phone varchar(32)[],
     address text
 );
 在该表中，由于一个人可能有多个电话号码，所以把phone定义为一个数组。
 
 为了实现按姓名快速查询，可以在字段name上建一个简单的B-tree索引
 CREATE INDEX idx_contacts_name on contacts(name);
 
 插入数据
 INSERT INTO contacts VALUES(1,'塞尔达',ARRAY['13422222221','13422222222'],'海拉鲁城堡');
 INSERT INTO contacts VALUES(2,'林克',ARRAY['13411111111','13411111112'],'海拉鲁城堡');
 INSERT INTO contacts VALUES(3,'英达',ARRAY['13433333333','13433333334'],'英拉村');
 
 如果想按电话号码(phone)字段做快速查询，比如，想查询一个电话号码是谁的，由于此字段是一个数组，前面所建的B-tree索引不再起作用，因此这时可以建一个GIN索引
 CREATE INDEX idx_contacts_phone on contacts using gin(phone);
 这时就可进行快速查询了。假设想查询号码"13422222222"是谁的，查询
 SELECT * FROM contacts WHERE phone @> array['13422222222'::varchar(32)];
 注意 "@>"是数组操作符，表示包含。GIN索引能在"@>"上起作用。
 
 Hash索引的更新不会记录到WAL日志中，索引在实际场景中应用较少。
 
 创建索引时指定存储参数"WITH(storage_parameter=value)" ,常用的存储参数为FILLFACTOR。
 CREATE INDEX idx_contacts_name_01 on contacts(name) WITH (FILLFACTOR=50);
 
 也可以按降序建索引
 CREATE INDEX idx_contacts_name_02 on contacts(name desc);
 
 如果时字段name 中有空值，可以在建索引时，指定空值排在非空值前面
 CREATE INDEX idx_contacts_name_03 on contacts(name DESC NULLS FIRST);
 或空值排在非空值后面
 CREATE INDEX idx_contacts_name_04 on contacts(name DESC NULLS LAST);
 

并发创建索引

通常，在创建索引的说话PostgreSQL会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引操作。

在此过程中，其他用仍然可以读取表，但是插入、更新、删除等操作将被一直阻塞，知道索引创建完毕。如果这张表更新比较频繁，且表比较大，那么创建索引可能需要几十分钟，甚至数个小时，这段时间内都不能做任何的插入、删除、更新，这在大多数的在线数据库中都是不可接受的行尾。鉴于此，PostgreSQL支持不长时间阻塞更新的情况下创建索引，这是通过在CREATE INDEX中加CONCURRENTLY(并发创建索引)选项来实现的。当该选项被使用时，PostgreSQL会执行表的两次扫描，因此该方法需要更长一些的时间来建索引。尽管如此，这个选项也是很有用的一个功能。

 测试表
 CREATE TABLE test01(id int primary key, note text);
 
 插入数据
 INSERT INTO test01 select generate_series(1,500000), generate_series(1,500000);
 
 这时开两个psql的窗口，在其中一个创建中建索引：
 psql postgres
 \timing
 CREATE INDEX idx_test01_note on test01(note);
 
 另一个窗口中删除一条数据，可以看到，它一直在等另一个窗口中创建索引的操作完成：
 psql postgres
 \timing
 DELETE FROM test01 where id=1;
 
 如果创建索引时加上"CONCURRENTLY"选项
 DROP INDEX idx_test01_note;
 CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
 
 另一个窗口的删除操作不会出现等待
 DELETE FROM test01 where id=2;
 DELETE FROM test01 where id=3;
 
 一个表经过频繁更新后，如果想重建其上的索引该怎么做？
 要知道，在PostgreSQL中重建索引不支持"CONCURRENTLY"选项，但它支持在同个字段中建两个索引，因此可以考虑这样做：使用"CONCURRENTLY"选项建一个新的索引，
 然后把旧索引删除掉，这样就相对于重建了这个索引
 CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
 
 CREATE INDEX CONCURRENTLY idx_test01_note_2 on test01(note);
 
 DROP INDEX idx_test01_note;
 
 db01=# \d test01
                Table "public.test01"
  Column |  Type   | Collation | Nullable | Default
 --------+---------+-----------+----------+---------
  id     | integer |           | not null |
  note   | text    |           |          |
 Indexes:
     "test01_pkey" PRIMARY KEY, btree (id)
     "idx_test01_note_2" btree (note)
 
 并发创建索引的时候需要注意，如果索引在创建过程中被强制取消，可能会留下一个无效索引，这个索引仍然会导致更新变慢。
 如果创建的是唯一索引，这个无效的索引还会导致插入重复值失败，测试示例如下：
 
 先在索引创建过程中取消操作
 CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
 ^CCancel request sent
 ERROR:  canceling statement due to user request
 
 然后使用\d查看表，可以看到遗留一个INVALID索引:
 db01=# \d test01
                Table "public.test01"
  Column |  Type   | Collation | Nullable | Default
 --------+---------+-----------+----------+---------
  id     | integer |           | not null |
  note   | text    |           |          |
 Indexes:
     "test01_pkey" PRIMARY KEY, btree (id)
     "idx_test01_note" btree (note) INVALID
     "idx_test01_note_2" btree (note)
 
 这时，若插入重复数据，此无效唯一索引的约束仍然有效，如下：
 INSERT INTO test01 VALUES(10,'10');
 ERROR:  duplicate key value violates unique constraint "test01_pkey"
 DETAIL:  Key (id)=(10) already exists.
 
 对于此，手工删除索引就可以了。
 DROP INDEX idx_test01_note;

修改索引

语法:

 ALTER INDEX name RENAME TO new_name
 ALTER INDEX name SET TABLESPACE tablespace_name

例子:

 给索引改名
 ALTER INDEX idx_contacts_name RENAME TO idx_contacts_name_old;
 
 把索引移到表空间tbs_data01下
 ALTER INDEX idx_contacts_name_old SET TABLESPACE tbs_data01;
 
 把索引的填充因子(fillfactor)设置为50
 ALTER INDEX idx_contacts_name_old SET (fillfactor=75);
 
 把索引的填充因子重置为默认值
 ALTER INDEX idx_contacts_name_old RESET (fillfactor);
 
 查看索引的信息
 db01=# \d+ idx_contacts_name_old
                      Index "public.idx_contacts_name_old"
  Column |         Type          | Key? | Definition | Storage  | Stats target
 --------+-----------------------+------+------------+----------+--------------
  name   | character varying(40) | yes  | name       | extended |
 btree, for table "public.contacts"
 

删除所有

语法:

 DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

例子：

 确认索引是否存在，若存在则删除，若不存在，将不报错
 DROP INDEX IF EXISTS idx_contacts_name_old;
 
 DROP INDEX IF EXISTS idx_contacts_name_old;
 NOTICE:  index "idx_contacts_name_old" does not exist, skipping
 DROP INDEX
 
 删除索引时，默认会使用选项"RESTRICT",如果有依赖对象依赖这个索引，则删除会失败。而使用"CASCADE"选项，表示当有依赖于这个索引的对象时，一并把这些对象删除掉，例如外键约束。
 CREATE TABLE class(
 class_no int,
 class_name varchar(40)
 );
 
 CREATE UNIQUE INDEX index_unique_class_no ON class(class_no);
 
 CREATE TABLE student(
 student_no int primary key,
 student_name varchar(40),
 age int,
 class_no int REFERENCES class(class_no)
 );
 
 如果表"student"上的外键引用了表"class"上的唯一索引"index_unique_class_no",这时删除此索引将会失败
 DROP INDEX index_unique_class_no;
 ERROR:  cannot drop index index_unique_class_no because other objects depend on it
 DETAIL:  constraint student_class_no_fkey on table student depends on index index_unique_class_no
 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 
 此时，加上CASCADE即可删除成功
 DROP INDEX index_unique_class_no CASCADE;