以下是導致索引失效的常見情況，分類並舉例説明：
1. 對索引列進行運算或函數操作
當在索引列上使用函數、表達式、計算或類型轉換時，MySQL無法直接使用索引來定位數據。
失效示例：
-- 使用函數
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 使用表達式
SELECT * FROM products WHERE price * 2 > 100;
-- 進行運算
SELECT * FROM employees WHERE salary + 1000 > 5000;
優化方案：
將運算或函數操作轉移到常量一側。
-- 優化後：在create_time上建立索引有效
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
-- 優化後
SELECT * FROM products WHERE price > 50;
2. 使用 NOT LIKE, <>, NOT IN
這些否定操作符通常無法有效利用索引的結構進行快速查找。
-
<>或!=：需要檢查所有不等於該值的記錄，本質上接近全表掃描。 -
NOT LIKE：類似。 -
NOT IN：需要檢查所有不在列表中的值，效率低下。
失效示例：
SELECT * FROM customers WHERE name NOT LIKE 'A%';
SELECT * FROM orders WHERE status <> 'shipped';
SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);
優化方案：
考慮改寫查詢邏輯，或者有時使用 LEFT JOIN ... IS NULL 來替代 NOT IN。
3. 使用 OR 連接條件（並非所有情況）
如果 OR 連接的多個條件中，並非所有列都有索引，MySQL通常會放棄使用索引而進行全表掃描。
失效示例：
假設表有 a（有索引）和 b（無索引）兩個字段。
SELECT * FROM table WHERE a = 1 OR b = 2;
因為 b 列沒有索引，MySQL必須讀取所有行來檢查 b=2 的條件，所以它也會放棄使用 a 列的索引。
優化方案：
-
為
b列也添加索引。 -
使用
UNION或UNION ALL將查詢拆分，確保每個部分都能利用索引。SELECT * FROM table WHERE a = 1 UNION ALL SELECT * FROM table WHERE b = 2;（注意：此方案的前提是
b=2的結果集很小，或者最終也必須為b列創建索引）
4. 隱式類型轉換
如果查詢條件的數據類型與索引列的定義類型不一致，MySQL會進行隱式類型轉換，這相當於在索引列上使用了函數，導致索引失效。
失效示例：
假設 user_id 字段是字符串類型（VARCHAR），但查詢時使用了數字。
SELECT * FROM users WHERE user_id = 123456; -- 失效
MySQL需要將表中每行的 user_id 字符串轉換成數字才能與 123456 比較。
優化方案：
確保類型匹配。
SELECT * FROM users WHERE user_id = '123456'; -- 有效
5. 違反最左前綴原則
這是針對聯合索引（複合索引） 的經典陷阱。聯合索引的順序非常重要，它是從最左列開始有序的。
假設有一個聯合索引 idx_first_last (first_name, last_name)。
有效示例（遵循最左前綴）：
SELECT * FROM employees WHERE first_name = 'Zhang'; -- 使用索引
SELECT * FROM employees WHERE first_name = 'Zhang' AND last_name = 'San'; -- 使用索引
失效示例（違反最左前綴）：
SELECT * FROM employees WHERE last_name = 'San'; -- 失效！跳過了 first_name
這就像打電話簿，電話簿是按（姓，名）聯合排序的。如果你只知道名而不知道姓，是無法快速查找的。
6. 使用 LIKE 以通配符 % 開頭
使用 LIKE 進行模糊查詢時，如果通配符 % 出現在開頭，索引會失效。
失效示例：
SELECT * FROM products WHERE name LIKE '%apple%'; -- 可能全表掃描
SELECT * FROM products WHERE name LIKE '%apple'; -- 失效
有效示例：
SELECT * FROM products WHERE name LIKE 'apple%'; -- 有效，使用索引範圍掃描
因為 ‘apple%’ 是前綴，索引的有序性可以發揮作用。而 ‘%apple’ 不知道開頭是什麼，無法利用索引排序。
7. 索引列使用 IS NULL 或 IS NOT NULL
在某些情況下（特別是當表中允許為NULL的值非常多或非常少時），優化器可能選擇全表掃描而不是索引掃描。
示例：
SELECT * FROM users WHERE phone_number IS NULL; -- 可能失效
優化器會評估使用索引的成本，如果NULL值很多，它可能覺得全表掃描更快。
8. 數據分佈不均勻（優化器放棄索引）
當MySQL的優化器通過統計信息發現，使用索引查詢需要回表的數據量非常大（例如超過表記錄的20%-30%），它可能會認為全表掃描的成本比使用索引更低，從而放棄使用索引。
示例：
假設有一個 gender 字段，上面有索引，但值只有 ‘M‘ 和 ’F‘，且分佈大致為50%/50%。
SELECT * FROM students WHERE gender = 'M'; -- 優化器可能選擇全表掃描
因為需要回表獲取一半的數據，不如直接順序讀整個表。
