PostgreSQL 教程: CREATE INDEX 创建索引

九月 14, 2023

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

电话簿类比和索引

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

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

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

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

PostgreSQL CREATE INDEX 概述

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

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

在这个语法中:

  • 首先,在CREATE INDEX子句后指定索引名称。索引名称应该有意义且易于记忆。
  • 其次,指定索引所属的表的名称。
  • 第三,指定索引方法,例如btreehashgistspgistginbrin。PostgreSQL 默认使用btree
  • 最后,列出索引的一列或多列。ASCDESC指定排序顺序。默认排序顺序是ASC

如果列包含 NULL,您可以指定NULLS FIRSTNULLS LAST选项。指定DESCNULLS FIRST为默认值,未指定DESCNULLS LAST为默认值。

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

PostgreSQL CREATE INDEX 语句示例

我们将使用示例数据库中的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

要为addressphone列中的值创建索引,请使用以下语句:

CREATE INDEX idx_address_phone 
ON address(phone);

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

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

下面显示了输出:

PostgreSQL CREATE INDEX - index scan example

概括

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

  • 使用CREATE INDEX语句创建索引。