Mysql索引失效場景

以下是導(dǎo)致索引失效的常見情況，分類并舉例說明：

1. 對索引列進行運算或函數(shù)操作

當(dāng)在索引列上使用函數(shù)、表達式、計算或類型轉(zhuǎn)換時，MySQL無法直接使用索引來定位數(shù)據(jù)。

失效示例：

-- 使用函數(shù)
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 使用表達式
SELECT * FROM products WHERE price * 2 > 100;
-- 進行運算
SELECT * FROM employees WHERE salary + 1000 > 5000;

優(yōu)化方案：
將運算或函數(shù)操作轉(zhuǎn)移到常量一側(cè)。

-- 優(yōu)化后：在create_time上建立索引有效
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
-- 優(yōu)化后
SELECT * FROM products WHERE price > 50;

2. 使用 NOT LIKE, <>, NOT IN

這些否定操作符通常無法有效利用索引的結(jié)構(gòu)進行快速查找。

• <> 或 !=：需要檢查所有不等于該值的記錄，本質(zhì)上接近全表掃描。

• 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);

優(yōu)化方案：
考慮改寫查詢邏輯，或者有時使用 LEFT JOIN ... IS NULL 來替代 NOT IN。

3. 使用 OR 連接條件（并非所有情況）

如果 OR 連接的多個條件中，并非所有列都有索引，MySQL通常會放棄使用索引而進行全表掃描。

失效示例：
假設(shè)表有 a（有索引）和 b（無索引）兩個字段。

SELECT * FROM table WHERE a = 1 OR b = 2;

因為 b 列沒有索引，MySQL必須讀取所有行來檢查 b=2 的條件，所以它也會放棄使用 a 列的索引。

優(yōu)化方案：

• 為 b 列也添加索引。

• 使用 UNION 或 UNION ALL 將查詢拆分，確保每個部分都能利用索引。

  sql

  SELECT * FROM table WHERE a = 1
  UNION ALL
  SELECT * FROM table WHERE b = 2;

  （注意：此方案的前提是 b=2 的結(jié)果集很小，或者最終也必須為 b 列創(chuàng)建索引）

4. 隱式類型轉(zhuǎn)換

如果查詢條件的數(shù)據(jù)類型與索引列的定義類型不一致，MySQL會進行隱式類型轉(zhuǎn)換，這相當(dāng)于在索引列上使用了函數(shù)，導(dǎo)致索引失效。

失效示例：
假設(shè) user_id 字段是字符串類型（VARCHAR），但查詢時使用了數(shù)字。

SELECT * FROM users WHERE user_id = 123456; -- 失效

MySQL需要將表中每行的 user_id 字符串轉(zhuǎn)換成數(shù)字才能與 123456 比較。

優(yōu)化方案：
確保類型匹配。

SELECT * FROM users WHERE user_id = '123456'; -- 有效

5. 違反最左前綴原則

這是針對聯(lián)合索引（復(fù)合索引） 的經(jīng)典陷阱。聯(lián)合索引的順序非常重要，它是從最左列開始有序的。

假設(shè)有一個聯(lián)合索引 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

這就像打電話簿，電話簿是按（姓，名）聯(lián)合排序的。如果你只知道名而不知道姓，是無法快速查找的。

6. 使用 LIKE 以通配符 % 開頭

使用 LIKE 進行模糊查詢時，如果通配符 % 出現(xiàn)在開頭，索引會失效。

失效示例：

SELECT * FROM products WHERE name LIKE '%apple%'; -- 可能全表掃描
SELECT * FROM products WHERE name LIKE '%apple'; -- 失效

有效示例：

SELECT * FROM products WHERE name LIKE 'apple%'; -- 有效，使用索引范圍掃描

因為 ‘a(chǎn)pple%’ 是前綴，索引的有序性可以發(fā)揮作用。而 ‘%apple’ 不知道開頭是什么，無法利用索引排序。

7. 索引列使用 IS NULL 或 IS NOT NULL

在某些情況下（特別是當(dāng)表中允許為NULL的值非常多或非常少時），優(yōu)化器可能選擇全表掃描而不是索引掃描。

示例：

SELECT * FROM users WHERE phone_number IS NULL; -- 可能失效

優(yōu)化器會評估使用索引的成本，如果NULL值很多，它可能覺得全表掃描更快。

8. 數(shù)據(jù)分布不均勻（優(yōu)化器放棄索引）

當(dāng)MySQL的優(yōu)化器通過統(tǒng)計信息發(fā)現(xiàn)，使用索引查詢需要回表的數(shù)據(jù)量非常大（例如超過表記錄的20%-30%），它可能會認為全表掃描的成本比使用索引更低，從而放棄使用索引。

示例：
假設(shè)有一個 gender 字段，上面有索引，但值只有 ‘M‘ 和 ’F‘，且分布大致為50%/50%。

SELECT * FROM students WHERE gender = 'M'; -- 優(yōu)化器可能選擇全表掃描

因為需要回表獲取一半的數(shù)據(jù)，不如直接順序讀整個表。