16.PG的索引基础

索引类似于书籍的目录，方便对表中的记录快速查找或者排序，但是建索引会有以下代价：

• 增减数据库的存储空间
• 在插入和更新数据时，所以也会被更新

索引的分类

• BTree：PG默认的索引类型，适合等值和范围查询
• Hash：只能处理简单的等职查询
• Gist:不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略

索引创建语法

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 [, ...] ) ]
    [ NULLS [ NOT ] DISTINCT ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

URL: https://www.postgresql.org/docs/16/sql-createindex.html

　　通常创建索引时，会把表的数据都读一遍，创建索引的时间长短一般和表的大小有关，创建索引时，不影响对表的查询，但是会阻塞对表的增删改操作。但是pg提供了一种并发创建索引的方法。

索引创建示例

postgres=# create table contacts(id int not null primary key,name varchar(40),phone varchar(32)[],addres text);   #创建一张表

postgres=# create index idx_name on contacts(name);   # 给字段name添加一个BTree索引。
CREATE INDEX
postgres=# 
postgres=# 
postgres=# \d contacts;
                      Table "public.contacts"
 Column  |          Type           | Collation | Nullable | Default 
---------+-------------------------+-----------+----------+---------
 id      | integer                 |           | not null | 
 name    | character varying(40)   |           |          | 
 phone   | character varying(32)[] |           |          | 
 address | text                    |           |          | 
Indexes:
    "contacts_pkey" PRIMARY KEY, btree (id)
    "idx_name" btree (name)       -- 这里可以查到

　　创建索引的时候可以指定存储参数“WITH (storage_parameter=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);

并发创建索引

　　启用并发创建索引时，因为需要对表进行两次扫描，所以可能创建的时间会更长，并发创建索引可以加参数：CONCURRENTLY 来实现。

postgres=# create table testdb(id int not null primary key,note text);
CREATE TABLE
postgres=# 
postgres=# create index concurrently  idx_testdb_note on testdb(note);        -- 注意这里的参数 concurrently
CREATE 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 [ NO ] 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 ]

URL: https://www.postgresql.org/docs/16/sql-alterindex.html

　　示例：　

postgres=# alter index idx_name rename to idx_name_old;  -- 重命名索引
ALTER INDEX
postgres=# \d contacts
                      Table "public.contacts"
 Column  |          Type           | Collation | Nullable | Default 
---------+-------------------------+-----------+----------+---------
 id      | integer                 |           | not null | 
 name    | character varying(40)   |           |          | 
 phone   | character varying(32)[] |           |          | 
 address | text                    |           |          | 
Indexes:
    "contacts_pkey" PRIMARY KEY, btree (id)
    "idx_name_old" btree (name)

postgres=# 
postgres=# alter index idx_name_old set(fillfactor=70);   -- 修改索引的填充因子
ALTER INDEX
postgres=# alter index idx_name_old reset(fillfactor);    -- 充值索引的填充因子
ALTER INDEX
postgres=#

删除索引

　　语法：

Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/16/sql-dropindex.html

　　如果索引“idx_contacts_name_old”存在则删除，如果不存在也不报错(在pg16上如果索引不存在会报错)：

　　删除索引时，默认使用选项“RESTRICT”，所以加不加关键字“RESTRICT”效果都是一样的，如果有对象依赖该索引，则会删除失败，而使用CASCADE选项表示当有依赖这个索引的对象时，一并把这些对象删除掉，如外键约束