MySQL 最常用的索引結構是 B-Tree（實為 B+Tree），但 Hash 索引在特定場景下擁有 O(1) 查找優勢。本文深度對比兩者原理、特性、限制，並說明為何 InnoDB/MyISAM 不直接支援 Hash，以及如何通過自適應 Hash 間接受益。

B-Tree 索引特性（InnoDB/MyISAM 預設）

支援操作

• 精確匹配：=, <=>
• 範圍查詢：>, >=, <, <=, BETWEEN
• 前綴匹配：LIKE 'prefix%'（非通配符開頭）
• IS NULL 查詢
• ORDER BY、GROUP BY 排序優化
• 覆蓋索引（Index Only Scan）

最左前綴原則

複合索引 (a,b,c)：

• 可用：WHERE a=1, WHERE a=1 AND b=2, WHERE a=1 AND b=2 AND c=3
• 不可用：WHERE b=2, WHERE c=3, WHERE a=1 AND c=3（b 缺失）

優化器不使用索引的情況

• 預估掃描行數 > 30% 表行數（全表掃描更快）
• 含 OR 條件且無法合併使用索引
• LIKE '%pattern%' 通配符開頭
• 隱式類型轉換（字符串比數字、字符集不匹配）

Hash 索引特性（MEMORY/NDB 引擎）

優勢

• 等值查詢 O(1)，極快
• 適合「鍵值對」純點查場景

嚴重限制（生產環境慎用）

1. 僅支援等值比較：=, <=>，**不支援範圍、排序、GROUP BY、LIKE**
2. 無法避免排序：Hash 值無序，ORDER BY 仍需 filesort
3. 不支援最左前綴：複合 Hash 索引需完整鍵查詢
4. 無法統計範圍行數：優化器無法估算 < > 選擇率
5. Hash 衝突退化：低選擇性鍵（如性別、狀態）導致嚴重衝突，性能反而劣於 B-Tree
6. 引擎限制：僅 MEMORY、NDB 支援，**InnoDB/MyISAM 不支援顯式 Hash 索引**

InnoDB 自適應 Hash 索引（AHI）

InnoDB 內建自適應 Hash 索引，**無需人工創建**，自動監控熱點頁面訪問模式，為頻繁訪問的 B-Tree 頁面建立內存 Hash 索引。

特點

• 透明自動：DBA 不可控制建立/刪除，只能開關
• 針對熱點頁面：僅對高頻訪問的索引頁建立 Hash
• 僅加速等值查詢：WHERE id = ? 類點查
• 佔用緩衝池記憶體

控制參數

-- 查看狀態
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';  -- ON/OFF

-- 動態關閉（罕見需要）
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- 永久配置 my.cnf
[mysqld]
innodb_adaptive_hash_index = ON

何時考慮關閉 AHI

• 高併發寫入負載，Hash 維護開銷大於讀取收益
• 觀察 SHOW ENGINE INNODB STATUS 中 hash searches 佔比極低
• 大量 LIKE/範圍查詢，Hash 無用但佔內存

模擬 Hash 索引方案（InnoDB/MyISAM）

若極度需要 O(1) 點查，可手動模擬：

-- 原表
CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR(50), ...);

-- 增加一個 Hash 欄位（應用層計算）
ALTER TABLE users ADD COLUMN name_hash INT UNSIGNED NOT NULL DEFAULT 0;
CREATE INDEX idx_name_hash ON users (name_hash);

-- 查詢時
SELECT * FROM users WHERE name_hash = CRC32('target_name') AND name = 'target_name';

應用層計算 Hash（CRC32、MurmurHash、xxHash），索引查找候選行，再精確比對原值。此法廣泛用於長字符串、URL、Token 查找優化。

選型總結

需求	推薦方案
通用業務查詢（範圍、排序、分組、前綴）	B-Tree（預設）
純點查、極高 QPS、鍵值存儲	MEMORY 引擎 Hash / Redis / 自適應 Hash
長字符串/URL/Token 精確查找	手動 Hash 欄位 + B-Tree 索引
低基數欄位（性別、狀態、類型）	不要建索引，或組合索引靠後

深入理解索引底層結構，能讓你在架構設計、慢查詢優化、容量規劃中做出正確決策，避免「為了建索引而建索引」的反模式。