1. 索引失效的常見原因

雖然索引可以加速查詢，但在某些情況下，MySQL 可能不會使用索引，甚至使用索引反而更慢。
以下是一些常見導致索引失效的原因：

① 查詢條件使用了 != 或 <>

• 原因：索引通常用于范圍或等值查詢，而 != 無法高效利用 B+ 樹索引。

• 示例

  SELECT * FROM users WHERE age != 30;
  

  • 失效原因：!= 使 MySQL 需要掃描大量行，索引無法高效過濾。

② OR 連接多個條件，但只有部分字段有索引

• 示例

  SELECT * FROM users WHERE age = 30 OR name = 'Alice';
  

  • 失效原因：如果 name 字段沒有索引，MySQL 可能選擇 全表掃描 而不是使用 age 的索引。

• 優化方法：為 name 字段也添加索引，或者拆分查詢：

    SELECT * FROM users WHERE age = 30
  UNION
  SELECT * FROM users WHERE name = 'Alice';
  

③ LIKE 以 % 開頭

• 示例

  SELECT * FROM users WHERE name LIKE '%Alice%';
  

  • 失效原因：B+ 樹索引按照前綴匹配，以 % 開頭無法使用索引。

  • 優化方法：

    • 如果 name需要前綴匹配，可以使用

      前綴索引：

      CREATE INDEX idx_name ON users(name(3)); -- 僅索引前3個字符
      

    • 或者改用，全文索引：

      ALTER TABLE users ADD FULLTEXT(name);
      SELECT * FROM users WHERE MATCH(name) AGAINST('Alice');
      

④ 隱式類型轉換

• 示例

  SELECT * FROM users WHERE phone = 13800001234;  -- phone 是 VARCHAR 類型
  

  • 失效原因：phone 是 VARCHAR，但查詢時寫成 INT，MySQL 進行隱式轉換，導致索引失效。

  • 優化方法：使用正確的數據類型：

       
    SELECT * FROM users WHERE phone = '13800001234';
    

⑤ IS NULL 或 IS NOT NULL

• 示例

  SELECT * FROM users WHERE address IS NULL;
  

  • 失效原因：B+ 樹索引不存儲 NULL 值，查詢 NULL 可能導致索引失效。

• 優化方法：

  • 避免 NULL 值，改用默認值：

    ALTER TABLE users MODIFY address VARCHAR(255) NOT NULL DEFAULT '';
    

  • 使用 col IS NOT NULL 可能仍然走索引（視索引情況而定）。

⑥ 低選擇性字段

• 示例

  • 失效原因：gender 只有 male 和 female，選擇性低，索引加速效果不明顯，MySQL 可能選擇全表掃描。

• 優化方法：

  • 索引一般適用于高選擇性字段，如 id、email。

  • 如果 gender 需要頻繁查詢，可以考慮

    聯合索引，例如：

    CREATE INDEX idx_gender_age ON users(gender, age);
    

    這樣，查詢 WHERE gender = 'male' AND age > 30時仍能利用索引。

---

2. 如何排查索引效果？

可以使用 EXPLAIN 命令分析 SQL 是否走索引，以及索引的效率。

① 使用 EXPLAIN 分析 SQL 執行計劃

EXPLAIN SELECT * FROM users WHERE age = 30;

返回示例：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ref	idx_age	idx_age	4	const	10	Using index

• type = ref：表示使用了索引。
• key = idx_age：表示使用了 age 索引。
• rows = 10：表示掃描了 10 行數據，索引效果較好。

如果 type = ALL，表示全表掃描，說明索引可能失效！

---

② SHOW INDEX FROM table_name 查看索引

SHOW INDEX FROM users;

查看表 users 上的索引信息，確認索引是否創建正確。

---

③ ANALYZE TABLE & OPTIMIZE TABLE

如果表數據更新較多，索引可能變得不高效，可以手動優化：

ANALYZE TABLE users;  -- 更新索引統計信息
OPTIMIZE TABLE users; -- 重建索引

---

總結

影響索引使用的因素	是否會導致索引失效	解決方案
!= / < >	? 失效	改用 BETWEEN 或 IN
OR 但部分字段無索引	? 失效	拆分查詢或為所有字段加索引
LIKE '%xxx%'	? 失效	改用前綴索引或全文索引
類型轉換	? 失效	確保查詢和字段類型一致
IS NULL	? 可能失效	使用默認值替代 NULL
低選擇性索引	? 可能失效	使用聯合索引提高選擇性
EXPLAIN 顯示 ALL	? 失效	重新設計索引或優化 SQL

使用 EXPLAIN + SHOW INDEX + ANALYZE TABLE 等工具，可以有效排查 MySQL 索引是否生效，并進行優化。

---

希望這份索引優化指南對你有幫助！如果有任何疑問，歡迎繼續探討 😊🚀