MySQL Hash 索引 vs B-Tree 索引：原理差異、適用場景與選型指南

核心結論先行

B-Tree (B+Tree) 是通用首選，Hash 索引僅適合純等值查詢 (Memory/Heap 引擎)。InnoDB 自適應 Hash 索引 (AHI) 由引擎內部自動管理，使用者無法直接建立。

架構原理對比

Hash 索引五大限制 (致命傷)

1. 僅支援 =、IN、<=> 比較，無法處理範圍、排序、LIKE。
2. 無法避免排序操作：雜湊值大小與原值無關，ORDER BY 必須 filesort。
3. 複合索引無法部分使用：雜湊值基於完整鍵值計算，(a,b) 索引無法服務 WHERE a=1。
4. 無法避免表掃描：雜湊衝突導致同桶多行，仍需回表比對完整鍵值。
5. 低選擇性欄位效能崩潰：大量相同雜湊值集中同桶，退化為線性掃描，比 B-Tree 更慢。

InnoDB 自適應 Hash 索引 (AHI)

• **內部機制**：InnoDB 監控 B+Tree 頁面訪問模式，熱點頁面自動在記憶體建立 Hash 索引加速等值查詢。
• **使用者不可控**：無法手動建立/刪除，參數 innodb_adaptive_hash_index=ON/OFF 仅能全域開關。
• **適用場景**：重複等值查詢熱點資料 (如查詢熱門商品 ID)，自動提升至 O(1)。

Memory/Heap 引擎 Hash 索引實測

CREATE TABLE t_hash (id INT PRIMARY KEY, val VARCHAR(20)) ENGINE=MEMORY;
CREATE TABLE t_btree (id INT PRIMARY KEY, val VARCHAR(20)) ENGINE=MEMORY;
-- MEMORY 引擎預設 Hash 索引，可指定 USING BTREE
ALTER TABLE t_hash ADD INDEX idx_val (val) USING HASH;
ALTER TABLE t_btree ADD INDEX idx_val (val) USING BTREE;

測試：等值查詢 Hash 快 10-20%；範圍查詢 Hash 全表掃描，B-Tree 索引範圍掃描。

選型決策樹

1. 引擎是 InnoDB/MyISAM？→ **只能用 B-Tree** (InnoDB AHI 自動)。
2. 引擎是 Memory/Heap？
   • 純等值查詢、高選擇性、無排序需求 → USING HASH。
   • 其他所有情況 → USING BTREE (預設)。
3. 需求範圍查詢/排序/部分匹配/覆蓋索引 → **B-Tree 唯一選擇**。

總結

Hash 索引理論 O(1) 吸引人，但功能殘缺嚴重，生產環境**極少手動建立**。InnoDB 透過 AHI 自動在適當時機發揮 Hash 優勢，使用者專注於設計優質 B+Tree 索引 (複合索引、覆蓋索引、最左前綴) 即可獲得最佳綜合效能。