MySQL中索引失效的8大陷阱及排查指南

更新時間：2025年12月24日 09:38:09 作者：佛祖讓我來巡山

這篇文章主要為大家詳細(xì)介紹了MySQL中索引失效的8大場景以及排查的相關(guān)方法,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下

索引不是萬能的！用不對反而更慢！

索引失效的8大常見場景

1. 索引列上做計(jì)算（最常見）

-- ? 錯誤：在索引列上計(jì)算
SELECT * FROM users WHERE YEAR(created_time) = 2024;
SELECT * FROM products WHERE price * 2 > 100;

-- ? 正確：把計(jì)算移到右邊
SELECT * FROM users WHERE created_time >= '2024-01-01' AND created_time < '2025-01-01';
SELECT * FROM products WHERE price > 50;

原因：數(shù)據(jù)庫必須對每一行都計(jì)算才能比較，無法用索引快速定位。

2. 索引列使用函數(shù)

-- ? 錯誤：使用函數(shù)包裹索引列
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM logs WHERE DATE(create_time) = '2024-01-01';

-- ? 正確：使用范圍查詢
SELECT * FROM users WHERE name = 'john';  -- 存入時統(tǒng)一小寫
SELECT * FROM logs WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

3. 模糊查詢開頭用通配符

-- ? 錯誤：以 % 開頭
SELECT * FROM users WHERE name LIKE '%張%';  -- 全表掃描
SELECT * FROM users WHERE name LIKE '%明';

-- ? 正確：以確定字符開頭
SELECT * FROM users WHERE name LIKE '張%';   -- 可以使用索引
SELECT * FROM users WHERE name LIKE '張_明'; -- _匹配一個字符

生活類比：

張% → 知道姓張，直接翻到張姓區(qū)域 ?
%張 → 不知道姓什么，只能一頁頁翻 ?

4. OR 條件使用不當(dāng)

-- ? 錯誤：OR 條件中有的列沒索引
SELECT * FROM users WHERE age = 25 OR salary > 10000;
-- 如果 salary 沒索引，整個查詢都無法用索引

-- ? 正確：確保 OR 兩邊都有索引
-- 或者分開查詢
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE salary > 10000;

5. 類型不匹配（隱式轉(zhuǎn)換）

-- ? 錯誤：字符串列用數(shù)字查詢
CREATE TABLE users (
    id VARCHAR(20) PRIMARY KEY,  -- 字符串類型
    name VARCHAR(50)
);
SELECT * FROM users WHERE id = 123;  -- 字符串和數(shù)字比較

-- ? 正確：類型一致
SELECT * FROM users WHERE id = '123';

測試隱式轉(zhuǎn)換：

-- 查看執(zhí)行計(jì)劃
EXPLAIN SELECT * FROM users WHERE id = 123;
-- 注意看 key 列是否為 NULL（沒走索引）

6. 聯(lián)合索引沒用最左列

-- 索引：idx_abc (a, b, c)

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

-- ? 無效：沒從最左開始
SELECT * FROM table WHERE b = 2;            -- 跳過 a
SELECT * FROM table WHERE c = 3;            -- 跳過 a,b
SELECT * FROM table WHERE b = 2 AND c = 3;  -- 跳過 a

記憶技巧：就像查字典，必須知道第一個字母才能快速查找。

7. 數(shù)據(jù)量太少，不如全表掃描

-- 假設(shè)表只有 100 行數(shù)據(jù)
SELECT * FROM small_table WHERE id = 50;

-- 數(shù)據(jù)庫會想：走索引要查索引樹 + 回表，不如直接全表掃描更快

8. 索引列選擇性太低

-- 比如"性別"列，只有"男/女"兩種值
CREATE INDEX idx_gender ON users(gender);

SELECT * FROM users WHERE gender = '男';
-- 可能失效，因?yàn)橐祷亟咏话氲臄?shù)據(jù)，不如全表掃描

如何排查索引失效

第1步：用 EXPLAIN 快速診斷

-- 在查詢前加 EXPLAIN
EXPLAIN SELECT * FROM users WHERE name LIKE '%張%';

-- 關(guān)鍵看這幾列：
-- 1. type: ALL → 全表掃描（最差）
-- 2. key: NULL → 沒走索引
-- 3. rows: 數(shù)值很大 → 要掃描很多行
-- 4. Extra: Using filesort, Using temporary → 性能警告

第2步：看懂 EXPLAIN 結(jié)果

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 理想結(jié)果：
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
| id | select_type | table  | type | possible_keys | key     | key_len | rows        | Extra|
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user| 8       | 10          | NULL |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+

-- type 從好到差：
-- system > const > eq_ref > ref > range > index > ALL
-- 至少要到 range 級別才合格

第3步：使用性能分析工具

-- 1. 開啟 profiling（查看詳細(xì)耗時）
SET profiling = 1;
SELECT * FROM users WHERE ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 2. 查看索引使用統(tǒng)計(jì)
SELECT * FROM sys.schema_index_statistics 
WHERE table_name = 'users';

-- 3. 找出從未使用的索引（該刪除了！）
SELECT * FROM sys.schema_unused_indexes;

第4步：慢查詢?nèi)罩痉治?/h3>

-- 1. 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超過2秒記錄

-- 2. 查看慢查詢?nèi)罩疚恢?
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 3. 使用工具分析（推薦）
-- mysqldumpslow 或 pt-query-digest

實(shí)戰(zhàn)排查案例

案例1：為什么這個查詢慢

-- 原始查詢（很慢）
SELECT * FROM orders 
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01'
ORDER BY amount DESC;

-- 步驟1：EXPLAIN分析
EXPLAIN SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01';

-- 發(fā)現(xiàn)：type=ALL, key=NULL（沒走索引）
-- 原因：對 create_time 使用了函數(shù)

-- 優(yōu)化方案：
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2024-02-01'
ORDER BY amount DESC;

-- 再 EXPLAIN：type=range, key=idx_create_time ?

案例2：聯(lián)合索引問題

-- 查詢：經(jīng)常按城市和年齡搜索用戶
SELECT * FROM users WHERE city = '北京' AND age > 25;

-- 現(xiàn)有索引：idx_age_city (age, city)
-- 問題：最左列是 age，但查詢先過濾 city

-- 解決方案：
-- 1. 調(diào)整查詢順序（如果業(yè)務(wù)允許）
SELECT * FROM users WHERE age > 25 AND city = '北京';

-- 2. 或創(chuàng)建新索引
CREATE INDEX idx_city_age ON users(city, age);

索引效果評估表

檢查項(xiàng)	合格標(biāo)準(zhǔn)	如何檢查
索引使用率	> 90%	SHOW STATUS LIKE 'Handler_read%'
查詢響應(yīng)時間	< 100ms	慢查詢?nèi)罩?/td>
掃描行數(shù)	rows < 1000	EXPLAIN 的 rows 列
臨時表使用	盡量避免	EXPLAIN 的 Extra 列
文件排序	盡量避免	EXPLAIN 的 Extra 列

最佳實(shí)踐清單

創(chuàng)建索引前問自己

這個查詢真的需要索引嗎？（數(shù)據(jù)量小不需要）
索引列的選擇性高嗎？（唯一值多嗎？）
會頻繁更新這個列嗎？（更新頻繁的列不適合建索引）
已經(jīng)有類似的索引了嗎？（避免重復(fù)索引）

創(chuàng)建索引后要檢查

-- 1. 驗(yàn)證索引被使用
EXPLAIN SELECT ...;

-- 2. 監(jiān)控索引大小
SELECT 
    table_name,
    index_name,
    ROUND(SUM(index_length)/1024/1024, 2) AS '索引大小(MB)'
FROM information_schema.TABLES 
WHERE table_schema = '你的數(shù)據(jù)庫'
GROUP BY table_name, index_name;

-- 3. 定期清理無用索引
-- 查看使用頻率低的索引
SELECT * FROM sys.schema_unused_indexes;