Postgresql 简明教程

PostgreSQL - INDEXES

索引是数据库搜索引擎可用来加速数据检索的特殊查找表。简而言之，索引是表中数据的指针。数据库中的索引与书本后面的索引非常相似。

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

索引有助于加快 SELECT 查询和 WHERE 子句；但是，它会使带有 UPDATE 和 INSERT 语句的数据输入变慢。索引可以在不影响数据的情况下创建或删除。

创建索引涉及使用 CREATE INDEX 语句，该语句允许你命名索引、指定表和要索引的列（一个或多个）以及指示索引是按升序还是降序。

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

The CREATE INDEX Command

CREATE INDEX 的基本语法如下：

CREATE INDEX index_name ON table_name;

Index Types

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

Single-Column Indexes

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

CREATE INDEX index_name
ON table_name (column_name);

Multicolumn Indexes

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

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

无论是要创建单列索引还是多列索引，都要考虑你在查询的 WHERE 子句中可能非常频繁地用作过滤条件的列。

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

Unique Indexes

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

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Partial Indexes

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

CREATE INDEX index_name
on table_name (conditional_expression);

Implicit Indexes

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

Example

以下是一个示例，我们将在 COMPANY 表的 salary 列上创建一个索引：

# 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 命令列出整个索引数据库 −

The DROP INDEX Command

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

基本语法如下 −

DROP INDEX index_name;

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

# DROP INDEX salary_index;

When Should Indexes be Avoided?

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

  1. 不应该在小表中使用索引。

  2. 频繁进行大批量更新或插入操作的表。

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

  4. 不应该编制经常操作的列的索引。