介紹

隨著 Web 應用程式的擴展和資料量的成長，後端系統經常面臨查詢延遲等資料庫瓶頸問題。

在沒有適當索引優化的情況下，在具有數十萬筆記錄的表上執行聯接 (JOIN) 操作或複雜的搜尋查詢可能會導致資料庫 CPU 峰值，從而導致最終用戶的回應時間變慢。

設計資料庫索引是解決這些效能瓶頸的有效方法。本文解釋了資料庫索引的工作原理，詳細介紹了 B 樹結構，並分享了設計有效索引的指南。

1. 為什麼索引可以加快查詢速度？

將資料庫索引視為教科書後面的索引部分。

如果沒有索引，搜尋特定主題需要您掃描從開始到餘韻的每一頁。在資料庫中，這稱為全表掃描（All Table Scan）。隨著資料量的增加，這種線性掃描過程需要更長的時間。

透過在特定列上建立索引，資料庫以排序結構組織資料並記錄對實體行的參考（指標）。這使得查詢引擎可以快速找到目標記錄。

2. B 樹索引的機制

關聯式資料庫（如 MySQL 和 PostgreSQL）通常使用B-Tree（平衡樹）索引結構。

B 樹將鍵組織成由根節點、中間節點和葉節點組成的平衡樹佈局。

         [Root Node]
         /         \
     [100]         [200]
     /   \         /   \
  [...]  [...]   [...] [Leaf Nodes] -> References to physical rows

如何執行搜尋

1. 搜尋查詢從頂級根節點開始。
2. 查詢引擎將目標鍵值與節點值進行比較，以確定要遵循哪個子分支指標。
3. 這個過程向下重複到葉節點，葉節點包含指向磁碟上實體行的指標（如 ROWID），允許引擎取得匹配的記錄。

• 好處： 無論表格成長多大，搜尋複雜度都會以 O(log N) 對數縮放。這使得查找時間變快，通常在幾毫秒內解析。

3. 設計資料庫索引的三個準則

雖然索引可以提高搜尋速度，但在每一列上建立索引會適得其反。每個寫入作業（INSERT、UPDATE、DELETE）都需要更新對應的索引結構，這會增加開銷，從而降低寫入效能。

使用這三個準則來決定何時建立索引：

準則 1：具有高基數的目標列（選擇性）

基數是指儲存在列中的唯一值的數量。

• 高基數（建議用於索引）： user_id、email 或 serial_number 等列，其中值是唯一的或很少重複。
• 低基數（不建議用於索引）： 諸如 gender 或 status 標誌之類的欄位。對大多數值重複的列建立索引效率很低，因為查詢引擎通常會恢復到全表掃描來檢索資料。

準則 2：遵守綜合指數中從左到右的順序

建立複合索引（跨越多個列的索引）時，定義中列的順序至關重要。

例如，如果將索引定義為 INDEX (category_id, created_at)：

• 索引將最佳化查詢，例如： WHERE category_id = 5 （符合第一列） WHERE category_id = 5 AND created_at > '2025-01-01' （符合兩個欄位）
• 索引不會最佳化如下查詢： WHERE created_at > '2025-01-01' （繞過第一列）

只有當查詢依照索引中的定義從左到右匹配列時，複合索引才有效。

準則 3：索引連接鍵和排序列

除了搜尋條件（WHERE 子句）之外，也會對連接操作中使用的外鍵（JOIN ON）和用於排序的欄位（ORDER BY）進行索引。這有助於資料庫引擎直接從索引檢索排序的數據，從而避免昂貴的基於記憶體的排序操作。

結論

設計資料庫索引是後端效能調優的關鍵步驟。

1. **分析查詢執行計劃以識別具有高基數的列以進行索引。 **
2. **設計與查詢過濾器從左到右順序相符的複合索引。 **
3. **避免冗餘索引以保持最佳寫入效能。 **

在開發過程中，在 SQL 用戶端中使用 EXPLAIN 語句來驗證您的查詢是否如預期般使用索引。