前言

在MySQL的查詢優(yōu)化中，索引是一項(xiàng)至關(guān)重要的技術(shù)，它能夠大大提升數(shù)據(jù)檢索的效率。本文將討論這11種常見(jiàn)情況，幫助開(kāi)發(fā)者更好地理解索引的使用及優(yōu)化。

圖示

1. 使用不等式操作符（!=, <, >）

• 例子：

  SELECT * FROM users WHERE age != 30; 
  

• 原理：索引通常用于等值查詢（=）或范圍查詢（>, <），不等式操作無(wú)法有效利用索引。
• 解決方案：避免使用不等式條件，改用范圍查詢。

  SELECT * FROM users WHERE age NOT BETWEEN 30 AND 30;
  SELECT * FROM users WHERE`age > 30`AND`age < 30;
  

2. 使用 OR 連接多個(gè)條件

• 例子：

  SELECT * FROM users WHERE age = 30 OR gender = 'male'; 
  

• 原理：OR 會(huì)導(dǎo)致多個(gè)獨(dú)立查詢，尤其當(dāng)每個(gè)條件涉及不同列時(shí)，索引不會(huì)完全失效，會(huì)快速定位有索引列部分，無(wú)索引列進(jìn)行全部掃描。
• 解決方案：使用 UNION 替代 OR、創(chuàng)建聯(lián)合索引。

  --創(chuàng)建聯(lián)合索引
  create index idx_users_age_gender on users(age,gender);
  SELECT * FROM users WHERE age = 30 OR gender = 'male'; 
  --使用UNION合并子查詢
  SELECT * FROM users WHERE age = 30
  UNION
  SELECT * FROM users WHERE gender = 'male';
  

3. 對(duì)索引字段進(jìn)行計(jì)算操作

• 例子：

  SELECT * FROM orders WHERE YEAR(order_date) = 2024; 
  

• 原理：計(jì)算和函數(shù)操作會(huì)改變數(shù)據(jù)的表現(xiàn)形式，索引無(wú)法直接應(yīng)用。這個(gè)查詢中，使用了 YEAR(order_date) 函數(shù)來(lái)提取 order_date 字段的年份，與 2024 進(jìn)行比較。
• 解決方案：1.改為直接存儲(chǔ)處理后的數(shù)據(jù)。2.直接改為當(dāng)前字段的范圍查詢。

  --范圍查詢
  SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
  --直接存儲(chǔ)處理后的數(shù)據(jù)
  ALTER TABLE orders add column order_year INT; -- 新增字段 order_year
  UPDATE orders SET order_year = YEAR(order_date); 
  SELECT * FROM orders WHERE order_year = 2024;
  

4. 對(duì)索引字段進(jìn)行類型轉(zhuǎn)換

• 例子：

  SELECT * FROM users WHERE CAST(age AS CHAR) = '30'; 
  

• 原理：類型轉(zhuǎn)換會(huì)導(dǎo)致數(shù)據(jù)類型與索引數(shù)據(jù)類型不匹配，索引失效。
• 解決方案：確保查詢條件的數(shù)據(jù)類型與索引數(shù)據(jù)類型一致，避免使用類型轉(zhuǎn)換。

5. LIKE 頭部模糊查詢

• 例子：

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

• 原理：LIKE 查詢以 % 開(kāi)頭時(shí)，索引無(wú)法使用，因?yàn)閿?shù)據(jù)庫(kù)無(wú)法提前確定匹配的范圍。
• 解決方案：避免在 LIKE 查詢中使用前綴 %，改為 LIKE 'john%'。

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

6. NULL 值的查詢

• 例子：

  SELECT * FROM users WHERE age IS NULL; 
  

• 原理：索引對(duì) NULL 值的查詢支持有限，可能無(wú)法高效利用。
• 解決方案：避免頻繁查詢 NULL 值，或者為包含 NULL 值的字段設(shè)計(jì)專門(mén)的索引、將 NULL值替換為其他默認(rèn)值。

  -- 使用IFNULL() 函數(shù)
  SELECT * FROM users WHERE IFNULL(age, -1) = -1;
  -- 使用COALESCE() 函數(shù)
  SELECT * FROM users WHERE COALESCE(age, -1) = -1;
  --使用 NOT NULL 約束，修改字段默認(rèn)值為 0
  ALTER TABLE users MODIFY age NOT NULL DEFAULT 0;
  

7. DISTINCT 或 GROUP BY 操作

• 例子：

  SELECT DISTINCT age FROM users;
  SELECT age, COUNT(*) FROM users GROUP BY age;
  

• 原理：DISTINCT 和 GROUP BY 操作需要去重或聚合數(shù)據(jù)。這些操作不能直接利用索引來(lái)返回唯一結(jié)果，通常會(huì)導(dǎo)致數(shù)據(jù)庫(kù)掃描整個(gè)表（即全表掃描），尤其是在沒(méi)有合適索引的情況下。
• 解決方案：使用合適的索引（例如 GROUP BY 列上創(chuàng)建索引），或者將查詢分解成多個(gè)步驟。

  --創(chuàng)建索引
  CREATE INDEX idx_users_age ON users(age);
  SELECT age, COUNT(*) FROM users GROUP BY age;
  
  --子查詢獲取去重結(jié)果集再查詢
  SELECT age, COUNT(*) 
  FROM users 
  WHERE age IN (
      SELECT DISTINCT age 
      FROM users 
      WHERE age IS NOT NULL
  )
  GROUP BY age;
  

8. JOIN 查詢中沒(méi)有適當(dāng)?shù)乃饕?/h2>

• 例子：

  SELECT * FROM users u JOIN orders o ON u.id = o.user_id; 
  

• 原理：如果連接條件沒(méi)有索引，JOIN 查詢可能會(huì)導(dǎo)致全表掃描。
• 解決方案：為連接字段創(chuàng)建索引，確保連接操作高效執(zhí)行。

  CREATE INDEX idx_user_id ON orders(user_id);
  CREATE INDEX idx_user_id_users ON users(id);
  

• 使用合適的連接類型：在某些情況下，使用 INNER JOIN、LEFT JOIN 或其他連接類型可以影響查詢性能，選擇最合適的連接方式可以幫助優(yōu)化性能。

9. 排序（ORDER BY）與索引不匹配

• 例子：

  SELECT * FROM users ORDER BY name DESC,age ASC; 
  

• 原理：如果索引的順序與查詢的排序要求不匹配，可能無(wú)法利用索引。
• 解決方案：確保查詢的排序方式與索引的順序一致，使用復(fù)合索引支持多種排序需求。

  -- 創(chuàng)建復(fù)合索引
  CREATE INDEX idx_name_age ON users(name DESC, age ASC); 
  SELECT * FROM users ORDER BY name DESC, age ASC;
  

10. 表連接順序不當(dāng)

• 例子：

  SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.order_date > '2024-01-01'; 
  

• 原理：連接順序不當(dāng)可能導(dǎo)致某些表的索引無(wú)法使用，從而降低查詢性能。
• 解決方案：根據(jù)數(shù)據(jù)量和索引設(shè)計(jì)優(yōu)化 JOIN 順序。

  -- 使用子查詢（篩選大表后再去連接）
  SELECT * FROM 
  (SELECT * FROM orders WHERE order_date > '2024-01-01') o
  JOIN users u ON u.id = o.user_id;
  -- 小表驅(qū)動(dòng)大表（如果users表有100條，orders有20萬(wàn)數(shù)據(jù)）
  -- 使用 STRAIGHT_JOIN 強(qiáng)制左表為驅(qū)動(dòng)表
  SELECT * 
  FROM users u
  STRAIGHT_JOIN orders o ON u.id = o.user_id
  WHERE o.order_date > '2024-01-01';
  

11. 啟用 NO_INDEX 或 FORCE INDEX 提示時(shí)的索引失效

• 例子：

  SELECT * FROM users FORCE INDEX (idx_name) WHERE age = 30;
  

• 原理：強(qiáng)制索引或禁止索引可能導(dǎo)致查詢優(yōu)化器無(wú)法選擇最優(yōu)的執(zhí)行計(jì)劃。
• 解決方案：避免使用 FORCE INDEX 或 NO_INDEX，讓數(shù)據(jù)庫(kù)自動(dòng)選擇最優(yōu)索引。

總結(jié)

在 SQL 查詢優(yōu)化中，合適的索引設(shè)計(jì)和查詢結(jié)構(gòu)調(diào)整是提高性能的關(guān)鍵。通過(guò)以下措施可以避免常見(jiàn)的性能瓶頸：

• 使用適當(dāng)?shù)乃饕齺?lái)加速 DISTINCT、GROUP BY、JOIN 和 ORDER BY 操作。
• 優(yōu)化連接順序，確保合理使用索引。
• 避免強(qiáng)制使用或禁用索引，允許查詢優(yōu)化器自動(dòng)選擇最優(yōu)執(zhí)行計(jì)劃。

到此這篇關(guān)于MySQL進(jìn)階之路索引失效的11種情況的文章就介紹到這了,更多相關(guān)MySQL索引失效情況內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論