MySQL索引最佳左前綴法則詳解

基本概念

最佳左前綴法則（Leftmost Prefix Rule）是MySQL中複合索引使用的一條重要規則，它決定了查詢時索引能否被有效利用。具體來説，在使用複合索引時，查詢條件必須從索引的最左列開始，並且不能跳過中間的列。

工作原理

複合索引的內部結構是按照索引列順序構建的B+樹。索引的排序首先基於第一列，然後在第一列相等的情況下基於第二列，依此類推。因此，只有從最左列開始匹配，MySQL才能沿着B+樹高效查找。

具體表現

假設有一個複合索引 idx(a,b,c)：

1. 有效使用索引的情況：

   • 查詢條件包含a列 WHERE a = ?
   • 查詢條件包含a和b列 WHERE a = ? AND b = ?
   • 查詢條件包含全部三列 WHERE a = ? AND b = ? AND c = ?
   • 查詢條件包含a和c列但有範圍查詢 WHERE a = ? AND c > ?（此時只有a列能用到索引）

2. 無法使用索引或只能部分使用的情況：

   • 跳過最左列 WHERE b = ?（無法使用索引）
   • 跳過中間列 WHERE a = ? AND c = ?（只能使用a列部分的索引）
   • 索引列在條件中不連續 WHERE b = ? AND a = ?（雖然會被優化器重排，但建議按索引順序書寫）

實際案例分析

案例1：索引完全匹配

-- 表結構和索引
CREATE TABLE users (id INT, a INT, b INT, c INT, INDEX idx_a_b_c (a,b,c));

-- 查詢使用索引
EXPLAIN SELECT * FROM users WHERE a = 1 AND b = 2 AND c = 3;
-- type可能為ref，key顯示idx_a_b_c，key_len為三列的長度之和

案例2：只使用最左前綴

-- 只使用a列
EXPLAIN SELECT * FROM users WHERE a = 1;
-- 仍能使用索引，type可能為ref或range

-- 使用a和b列
EXPLAIN SELECT * FROM users WHERE a = 1 AND b = 2;
-- 能使用索引的前兩列，key_len為a和b列長度之和

案例3：違反最佳左前綴法則

-- 跳過a列，無法使用索引
EXPLAIN SELECT * FROM users WHERE b = 2;
-- type可能為ALL（全表掃描），key為NULL

-- 跳過b列，只使用a列索引
EXPLAIN SELECT * FROM users WHERE a = 1 AND c = 3;
-- 只能使用a列索引，c列條件無法通過索引過濾

優化建議

1. 索引列順序設計：

   • 將最常作為查詢條件的列放在最前面
   • 將區分度高的列放在前面
   • 考慮列的更新頻率，更新頻繁的列儘量放在後面

2. 查詢條件優化：

   • 按索引順序編寫WHERE條件（雖然MySQL優化器會重排，但可讀性更好）
   • 避免在索引列上使用函數或表達式，這會導致索引失效
   • 對於範圍查詢後的列，索引無法使用，應合理安排索引列順序

3. 覆蓋索引考慮：

   • 如果SELECT的列都在索引中，可以形成覆蓋索引，減少回表操作
   • 例如：SELECT a,b FROM users WHERE a = 1 AND b = 2 可能使用索引且避免回表

與索引失效的關係

最佳左前綴法則被違反是導致索引失效的常見原因之一。此外，索引列上的計算、函數調用、類型轉換、不等於條件、NOT IN、LIKE以通配符開頭等情況也可能導致索引失效。

實際應用中的注意事項

1. 聯合索引與單索引選擇：

   • 如果有多個單列索引，MySQL優化器通常只會選擇其中一個效率最高的索引
   • 合理設計聯合索引可以替代多個單列索引，減少索引維護開銷

2. 查詢重寫技巧：

   • 當遇到範圍查詢後面的列無法使用索引時，可以考慮將範圍查詢條件分解或使用覆蓋索引
   • 對於WHERE a > 10 AND b = 20，如果a的範圍很大，b列的條件無法使用索引，可考慮調整索引順序為(b,a)

3. EXPLAIN分析：

   • 使用EXPLAIN查看執行計劃，通過key和key_len字段判斷索引使用情況
   • 關注possible_keys和key的差異，瞭解優化器的選擇

通過理解和遵循最佳左前綴法則，可以顯著提高查詢效率，減少數據庫服務器負載，是MySQL性能優化的重要基礎。