PostgreSQL 索引优化 - 提升数据库性能

介绍

在这个实验中，你将学习如何通过索引优化 PostgreSQL 数据库性能。你将从创建一个示例 users 表并用数据填充它开始。然后，你将创建一个单列索引，使用 EXPLAIN 分析查询计划，构建一个多列索引，最后，学习如何删除未使用的索引以保持数据库效率。这种实践经验将为你提供 PostgreSQL 索引管理方面的实用技能。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/idx_simple("Create Simple Index") postgresql/PostgreSQLGroup -.-> postgresql/idx_drop("Drop Existing Index") subgraph Lab Skills postgresql/table_init -.-> lab-550955{{"PostgreSQL 索引优化"}} postgresql/row_add -.-> lab-550955{{"PostgreSQL 索引优化"}} postgresql/data_where -.-> lab-550955{{"PostgreSQL 索引优化"}} postgresql/idx_simple -.-> lab-550955{{"PostgreSQL 索引优化"}} postgresql/idx_drop -.-> lab-550955{{"PostgreSQL 索引优化"}} end

创建单列索引

在这一步中，你将创建一个名为 users 的示例表，然后在 email 列上创建一个单列索引。索引对于提高数据库查询的性能至关重要，尤其是在处理大型表时。

首先，以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

现在，创建 users 表。执行以下 SQL 命令：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

此命令创建一个名为 users 的表，其中包含 id、username、email 和 created_at 列。id 列是主键并且自动递增。

接下来，将一些示例数据插入到 users 表中。执行以下 SQL 命令：

INSERT INTO users (username, email, created_at) VALUES
('john_doe', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('jane_smith', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('peter_jones', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('mary_brown', '[email protected]', NOW());

你现在已经将四行数据插入到 users 表中。

为了加速基于 email 列的查询，在 email 列上创建一个索引。执行以下 SQL 命令：

CREATE INDEX idx_users_email ON users (email);

此命令在 users 表的 email 列上创建一个名为 idx_users_email 的索引。

要验证索引是否已创建，你可以在 psql 中使用 \di 命令。执行以下命令：

\di

你应该在输出中看到列出的 idx_users_email 索引。

最后，通过键入以下内容退出 psql shell：

\q

使用 EXPLAIN 分析查询计划

在这一步中，你将学习如何在 PostgreSQL 中使用 EXPLAIN 命令来分析查询计划。理解查询计划对于优化数据库查询和确保高效性能至关重要。

首先，以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

现在，让我们使用 EXPLAIN 命令来分析一个简单的查询。执行以下命令：

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

此命令将显示 SELECT 语句的查询计划。输出显示 PostgreSQL 打算如何执行查询，包括它是否将使用索引。

为了获得更详细的信息，包括成本（cost），你可以使用 EXPLAIN ANALYZE。但是，对于这个基本示例，EXPLAIN 就足够了。

现在，让我们分析一个可能不使用索引的查询。执行以下命令：

EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

输出可能会在 users 表上显示一个“Seq Scan”（Sequential Scan，顺序扫描），这意味着 PostgreSQL 正在扫描整个表以查找匹配的行。这不如使用索引有效。

通过使用 EXPLAIN 分析查询计划，你可以识别潜在的性能瓶颈，并确定你的索引是否被有效地使用。

最后，通过键入以下内容退出 psql shell：

\q

构建多列索引

在这一步中，你将学习如何在 PostgreSQL 中创建多列索引。多列索引是包含两个或多个列的索引。当查询同时按多个列进行筛选或排序时，它可以显著提高查询性能。

首先，以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

假设你经常根据 username 和 email 列查询 users 表。为了优化这些查询，你可以在这两个列上创建一个多列索引。执行以下 SQL 命令：

CREATE INDEX idx_users_username_email ON users (username, email);

此命令在 users 表的 username 和 email 列上创建一个名为 idx_users_username_email 的索引。索引定义中列的顺序很重要。当查询按照索引中出现的相同顺序对列进行筛选时，该索引最有效。

要验证索引是否已创建，你可以在 psql 中使用 \di 命令。执行以下命令：

\di

你应该在输出中看到列出的 idx_users_username_email 索引。

现在，让我们分析一个可以从此多列索引中受益的查询。执行以下命令：

EXPLAIN SELECT * FROM users WHERE username = 'john_doe' AND email = '[email protected]';

输出应该显示 PostgreSQL 正在使用 idx_users_username_email 索引来执行查询。

最后，通过键入以下内容退出 psql shell：

\q

移除未使用的索引

在这一步中，你将学习如何在 PostgreSQL 中移除未使用的索引。索引可以提高查询性能，但它们也会消耗存储空间，并可能降低写入操作（插入、更新和删除）的速度。因此，识别并移除不再使用的索引非常重要。

首先，以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

假设在分析了你的查询模式后，你确定 idx_users_email 索引很少使用。要移除此索引，请执行以下 SQL 命令：

DROP INDEX idx_users_email;

此命令从数据库中移除名为 idx_users_email 的索引。

要验证索引是否已移除，你可以在 psql 中使用 \di 命令。执行以下命令：

\di

你应该不再在输出中看到列出的 idx_users_email 索引。

在删除索引之前，务必确保它确实未使用。你可以使用 PostgreSQL 的统计信息收集器（statistics collector）来收集有关索引使用情况的信息。但是，启用和分析这些统计信息超出了本实验（lab）的范围。在实际场景中，你需要在决定删除索引之前，监控一段时间的索引使用情况。

删除错误的索引可能会对查询性能产生负面影响。因此，在移除任何索引之前，请务必谨慎并彻底分析你的查询模式。

最后，通过键入以下内容退出 psql shell：

\q

总结

在本实验（lab）中，你学习了如何在 PostgreSQL 中创建单列索引和多列索引，以提高查询性能。你还学习了如何使用 EXPLAIN 分析查询计划，以确定你的索引是否被有效使用。最后，你学习了如何删除未使用的索引以保持数据库效率。这些技能对于优化 PostgreSQL 数据库性能至关重要。