PostgreSQL 索引简介

假设有一个类似于以下的表：

CREATE TABLE test1 (
    id integer,
    content varchar
);

应用程序会发出许多形式如下所示的查询：

1	SELECT content FROM test1 WHERE id = 常量;

如果没有预先准备，系统将不得不逐行扫描整个 test1 表以找到所有匹配的记录。如果 test1 表中有许多行，而这样的查询只会返回少数几行（可能零行或一行），这显然是一个低效的方法。但如果系统已被指示在 id 列上维护一个索引，它可以使用一种更有效的方法来定位匹配的行。例如，它可能只需要深入搜索树的几层。

以下命令可用于在 id 列上创建索引，如前所述：

1	CREATE INDEX test1_id_index ON test1 (id);

索引名称 test1_id_index 可以自由选择，但你应该选择一个能让你日后记住该索引用途的名称。

要删除索引，可以使用 DROP INDEX 命令。索引可以在任何时候添加或删除。

一旦创建了索引，就不需要进一步干预：当表被修改时，系统会更新索引；当系统认为使用索引比顺序扫描表更高效时，它会在查询中使用索引。但你可能需要定期运行 ANALYZE 命令来更新统计信息，以便查询规划器做出明智的决策。

索引还可以使带有搜索条件的 UPDATE 和 DELETE 命令受益。此外，索引也可以用于连接查询，在连接条件中定义的列上的索引也可以显著加快带有连接的查询的速度。

一般来说，PostgreSQL 索引可以用于优化包含一个或多个 WHERE 或 JOIN 子句的查询，形式如下：

1	indexed-column indexable-operator comparison-value

这里，索引列是索引已定义在其上的列或表达式。可索引操作符是索引的列的操作符类的成员。比较值可以是任何非易变且不引用索引表的表达式。

在某些情况下，查询规划器可以从其他 SQL 构造中提取出这种形式的可索引子句。一个简单的例子是，如果原始子句是：

1	comparison-value operator indexed-column

那么只要原始操作符具有索引操作符类的交换操作符，就可以将其转换为可索引形式。

在大表上创建索引可能需要很长时间。默认情况下，PostgreSQL 允许在创建索引的同时对表进行读取（SELECT 语句），但写入（INSERT、UPDATE、DELETE）操作会被阻塞，直到索引构建完成。在生产环境中，这通常是不可接受的。允许在创建索引的同时进行写入操作是可能的，但需要注意几个注意事项，更多信息请参阅：

创建索引后，系统必须保持索引与表的同步。这会增加数据操作的开销。索引还会阻止仅堆元组的创建。因此，很少或从未在查询中使用的索引应该被删除。

PostgreSQL 索引简介

相关链接

OB links

OB tags