MySQL 索引設計原則與實戰避坑指南

MySQL 索引設計原則與實戰避坑指南

索引是提升查詢效能的核心手段。本文梳理索引設計原則、常見失效場景與 InnoDB 主鍵選擇建議。

索引設計核心原則

1. 最適合索引的欄位：出現在 WHERE、JOIN、ORDER BY、GROUP BY 子句中的欄位。
2. 高基數優先：唯一值越多（如身分證、手機號），索引選擇性越高；低基數欄位（如性別）索引效果差。
3. 前綴索引：長字串欄位（VARCHAR/TEXT）指定前綴長度（如 INDEX idx_name(name(10))），節省空間。
4. 最左前綴原則：複合索引 (a,b,c)，查詢必須從最左列開始，不可跳過中間列。
5. 避免過度索引：每次 INSERT/UPDATE/DELETE 需維護索引，佔用空間；小表全表掃描可能比走索引更快。
6. InnoDB 主鍵選短：二級索引葉子節點存主鍵值，主鍵過大會放大所有二級索引體積；建議用自增整數或短業務鍵。
7. 索引列不參與運算：WHERE YEAR(create_time) = 2023 會導致索引失效，應改為範圍查詢 create_time >= '2023-01-01' AND create_time < '2024-01-01'。

實戰失效案例分析

-- 建表
CREATE TABLE friends (
  uid INT,
  fuid INT,
  PRIMARY KEY (uid, fuid)
) ENGINE=InnoDB;

-- 複合索引 (uid, fuid)
-- 案例 1：WHERE uid = 1 AND fuid = 2  → 命中索引
-- 案例 2：WHERE fuid = 2             → 未從最左列開始，全表掃描
-- 案例 3：WHERE uid = 1               → 命中索引最左列
-- 案例 4：WHERE uid > 10              → 範圍查詢後列 fuid 無法用索引
-- 案例 5：WHERE uid = 1 ORDER BY fuid → 命中索引，避免 filesort
-- 案例 6：WHERE uid = 1 ORDER BY fuid DESC → 同樣命中（MySQL 8.0+ 支援降序索引）
-- 案例 7：WHERE uid = 1 AND fuid > 10 ORDER BY fuid → 命中
-- 案例 8：WHERE fuid = 2 ORDER BY uid → 未命中，需 filesort

索引失效常見原因速查表

• 隱式類型轉換（字串欄位比較數字）
• 對索引列使用函數/運算（col + 1 = 10）
• LIKE 以萬用字元開頭（%keyword）
• OR 條件兩側索引不一致
• IS NULL/IS NOT NULL（部分版本）
• 複合索引未遵循最左前綴

總結

索引設計是空間換時間的藝術。遵循最左前綴、高基數優先、避免列運算、控制索引數量，並定期用 EXPLAIN 驗證執行計畫，才能持續保持查詢高效。