前言：索引在存儲引擎層實現
MySQL 索引**不在 Server 層**，而是由各存儲引擎自行實現。因此不同引擎索引結構、能力、限制差異巨大。本文聚焦 InnoDB（B+Tree）、MEMORY（Hash）、MyISAM（Fulltext）三大核心類型。
一、B+Tree 索引（InnoDB、MyISAM、NDB 等主流引擎）
結構特點
- 有序性：葉子節點按鍵值順序雙向鏈表連接，支援範圍查詢、排序、GROUP BY。
- 平衡性：根到任意葉子高度相同（通常 3-4 層），查詢性能穩定 O(log N)。
- 葉子存數據指針：
- MyISAM：葉子存**行物理地址**（行號/文件偏移量）。
- InnoDB：聚簇索引葉子存**完整行數據**；二級索引葉子存**主鍵值**（邏輯指針），查詢需「回表」再走聚簇索引。
- 前綴壓縮：MyISAM 支援索引鍵前綴壓縮節省空間；InnoDB 5.7+ 支援頁面級壓縮，但不做鍵前綴壓縮。
適用查詢類型（最左前綴原則）
- 全鍵值匹配：
WHERE a=1 AND b=2 AND c=3
- 最左前綴匹配：
WHERE a=1、WHERE a=1 AND b=2
- 列前綴匹配：
WHERE a LIKE 'abc%'
- 範圍查詢：
WHERE a > 10、WHERE a BETWEEN 1 AND 100
- 精確匹配某列 + 範圍匹配後續列：
WHERE a=1 AND b > 10
- 覆蓋索引：查詢列全部包含在索引中，避免回表。
B+Tree 硬性限制
- 查詢**必須從最左列開始**，不能跳過列（
WHERE b=2 不走 idx(a,b)）。
- 範圍列**之後的列索引失效**（
WHERE a > 10 AND b=2，b 不走索引）。
!=、<>、IS NULL、LIKE '%abc'、函數運算列、隱式類型轉換 → 全表掃描。- JOIN 條件列類型、字符集、長度不一致 → 索引失效。
二、Hash 索引（MEMORY/HEAP 引擎，NDB 部分支援）
構建機制
- 對索引列計算哈希值，將「哈希值 → 行指針」存入哈希表。
- 哈希衝突用鏈表解決，同一桶掛多行指針。
- 索引極其緊湊，僅存定長哈希值 + 指針。
能力邊界
| 操作 | 支援 | 備註 |
|---|
| = / IN / <=> | ✅ | O(1) 精確查找 |
| 範圍查詢 (>, <, BETWEEN) | ❌ | 哈希無序 |
| ORDER BY / GROUP BY | ❌ | 無序 |
| 最左前綴 / 部分鍵匹配 | ❌ | 需完整鍵值計算哈希 |
| 覆蓋索引 | ✅ | 不回表 |
性能陷阱
- 哈希衝突率高時（大量重複值），鏈表遍歷退化為 O(N)，嚴重拖慢寫入與讀取。
- InnoDB **自適應哈希索引 (AHI)**：熱頁面自動在內存建立哈希索引，**完全自動、不可控制、不可禁用**（除非
innodb_adaptive_hash_index=OFF）。
三、全文索引 Fulltext（MyISAM 5.6 前專有，5.6+ InnoDB 支援）
- 基於**倒排索引**：分詞 → 建立「詞 → 文檔 ID 列表」映射。
- 支援自然語言模式（相關性排序）、布爾模式（邏輯操作符 + - * ~）。
- 中文需
ngram 解析器（5.7.6+）或第三方分詞插件。
- 不支援事務（MyISAM）、索引維護開銷大、大批量導入建議「先導數據後建索引」。
四、引擎級索引差異速查表
| 特性 | InnoDB (B+Tree) | MyISAM (B+Tree) | MEMORY (Hash) | InnoDB Fulltext (5.6+) |
|---|
| 聚簇索引 | ✅ (主鍵) | ❌ (堆表) | ❌ | ✅ |
| 二級索引存儲 | 主鍵值 | 行物理地址 | 哈希值 | 詞向量 |
| 事務支援 | ✅ | ❌ | ❌ | ✅ |
| 行鎖 | ✅ | ❌ (表鎖) | ❌ (表鎖) | ✅ |
| 崩潰恢復 | ✅ | ❌ | ❌ (內存丟失) | ✅ |
| 範圍查詢 | ✅ | ✅ | ❌ | ❌ |
| 全文搜索 | ✅ (5.6+) | ✅ | ❌ | ✅ |
五、實戰選型指南
- OLTP 核心業務：InnoDB B+Tree 一択，主鍵自增、二級索引覆蓋高頻查詢。
- 唯讀歸檔/日志分析：MyISAM 壓縮表（
myisampack）+ 全文索引，存儲效率最高。
- 極高頻精確查找、數據可重建：MEMORY Hash 表（如會話查找、IP 白名單），重啟自動恢復腳本。
- 中文全文搜索：InnoDB + ngram 解析器（中小型）、Elasticsearch（大型/複雜聚合）。
總結
理解 **存儲引擎決定索引結構**，才能在架構選型、SQL 優化、故障排查中做正確決策。
- B+Tree：有序、範圍、覆蓋、最左前綴 —— 通用王者。
- Hash：O(1) 精確、無序、衝突敏感 —— 專用利器。
- Fulltext：倒排、分詞、相關性 —— 搜索專屬。
**沒有最好的索引，只有最適合業務訪問模式的索引。**
