資料庫索引 (index) 的優點及缺點

為什麼需要索引？

在關聯式資料庫中，若沒有索引，查詢時必須逐列（row‑by‑row）掃描整張表（全表掃描 / Full Table Scan）。
建立索引就像在書本的目錄貼上標籤，讓資料庫能快速定位資料，降低 I/O 成本，提升查詢效率。

---

索引的主要類型

類型	主要結構	適用情境
B‑Tree (預設)	平衡搜尋樹	大多數範圍查詢、排序
Hash Index	雜湊表	精準比對 (=)
GiST / GIN	特殊索引	文字搜尋、GIS、JSONB
Bitmap Index	位元圖	大型資料倉儲、低寫入量，高查詢壓力

不同資料庫支援的索引型別略有差異，請以官方文件為準。

---

索引的優點

1. 加速查詢

   • O(log n) 級別搜尋（B‑Tree）代替 O(n) 全表掃描
   • 對高選擇性欄位（distinct 值多）效果最佳

2. 支援唯一性約束（UNIQUE）

   • 防止重複資料，同時利用索引查找衝突

3. 提升排序與分組效率

   • ORDER BY / GROUP BY 若使用索引涵蓋欄位，可避免額外排序

4. 覆蓋索引（Covering Index）

   • 查詢所需欄位全在索引中，省去回表（回到資料列）的成本

5. 優化關聯 / JOIN

   • 在外鍵、關聯欄位建索引，可顯著降低複雜查詢時間

---

索引的缺點

1. 寫入放大（Write Amplification）

   • INSERT / UPDATE / DELETE 必須同步更新索引
   • 高頻寫入場景可能因索引過多而拖慢整體效能

2. 額外儲存空間

   • 索引需要保存鍵值與指標，通常佔用資料表數倍空間
   • SSD 成本高或磁碟資源有限時需要權衡

3. 維護成本

   • 索引碎片化（Fragmentation）導致效能降低
   • 需定期 REINDEX / OPTIMIZE，佔用系統資源

4. 查詢規劃複雜度

   • 不適當或重疊的索引會混淆查詢最佳化器
   • 可能反而導致 sub‑optimal 計劃

5. 鎖定與競爭

   • 部分資料庫在建立／重建索引時需鎖表或消耗大量 CPU
   • 線上重建（Online Rebuild）仍有資源競爭風險

---

建立索引的最佳實務

1. 以查詢為核心

   • 依實際慢查詢（slow query log）或 EXPLAIN 報告決定索引策略
   • 先優化 schema 與 SQL，再考慮添加索引

2. 選擇性 > 0.2

   • 選擇性（distinct/total rows）太低的欄位通常不該單獨建索引
   • 可考慮複合索引或前綴索引

3. 覆蓋索引優先

   • 常見查詢僅讀取少量欄位時，可設計覆蓋索引取得最大效益

4. 觀察寫入壓力

   • 高寫入 OLTP 系統：少量、精準索引
   • 查詢為主 OLAP / 報表系統：可接受多重索引

5. 定期審計與重建

   • 使用 pg_stat_user_indexes、mysql.innodb_index_stats 等檢視未使用索引
   • 失效或冗餘索引要及時移除

---

範例：PostgreSQL 建立複合索引

-- 針對 (status, created_at) 的高頻查詢
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

注意：複合索引遵循最左前綴原則，若只查 created_at 而不含 status，此索引將無法被使用。

---

面試回答

索引的優點有：

1. 提升查詢效率

   • 對於常用在 WHERE、JOIN、ORDER BY 的欄位建立索引，可以大幅降低查詢時間，從全表掃描降到數筆查找。

2. 支援唯一性限制

   • 像是 UNIQUE 或主鍵索引可以避免重複資料。

3. 覆蓋查詢（Covering Index）

   • 若查詢所需欄位都包含在索引中，可直接從索引取得資料，省略回表動作。

---

索引的缺點有：

1. 寫入效能降低

   • 每次 INSERT、UPDATE、DELETE 都要同步更新相關索引，會增加寫入成本。

2. 占用額外空間

   • 索引是額外資料結構，尤其在高頻查詢、大資料量的系統中，可能需要數倍的空間。

3. 維護成本

   • 索引太多可能會混淆查詢最佳化器（optimizer），導致選錯查詢路徑。也可能隨時間產生碎片，影響效能。

---

補充實務觀點（加分項）：

平常會配合 EXPLAIN 去分析查詢是否有用到索引，避免建立太多沒被使用的索引。也會觀察查詢選擇性（如某欄位是否夠分散），確保索引是有價值的。

---

結論

索引是資料庫效能調校的雙刃劍：

• 讀取性能 與 寫入開銷 必須取得平衡
• 透過 持續監控、資料分佈分析 和 業務場景理解，才能打造最貼合需求的索引策略

行動項目：

1. 立刻檢查慢查詢日誌，找出無索引的查詢。
2. 使用 EXPLAIN ANALYZE 確認索引是否生效。
3. 訂定週期性索引健康檢查排程。