SQL Server 2014 記憶體優化表哈希索引原理深度解析：Bucket 設計、衝突處理、適用場景與 2016 新特性

背景：Hekaton 記憶體優化引擎

SQL Server 2014 引入 **In-Memory OLTP (Hekaton)**，針對高並發、低延遲場景設計。記憶體優化表支援兩類索引：

• 哈希索引 (HASH) - 本文重點
• 範圍索引 (RANGE / Bw-Tree) - 支援範圍查詢/排序

哈希索引核心原理

1. 哈希函數與 Bucket 映射

建表時指定 BUCKET_COUNT：

CREATE TABLE dbo.HK_tbl (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    Data CHAR(32) COLLATE Latin1_General_100_BIN2 NULL,
    dt DATETIME NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

• 引擎內建哈希函數 (不可自訂)，輸入索引鍵值 → 輸出 32-bit 雜湊碼。
• Bucket_ID = Hash_Value % BUCKET_COUNT (實際為位運算優化)。
• 每個 Bucket 為一個**指標陣列**，指向該雜湊值對應的**資料行版本鏈表** (行版本控制 MVCC)。

2. Bucket Count 設計鐵律

• 必須為 2 的冪次方：引擎自動向上取最接近的 2^n (1024, 2048, 4096... 100000 → 131072)。
• 建議值 = 預估唯一鍵值數 × 1.0 ~ 2.0：過小 → 嚴重衝突 (長鏈表) → 掃描退化為 O(N)；過大 → 記憶體浪費 (空 Bucket 佔 8 bytes 指標)。
• 無法建表後修改 (2014)：**2016+ 支援 ALTER TABLE ... REBUILD WITH (BUCKET_COUNT = ...) 離線重建**。

3. 衝突處理：分離鏈表法

同一 Bucket 內多個不同鍵值 → 形成**單向鏈表**。查找流程：

1. 計算鍵值哈希 → 定位 Bucket。
2. 遍歷鏈表，**逐一比對完整鍵值** (非哈希碼) 確認匹配 (防止哈希碰撞誤判)。
3. 找到 → 依版本鏈可見性判斷 → 回傳行指標 → 讀取資料。

4. 查找複雜度

• 理想 (無衝突)：O(1) - 直接定位 Bucket → 行指標。
• 衝突情況：O(1 + α) (α = 載入因子 = 行數/Bucket數)。α ≤ 1 時極快；α > 5 明顯退化；α > 10 接近全掃描。
• 範圍查詢/排序：**完全不支援** (哈希破壞順序)，必須建立範圍索引輔助。

SQL Server 2016+ 關鍵增強

適用場景與選型決策樹

1. 點查詢 (Point Lookup)：WHERE ID = @val、JOIN ON T1.ID = T2.ID → **首選哈希索引**。
2. 範圍查詢：WHERE Date BETWEEN ...、ORDER BY Date → **必須範圍索引**。
3. 混合負載**：主鍵/唯一鍵建哈希，常用範圍欄位建範圍索引 (一表可建多索引)。
4. 高基數、均勻分布**：哈希效能最佳。
5. 低基數/嚴重傾斜**：慎用哈希，考慮範圍索引或複合鍵分散哈希。

效能監控與避坑

-- 查看哈希索引 Bucket 使用情況 (衝突統計)
SELECT 
    object_name(object_id) AS TableName,
    name AS IndexName,
    total_bucket_count,
    empty_bucket_count,
    avg_chain_length,
    max_chain_length,
    CAST(1.0 * total_bucket_count / NULLIF(empty_bucket_count,0) AS DECIMAL(10,2)) AS LoadFactor
FROM sys.hash_indexes
WHERE object_id = OBJECT_ID('dbo.HK_tbl');

• avg_chain_length > 1：有衝突，評估是否需增大 Bucket Count。
• max_chain_length > 10：嚴重衝突，**立即重建擴大 Bucket**。
• empty_bucket_count = 0：Bucket 已滿，必須擴容。

與 MySQL 自適應哈希索引 (AHI) 對比

總結

SQL Server 2014+ 哈希索引是 **記憶體優化表點查詢的極致加速器**。核心在於 **Bucket Count 科學預估 (2^n, 1-2倍行數) 與持續監控鏈長**。2016 帶來的離線重建能力徹底解決了「建錯無法改」的痛點。**不適用範圍查詢/排序**，務必配合範圍索引混合部署。掌握「哈希定位 → 鏈表遍歷 → 版本可見性」三步查找鏈路，即可在 Hekaton 架構下榨乾記憶體級效能。