探索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;