資料庫索引種類繁多,依**邏輯特性、物理儲存、結構演算法**三維度分類,各有優勢場景。本文構建完整分類地圖,對比 B-tree、Bitmap、Hash、Full-text、空間、JSON 等 10+ 索引類型特性,提供場景化選型決策矩陣。
| 類型 | 定義 | 典型用途 |
|---|---|---|
| Single Column (單欄) | 單一欄位建立 | 簡單等值/範圍查詢 |
| Concatenated / Composite (複合/聯集) | 多欄位組合,順序敏感 | 多條件聯合查詢、最左前綴、覆蓋索引 |
| Unique (唯一) | 鍵值不可重複 | PK、UK、業務唯一鍵 (身分證、Email) |
| Non-Unique (非唯一) | 允許重複值 | 一般查詢加速、FK、狀態碼 |
| Function-Based (函數/運算式) | 索引鍵 = 函數/運算式結果 | UPPER(name)、TRUNC(date)、JSON_EXTRACT(doc, '$.price') |
| Domain / Extensible (域/擴展) | 特定資料類型專用 (空間、XML、JSON、全文) | PostGIS GiST/GIN、Oracle Text、XMLIndex、JSON Index |
| Partial / Filtered (部分/過濾) | 僅索引滿足 WHERE 條件的行 | 軟刪除 WHERE deleted=0、熱點資料 WHERE status='ACTIVE' |
| Invisible / Hidden (隱形) | 存在但優化器忽略 (11g+/MySQL 8.0+) | 測試索引效果、平滑切換、軟刪除索引 |
| 類型 | 資料與索引關係 | 關鍵特徵 |
|---|---|---|
| Clustered Index (叢集/索引組織表 IOT) | **資料即索引**,葉子節點 = 完整行 | 每表僅一、無回表、範圍掃描極快、插入易分裂、PK 預設叢集 (SQL Server/MySQL InnoDB) |
| Non-Clustered (非叢集/二級索引) | **索引獨立於資料**,葉子 = 鍵值 + Rowid/PK | 可多個、需回表、維護較輕、適合多維查詢 |
| Covering Index (覆蓋索引) | 非叢集索引 + INCLUDE 欄位包含查詢所有欄位 | 免回表、讀取極快、寫入成本略增、大幅降低 I/O |
| Partitioned Index (分區索引) | 索引隨表分區 (Local) 或跨分區 (Global) | Local: 分區裁剪、獨立維護、PITR 友善;Global: 跨分區唯一性、維護複雜 |
| 演算法 | 資料結構 | 優勢場景 | 劣勢/限制 | 代表產品 |
|---|---|---|---|---|
| B+ Tree | 多路平衡樹,葉子鏈表 | **通用 OLTP**:等值、範圍、排序、JOIN、高基數 | 寫入放大、空間放大 | Oracle, MySQL, PostgreSQL, SQL Server, DB2 |
| B Tree (原始) | 資料分佈所有節點 | 嵌入式、記憶體資料庫 | 範圍查詢慢、節點大小不均 | SQLite (早期), Berkeley DB |
| Hash Index | 雜湊表 (Bucket + Chain) | **純等值查詢 O(1)**、記憶體表、臨時表 | 不支援範圍/排序、衝突處理、重新雜湊代價大 | MySQL MEMORY, PostgreSQL Hash, Oracle Hash Cluster |
| Bitmap Index | 位圖 (每值一個 Bitmap) | **DW/OLAP**:低基數 (性別、狀態)、多條件 AND/OR、星型查詢 | **DML 鎖定整塊 Bitmap、極不適合高併發 OLTP** | Oracle, SQL Server (DW), Sybase IQ |
| Reverse Key (反轉鍵) | B+ Tree + 位元組反轉 | **消除單調遞增鍵右側熱點** (RAC Cache Fusion 爭用) | 不支援範圍掃描 | Oracle |
| Full-Text / Inverted Index | 倒排索引 (Term → DocID List) | 自然語言全文檢索、關鍵字搜尋、相關性排序 | 維護重、非結構化 | Oracle Text, PostgreSQL GIN/GiST, MySQL FULLTEXT, Elasticsearch |
| Spatial (R-Tree / Quad-Tree) | 多維空間分割樹 | GIS、經緯度範圍、幾何關係 (含含、相交、距離) | 高維度效能遞減 | PostGIS, Oracle Spatial, SQL Server Geometry |
| LSM Tree (Log-Structured Merge) | 記憶體 MemTable + 磁碟 SSTable 分層合併 | **寫入密集**、時序、Key-Value (RocksDB, LevelDB, Cassandra, HBase) | 讀取放大、範圍查詢需合併多層 | NoSQL/NewSQL (TiDB, OceanBase 亦支援 B+ Tree) |
| Adaptive / Learned Index | 機器學習模型預測位置 | 唯讀/靜態超大資料集、極致查詢速度 | 建模成本高、不支援寫入 | Google Learned Index, Microsoft FITing-Tree |
| 業務場景 | 推薦索引類型 | 避坑提醒 |
|---|---|---|
| OLTP 核心交易表 (PK/FK/高基數) | B+ Tree (PK 叢集 + FK 非叢集) | 避免過度索引、監控寫入放大 |
| OLTP 狀態碼/性別/類型 (低基數) | **不建單列 B-tree** → 複合索引前導欄位 / 部分索引 | 單列 Bitmap 會鎖死併發 |
| DW 事實表維度外鍵 (低基數、多維篩選) | Bitmap Index (Oracle) / Columnstore Index (SQL Server) | 僅限批次載入/唯讀環境 |
| RAC 環境單調遞增 PK (Sequence/Identity) | Reverse Key Index / Hash Partitioned Index | 普通 B-tree 導致右側葉子熱點、GC 爭用 |
| 全文搜尋 (文章、日誌、文檔) | Inverted Index (Oracle Text / PG GIN / ES) | 勿用 LIKE '%...%' 全表掃描 |
| GIS 經緯度/幾何查詢 | R-Tree (PostGIS GiST / Oracle Spatial) | 平面座標系 vs 球面座標系選型 |
| JSON 半結構化查詢 | JSON Index (PG GIN / Oracle JSON Search Index / MySQL Multi-valued Index) | 路徑固定可用虛擬欄位 + B-tree |
| 時序/寫入極密集 (IoT、監控) | LSM Tree (TimescaleDB / InfluxDB / TiDB) | B-tree 寫入放大不可接受 |
索引類型選擇的本質是 **「查詢模式 × 資料特性 × 寫入強度 × 硬體資源」四維權衡**。建立「場景 → 類型 → 參數 → 驗證 → 監控」標準化選型流程,並緊跟 HTAP、AI、Serverless 等新興融合趨勢,才能持續交付最優索引架構。