目錄
一、快速定位索引失效的步驟
1. 使用?EXPLAIN?分析執行計劃詳解Mysql的Explain語句
2. 確認索引是否存在
3. 檢查查詢條件是否符合索引規則
二、常見索引失效場景及解決方法?
1. 索引列參與計算或函數
2. 隱式類型轉換
3. 使用?LIKE?以通配符開頭
4. 使用?OR?連接非索引列?
5. 索引選擇性過低
6.?聯合索引順序錯誤
7. 使用?!=?或?<>?操作符
8.?IS NULL?或?IS NOT NULL?條件
9. 多個索引ORDER BY?順序不一致
10.? 全文索引的誤用
三、高級排查工具
1. 開啟慢查詢日志
2. 使用?OPTIMIZER_TRACE?分析優化器決策
3. 強制使用索引測試
四、總結
一、快速定位索引失效的步驟
1. 使用?EXPLAIN?分析執行計劃詳解Mysql的Explain語句
EXPLAIN SELECT * FROM users WHERE age = 25 AND name = 'John';重點關注以下字段：
-
type：若顯示?
ALL（全表掃描）或?index（全索引掃描），可能索引未生效。 -
key：實際使用的索引名稱，若為?
NULL?表示未使用索引。 -
rows：預估掃描的行數，數值過大說明索引可能未生效。
-
Extra：若出現?
Using filesort?或?Using temporary，可能索引未被用于排序或分組。
2. 確認索引是否存在
SHOW INDEX FROM users; -- 查看表的索引信息確保查詢涉及的列（尤其是?WHERE、JOIN、ORDER BY?中的列）已創建索引。
3. 檢查查詢條件是否符合索引規則
-
最左前綴原則：聯合索引?
(a, b, c)?必須按順序使用，跳過中間列會導致后續列無法使用索引。WHERE a=1 AND c=3; -- 僅使用到 a 列的索引，c 列無法生效 -
范圍查詢阻斷索引：范圍查詢（
>、<、BETWEEN）后的列無法使用索引。WHERE a>10 AND b=20; -- 僅 a 列使用索引，b 列無法生效
二、常見索引失效場景及解決方法?
1. 索引列參與計算或函數
-
失效示例：
SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 對索引列使用函數 SELECT * FROM users WHERE age + 10 > 30; -- 對索引列進行運算 -
優化方法：改寫為直接使用索引列。
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
2. 隱式類型轉換
-
失效示例：
-- 假設 phone 是 VARCHAR 類型，但傳入數字 SELECT * FROM users WHERE phone = 13800138000; -- 字符串轉數字導致索引失效 -
優化方法：確保數據類型一致。
SELECT * FROM users WHERE phone = '13800138000';
3. 使用?LIKE?以通配符開頭
-
失效示例：
SELECT * FROM users WHERE name LIKE '%John%'; -- 前導通配符導致索引失效 -
優化方法：盡量使用右通配符。
SELECT * FROM users WHERE name LIKE 'John%'; -- 可能使用索引
4. 使用?OR?連接非索引列?
-
失效示例：
-- 假設 age 有索引，address 無索引 SELECT * FROM users WHERE age = 25 OR address = 'Beijing'; -- 全表掃描 -
優化方法：改用?
UNION?拆分查詢。SELECT * FROM users WHERE age = 25 UNION SELECT * FROM users WHERE address = 'Beijing';
5. 索引選擇性過低
-
問題現象：索引列的值重復率過高（如性別字段），MySQL 可能放棄使用索引。
-
優化方法：刪除低選擇性索引，或結合其他列創建聯合索引。
ALTER TABLE users ADD INDEX idx_gender_age (gender, age);
6.?聯合索引順序錯誤
-
問題現象：聯合索引?
(a, b, c)，但查詢未按最左前綴順序使用，導致索引部分失效。 -
失效示例：
-- 索引 (a, b, c) SELECT * FROM table WHERE b = 2 AND a = 1; -- 正常使用索引（優化器自動調整順序） SELECT * FROM table WHERE a = 1 AND c = 3; -- 僅用到 a 列索引，c 未生效 SELECT * FROM table WHERE b = 2; -- 索引完全失效（未使用最左列 a） -
優化方法：
-
調整查詢條件順序，確保按最左前綴匹配。
-
根據高頻查詢場景，設計合理的聯合索引順序。
-
7. 使用?!=?或?<>?操作符
-
問題現象：非等值查詢（如?
!=、NOT IN）可能導致索引失效。 -
失效示例：
SELECT * FROM users WHERE age != 25; -- 可能全表掃描 SELECT * FROM orders WHERE status NOT IN (1, 2); -
優化方法：
-
改寫為等值查詢或范圍查詢：
SELECT * FROM users WHERE age < 25 OR age > 25; -- 仍可能失效，需結合其他條件 -
若數據分布傾斜，強制使用索引（需測試驗證）：
SELECT * FROM users FORCE INDEX(idx_age) WHERE age != 25;
-
8.?IS NULL?或?IS NOT NULL?條件
-
?問題現象：索引列上使用?
IS NULL?或?IS NOT NULL?可能導致索引失效。 -
失效示例：
SELECT * FROM users WHERE phone IS NULL; -- 可能全表掃描 -
優化方法：若 NULL 值較少，添加條件冗余字段：
ALTER TABLE users ADD COLUMN is_phone_null TINYINT(1) DEFAULT 0; CREATE INDEX idx_phone_null ON users(is_phone_null); SELECT * FROM users WHERE is_phone_null = 1;
9. 多個索引ORDER BY?順序不一致
-
?問題現象：排序字段順序與索引順序不匹配，導致無法利用索引排序。
-
失效示例：
-- 索引 (a, b) SELECT * FROM table WHER ORDER BY a ASC, b DESC; -- 2個索引順序不一致導致失效 -
優化方法：調整聯合索引順序，同升同降。
SELECT * FROM table WHER ORDER BY a ASC, b ASC; 同升同降
10.? 全文索引的誤用
-
?問題現象：錯誤使用?
LIKE?或?MATCH AGAINST?導致索引失效。 -
失效示例：
-- 未使用全文索引 SELECT * FROM articles WHERE content LIKE '%database%'; -
優化方法：
-
對文本搜索需求改用全文索引（FULLTEXT Index）。
-
避免在全文索引列上混合使用?
LIKE?和?MATCH。SELECT * FROM articles WHERE MATCH(content) AGAINST('database');
-
三、高級排查工具
1. 開啟慢查詢日志
-- 配置 my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 -- 記錄執行超過2秒的SQL通過慢日志定位高頻低效 SQL。
2. 使用?OPTIMIZER_TRACE?分析優化器決策
SET optimizer_trace = 'enabled=on';
SELECT * FROM users WHERE age = 25; -- 執行你的查詢
SELECT * FROM information_schema.optimizer_trace; -- 查看優化器選擇索引的過程3. 強制使用索引測試
SELECT * FROM users FORCE INDEX (idx_age) WHERE age = 25; -- 強制使用索引對比強制索引前后的執行時間，判斷優化器是否選錯索引。
四、總結
-
核心原則：索引失效的本質是?無法快速定位數據范圍。
-
關鍵檢查點：
-
避免對索引列進行計算或函數操作。
-
確保查詢條件符合最左前綴原則。
-
注意隱式類型轉換和通配符使用。
-
-
工具輔助：
EXPLAIN、慢查詢日志、OPTIMIZER_TRACE?是排查利器。
 ----- Python的類型)
