一、索引建立的兩大核心目的
- 強制唯一性:Primary Key / Unique Index 確保資料唯一。
- 加速存取路徑:提供快速定位行的策略,避免全表掃描。
二、索引五大優勢
| # | 優勢 | 說明 |
| 1 | 唯一性保證 | Unique Index / PK 防止重複資料 |
| 2 | 查詢加速 | Range Scan、Unique Scan、Index Fast Full Scan 替代 Full Table Scan |
| 3 | 連線加速 | Nested Loop Join 內表索引驅動,Foreign Key 索引避免鎖升級 |
| 4 | 排序/分組優化 | 索引天然有序,ORDER BY/GROUP BY 可省略 Sort 操作 |
| 5 | 優化器選項 | 提供更多存取路徑供 CBO 選擇,啟用 Index Hint 微調 |
三、索引三大代價
| # | 代價 | 影響 |
| 1 | 建立/維護時間 | 大表建索引耗時久;DML 時同步維護 B-Tree 結構產生額外 Redo/Undo |
| 2 | 儲存空間 | 每個索引佔獨立段空間;複合索引、覆蓋索引更大 |
| 3 | DML 效能下降 | INSERT/UPDATE/DELETE 需同步修改所有相關索引,批次作業尤為明顯 |
四、必建索引場景 (正向清單)
- **主鍵欄位** (PK 自動建 Unique Index)。
- **外鍵欄位** (避免父表刪除/更新時全表掃描鎖定子表)。
- **高頻 WHERE 條件欄位** (選擇度高、基數大)。
- **範圍查詢欄位** (日期、金額、序號等
BETWEEN/>=)。
- **頻繁 ORDER BY/GROUP BY 欄位** (索引有序特性消除 Sort)。
- **連線鍵欄位** (JOIN ON 兩側欄位)。
五、不建索引場景 (反向清單)
- **極少查詢/從不查詢欄位**:維護成本 > 查詢收益。
- **低基數欄位** (如性別、狀態碼、是否刪除):**選擇度 > 10-15% 時全表掃描更快**,索引無效。
例外:BITMAP INDEX 適用低基數 + 低併發 (DW 環境)。
- **LOB / LONG / 大型物件欄位**:不可建 B*Tree,需 Oracle Text / Context 索引。
- **寫多讀少表** (如日誌、暫存、階段表):DML 開銷遠超查詢收益。
- **極小表** (< 1000 行 / < 幾十個區塊):Full Table Scan 更快,優化器會自動忽略索引。
六、進階索引策略
- 複合索引欄位順序:
WHERE a=1 AND b=2 → INDEX (a,b);WHERE b=2 不能用該索引 (跳過前導欄)。
- 覆蓋索引:查詢欄位全包含於索引 →
INDEX FAST FULL SCAN 避免回表。
- 函數索引:
CREATE INDEX idx ON t(UPPER(name)) 支援 WHERE UPPER(name)='ABC'。
- 部分索引 (12c+):
CREATE INDEX idx ON t(status) WHERE status='ACTIVE' 縮小索引體積。
- 索引壓縮:
COMPRESS 1 (前導欄壓縮) 節省空間、提升快掃效能。
七、索引健康度監控
-- 索引使用統計 (需開啟 MONITORING)
SELECT index_name, table_name, monitoring, used
FROM user_indexes;
-- 未使用索引候選刪除
SELECT i.index_name, i.table_name
FROM user_indexes i
LEFT JOIN v$object_usage u ON i.index_name = u.index_name
WHERE u.used = 'NO' OR u.used IS NULL;
-- 碎片化程度
ANALYZE INDEX idx VALIDATE STRUCTURE;
SELECT height, lf_blks, br_blks, pct_used FROM index_stats;
索引是「空間換時間、維護換查詢」的經典權衡,**以查詢負載為導向、以 DML 代價為約束**,定期清理無效索引、重建碎片索引、調整複合索引欄位順序,是 DBA 持續優化的核心工作。