PostgreSQL - 索引：初学者指南

你好，未来的数据库法师们！今天，我们将踏上一段激动人心的旅程，探索PostgreSQL索引的世界。如果你之前从未编写过一行代码，也不要担心——我会成为你的友好向导，我们将一起逐步探索这个主题。所以，拿起你最喜欢的饮料，让我们一起跳进去吧！

PostgreSQL - Indexes

索引是什么？

在我们深入细节之前，让我们从一个简单的类比开始。想象你在一个图书馆里寻找一本特定的书。如果没有任何组织系统，你可能需要翻遍每一本书才能找到你想要的。那会花费很多时间！但幸运的是，图书馆有索引——比如卡片目录或计算机系统——帮助你快速找到需要的书。

在数据库的世界里，索引扮演着类似的角色。它们是特殊的查找表，数据库搜索引擎可以使用它们来加速数据检索。本质上，索引是一种提高表操作速度的数据结构。

让我们创建一个简单的表来贯穿整个教程：

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);

INSERT INTO students (name, age, grade) VALUES
('Alice', 18, 'A'),
('Bob', 19, 'B'),
('Charlie', 20, 'A'),
('David', 18, 'C'),
('Eve', 19, 'B');

这创建了一个名为students的表，包含四列：id、name、age和grade。我们还添加了一些样本数据以便操作。

索引类型

现在我们有了表，让我们探索PostgreSQL提供的不同类型的索引。每种索引都有其自身的优势，适用于不同的场景。

1. B-tree索引

B-tree（平衡树）是PostgreSQL中的默认索引类型。它就像瑞士军刀一样的索引——多功能且适用于大多数情况。

让我们在name列上创建一个B-tree索引：

CREATE INDEX idx_student_name ON students USING BTREE (name);

这个索引对于搜索或排序name列的查询将非常有用。

2. 哈希索引

哈希索引专为等值比较优化。它们就像一本你可以快速查找到单词的字典。

让我们在age列上创建一个哈希索引：

CREATE INDEX idx_student_age ON students USING HASH (age);

这个索引将加快查找精确年龄匹配的查询，比如WHERE age = 18。

3. GiST索引

GiST（通用搜索树）索引对于全文搜索和索引几何数据或自定义数据类型非常有用。

以下是一个为全文搜索创建GiST索引的示例：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_student_name_gist ON students USING GIST (name gist_trgm_ops);

这个索引对于name列的部分匹配或相似度搜索将很有帮助。

4. GIN索引

GIN（通用倒排索引）索引最适合包含单个列中多个值的列，比如数组或JSON数据。

让我们向表中添加一个JSON列并创建一个GIN索引：

ALTER TABLE students ADD COLUMN hobbies JSONB;
CREATE INDEX idx_student_hobbies ON students USING GIN (hobbies);

这个索引将有助于在JSON数据中查询特定的爱好。

下面是一个总结这些索引类型的表格：

索引类型	最佳用途	示例用例
B-tree	通用目的，排序	按名称搜索或排序
哈希	等值比较	查找精确年龄匹配
GiST	全文搜索，几何数据	部分名称匹配
GIN	数组或JSON数据	在JSON字段中搜索

部分索引

有时，你只需要索引数据的一个子集。这时，部分索引就派上用场了。它们就像为书籍的特定部分创建索引。

让我们为成绩为'A'的学生创建一个部分索引：

CREATE INDEX idx_student_grade_a ON students (name) WHERE grade = 'A';

这个索引将加快专门查找'A'成绩学生的查询。

隐式索引

在某些情况下，PostgreSQL会自动创建索引。最常见的是当你定义一个PRIMARY KEY或UNIQUE约束时。

在我们的students表中，因为我们将id列定义为PRIMARY KEY，PostgreSQL自动创建了一个id列的索引。

DROP INDEX命令

正如我们可以创建索引一样，我们也可以在不再需要时删除它们。以下是如何删除一个索引的示例：

DROP INDEX idx_student_name;

使用这个命令时要小心——删除索引可能会显著影响查询性能，如果该索引正在被使用的话。

应该避免索引的情况

虽然索引可以极大地提高查询性能，但它们并不总是最佳解决方案。以下是一些在创建索引之前你可能要想两次的情况：

小表：如果一个表行数很少，全表扫描可能比使用索引更快。
经常更新的表：索引需要在表数据更改时更新，这可能会减慢写入操作。
低选择性列：如果一个列相对于总行数来说独特值很少，索引可能不太有用。
很少查询的表：如果一个表主要用于写数据且很少查询，维护索引的开销可能会超过其带来的好处。

记住，索引既是一门艺术，也是一门科学。它通常需要实验和性能测试来找到特定用例的正确平衡。

就这样，朋友们！我们已经穿越了PostgreSQL索引的土地，从基础到一些更高级的概念。我希望这个指南能帮助你揭开索引的神秘面纱。记住，熟能生巧，所以不要害怕在你们自己的数据库项目中尝试这些概念。

快乐索引，愿你的查询永远快速！

Credits: Image by storyset

上一教程:

PostgreSQL - 触发器

下一教程:

PostgreSQL - Alter Table 命令