PostgreSQL 教程: CREATE INDEX 创建索引

九月 14, 2023

摘要：在本教程中，您将了解索引以及如何使用 PostgreSQL 的CREATE INDEX语句为表定义新索引。

电话簿类比和索引

假设您需要在电话簿中查找John Doe的电话号码。假设电话簿上的名字按字母顺序排列。要查找John Doe的电话号码，您首先查找姓氏Doe所在的页面，然后查找名字John，最后获取他的电话号码。

如果电话簿上的名字不是按字母顺序排列的，您就必须浏览所有页面并检查每个名字，直到找到John Doe的电话号码。这称为顺序扫描，您将遍历所有条目，直到找到您要查找的条目。

与电话簿类似，表中存储的数据应按特定顺序组织，以加快各种搜索速度。这就是索引发挥作用的原因。

根据定义，索引是一种独立的数据结构，它可以加快表上的数据检索速度，但需要额外的写入和存储来维护索引。

下面显示了CREATE INDEX语句的基本语法：

CREATE INDEX index_name ON table_name [USING method]
(
    column_name [ASC | DESC] [NULLS {FIRST | LAST }],
    ...
);

在这个语法中：

如果列包含 NULL，您可以指定NULLS FIRST或NULLS LAST选项。指定DESC时NULLS FIRST为默认值，未指定DESC时NULLS LAST为默认值。

要检查查询是否使用索引，请使用EXPLAIN语句。

我们将使用示例数据库中的address表进行演示。

address table

以下查询查找电话号码为223664661973的用户地址：

SELECT * FROM address
WHERE phone = '223664661973';

显然，数据库引擎必须扫描整个address表来查找地址，因为该phone列没有可用的索引。

要显示查询计划，请使用EXPLAIN语句，如下：

EXPLAIN SELECT *
FROM address
WHERE phone = '223664661973';

这是输出：

PostgreSQL CREATE INDEX - sequential scan example

要为address表phone列中的值创建索引，请使用以下语句：

CREATE INDEX idx_address_phone 
ON address(phone);

现在，如果再次执行查询，您会发现数据库引擎会使用索引进行查找：

EXPLAIN SELECT *
FROM address
WHERE phone = '223664661973';

下面显示了输出：

PostgreSQL CREATE INDEX - index scan example