PostgreSQL - 索引

---

---

索引是資料庫搜尋引擎可以使用以加速資料檢索的特殊查詢表。簡單來說，索引是指向表中資料的指標。資料庫中的索引與書籍後面的索引非常相似。

例如，如果您想參考書中討論某個特定主題的所有頁面，您必須首先參考索引，索引按字母順序列出所有主題，然後參考一個或多個特定頁碼。

索引有助於加快 SELECT 查詢和 WHERE 子句的速度；但是，它會減慢資料輸入、UPDATE 和 INSERT 語句的速度。可以建立或刪除索引，而不會影響資料。

建立索引涉及 CREATE INDEX 語句，該語句允許您命名索引，指定表以及要索引的列或列，並指示索引是升序還是降序。

索引也可以是唯一的，類似於 UNIQUE 約束，因為索引可以防止在存在索引的列或列組合中插入重複項。

CREATE INDEX 命令

CREATE INDEX 的基本語法如下：

CREATE INDEX index_name ON table_name;

索引型別

PostgreSQL 提供了幾種索引型別：B 樹、雜湊、GiST、SP-GiST 和 GIN。每種索引型別都使用不同的演算法，最適合不同型別的查詢。預設情況下，CREATE INDEX 命令建立 B 樹索引，這適合大多數常見情況。

單列索引

單列索引是僅基於一個表列建立的索引。基本語法如下：

CREATE INDEX index_name
ON table_name (column_name);

多列索引

多列索引是在表的多個列上定義的。基本語法如下：

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

無論建立單列索引還是多列索引，都要考慮在查詢的 WHERE 子句中可能經常用作篩選條件的列。

如果僅使用一列，則應選擇單列索引。如果在 WHERE 子句中經常使用兩列或多列作為篩選器，則多列索引將是最佳選擇。

唯一索引

唯一索引不僅用於效能，還用於資料完整性。唯一索引不允許將任何重複值插入表中。基本語法如下：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

部分索引

部分索引是在表的子集上構建的索引；子集由條件表示式（稱為部分索引的謂詞）定義。該索引僅包含滿足謂詞的那些錶行的條目。基本語法如下：

CREATE INDEX index_name
on table_name (conditional_expression);

隱式索引

隱式索引是在建立物件時由資料庫伺服器自動建立的索引。為主鍵約束和唯一約束自動建立索引。

示例

以下是一個示例，我們將為 COMPANY 表的 salary 列建立一個索引：

# CREATE INDEX salary_index ON COMPANY (salary);

現在，讓我們使用 \d company 命令列出 COMPANY 表上所有可用的索引。

# \d company

這將產生以下結果，其中 company_pkey 是一個隱式索引，在建立表時建立。

       Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

您可以使用 \di 命令列出整個資料庫範圍內的所有索引：

DROP INDEX 命令

可以使用 PostgreSQL DROP 命令刪除索引。刪除索引時應注意，因為效能可能會降低或提高。

基本語法如下：

DROP INDEX index_name;

您可以使用以下語句刪除先前建立的索引：

# DROP INDEX salary_index;

何時應避免使用索引？

儘管索引旨在增強資料庫的效能，但在某些情況下應避免使用它們。以下指南指示何時應重新考慮使用索引：

• 不應在小型表上使用索引。

• 具有頻繁、大型批處理更新或插入操作的表。

• 不應在包含大量 NULL 值的列上使用索引。

• 不應為經常操作的列建立索引。