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 表中，PostgreSQL 自動在 id 列上創建了索引，因為我們將其定義為 PRIMARY KEY。

DROP INDEX 命令

正如我們可以創建索引一樣，我們也可以在不再需要時刪除它們。下面是如何刪除索引的方法：

DROP INDEX idx_student_name;

使用這個命令要小心——刪除索引可能會對查詢性能產生重大影響，如果該索引正在被使用。

什麼時候應避免使用索引？

雖然索引可以大大提高查詢性能，但它們並不是在所有情況下都是最佳解決方案。以下是一些你可能想要重新考慮創建索引的情況：

小表：如果表中的行數很少，全表掃描可能比使用索引更快。
頻繁更新的表：索引需要在表數據變化時更新，這可能會減慢寫操作。
選擇性低的文章：如果一列的獨特值相對於總行數很少，索引可能不會很有幫助。
很少查詢的表：如果表主要用於寫入數據且很少被查詢，維護索引的開銷可能會超過其好處。

記住，索引是藝術和科學的結合。它通常需要實驗和性能測試來為你的特定用例找到正確的平衡。

至此，各位！我們已經穿越了 PostgreSQL 索引的土地，從基礎到一些更先進的概念。我希望這個指南能夠幫助你解開索引的神秘。記住，熟能生巧，所以不要害怕在你的數據庫項目中實驗這些概念。

愉快地進行索引操作，願你的查詢總是迅速！

Credits: Image by storyset

上一教程:

PostgreSQL - 觸發器

下一教程:

PostgreSQL - Alter Table 指令