萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 索引設計指南:特點、優缺點、適用與不適用場景全景圖

一、索引建立的兩大核心目的

  1. 強制唯一性:Primary Key / Unique Index 確保資料唯一。
  2. 加速存取路徑:提供快速定位行的策略,避免全表掃描。

二、索引五大優勢

#優勢說明
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儲存空間每個索引佔獨立段空間;複合索引、覆蓋索引更大
3DML 效能下降INSERT/UPDATE/DELETE 需同步修改所有相關索引,批次作業尤為明顯

四、必建索引場景 (正向清單)

  1. **主鍵欄位** (PK 自動建 Unique Index)。
  2. **外鍵欄位** (避免父表刪除/更新時全表掃描鎖定子表)。
  3. **高頻 WHERE 條件欄位** (選擇度高、基數大)。
  4. **範圍查詢欄位** (日期、金額、序號等 BETWEEN/>=)。
  5. **頻繁 ORDER BY/GROUP BY 欄位** (索引有序特性消除 Sort)。
  6. **連線鍵欄位** (JOIN ON 兩側欄位)。

五、不建索引場景 (反向清單)

  1. **極少查詢/從不查詢欄位**:維護成本 > 查詢收益。
  2. **低基數欄位** (如性別、狀態碼、是否刪除):**選擇度 > 10-15% 時全表掃描更快**,索引無效。
    例外:BITMAP INDEX 適用低基數 + 低併發 (DW 環境)。
  3. **LOB / LONG / 大型物件欄位**:不可建 B*Tree,需 Oracle Text / Context 索引。
  4. **寫多讀少表** (如日誌、暫存、階段表):DML 開銷遠超查詢收益。
  5. **極小表** (< 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 持續優化的核心工作。

copyright © 萬盛學電腦網 all rights reserved