MySQL索引使用一定有效嗎？如何排查索引效果？
1. 索引一定有效嗎？
不一定！ 即使你創建了索引，MySQL 也可能因為以下原因 不使用索引 或 索引效果不佳：
- 索引選擇錯誤：MySQL 優化器可能選擇了錯誤的索引。
- 索引失效場景：某些 SQL 寫法會導致索引失效。
- 數據分布問題：數據量太少或數據分布不均，導致全表掃描更快。
- 索引設計不合理：索引列順序、類型不匹配等。
2. 索引失效的常見場景
(1) 使用 !=、NOT IN、NOT EXISTS
SELECT * FROM users WHERE age != 20; -- 可能不走索引
SELECT * FROM users WHERE id NOT IN (1, 2, 3); -- 可能全表掃描
(2) 使用 LIKE 以通配符開頭
SELECT * FROM users WHERE name LIKE '%Alice%'; -- 不走索引
SELECT * FROM users WHERE name LIKE 'Alice%'; -- 可能走索引
(3) 對索引列使用函數或計算
SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 不走索引
-- 優化：使用范圍查詢
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
(4) 數據類型不匹配（隱式轉換）
-- name 是 VARCHAR，但用數字查詢
SELECT * FROM users WHERE name = 123; -- 不走索引（隱式轉換成字符串）
(5) 復合索引未遵循最左前綴原則
-- 假設有聯合索引 (name, age)
SELECT * FROM users WHERE age = 20; -- 不走索引（缺少 name 條件）
(6) 數據量太少
- 如果表只有幾十行數據，MySQL 可能直接全表掃描，因為索引查找+回表的開銷更大。
3. 如何排查索引效果？
方法 1：使用 EXPLAIN 分析執行計劃
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
重點關注：
type：查詢類型（const>ref>range>index>ALL）key：實際使用的索引rows：預估掃描行數Extra：額外信息（Using index表示覆蓋索引）
示例輸出：
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | ref | idx_name | 1 | NULL |
type=ALL：全表掃描（索引可能未生效）key=NULL：未使用索引
方法 2：檢查索引使用情況
-- 查看表的索引
SHOW INDEX FROM users;-- 查看索引使用統計（需開啟性能模式）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'users';
index_name：索引名稱count_read：索引被讀取次數（越高說明索引越有效）
方法 3：使用 OPTIMIZER_TRACE 查看優化器決策
-- 開啟優化器跟蹤
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;-- 執行查詢
SELECT * FROM users WHERE name = 'Alice';-- 查看優化器決策
SELECT * FROM information_schema.optimizer_trace;
- 可以看到 MySQL 為什么選擇（或不選擇）某個索引。
方法 4：強制使用索引（測試對比）
-- 強制使用索引
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'Alice';-- 對比性能
EXPLAIN SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'Alice';
EXPLAIN SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = 'Alice';
- 如果強制索引后查詢變快，說明優化器可能選錯了索引。
4. 如何優化索引？
(1) 選擇合適的索引列
- 高選擇性列（如
user_id比gender更適合索引）。 - 頻繁查詢的列（如
WHERE、ORDER BY、JOIN條件）。
(2) 使用覆蓋索引
-- 優化前：需要回表
SELECT id, name, age FROM users WHERE name = 'Alice';-- 優化后：使用 (name, age) 聯合索引，避免回表
ALTER TABLE users ADD INDEX idx_name_age (name, age);
(3) 避免索引冗余
-- 已有 (name, age) 索引，再建 (name) 就是冗余的
ALTER TABLE users DROP INDEX idx_name;
(4) 定期分析表
-- 更新索引統計信息
ANALYZE TABLE users;-- 重建索引（修復碎片化）
OPTIMIZE TABLE users;
5. 總結
| 問題 | 解決方案 |
|---|---|
| 索引未生效 | 檢查 EXPLAIN，避免索引失效場景 |
| 優化器選錯索引 | 使用 FORCE INDEX 或 OPTIMIZER_TRACE 分析 |
| 索引效果差 | 優化索引設計，使用覆蓋索引 |
| 數據量太少 | 可能不需要索引 |
📌 建議：
- 使用
EXPLAIN分析關鍵查詢。 - 避免索引失效寫法（如
LIKE '%xxx%、函數計算）。 - 定期檢查索引使用情況，刪除冗余索引。
