Postgresql 简明教程

PostgreSQL - INDEXES

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

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

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

For example, if you want to reference all pages in a book that discusses a certain topic, you have to first refer to the index, which lists all topics alphabetically and then refer to one or more specific page numbers.

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

An index helps to speed up SELECT queries and WHERE clauses; however, it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

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

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

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

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there’s an index.

The CREATE INDEX Command

CREATE INDEX 的基本语法如下：

The basic syntax of CREATE INDEX is as follows −

CREATE INDEX index_name ON table_name;

Index Types

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

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. Each Index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

Single-Column Indexes

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

A single-column index is one that is created based on only one table column. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column_name);

Multicolumn Indexes

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

A multicolumn index is defined on more than one column of a table. The basic syntax is as follows −

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

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

Whether to create a single-column index or a multicolumn index, take into consideration the column(s) that you may use very frequently in a query’s WHERE clause as filter conditions.

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

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the multicolumn index would be the best choice.

Unique Indexes

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

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows −

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Partial Indexes

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

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. The basic syntax is as follows −

CREATE INDEX index_name
on table_name (conditional_expression);

Implicit Indexes

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

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Example

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

The following is an example where we will create an index on COMPANY table for salary column −

# CREATE INDEX salary_index ON COMPANY (salary);

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

Now, let us list down all the indices available on COMPANY table using \d company command.

# \d company

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

This will produce the following result, where company_pkey is an implicit index, which got created when the table was created.

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

You can list down the entire indexes database wide using the \di command −

The DROP INDEX Command

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

An index can be dropped using PostgreSQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

基本语法如下 −

The basic syntax is as follows −

DROP INDEX index_name;

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

You can use following statement to delete previously created index −

# DROP INDEX salary_index;

When Should Indexes be Avoided?

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

Although indexes are intended to enhance a database’s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered −

  1. Indexes should not be used on small tables.

  2. Tables that have frequent, large batch update or insert operations.

  3. Indexes should not be used on columns that contain a high number of NULL values.

  4. Columns that are frequently manipulated should not be indexed.