目录

索引分类

  • B-Tree 最常用的索引，适合用于处理等值查询和范围查询 。
  • Hash 只能简单的处理等值查询。
  • GiST 不是单独的一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。常用于特殊类型字段。
  • SP-GiST(Space-Partitioned Gist) 空间分区GiST索引 ，针对GiST 索引的优化。
  • GIN 反转索引，可以处理包含多个键的值，如数组等。与GiST 类似，GIN 支持用户自定义的索引策略。包含操作符@> 被包含操作符<@ 相等操作符= 重叠操作符&& 等。

创建索引

CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING method] ({column_name | (expression) }

在创建索引的过程中会将表中的数据全部读一遍，该过程所用的时间是由表的大小决定的，在创建过程中可以正常读取，但是增、删、改等操作需要等索引创建完成后才能进行。

PostgreSQL 也支持并发的创建索引，在创建语句中加入CONCURRENTLY 选项实现。当加入该选项，PostgreSSQL会执行表的两次扫描，因此需要更长的时间来创建索引。

books表中对book_name增加索引

CREATE INDEX book_name_index on books(book_name);

books表中对author增加索引，author是一个数组，可以存储多个作者

CREATE INDEX author_index on books using gin(author);

使用author_index索引 查询

SELECT * FROM books where author @> array['68hub.com'::varchar];

创建索引时可以带一些参数:

  • 降序创建索引:

    CREATE INDEX book_no_index on books(book_no desc);

  • 设置空值的排序

    CREATE INDEX created_at_index on books(created_at DESC NULLS <FIRST,LAST>);

  • 并发的创建索引

    CREATE INDEX CONCURRENTLY book_name_index ON books(book_name);

创建并发索引时如果强行取消会流程一个无效的索引，这个索引仍然会影响数据的更新速度，仍然会起某些作用。

删除索引

DROP INDEX book_name_index;

DROP INDEX IF EXISTS <索引名称>