PostgreSQL - 索引

索引是数据库搜索引擎可以用来加速数据检索的特殊查找表。 简单地说，索引是指向表中数据的指针。 数据库中的索引与书后的索引非常相似。

例如，如果您想引用一本书中讨论某个主题的所有页面，您必须首先引用索引，该索引按字母顺序列出所有主题，然后引用一个或多个特定页码。

索引有助于加快 SELECT 查询和 WHERE 子句； 但是，使用 UPDATE 和 INSERT 语句会减慢数据输入速度。 可以创建或删除索引而不影响数据。

创建索引涉及 CREATE INDEX 语句，该语句允许您命名索引、指定表和要索引的列，以及指示索引是升序还是降序。

索引也可以是唯一的，类似于 UNIQUE 约束，因为索引可以防止列中的重复条目或存在索引的列组合。

CREATE INDEX 命令

CREATE INDEX的基本语法如下 −

CREATE INDEX index_name ON table_name;

索引类型

PostgreSQL 提供了几种索引类型：B-tree、Hash、GiST、SP-GiST 和 GIN。 每种索引类型使用最适合不同类型查询的不同算法。 默认情况下，CREATE INDEX 命令创建适合最常见情况的 B 树索引。

单列索引

单列索引是仅基于一个表列创建的索引。 基本语法如下 −

CREATE INDEX index_name
ON table_name (column_name);

多列索引

多列索引是在一个表的多个列上定义的。 基本语法如下 −

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

无论是创建单列索引还是多列索引，请考虑您可能在查询的 WHERE 子句中经常使用的列作为过滤条件。

如果只使用一列，则应选择单列索引。 如果在 WHERE 子句中经常使用两列或多列作为过滤器，多列索引将是最佳选择。

唯一索引

唯一索引不仅用于性能，还用于数据完整性。 唯一索引不允许将任何重复值插入到表中。 基本语法如下 −

CREATE UNIQUE INDEX index_name
on table_name (column_name);

部分索引

部分索引是建立在表子集上的索引； 子集由条件表达式（称为部分索引的谓词）定义。 索引仅包含满足谓词的那些表行的条目。 基本语法如下 −

CREATE INDEX index_name
on table_name (conditional_expression);

隐式索引

隐式索引是在创建对象时由数据库服务器自动创建的索引。 为主键约束和唯一约束自动创建索引。

示例

下面是一个示例，我们将在 COMPANY 表上为薪金列创建索引 −

# CREATE INDEX salary_index ON COMPANY (salary);

现在，让我们使用 \d company 命令列出 COMPANY 表上所有可用的索引。

# \d company

这将产生以下结果，其中 company_pkey 是一个隐式索引，它是在创建表时创建的。

       Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

您可以使用 \di 命令列出整个索引数据库范围−

DROP INDEX 命令

可以使用 PostgreSQL DROP 命令删除索引。 删除索引时应小心，因为性能可能会减慢或提高。

基本语法如下 −

DROP INDEX index_name;

您可以使用以下语句删除以前创建的索引 −

# DROP INDEX salary_index;

何时应避免使用索引?

虽然索引旨在提高数据库的性能，但有时应该避免使用索引。 以下指南指出何时应重新考虑索引的使用 −

• 不应在小表上使用索引。

• 具有频繁、大批量更新或插入操作的表。

• 不应在包含大量 NULL 值的列上使用索引。

• 不应为频繁操作的列编制索引。