MySQL 索引深度解析：B-Tree 與 Hash 索引的原理、適用場景與優化策略

前言

索引是資料庫效能調校的核心關鍵。在 MySQL 中，最常見的兩大索引類型為 B-Tree 索引 與 Hash 索引。本文將系統梳理兩者的運作機制、適用條件、限制細節，並給出建立索引的實戰策略，助你在海量資料下依然能維持毫秒級查詢。

一、B-Tree 索引：關係型資料庫的「標準配備」

B-Tree（平衡樹）索引將資料按順序儲存，支援精確查找、範圍查詢、排序與分組。InnoDB 的主鍵索引即為聚簇索引，葉子節點直接包含完整行資料；輔助索引葉子節點則存主鍵值，查詢時需「回表」取完整行。

1. 可使用 B-Tree 索引的六大場景

1. 全值匹配：WHERE order_id = '123' — 所有索引列皆精確給值。
2. 最左前綴匹配：聯合索引 (user_id, create_time)，僅給 user_id 可用索引；單給 create_time 則不可用。
3. 列前綴匹配：WHERE order_sn LIKE '134%' — 通配符在右側可用索引。
4. 範圍值查詢：create_time > '2015-01-09' AND create_time < '2015-01-10'。
5. 精確匹配左列 + 範圍匹配右列：user_id = 1 AND create_time > '2016-09-18' — 範圍列右側的索引列將失效。
6. 覆蓋索引：SELECT user_id, create_time FROM t WHERE user_id = 1 — 查詢列全在索引中，無需回表，極大提升效能。

2. B-Tree 索引的四大限制

1. 必須從最左列開始：聯合索引 (order_id, create_time)，單查 create_time 會全表掃描。
2. 不可跳過索引列：索引 (date, name, phone)，查 date, phone 僅用到 date。
3. NOT IN、<> 無法使用索引：會導致全表掃描。
4. 範圍查詢右側列失效：WHERE a = 1 AND b > 2 AND c = 3 中 c 不走索引。

二、Hash 索引：極速等值查找的「特種兵」

Hash 索引基於雜湊表實現，僅支援精確等值查詢（=、IN），不支援排序、範圍、前綴匹配。儲存引擎對索引列計算 Hash 碼，索引中僅存 Hash 碼與行指標。

Hash 索引的三大限制

1. 需兩次讀取：先查 Hash 碼定位行指標，再回表讀資料。
2. 無法用於 ORDER BY / GROUP BY 排序。
3. 不支援部分匹配與範圍查詢。

Hash 衝突與選擇性

選擇性極低的欄位（如性別）建 Hash 索引會產生大量衝突，反而拖慢效能。僅在高選擇性列（如用戶 ID、訂單號）考慮使用。

三、為什麼需要索引？

1. 大幅減少掃描資料量：索引體積遠小於資料表。
2. 避免臨時表排序：索引天然有序，可直接滿足 ORDER BY。
3. 隨機 I/O 轉順序 I/O：範圍掃描時順序讀取葉子節點，磁碟吞吐最大化。

四、索引並非越多越好

• 寫入成本上升：每次 INSERT/UPDATE/DELETE 需同步維護所有索引。
• 優化器選擇時間變長：過多索引增加執行計劃評估開銷。

五、建立索引的五大實戰策略

1. 索引列不參與運算/函數：
   ❌ WHERE TO_DAYS(out_date) - TO_DAYS(NOW()) <= 30
   ✅ WHERE out_date < DATE_ADD(NOW(), INTERVAL 30 DAY)
2. 控制索引長度：InnoDB 單列索引長度上限 767 bytes（開啟 innodb_large_prefix 可至 3072 bytes），超長字串用前綴索引。
3. 前綴索引選擇性權衡：CREATE INDEX idx_name ON t(account(10)) — 取前 10 字元，平衡空間與區分度。
4. 聯合索引列序遵循「高選擇性優先、常用於 WHERE/JOIN/ORDER BY、短列優先」。
5. 覆蓋索引優化：常見查詢若能只走索引，適度增加索引列以實現覆蓋。

六、總結

索引設計是空間換時間、維護成本換查詢速度的藝術。掌握 B-Tree 與 Hash 的適用邊界、熟記最左前綴原則、善用覆蓋索引與前綴索引，並定期用 EXPLAIN 驗證執行計劃，才能讓 MySQL 在高併發、大數據場景下穩如磐石。