前言

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

圖示

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

• 例子：

  SELECT * FROM users WHERE age != 30; 
  

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

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

2. 使用 OR 連接多個條件

• 例子：

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

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

  --創(chuàng)建聯合索引
  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. 對索引字段進行計算操作

• 例子：

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

• 原理：計算和函數操作會改變數據的表現形式，索引無法直接應用。這個查詢中，使用了 YEAR(order_date) 函數來提取 order_date 字段的年份，與 2024 進行比較。
• 解決方案：1.改為直接存儲處理后的數據。2.直接改為當前字段的范圍查詢。

  --范圍查詢
  SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
  --直接存儲處理后的數據
  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. 對索引字段進行類型轉換

• 例子：

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

• 原理：類型轉換會導致數據類型與索引數據類型不匹配，索引失效。
• 解決方案：確保查詢條件的數據類型與索引數據類型一致，避免使用類型轉換。

5. LIKE 頭部模糊查詢

• 例子：

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

• 原理：LIKE 查詢以 % 開頭時，索引無法使用，因為數據庫無法提前確定匹配的范圍。
• 解決方案：避免在 LIKE 查詢中使用前綴 %，改為 LIKE 'john%'。

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

6. NULL 值的查詢

• 例子：

  SELECT * FROM users WHERE age IS NULL; 
  

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

  -- 使用IFNULL() 函數
  SELECT * FROM users WHERE IFNULL(age, -1) = -1;
  -- 使用COALESCE() 函數
  SELECT * FROM users WHERE COALESCE(age, -1) = -1;
  --使用 NOT NULL 約束，修改字段默認值為 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 操作需要去重或聚合數據。這些操作不能直接利用索引來返回唯一結果，通常會導致數據庫掃描整個表（即全表掃描），尤其是在沒有合適索引的情況下。
• 解決方案：使用合適的索引（例如 GROUP BY 列上創(chuàng)建索引），或者將查詢分解成多個步驟。

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

8. JOIN 查詢中沒有適當的索引

• 例子：

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

• 原理：如果連接條件沒有索引，JOIN 查詢可能會導致全表掃描。
• 解決方案：為連接字段創(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; 
  

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

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

10. 表連接順序不當

• 例子：

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

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

  -- 使用子查詢（篩選大表后再去連接）
  SELECT * FROM 
  (SELECT * FROM orders WHERE order_date > '2024-01-01') o
  JOIN users u ON u.id = o.user_id;
  -- 小表驅動大表（如果users表有100條，orders有20萬數據）
  -- 使用 STRAIGHT_JOIN 強制左表為驅動表
  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 提示時的索引失效

• 例子：

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

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

總結

在 SQL 查詢優(yōu)化中，合適的索引設計和查詢結構調整是提高性能的關鍵。通過以下措施可以避免常見的性能瓶頸：

• 使用適當的索引來加速 DISTINCT、GROUP BY、JOIN 和 ORDER BY 操作。
• 優(yōu)化連接順序，確保合理使用索引。
• 避免強制使用或禁用索引，允許查詢優(yōu)化器自動選擇最優(yōu)執(zhí)行計劃。

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

最新評論