PostgreSQL - 第 9 天 - 基本索引

課程簡介

索引是一種數據結構，能夠加速查詢的處理速度，尤其是在大規模數據集上。理解和使用索引能顯著提高資料庫的性能，今天我們將介紹基本索引的概念、創建方式以及如何選擇適合的索引。

---

索引概述

在資料庫中，索引是對資料表的某些欄位進行排序和結構化的一種方式。其目的是加速查詢的速度，特別是對大量數據進行查詢時。如果資料表中的某個欄位經常在 WHERE、JOIN、ORDER BY 或 GROUP BY 子句中被引用，則可以考慮對該欄位創建索引。

索引的優勢

• 加速查詢：索引能顯著提高對資料表的查詢速度，特別是當查詢條件中涉及到索引欄位時。
• 避免全表掃描：使用索引可以避免 PostgreSQL 在查詢時進行全表掃描，提高查詢效率。

索引的缺點

• 佔用空間：索引需要額外的存儲空間，尤其是對於大型資料表。
• 影響更新操作：對資料表進行 INSERT、UPDATE 或 DELETE 操作時，需要同時更新索引，這會影響寫入性能。

---

創建索引 (CREATE INDEX)

在 PostgreSQL 中，可以使用 CREATE INDEX 語句創建索引。基本語法如下：

CREATE INDEX 索引名 ON 表名 (欄位1, 欄位2, ...);

範例

1. 創建單一欄位索引

   假設我們有一個 員工 資料表，我們可以對 姓名 欄位創建索引：

   CREATE INDEX idx_員工_姓名 ON 員工 (姓名);
   

   這樣一來，對 姓名 欄位進行查詢時，PostgreSQL 就可以利用索引來加速查詢。

2. 創建多欄位索引

   假設我們經常會根據 職位 和 月薪 兩個欄位進行查詢，我們可以創建多欄位索引：

   CREATE INDEX idx_員工_職位_月薪 ON 員工 (職位, 月薪);
   

   當 WHERE 子句中包含 職位 和 月薪 時，PostgreSQL 可以更高效地查詢。

---

使用索引的查詢範例

創建索引後，當我們執行查詢時，資料庫管理系統會根據索引自動選擇是否使用索引來加速查詢。

範例

1. 使用索引進行查詢

   假設我們創建了 idx_員工_姓名 索引，當我們查詢 姓名 為「王小明」的員工時，PostgreSQL 會利用索引加速查詢：

   SELECT * FROM 員工 WHERE 姓名 = '王小明';
   

2. 使用多欄位索引進行查詢

   假設我們創建了 idx_員工_職位_月薪 索引，當我們查詢某個特定 職位 且月薪高於某一數值時，PostgreSQL 可以利用多欄位索引：

   SELECT * FROM 員工 WHERE 職位 = '工程師' AND 月薪 > 60000;
   

---

查看索引

要查看資料表中已創建的索引，可以查詢 pg_indexes 系統視圖。

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = '員工';

這會列出 員工 資料表中所有的索引名稱及其定義。

---

刪除索引 (DROP INDEX)

當索引不再需要時，或者為了節省存儲空間時，可以使用 DROP INDEX 語句刪除索引。

語法

DROP INDEX 索引名;

範例

刪除之前創建的 idx_員工_姓名 索引：

DROP INDEX idx_員工_姓名;

---

本日總結

今天我們學習了基本的索引概念和使用方法，並了解如何創建、查看和刪除索引。索引對於提高查詢效率至關重要，尤其是在處理大數據集時。選擇合適的欄位創建索引，能夠顯著提升資料庫的查詢性能。明天我們將學習如何使用聚合函數（如 COUNT、SUM 等）來處理和分析數據。