PostgreSQL 创建索引

moonstar

本文介绍在 PostgreSQL 中如何使用 CREATE INDEX 向一个表中添加索引。

在 PostgreSQL 中，您可以使用 CREATE INDEX 向一个表中添加索引。
什么是索引

简单类比，索引可以看作是一个字典的目录。通过目录，您可以更快的查找词语。通过索引，您可以更快的从表中检索数据。索引是一种有序的数据结构，需要额外的空间存储。
PostgreSQL CREATE INDEX 语法

以下是 PostgreSQL CREATE INDEX 语句的简单语法：

CREATE [ UNIQUE ] INDEX [ [ IF NOT EXISTS ] name ]
    ON table_name [ USING method ]
(
    column_name [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
    [, ...]
);

说明：

    name 索引的名称。它是可选的。如果您不指定索引名称，PostgreSQL 将会自动生成一个。
    table_name 是要为其创建索引的表名。
    method 是索引方法的名称。包括 btree, hash, gist, spgist, gin, 和 brin。 btree 是默认的方法。您可以查看索引类型以了解更多。
    column_name 是要创建索引的列名。
    [ ASC | DESC ] 指定排序是顺序还是逆序。 它是可选的， ASC 是默认值。
    NULLS FIRST 或 NULLS LAST 指定排序时空值在非空值之前或之后。当指定 DESC 时, NULLS FIRST 是默认的，否则 NULLS LAST 是默认的。
    UNIQUE 指示创建一个唯一索引。
    IF NOT EXISTS 指示只有指定的索引名称不存在时才创建索引。

PostgreSQL 会对主键列自动创建索引。

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

如果您在一个索引中使用了多个列，那么此索引被称为多列索引或复合索引。
PostgreSQL CREATE INDEX 示例

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

以下是

以下查询查找邮编为 x 的地址：

SELECT * FROM address
WHERE postal_code = 'x';

由于该 postal_code 列没有可用的索引， PostgreSQL 不得不进行全表扫描。进可以通过查看查询计划来验证这一点。

要显示执行计划，请使用 EXPLAIN 如下语句：

EXPLAIN
SELECT * FROM address
WHERE postal_code = 'x';

                        QUERY PLAN
----------------------------------------------------------
Seq Scan on address  (cost=0.00..13.54 rows=1 width=161)
   Filter: ((postal_code)::text = 'x'::text)

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

CREATE INDEX ON address (postal_code);

这里没有指定索引名称，PostgreSQL 将会自动生成一个索引名称： address_postal_code_idx。 格式为 表名加列名再加 idx 后缀。

现在，再次查看执行计划，您会发现数据库引擎使用索引进行查找：

EXPLAIN
SELECT * FROM address
WHERE postal_code = 'x';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
   Index Cond: ((postal_code)::text = 'x'::text)

这里， using address_postal_code_idx 说明了 PostgreSQL 使用了索引进行查找。
结论

在 PostgreSQL 中，您可以使用 CREATE INDEX 向一个表中添加索引。