什么是索引

索引是数据库中一种快速查询数据的方法，索引是对数据库中一列或几列的数据按照特定的数据结构进行排序保存的一种方式。记录了表中一列或多列值与其物理位置之间的对应关系，类似一本书的目录。

索引的优缺点

优点：

加快对表记录的查找或排序。除此之外，索引还具有其他用例，例如唯一索引可以起到唯一约束的作用

缺点：

1）增加了数据库的存储空间。

2）在修改和插入数据时索引也需要更新，需要花费更多的时间。

索引的分类

• BTree： postgres里的默认索引方式，适用于处理等值查询和范围查询（简单来说，等号(=)和范围操作符 (<, <=, >, >=, BETWEEN, and IN)）

• HASH： 只能处理简单的等值查询，HASH索引性能不比BTree强，HASH索引在PG10之前不写WAL日志，因此如果发生了崩溃，可能需要用REINDEX重建HASH索引，一般情况下HASH索引都可以用BTree索引替代，但某些情况，例如字段过长时仅能使用HASH索引，使用BTree索引报错。

  例：创建test_index表，插入一行数据，使用update语句增加列长，之后创建索引

postgres=# create table test_index(a int , b text);
CREATE TABLE
postgres=# insert into test_index values(1, now());
INSERT 0 1
--多次执行下述update语句
postgres=# update test_index set b=b||b;
UPDATE 1
postgres=# update test_index set b=b||b;
UPDATE 1
.
.
.
--创建btree索引失败，创建hash索引成功
postgres=# create index t_index on test_index using btree (b);
ERROR:  index row requires 1392392 bytes, maximum size is 8191
postgres=# create index t_index on test_index using hash (b);
CREATE INDEX

• GiST： 广义搜索树的缩写，不是一种单独的索引类型，而是一种架构，可以在该架构上实现多种不同的索引策略。GiST索引定义的特定操作符可以用于特定索引策略。

  优缺点

  和Btree索引相比，BTree能够支持大于，小于和等于运算符。但是，当数据库存储的数据是地理数据，文本文档，图像等数据的时候，btree是无效的。GiST索引缺点在于创建耗时较长，占用空间也比较大。

• SP-GiST： 空间分区GiST索引，PG9.2开始提供的一种新索引类型，通过一些新的索引算法提高GiST索引在某些情况的性能。

• GIN： 反转索引或者叫倒排索引，可以处理包含多个键的值，如数组。GIN的主要应用领域是加速全文搜索。与GiST类似，但比GiST占用更多空间，支持用户定义的索引策略。支持包含操作符"@>"、被包含操作符"<@"、相等操作符"="、重叠操作符"&&"等

创建索引

创建索引会将表的所有数据读一遍，所以对于较大的表来说，创建索引可能会花比较长的时间，而在创建索引这个过程中，对表的增、删、改等操作将会在索引完成后进行。

语法：

Command:     CREATE INDEX
Description: define a new index
Syntax:
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 ]

示例：

postgres=# create table test(a int ,b varchar(10), c varchar(10)[]);
CREATE TABLE
postgres=# insert into test values(1,'aaa','{123,234}');
INSERT 0 1
postgres=# insert into test values(2,'bbb','{345,567}');
INSERT 0 1
postgres=# create index test_a on test(a);
CREATE INDEX
postgres=# create index test_c on test using gin(c);
CREATE INDEX

postgres=# \d+ test
                                            Table "public.test"
 Column |          Type           | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-------------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer                 |           |          |         | plain    |              |
 b      | character varying(10)   |           |          |         | extended |              |
 c      | character varying(10)[] |           |          |         | extended |              |
Indexes:
    "test_a" btree (a)
    "test_c" gin (c)
Access method: heap

postgres=# select * from test where c @> array['123'::varchar(10)];
 a |  b  |     c
---+-----+-----------
 1 | aaa | {123,234}
(1 row)

其余创建索引示例

--指定填充因子创建索引
postgres=# drop index test_a ;
DROP INDEX
postgres=# create index test_a on test(a) with (FILLFACTOR=50);
CREATE INDEX

--降序创建索引
postgres=# drop index test_a ;
DROP INDEX
postgres=# create index test_a on test(a desc) with (FILLFACTOR=50);
CREATE INDEX

--指定空值排在非空值前面/后面
postgres=# drop index test_a ;
DROP INDEX
postgres=# create index test_a on test(a desc NULLS FIRST) with (FILLFACTOR=50);
CREATE INDEX
postgres=# drop index test_a ;
DROP INDEX
postgres=# create index test_a on test(a desc NULLS LAST) with (FILLFACTOR=50);
CREATE INDEX

注：填充因子可参考www.modb.pro/db/52346

并发创建索引

创建索引会锁表防止写入，直到索引创建成功。为了解决这个问题，创建索引时可添加CONCURRENTLY选项，当设置了该选项后，PG会执行表的两次扫描，所以该方法需要更多时间建索引。

示例

--开两个session，不并发创建索引
--session1创建索引
postgres=# insert into test values(generate_series(1,5000000),'bbb','{345,567}');
INSERT 0 5000000
Time: 14208.445 ms (00:14.208)
postgres=# create index test_a on test(a desc NULLS LAST) with (FILLFACTOR=50);
CREATE INDEX
Time: 5692.868 ms (00:05.693)
--session2删除数据
postgres=# delete from test where a=2;
DELETE 2
Time: 4820.448 ms (00:04.820)

--开两个session，并发创建索引
--seesion创建索引，时长明显增加
postgres=# create index concurrently test_a on test(a desc NULLS LAST) with (FILLFACTOR=50);
CREATE INDEX
Time: 8445.574 ms (00:08.446)
--session2删除数据
postgres=# delete from test where a=3;
DELETE 1
Time: 1345.016 ms (00:01.345)

注意：并发创建索引时取消可能会留下一个无效索引，该索引会导致更新变慢，如果是唯一索引，该无效索引还会导致插入重复值失败。

修改索引

Command:     ALTER INDEX
Description: change the definition of an index
Syntax:
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name ATTACH PARTITION index_name
ALTER INDEX name DEPENDS ON EXTENSION extension_name
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number
    SET STATISTICS integer
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

删除索引

Command:     DROP INDEX
Description: remove an index
Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

参考《PostgreSQL修炼之道 从小工到专家 第2版》