优享资讯 | 在PostgreSQL中管理索引

掘金后端 ( ) • 2024-04-10 14:04

在PostgreSQL中管理索引

简介

在 PostgreSQL 中，索引是用于加速查询性能的关键数据库对象。没有索引，数据库可能需要对整个表进行扫描以找到相关的行，这通常被称为全表扫描，对于大型表来说，这种操作可能非常慢。通过使用索引，数据库可以更快地定位到所需的数据行。

选择使用哪种类型的索引类型取决于您正在索引的值。以下是关于 PostgreSQL 中支持的一些索引：

B-tree 索引：这是 PostgreSQL 的默认索引类型，适用于大多数查询。
Hash 索引：主要用于等值查询，但不如 B-tree 索引通用。
GiST 索引：用于支持各种数据类型上的多种操作符。
GIN 索引：用于支持包含“部分包含”关系的查询。
SP-GiST 索引：与 GiST 类似，但使用不同的算法。
BRIN 索引：块范围索引，适用于某些特定的查询模式。
Bitmap 索引：在某些情况下可能比 B-tree 索引更有效。
部分索引：只索引表中的一部分行。

到目前为止最常见的索引类型，也是Postgres中的默认类型，是B-Tree。B-Tree是二叉搜索树的一般形式，其中节点可以有两个以上的子节点。接下来我们就来实际操作一下如何在Postgres中使用B-Tree索引。

使用教程

注册登录MemFire Cloud平台，创建一个应用。应用创建成功后，即可获得一个云端的Postgres数据库。

创建数据表

首先创建一张persons数据表，persons 表包含 id（主键）、age（年龄）、height（身高）、weight（体重）和 name（姓名）这些字段的值。

create table persons (
  id bigint generated by default as identity primary key,
  age int,
  height int,
  weight int,
  name text
);

接下来，您可以插入一些示例数据到 persons 表中。

INSERT INTO persons (age, height, weight, name) VALUES  
(16, 170, 65, 'Alice'),  
(3, 185, 80, 'Bob'),  
(4, 168, 70, 'Charlie'),  
(17, 175, 85, 'David'),  
(32, 156, 70, 'Harry'), 
(10, 170, 85, 'Lily'), 
(21, 165, 70, 'Happy'),  
(60, 164, 55, 'Eve');

在SQL编辑器工具执行上述命令，在表编辑器中可以查看执行结果：

我们可能想经常根据年龄查询用户。没有索引，Postgres将扫描表中的每一行来找到年龄上的相等匹配。

select name from persons where age = 32;

您可以通过执行查询的explain来验证这一点：

explain select name from persons where age = 32;

输出结果如下：

创建索引

一旦您有了数据，就可以为 age 字段创建索引了。默认情况下，这将创建一个 B-tree 索引，它是 PostgreSQL 中最常用的索引类型。

CREATE INDEX persons_age_idx ON public.persons(age)

也可以在数据库-索引页面为age字段创建索引：

这将创建一个名为 persons_age_idx 的索引，用于加速基于 age 字段的查询。

现在，您的 persons 表已经创建完毕，包含了多条数据，并且在 age 字段上有了索引，这将有助于加速基于年龄的查询。记得在生产环境中，创建索引之前要评估其对写入性能的影响，因为索引会略微增加插入、更新和删除操作的开销。

这是我们刚刚创建的索引的简化图（请注意，在实践中，节点实际上有不止两个子节点）。

您可以看到，在任何大型数据集中，遍历索引以定位给定值的操作（O(log n)）比从上到下逐个扫描表中的每个值（O(n)）要少得多。

查看索引

您可以使用 \di 命令在 psql 命令行工具中查看数据库中的所有索引，或者使用 SQL 查询来验证索引是否已创建。

SELECT * FROM pg_indexes WHERE tablename = 'persons';

这将列出 persons表上的所有索引，包括您刚刚创建的 persons_age_idx。

部分索引

如果您经常查询行的子集，那么构建一个部分索引可能更有效。在我们的例子中，也许我们只想在height大于170的情况下匹配年龄。我们可以构建一个部分索引：

create index idx_height_persons_age on persons (age) where height > 170;

对索引进行排序

默认情况下，B-Tree索引按升序排序，但有时您可能想要提供不同的排序。也许我们的应用程序有一个页面展示最年长的前10个人。在这里，我们希望按降序排序，并包括NULL值在最后。为此我们可以使用：

create index idx_persons_age_desc on persons (age desc nulls last);

重新编制索引

过了一段时间，索引可能会变得过时，可能需要重建。Postgres提供了一个reindex命令来完成这个任务，但由于在这个过程中Postgres会对索引放置锁，您可能希望使用concurrent关键字。

reindex index concurrently idx_persons_age;

或者您可以重新索引特定表上的所有索引：

reindex table concurrently persons;

请注意，reindex可以在事务内部使用，但reindex [index/table] concurrently不能。

删除索引

如果你发现某个索引不再需要或没有提高查询性能，可以使用 DROP INDEX 语句删除它。总之，正确地使用和管理索引是优化 PostgreSQL 数据库性能的关键部分。但是，也需要谨慎地选择何时创建和删除索引，以避免不必要的开销。

注意事项

索引会占用额外的磁盘空间。
插入、更新和删除操作可能会变得更慢，因为索引也需要更新。
不是所有的查询都会使用索引。数据库优化器会基于统计信息和查询成本估计来决定是否使用索引。
有时，多个索引可能不如一个复合索引有效。例如，如果你经常按 column1 和 column2 的组合进行查询，那么创建一个 (column1, column2) 的复合索引可能比单独为这两个列创建索引更有效。