mysql索引失效的現象有哪些

# MySQL索引失效的現象有哪些

## 引言

在MySQL數據庫優化中，索引是提升查詢性能的關鍵手段。然而，即使創建了合適的索引，某些情況下索引可能無法生效，導致查詢性能急劇下降。本文將詳細分析常見的MySQL索引失效現象，幫助開發者識別和避免這些問題。

---

## 1. 索引失效的常見現象

### 1.1 使用`!=`或`<>`操作符
當查詢條件中使用`!=`或`<>`時，MySQL通常無法使用索引：
```sql
-- 索引失效示例
SELECT * FROM users WHERE age != 30;

原因：非等值查詢需要掃描大部分數據，優化器會放棄使用索引。

1.2 對索引列使用函數或運算

對索引列進行函數調用、數學運算或類型轉換會導致索引失效：

-- 索引失效示例
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
SELECT * FROM products WHERE price + 10 > 100;

解決方案：

-- 優化為范圍查詢
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

1.3 使用OR連接非索引列

當OR連接的列中有未建立索引的字段時，索引會失效：

-- 假設name有索引而email無索引
SELECT * FROM users WHERE name = 'John' OR email = 'john@example.com';

解決方案： - 為所有OR條件列創建索引 - 改用UNION ALL：

SELECT * FROM users WHERE name = 'John'
UNION ALL
SELECT * FROM users WHERE email = 'john@example.com';

1.4 模糊查詢以通配符開頭

LIKE查詢以%開頭時索引失效：

-- 索引失效
SELECT * FROM articles WHERE title LIKE '%數據庫%';

解決方案： - 使用全文索引（FULLTEXT） - 考慮搜索引擎如Elasticsearch

1.5 數據類型隱式轉換

當查詢條件與索引列類型不匹配時會發生隱式轉換：

-- 假設mobile是varchar類型
SELECT * FROM users WHERE mobile = 13800138000; -- 數字未加引號

解決方案：確保類型一致：

SELECT * FROM users WHERE mobile = '13800138000';

1.6 復合索引未遵循最左前綴原則

對于復合索引(a,b,c)，以下情況會失效：

-- 有效：使用最左列
SELECT * FROM table WHERE a = 1 AND b = 2;

-- 失效：跳過最左列
SELECT * FROM table WHERE b = 2 AND c = 3;

1.7 使用IS NULL或IS NOT NULL

-- 可能失效（取決于數據分布）
SELECT * FROM employees WHERE department_id IS NULL;

優化建議：為可為NULL的列設置默認值

1.8 查詢范圍條件后的列

當復合索引中某一列使用范圍查詢后，后續列的索引會失效：

-- 復合索引(a,b,c)
SELECT * FROM table WHERE a > 1 AND b = 2; -- 只有a能用索引

2. 如何診斷索引失效

2.1 使用EXPLN分析

通過EXPLN查看執行計劃：

EXPLN SELECT * FROM users WHERE name LIKE '%張%';

關注： - type列：ALL表示全表掃描 - key列：顯示實際使用的索引

2.2 開啟慢查詢日志

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

3. 避免索引失效的最佳實踐

1. 合理設計索引：

   • 為高頻查詢條件創建索引
   • 控制單表索引數量（一般不超過5-6個）

2. 編寫優化SQL：

   • 避免SELECT *
   • 使用覆蓋索引（Covering Index）

3. 定期維護：

   ANALYZE TABLE users;
   OPTIMIZE TABLE orders;
   

4. 監控調整：

   • 使用SHOW INDEX FROM table查看索引基數
   • 關注Handler_read%狀態變量

結語

索引失效是MySQL性能問題的常見原因。通過理解這些典型場景，結合EXPLN工具和慢查詢分析，可以顯著提升查詢效率。建議在開發過程中建立索引使用規范，并定期進行SQL審計和優化。

提示：不同MySQL版本（如5.7 vs 8.0）的索引優化策略可能有所差異，建議根據實際環境進行測試驗證。 “`

注：本文約1300字，涵蓋主要索引失效場景及解決方案，采用Markdown格式便于技術文檔的傳播和編輯?？筛鶕嶋H需要補充具體案例或性能對比數據。