MySQL 的最左前綴匹配原則主要是針對復合索引（也稱為聯合索引）而言的。其核心思想是：只有查詢條件中包含索引最左側（第一列）開始的連續一段列，才能讓 MySQL 有效地利用該索引。

一、 復合索引的結構

• 復合索引：在一個復合索引中，多個列被組合在一起構成一個索引。例如，一個索引定義為 (col1, col2, col3)，其內部數據是按 col1 排序的，當 col1 相同時，再按 col2 排序，以此類推。
• 排序方式：由于索引是按照從最左側到最右側的順序存儲的，所以查詢時如果能夠利用這個順序，就能高效地定位數據。

二、 最左前綴匹配原則的基本概念

• 定義：當查詢條件中包含索引的最左側一列（或連續多列）時，MySQL 才能利用該復合索引來加速查詢。只要查詢中缺少了最左側開始的一部分條件，就會使后面的列無法利用索引。
• 連續性要求：例如，對于索引 (col1, col2, col3)：
  • 如果查詢中有 col1 = ?，則整個索引可以被使用；
  • 如果查詢中有 col1 = ? AND col2 = ?，索引仍然可用；
  • 如果查詢中有 col1 = ? AND col2 > ?，索引也可以使用，但在范圍查詢后（>、<、BETWEEN 等條件）后面的 col3 則無法利用；
  • 如果查詢中缺失 col1，比如只使用 col2 = ? AND col3 = ?，則該復合索引不能被使用。

三、 工作原理與原理示例

• 如何工作：當 MySQL 在執行查詢時，會嘗試匹配復合索引中的列。若查詢條件中從最左側開始的連續列都被用到了，那么索引就可以按序列進行范圍查找或定位，從而加快查詢速度。
• 舉例說明：

  • 有效使用索引：

    SELECT * FROM table WHERE col1 = 'A' AND col2 = 'B';
    

    此查詢能利用 (col1, col2, col3) 索引，因為查詢條件中包含了索引的最左側兩列。

  • 無效使用索引：

    SELECT * FROM table WHERE col2 = 'B' AND col3 = 'C';
    

    此查詢不能利用 (col1, col2, col3) 索引，因為缺少了最左側的 col1 條件。

四、 特殊情況與注意事項

• 范圍查詢后的限制：在使用復合索引時，如果條件中出現了范圍查詢（如 >、<、LIKE 'abc%' 等），索引的連續性會中斷。例如：

  SELECT * FROM table WHERE col1 = 'A' AND col2 > 'B' AND col3 = 'C';
  

  在這種情況下，雖然 col1 能匹配，但由于 col2 是一個范圍查詢，col3 的等值匹配就無法繼續利用索引進行快速定位。

• 索引設計建議：

  • 在設計復合索引時，應將查詢中經常用作過濾條件的列放在最前面；
  • 避免在復合索引中把不常用或者選擇性低的列放在最左側；
  • 針對 LIKE 操作，要注意使用通配符的位置。比如 LIKE 'abc%' 能利用索引，而 LIKE '%abc' 則無法利用索引。

總結

最左前綴匹配原則是 MySQL 利用復合索引優化查詢性能的重要規則。理解和正確應用這一原則，不僅能讓查詢更高效，還能幫助設計更合理的索引結構。關鍵在于確保查詢條件從索引的最左側開始連續覆蓋，這樣 MySQL 才能充分利用索引帶來的性能優勢。