MySQL 索引學習教程：B+樹與 Hash 索引原理、適用場景、限制與建立策略
索引是 MySQL 查詢性能的核心引擎。本文系統講解 InnoDB 默認 B+樹索引與 Memory 引擎 Hash 索引的數據結構原理、適用查詢模式、硬性限制，並給出生產環境索引建立的三大策略與避坑清單。
一、 兩大索引類型架構對比
| 維度 | B+樹索引 (InnoDB/Default) | Hash 索引 (Memory/HEAP) |
|---|
| 數據結構 | 平衡多路搜索樹 (所有數據在葉子節點，葉子雙向鏈表) | 哈希表 (Key → Value 直接映射) |
| 查詢複雜度 | O(log N) 穩定 | O(1) 平均，最差 O(N) (衝突嚴重時) |
| 支援操作 | 等值、範圍、前綴、排序、分組、覆蓋索引 | **僅等值查詢** (=, IN, <=>) |
| 排序能力 | 天然有序，支援 ORDER BY/GROUP BY 避免 filesort | 無序，不支援排序 |
| 部分匹配 | 支援最左前綴 (如 `LIKE 'abc%'`) | 不支援部分匹配 |
| 範圍查詢 | 支援 (>, <, >=, <=, BETWEEN) | 不支援 |
| 適用引擎 | InnoDB, MyISAM | Memory, NDB Cluster |
| 持久化 | 磁盤持久化 | 內存表重啟丟失 (需從磁盤表重建) |
二、 B+樹索引可用的 6 大查詢模式
- 全值匹配：`WHERE id = 123` (主鍵/唯一索引點查，最快)
- 最左前綴匹配：聯合索引 `(a,b,c)`，查 `WHERE a=1` / `WHERE a=1 AND b=2` → 可用；查 `WHERE b=2` → **不可用**
- 列前綴匹配：`WHERE name LIKE 'Zhang%'` → 可用 (非 `%` 開頭)
- 範圍值查詢：`WHERE create_time > '2024-01-01' AND create_time < '2024-02-01'`
- 精確匹配左列 + 範圍匹配右列：`WHERE user_id = 100 AND create_time > '2024-01-01'` → `user_id` 走索引，`create_time` 走索引範圍掃描
- 覆蓋索引：查詢列全包含在索引中 → `Extra: Using index`，無需回表，性能倍增
三、 B+樹索引 4 大硬性限制 (必須背誦)
- 非最左列開頭查詢不可用：`INDEX(a,b,c)`，`WHERE b=1` 或 `WHERE c=1` 全表掃描
- 不能跳過中間列：`INDEX(a,b,c)`，`WHERE a=1 AND c=2` → 僅 `a` 走索引，`c` 無效
- 非等值操作不可用：`!=`、`<>`、`NOT IN`、`NOT EXISTS` → 全表掃描
- 範圍列右側列全部失效：`INDEX(a,b,c)`，`WHERE a=1 AND b>10 AND c=2` → `a`、`b` 走索引，`c` **完全失效**
四、 Hash 索引特點與避坑
- 僅支援精確等值查找 (`=`、`IN`、`<=>`)
- 存儲引擎對所有索引列計算 Hash 碼，存儲 Hash 碼 + 行指針
- 查詢兩步：計算 Hash → 定位桶 → 遍歷桶內行比對 → 回表取數據
- 嚴禁在低選擇性列建 Hash 索引 (如 `sex`、`status`)，嚴重衝突退化為全表掃描
- 適合高選擇性列：`user_id`、`phone`、`email`、`session_id`
五、 為什麼需要索引？三大核心收益
- 劇減掃描行數：索引體積 ≪ 數據體積，從 O(N) 降為 O(log N)
- 避免排序臨時表：索引天然有序，`ORDER BY`/`GROUP BY` 直接走索引
- 隨機 IO 變順序 IO：範圍掃描葉子節點鏈表，磁盤順序讀取，吞吐量提升數量級
六、 索引越多越好？三大代價
- 寫入放大：每次 INSERT/UPDATE/DELETE 需同步維護所有索引
- 優化器選擇困難：索引過多導致統計信息收集慢、執行計劃評估時間長
- 空間佔用：每個非聚簇索引葉子節點存主鍵值，體積可觀
七、 索引建立三大策略 (生產環境鐵律)
- 索引列不可使用表達式/函數
-- ❌ 失效
WHERE TO_DAYS(out_date) - TO_DAYS(NOW()) <= 30
-- ✅ 改寫
WHERE out_date < DATE_ADD(NOW(), INTERVAL 30 DAY)
- 索引長度控制：InnoDB 單列 ≤ 767 字節 (utf8mb4 255 字符)，組合索引總長 ≤ 3072 字節。超長列用前綴索引 `INDEX(name(20))`
- 前綴區分度驗證：
