MySQL 索引的最左前綴匹配原則詳解

最左前綴匹配原則（Leftmost Prefix Principle）是 MySQL 復合索引（聯合索引）查詢優化中的核心規則，理解這一原則對于高效使用索引至關重要。

核心概念

定義：當查詢條件包含復合索引的最左列或連續前綴列時，索引才能被有效利用；若跳過最左列或列順序不連續，則無法利用索引的全部或部分功能。

---

核心規則與示例解析

1. 索引列順序決定匹配范圍

假設有復合索引 (a, b, c)：

• 有效查詢：

  WHERE a = 1 AND b = 2 AND c = 3  -- 完全匹配索引列
  WHERE a = 1 AND b = 2            -- 匹配前兩列
  WHERE a = 1                      -- 僅匹配第一列
  

• 無效查詢：

  WHERE b = 2 AND c = 3            -- 跳過最左列a，索引失效
  WHERE c = 3                      -- 僅使用索引末尾列，全表掃描
  

2. 范圍查詢中斷后續列匹配

當使用 >、<、BETWEEN、LIKE（非前綴匹配）等范圍查詢時，該列后的索引列將失效：

-- 索引 (a, b, c)
WHERE a = 1 AND b > 10 AND c = 3  -- 僅a和b有效，c失效
WHERE a = 1 AND b LIKE '張%' AND c = 3  -- 若LIKE是前綴匹配，c仍有效

3. 排序與分組的最左前綴依賴

• 排序：ORDER BY 需與索引列順序一致：

  -- 索引 (a, b, c)
  ORDER BY a, b, c  -- 有效
  ORDER BY a, c     -- 僅a有效，c無法利用索引
  

• 分組：GROUP BY 需包含最左前綴列：

  GROUP BY a, b     -- 有效
  GROUP BY b, c     -- 無效
  

4. 函數/計算導致索引失效

對索引列使用函數或計算會破壞最左前綴：

-- 索引 (a, b, c)
WHERE YEAR(a) = 2023  -- 索引失效（對a列使用函數）
WHERE a + 1 = 10       -- 索引失效（對a列進行計算）

---

優化實踐建議

1. 設計復合索引時：

   • 將高選擇性列（區分度高的列）放在左側
   • 將等值查詢列放在范圍查詢列之前
   • 避免將頻繁更新的列作為索引首列

2. 查詢重構技巧：

   • 范圍查詢優化：將范圍條件后置或拆分為多個查詢

     -- 原查詢（索引(a,b,c)）：
     WHERE a = 1 AND b > 10 AND c = 3  -- c失效
     -- 優化方案：
     SELECT * FROM t WHERE a=1 AND c=3 AND b > 10  -- 調整順序無效，需重構業務邏輯
     

   • LIKE前綴匹配：使用 LIKE '張%' 而非 LIKE '%張'

3. 覆蓋索引優化：

   • 當查詢列完全包含在索引中時，可避免回表：

     -- 索引 (a, b, c)
     SELECT a, b FROM t WHERE a = 1 AND b = 2  -- 覆蓋索引，無需回表
     

---

底層原理說明

MySQL 的 B+ 樹索引按列順序組織數據。復合索引 (a, b, c) 的物理存儲結構如下：

• 第一層按 a 排序
• 第二層在相同 a 值下按 b 排序
• 葉子節點在相同 a, b 值下按 c 排序

因此，只有從最左列開始連續匹配，才能通過索引快速定位數據。

---

特殊場景說明

1. 跳過中間列：

   -- 索引 (a, b, c)
   WHERE a = 1 AND c = 3  -- 僅a有效，c無法直接利用索引（除非a=1的記錄數極少）
   

2. 多列等值查詢：

   -- 索引 (a, b, c)
   WHERE a = 1 AND b = 2 AND c > 3  -- a,b有效，c范圍查詢
   

3. ORDER BY 與 WHERE 結合：

   -- 索引 (a, b, c)
   SELECT * FROM t WHERE a=1 ORDER BY b, c  -- 有效
   SELECT * FROM t WHERE a=1 ORDER BY c, b  -- 僅a有效，c,b無法利用索引
   

---

驗證方法

通過 EXPLAIN 命令檢查查詢是否使用索引：

EXPLAIN SELECT * FROM user WHERE name = '張三' AND age = 20;
-- 查看type列為"ref"或"range"且key顯示實際使用的索引

---

總結

最左前綴匹配原則的本質是 MySQL 復合索引的有序性約束。設計索引時應：

1. 將高頻查詢條件列置于左側
2. 將等值查詢列放在范圍查詢列之前
3. 避免在索引列上使用函數或計算
4. 通過 EXPLAIN 驗證索引使用情況

理解并遵循這一原則，可顯著提升查詢性能，避免因索引失效導致的全表掃描。

我正在程序員刷題神器面試鴨上高效準備面試，9000+ 高頻面試真題、800 萬字優質題解，覆蓋主流編程方向，跟我一起刷原題、過面試：點擊進入