MySQL B-Tree vs Hash 索引深度解析：原理、適用場景與版本限制

MySQL B-Tree vs Hash 索引深度解析：原理、適用場景與版本限制

MySQL 最常用的索引結構是 B-Tree (O(log n))，但在特定場景下 Hash 索引 (O(1)) 能帶來極致查詢效能。本文基於官方文檔深度解析兩者特徵差異、適用條件、MySQL 版本引擎支援現狀，並補充實戰避坑經驗。

一、核心特徵對比

二、B-Tree 索引深度特徵

1. 適用運算符與 LIKE 模式

-- 使用索引
WHERE col = 'value'
WHERE col > 'value'
WHERE col BETWEEN 10 AND 20
WHERE col LIKE 'abc%'      -- 前綴固定
WHERE col LIKE 'ab_%'      -- 模式固定前綴

-- 不使用索引
WHERE col LIKE '%abc'      -- 萬用字元開頭
WHERE col LIKE '%abc%'     -- 兩端萬用字元
WHERE col LIKE other_col   -- 非常量

長字串 LIKE '%string%' (string > 3 字符) 會啟用 Turbo Boyer-Moore 算法優化。

2. IS NULL 優化

WHERE col IS NULL  -- 可使用索引

3. 複合索引最左前綴原則

INDEX idx_a_b_c (a, b, c)
-- 可用到索引的查詢
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 僅用到 a

-- 無法使用索引
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3

4. OR 條件索引使用

-- 使用索引
WHERE a = 1 OR a = 2
WHERE a = 1 OR (b = 2 AND a = 3)

-- 不使用索引
WHERE a = 1 OR b = 2  -- a 有索引但 b 無

5. 優化器放棄索引的情況

• 預估索引掃描行數 > 表總行數 20%-30%（全表掃描更快）
• 但加 LIMIT 限制返回行數時，優化器傾向使用索引

三、Hash 索引深度特徵

1. 純等值查詢：僅支援 =、<=>，極快但功能受限
2. 無序結構：無法支援 ORDER BY、GROUP BY、範圍查詢
3. 行數估算失效：優化器無法判斷兩值間行數，影響連接優化
4. 完整鍵匹配：複合 Hash 索引需提供所有鍵列值才能查找

四、MySQL 版本與引擎支援現狀 (關鍵限制)

關鍵結論：InnoDB/MyISAM (最常用引擎) 僅支援 B-Tree。無法在主流業務表上直接建立 Hash 索引。

五、InnoDB 自適應 Hash 索引 (AHI)

InnoDB 內建 Adaptive Hash Index：監控索引頁訪問模式，自動為熱門 B-Tree 頁建立記憶體 Hash 索引，加速等值查找。屬內部機制，用戶不可控，可透過 innodb_adaptive_hash_index=ON/OFF 開關。

六、實戰選型指南

1. 通用業務表 (InnoDB)：只能用 B-Tree，設計複合索引遵循最左前綴
2. 記憶體表 (MEMORY)：
   • 純鍵值查找 (用戶 ID → Session) → USING HASH
   • 需範圍/排序 → USING BTREE
3. 極致效能場景：若確為等值查詢熱點，考慮：
   • 應用層引入 Redis (原生 Hash 結構)
   • MySQL 8.0+ 功能性索引 / 多值索引

七、實戰避坑筆記

-- 字符集亂碼問題：即使表設 utf8，連接也需設定
SET NAMES utf8mb4;  -- 或 SET CHARACTER SET utf8mb4;
-- 建議在連接池配置 connectionInitSql 執行

總結

B-Tree 是關係型資料庫索引的通用基石，功能完整、穩定可靠。Hash 索引雖理論 O(1) 極快，但受限於僅支援 MEMORY/NDB 引擎、不支援範圍查詢、無序、需完整鍵等硬性限制，在 InnoDB/MyISAM 主流業務場景無法直接使用。理解兩者邊界，合理設計 B-Tree 複合索引、善用覆蓋索引、引入 Redis 處理純鍵值熱點，才是實務中的正解。