索引不生效？先查查有沒有踩中這五大陷阱
百萬級資料表加了索引查詢仍慢？九成以上是觸發了索引限制導致全表掃描。本文系統整理五大失效場景、五類索引適用場景與建立策略。
一、五大索引失效陷阱與改寫方案
| # | 陷阱場景 | 失效範例 | 改寫方案 | 原理 |
|---|
| 1 | 不等於運算子 | WHERE dept_id <> 1000 | WHERE dept_id < 1000 OR dept_id > 1000 | 優化器無法透過區間掃描定位 |
| 2 | IS NULL / IS NOT NULL | WHERE col IS NULL | 建表時設NOT NULL；或使用位圖索引 | B-tree不儲存全NULL鍵值 |
| 3 | 索引欄位使用函數 | WHERE TRUNC(birthdate) = '01-MAY-82' | WHERE birthdate < TO_DATE('02-MAY-82') 或建函數索引 | 函數破壞索引鍵值順序 |
| 4 | 隱含資料型別轉換 | WHERE varchar_col = 900198 | WHERE varchar_col = '900198' | Oracle隱含TO_NUMBER()導致函數化 |
| 5 | 複合索引前導欄未用 | 索引(a,b,c) 查WHERE b=1 AND c=2 | 調整欄位順序或建獨立索引 | B-tree需前導欄參與才能掃描 |
二、五類索引類型選型指南
| 索引類型 | 建立語法 | 適用場景 | 不適用場景 | 關鍵特性 |
|---|
| B-tree(標準) | CREATE INDEX idx ON t(c); | OLTP、高基數欄位、精確/區間查詢 | 低基數、全表掃描優勢時 | 平衡樹、支援排序、最大32欄 |
| 反向鍵 | CREATE INDEX idx ON t(c) REVERSE; | RAC環境序列鍵熱塊爭用 | 區間掃描、ORDER BY | 鍵值位元反轉分散插入熱點 |
| 降序 | CREATE INDEX idx ON t(c DESC); | 頻繁ORDER BY c DESC | 一般OLTP混合負載 | 儲存降序鍵值，避免排序 |
| 位圖 | CREATE BITMAP INDEX idx ON t(c); | DSS/OLAP、低基數(性別、狀態)、多條件AND/OR | 高併發DML、OLTP | 位圖壓縮、鎖定粒度大(全值鎖定) |
| 函數索引 | CREATE INDEX idx ON t(UPPER(name)); | 查詢含函數條件且無法改寫SQL | 函數非確定性、DML頻繁 | 儲存函數運算結果，查詢直接匹配 |
三、索引建立策略六原則
- 資料導入後再建索引：避免逐筆維護開銷，排序一次完成
- 小表勿建索引：全表掃描更快(閾值視區塊大小/緩衝區而定)
- 低基數欄位用位圖索引：性別、狀態、類型等
- 限制單表索引數量：每個索引增加DML開銷，建議
≤5個
- 設定適當PCTFREE：預留空間減少分裂，建議
10-20
- 索引獨立表空間：隔離I/O、便於備份/恢復/離線
四、黃金法則：能用唯一索引，一定用唯一索引
CREATE UNIQUE INDEX → 優化器知悉唯一性，選擇更優執行計畫(UNIQUE SCAN vs RANGE SCAN)- 配合
NOT NULL約束 → 完整主鍵語意
- 統計資訊必收集：表、索引、直方圖(
DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'))
- 複合索引欄位順序：高選擇性(基數大)優先，或
WHERE子句最常用欄位優先
五、驗證索引使用狀態
-- 監控索引使用情況(11g+)
SELECT * FROM dba_index_usage WHERE object_name='IDX_NAME';
-- 檢查未使用索引
SELECT i.index_name, i.table_name, i.uniqueness
FROM user_indexes i
LEFT JOIN dba_index_usage u ON i.index_name=u.object_name
WHERE u.object_name IS NULL AND i.index_type='NORMAL';
