索引是查詢效能的加速器，也是寫入效能的負擔。本文建立索引決策框架：五大創建目的、三大維護代價、六類強烈建議建立場景、四類應避免建立場景，助你在效能與成本間找到平衡點。
一、創建索引的兩大核心目的
- 維護唯一性：Primary Key、Unique Constraint 內部依賴唯一索引
- 提供快速存取路徑：Range Scan、Unique Scan、Index Full Scan 替代 Full Table Scan
二、索引五大優勢 (收益面)
| 優勢 | 說明 |
|---|
| 1. 唯一性保證 | Unique Index 強制業務鍵不重複 |
| 2. 加速檢索 | WHERE、JOIN、ORDER BY、GROUP BY 利用索引順序 |
| 3. 加速連接 | 外鍵欄位建索引，Nested Loop Join 驅動表高效 |
| 4. 加速排序分組 | 索引已有序，省略 SORT ORDER BY/GROUP BY 操作 |
| 5. 優化器輔助 | 提供統計資訊、基數估算，生成更優執行計畫 |
三、索引三大代價 (成本面)
| 代價 | 影響 |
|---|
| 1. 建立與維護時間 | 隨資料量增長呈 O(N log N)，大表建索引需維護窗口 |
| 2. 物理空間佔用 | 每個索引獨立段，叢集索引更大，影響備份/還原速度 |
| 3. DML 效能下降 | INSERT/UPDATE/DELETE 需同步維護所有相關索引，產生額外 Redo/Undo |
四、六類「必須建索引」場景
- 高頻搜尋欄位：WHERE 條件常用、選擇度高 (如
user_id, order_no)
- 主鍵欄位：強制唯一 + 組織資料順序 (Heap Table 除外)
- 外鍵欄位：JOIN 驅動、FK 約束檢查 (避免鎖升級、Full Scan 父表)
- 範圍查詢欄位：
BETWEEN, >, <, LIKE 'prefix%' (索引區間掃描)
- 排序/分組欄位：
ORDER BY, GROUP BY 常用欄位 (避免 Sort 操作)
- WHERE 子句高頻欄位：複合索引前導欄位覆蓋常用過濾條件
五、四類「不應建索引」場景
- 低頻/無查詢欄位：維護成本 > 查詢收益
- 低基數欄位 (選擇度差)：如性別 (M/F)、狀態碼 (0/1/2)、布林旗標 — 索引掃描回表成本接近 Full Scan
- 大型物件 (LOB/BFILE)：資料量大、不適合索引結構
- 寫入密集、讀取稀疏表：日誌表、暫存表、階段表 — DML 代價遠大於查詢收益
六、進階決策矩陣
| 維度 | 強烈建議 | 視情況而定 | 不建議 |
|---|
| 選擇度 | > 95% (近似唯一) | 10%-95% | < 10% (低基數) |
| 查詢頻率 | 核心業務高頻 | 報表/分析低頻 | 極少查詢 |
| DML 比例 | 讀多寫少 | 讀寫平衡 | 寫入極頻 (日誌/流水) |
| 欄位型別 | 數值、日期、短字串 | 長字串 (前綴索引) | LOB、XML、JSON |
七、驗證索引效果的關鍵指標
V$OBJECT_USAGE (監控索引使用情況，ALTER INDEX ... MONITORING USAGE)- 執行計畫：
INDEX RANGE SCAN, INDEX UNIQUE SCAN, INDEX FAST FULL SCAN
CLUSTERING_FACTOR 接近區塊數 = 好，接近行數 = 差- 定期
DBMS_STATS.GATHER_INDEX_STATS 更新統計資訊
索引設計無銀彈，唯有「測試驗證、持續監控、定期清理未用索引」才能維持最佳平衡。將上述框架納入資料庫變更管理流程，讓每個索引都經得起成本收益審視。
