当数据库中表的数据量很大时，可以通过索引提升数据库性能，索引是一种特殊的数据结构，用来加速数据的查找。索引本身会占用额外的存储空间，是典型的通过空间换取时间效率。PostgreSQL 支持丰富的索引类型，比如 btree 索引，Hash 索引，GiST 索引，GIN 索引等等。

1. 索引类型

PostgreSQL 支持多种索引类型，不同的索引使用不同的算法适用于不同的场景，通常 B-tree 索引是使用最多也是最通用的一种索引类型。

PostgreSQL 常用的索引类型如下：

• 唯一索引（B-tree）
• B-tree 索引
• Hash 索引
• GiST 索引
• SP-GiST 索引
• GIN 索引
• BRIN 索引
• bloom 索引

2. 创建索引语法

创建一张表 t，如下：

create table t(id int, c1 int, c2 text);

（1）创建索引，默认为 B-tree 索引

create index on t(c1);

（2）创建索引，指定索引类型为 hash

create index on t using hash (c2);

（3）创建索引，指定索引名称

create index idx_c2 on t using btree (c2);

（4）创建多字段联合索引

create index idx_c1_c2 on t using btree (c1,c2);

（5）指定索引排序规则

PostgreSQL 可以指定索引字段的排序规则，如升序，降序，以及 null 值优先或者 null 值最后存储。

（6）bitmap组合多个索引

（7）函数索引、表达式索引

3. 查看索引

通过 \d+ 查看表结构，可以看到表上关联的索引，如下：

postgres=# \d+ t
                                     Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              |
 c1     | integer |           |          |         | plain    |              |
 c2     | text    |           |          |         | extended |              |
Indexes:
    "idx_c1_c2" btree (c1, c2)
    "t_c1_idx" btree (c1)
    "t_c2_idx" hash (c2)
Access method: heap

4. 删除索引语法

drop index idx_c1_c2;

索引名称 idx_c1_c2 可以通过 \d+ 查看表结构信息获取。

文章评论