復合索引失效主要由以下幾種情況導致：1. 范圍查詢（如like或>）出現在復合索引列的中間，導致索引只能使用到范圍查詢之前的列；2. 查詢條件未按索引列順序排列，mysql無法利用索引的順序特性；3. 對索引列使用函數或表達式操作，mysql無法直接利用索引；4. 索引列類型與查詢條件類型不匹配，需要進行類型轉換；5. 索引列包含null值，且查詢條件使用is null或is not NULL。 理解這些失效場景，并根據查詢條件設計合適的復合索引，才能提升數據庫查詢效率。

復合索引失效？別慌，讓我來給你扒一扒！

很多朋友在數據庫優化時，都聽過“復合索引最左前綴原則”。 它就像數據庫優化里的武林秘籍，但秘籍上往往沒寫清楚失效的場景，導致不少人掉坑里。這篇文章，咱們就來深入探討一下這個原則失效的那些事兒，讓你徹底掌握它！

這篇文章的目標很簡單：讓你徹底理解復合索引最左前綴原則，并能輕松識別它失效的場景，從而寫出更高效的sql。讀完之后，你將能獨立分析sql語句，并針對索引進行優化，編寫出性能更優的代碼。

咱們先溫習一下基礎知識。復合索引，顧名思義，就是在一個表上建立多個列的索引。比如，在users表上創建index(name, age, city)索引，就是個復合索引。最左前綴原則的核心思想是：mysql會優先使用索引中最左邊的列進行匹配，只有當最左邊的列匹配成功后，才會繼續匹配后面的列。

那么，這個原則啥時候會失效呢？ 情況可不少！

場景一：范圍查詢（Range Query）

假設我們有index(name, age, city)索引，如果你的查詢語句是select FROM users WHERE name LIKE ‘A%’ AND age > 25 AND city = ‘Beijing’;，那么只有name列的索引會被用到。 LIKE操作符和>操作符都是范圍查詢，它們會阻止MySQL繼續使用后面的age和city列的索引。 這是因為范圍查詢的特性決定了它無法高效地利用索引的順序特性。 MySQL引擎在處理范圍查詢時，會先進行范圍掃描，然后根據結果再進行過濾，導致索引后面的列無法參與優化。

場景二：不按索引順序查詢

還是index(name, age, city)索引，如果查詢語句是SELECT FROM users WHERE city = ‘Beijing’ AND age > 25;，索引失效！ 因為你的查詢條件沒有按照索引列的順序排列。MySQL只會在city列上進行查找，而不會利用age列的索引。 記住，順序很重要！ 這就像你拿著地圖找地方，如果路線不對，就算地圖再好也白搭。

場景三：函數或者表達式操作索引列

如果你的查詢語句是SELECT FROM users WHERE UPPER(name) = ‘JOHN’ AND age = 30;，索引失效！因為UPPER()函數會對name列進行轉換，MySQL無法直接利用索引。 類似的，如果你的查詢條件中包含表達式，例如SELECT FROM users WHERE name + age > 100;，索引也可能失效。 MySQL需要先計算表達式，然后再進行匹配，這會降低查詢效率。

場景四：索引列類型不匹配

如果索引列是數值型，而查詢條件是字符串類型，或者類型不匹配，索引也可能失效。 MySQL需要進行類型轉換，這會增加額外的開銷。

場景五：NULL值

如果索引列包含NULL值，那么在查詢條件中使用IS NULL或IS NOT NULL時，索引可能失效。 這是因為NULL值無法進行比較。

代碼示例 (MySQL):

假設我們的users表有如下結構和數據：

CREATE table users (</p><pre class='brush:sql;toolbar:false;'>id INT PRIMARY KEY, name VARCHAR(255), age INT, city VARCHAR(255)

);

INSERT INTO users (id, name, age, city) VALUES
(1, ‘Alice’, 25, ‘Beijing’),
(2, ‘Bob’, 30, ‘Shanghai‘),
(3, ‘Charlie’, 28, ‘Beijing’),
(4, ‘David’, 35, ‘Guangzhou’);

CREATE INDEX idx_name_age_city ON users (name, age, city);

以下查詢語句會利用索引：

SELECT <em> FROM users WHERE name = 'Alice' AND age = 25 AND city = 'Beijing';

以下查詢語句索引會部分失效或完全失效：

SELECT </em> FROM users WHERE age = 25 AND name = 'Alice';  --順序不對<br>SELECT * FROM users WHERE name LIKE 'A%' AND age > 25; --范圍查詢<br>SELECT * FROM users WHERE city = 'Beijing'; --只用city索引

性能優化與建議

在設計索引時，要充分考慮查詢語句的模式。 盡量按照查詢條件的順序創建復合索引，避免范圍查詢出現在索引列的中間位置。 合理使用索引，才能提升數據庫查詢效率。 如果發現索引失效，需要仔細分析SQL語句和索引結構，找出問題所在，并進行相應的調整。 此外，定期進行數據庫性能監控和分析，可以及時發現并解決潛在的性能問題。

記住，索引不是萬能的，它只是數據庫優化的一種手段。 只有合理地使用索引，才能讓你的數據庫飛起來！ 別忘了監控你的數據庫，定期分析查詢語句，找到瓶頸，才能真正掌握數據庫優化的精髓。