MySQL 索引學習筆記：B-Tree/Hash 索引適用場景、限制與建立策略

MySQL 索引分 B-Tree（含 B+Tree） 與 Hash 兩大類，不同存儲引擎支援不同。本文系統整理適用條件、限制、建立策略與選擇性分析。

一、B-Tree 索引適用場景

二、B-Tree 索引限制

1. 非最左列開頭無法使用：INDEX(a,b,c)，WHERE b=1 不走索引
2. 不能跳過列：INDEX(日期,姓名,電話)，WHERE 日期=... AND 電話=... 僅用「日期」
3. NOT IN、<> 不走索引（全表掃描）
4. 範圍列右側列失效：WHERE a=1 AND b>2 AND c=3，「c」無法用索引

三、Hash 索引特點與限制

• 基於 Hash 表，**僅支援等值查詢**（=、IN）
• 存儲引擎對所有索引列計算 Hash 碼，葉節點存 Hash 值 + 行指標
• 必須兩次讀取：先查 Hash 找行指標，再讀行資料
• 不支援排序、範圍、前綴、部分列匹配
• Hash 衝突風險：低選擇性欄位（如性別）極易衝突，**嚴禁建 Hash 索引**

四、為什麼要用索引？

1. 大幅減少存儲引擎掃描資料量（索引 ≪ 資料）
2. 天然有序，避免 ORDER BY/GROUP BY 臨時表排序
3. 將隨機 I/O 轉為順序 I/O（範圍掃描）

五、索引越多越好？

• 寫入成本線性增長（每個索引皆需維護）
• 優化器選擇時間增加（統計資訊越多、選擇越慢）
• MySQL 單表索引上限 16 個（含主鍵）

六、建立索引策略

1. 索引列不可用函數/表達式：

   -- 錯誤
   WHERE TO_DAYS(out_date) - TO_DAYS(NOW()) <= 30
   -- 正確
   WHERE out_date < DATE_ADD(NOW(), INTERVAL 30 DAY)

2. 索引長度限制：InnoDB 單列 ≤ 767 bytes（組合 ≤ 3072 bytes），超長用前綴索引
3. 選擇性優先：選擇性 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好
4. 聯合索引列序：
   1. 高頻查詢列優先
   2. 高選擇性列優先
   3. 小長度列優先（節省空間）

七、前綴索引選擇性測試

SELECT
  COUNT(DISTINCT LEFT(account, 10)) / COUNT(*) AS sel10,
  COUNT(DISTINCT LEFT(account, 15)) / COUNT(*) AS sel15
FROM t;
-- 選擇性接近全列時，採用該前綴長度

索引是資料庫效能杠桿，但需「精準施力、定期複盤」。