在本实验中，你将学习如何通过索引优化 PostgreSQL 数据库性能。你将首先创建一个示例 users 表并填充数据。然后，你将创建一个单列索引，使用 EXPLAIN 分析查询计划，构建一个多列索引，最后学习如何移除未使用的索引以保持数据库效率。这种实践经验将为你提供 PostgreSQL 索引管理的实用技能。
在此步骤中，你将创建一个名为 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', 'john.doe@example.com', NOW()),
('jane_smith', 'jane.smith@example.com', NOW()),
('peter_jones', 'peter.jones@example.com', NOW()),
('mary_brown', 'mary.brown@example.com', NOW()),
('alice_wilson', 'alice.wilson@example.com', NOW()),
('bob_davis', 'bob.davis@example.com', NOW()),
('carol_garcia', 'carol.garcia@example.com', NOW()),
('david_martinez', 'david.martinez@example.com', NOW()),
('emma_anderson', 'emma.anderson@example.com', NOW()),
('frank_taylor', 'frank.taylor@example.com', NOW());
-- Insert additional rows to make the table large enough for index usage
INSERT INTO users (username, email, created_at)
SELECT
'user_' || generate_series(1, 1000),
'user' || generate_series(1, 1000) || '@example.com',
NOW();你现在已经向 users 表插入了超过 1000 行数据。这个更大的数据集将有助于更有效地演示索引的使用，因为 PostgreSQL 通常会在索引比扫描整个表提供性能优势时使用索引。
为了加速基于 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在此步骤中，你将学习如何在 PostgreSQL 中使用 EXPLAIN 命令来分析查询计划。理解查询计划对于优化数据库查询和确保高效性能至关重要。
首先，以 postgres 用户连接到 PostgreSQL 数据库：
sudo -u postgres psql现在，让我们使用 EXPLAIN 命令来分析一个简单的查询。执行以下命令：
EXPLAIN SELECT * FROM users WHERE email = 'jane.smith@example.com';此命令将显示 SELECT 语句的查询计划。对于我们的大型数据集，你应该会在输出中看到“Index Scan”或“Bitmap Index Scan”，这表明 PostgreSQL 正在使用 idx_users_email 索引来高效地定位具有特定电子邮件地址的行。
要获取更详细的信息，包括成本，你可以使用 EXPLAIN ANALYZE。但是，对于这个基本示例，EXPLAIN 就足够了。
现在，让我们分析一个不使用 email 索引的查询。执行以下命令：
EXPLAIN SELECT * FROM users WHERE username LIKE 'user_%';输出将显示对 users 表的“Seq Scan”（顺序扫描），这意味着 PostgreSQL 正在扫描整个表以查找匹配的行。发生这种情况是因为：
username 列上创建索引LIKE 操作符可以从索引中受益，但如果没有索引，PostgreSQL 必须扫描所有行这证明了在经常用于 WHERE 子句的列上创建索引的重要性。
通过使用 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 = 'user_1' AND email = 'user1@example.com';输出应该显示 PostgreSQL 正在使用 idx_users_username_email 索引来高效地执行查询。你应该在查询计划中看到“Index Scan”或“Bitmap Index Scan”，这表明多列索引正在被使用。
最后，通过输入以下命令退出 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 的统计信息收集器来收集有关索引使用情况的信息。但是，启用和分析这些统计信息超出了本实验的范围。在实际场景中，你会在决定删除索引之前，先监控一段时间的索引使用情况。
注意：PostgreSQL 的查询规划器非常智能，并且仅在索引能带来性能优势时才会使用它们。对于非常小的表（通常少于几百行），PostgreSQL 可能会选择顺序扫描而不是索引扫描，因为使用索引的开销超过了其带来的好处。这就是我们向 users 表添加了许多行——为了演示真实的索引使用场景。
删除错误的索引可能会对查询性能产生负面影响。因此，请谨慎操作，并在删除任何索引之前彻底分析你的查询模式。
最后，通过输入以下命令退出 psql shell：
\q在本实验中，你学习了如何在 PostgreSQL 中创建单列和多列索引以提高查询性能。你还学习了如何使用 EXPLAIN 分析查询计划，以确定你的索引是否被有效使用。主要收获包括：
这些技能对于在实际应用中优化 PostgreSQL 数据库性能至关重要。
