| 索引類型 | 適用場景 | 核心特性 | 維護成本 |
|---|---|---|---|
| B-Tree (預設) | 高選擇性列、主鍵、外鍵、範圍查詢、排序 | 平衡樹結構、支援 `=、>、<、>=、<=、BETWEEN、LIKE 'prefix%'` | 中等 |
| Bitmap | 低基數列 (性別、狀態、類型)、數據倉庫、星型模型 | 位圖編碼、極佳壓縮、支援布林運算 (AND/OR/NOT) | 高 (DML 鎖升級) |
| Function-Based (FBI) | 查詢含函數/運算式的列 (`UPPER(name)='SMITH'`, `sal*12>50000`) | 將運算式結果物質化索引、透明改寫 | 中等 |
| Partitioned (本地/全域) | 大表分區、歷史數據歸檔、ILM | 本地索引與表分區對齊、全域索引跨分區唯一性 | 中等 |
| Reverse Key | 單調遞增列 (序列主鍵) 熱塊競爭 | 反轉鍵值分散插入、不支援範圍掃描 | 中等 |
| Composite (組合) | 多列聯合查詢、覆蓋索引 | 遵循最左前綴、列序 = 選擇性遞減 + WHERE 頻率遞減 | 中等 |
| Invisible (11g+) | 測試索引移除影響、軟刪除 | 優化器忽略、維護正常、可 `ALTER INDEX ... VISIBLE` 瞬時啟用 | 中等 |
| Virtual Column + Index (11g+) | 常用運算式透明索引化 | 虛擬欄位不佔儲存、自動維護、查詢重寫透明 | 低 |
-- 基本 B-Tree CREATE INDEX idx_emp_dept ON emp(deptno); -- 唯一索引 (主鍵/唯一約束自動建立) CREATE UNIQUE INDEX idx_emp_uk ON emp(empno); -- 組合索引：列序極其重要 CREATE INDEX idx_emp_dept_job ON emp(deptno, job); -- deptno 選擇性高、常用於 WHERE -- 位圖索引：僅適合低基數、低併發 CREATE BITMAP INDEX idx_emp_sex ON emp(sex); -- 函數索引：解決 WHERE 子句函數導致索引失效 CREATE INDEX idx_emp_upper_name ON emp(UPPER(ename)); CREATE INDEX idx_emp_annual_sal ON emp(sal*12); -- 反向鍵索引：緩解單調遞增主鍵右側增長熱點 CREATE INDEX idx_emp_rev_pk ON emp(empno) REVERSE; -- 本地分區索引：與表分區一一對應，維護靈活 CREATE INDEX idx_sales_local ON sales(sale_date) LOCAL; -- 全域分區索引：跨分區強制唯一性 CREATE INDEX idx_sales_global ON sales(cust_id) GLOBAL PARTITION BY HASH(cust_id) PARTITIONS 8; -- 不可見索引：安全部署/測試 CREATE INDEX idx_emp_test ON emp(hiredate) INVISIBLE; ALTER INDEX idx_emp_test VISIBLE; -- 正式啟用
-- 監控索引使用情況 (11g+)
ALTER INDEX idx_emp_dept MONITORING USAGE;
-- 一段時間後
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_DEPT';
ALTER INDEX idx_emp_dept NOMONITORING USAGE;
-- 定期重建碎片化索引
SELECT index_name,
ROUND(100 * (leaf_blocks - (SELECT SUM(bytes)/8192 FROM user_segments WHERE segment_name = index_name)) / leaf_blocks, 2) AS pct_fragmented
FROM user_indexes
WHERE pct_fragmented > 20 -- 碎片率超 20% 考慮重建
ORDER BY pct_fragmented DESC;
索引是資料庫效能的加速器，亦是維護成本的來源。**精準選型、定期體檢、适時重建**，讓索引持續賦能業務增長。
