明明建了索引，為什么還是慢

在開發(fā)環(huán)境跑一條查詢，發(fā)現(xiàn)是毫秒級返回。上線之后，同樣的查詢，用戶反饋頁面轉圈要好幾秒。你打開 EXPLAIN 一看，type: ALL，全表掃描。明明在 create_time 上建了索引，為什么沒有用上？

這類問題的根源，往往不是索引壞了，而是寫法觸發(fā)了某種"索引失效"的條件。MySQL 的優(yōu)化器在選擇執(zhí)行計劃時，會判斷當前查詢能否利用索引的有序性。如果不能，它寧可走全表掃描，也不會用一個沒有用的索引。

這篇文章將把常見的失效場景做一次系統(tǒng)性的梳理，每種都給出原因和改法，讓你在遇到慢查詢時能快速定位問題。

索引失效的本質

在逐個看場景之前，先搞清楚一件事：索引為什么會失效？

索引的本質是一棵 B+ 樹，它的價值在于有序性。二分查找之所以快，是因為數(shù)據排好了序。索引失效就是：寫法破壞了 B+ 樹有序性的利用條件，MySQL 無法通過索引快速定位數(shù)據。

索引就像一本按拼音排序的字典。你可以用拼音快速找到"張"字在哪一頁。但如果你的查詢是"找所有姓名中包含’張’字的人"，這本字典就幫不了你了，因為"包含張"這個條件無法利用拼音的排序規(guī)則，你只能一頁一頁翻。

索引只在能利用有序性的查詢中生效。一旦查詢條件破壞了有序性的利用方式，索引就會失效。

場景一：對索引列做函數(shù)運算

這是最常見、也最容易犯的索引失效場景。

-- 索引：idx_create_time(create_time)

-- 失效：對索引列用了 YEAR() 函數(shù)
SELECT * FROM orders WHERE YEAR(create_time) = 2025;

-- 生效：改成范圍查詢
SELECT * FROM orders WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';

第一條查詢?yōu)槭裁词?？因?YEAR(create_time) 的結果是計算出來的，MySQL 需要對表中的每一行都執(zhí)行一次 YEAR() 函數(shù)，然后拿結果去比較。這時候 B+ 樹里存的是 create_time 的原始值，不是 YEAR() 的計算結果，有序性對計算后的值沒有意義。

場景二：隱式類型轉換

-- 索引：idx_phone(phone)，phone 列是 VARCHAR 類型

-- 失效：傳入了數(shù)字
SELECT * FROM user WHERE phone = 13800138000;

-- 生效：傳入字符串
SELECT * FROM user WHERE phone = '13800138000';

phone 是 VARCHAR，你傳了一個 INT。MySQL 的規(guī)則是：當字符串和數(shù)字比較時，MySQL 會把字符串轉成數(shù)字。 這意味著對 phone 列的每一行，MySQL 都要先把它從字符串轉成數(shù)字，再和 13800138000 比較。這等價于對索引列做了 CAST(phone AS SIGNED) 函數(shù)運算，和場景一相同，索引就會失效。

那如果反過來呢？

-- id 列是 INT 類型
-- 傳入字符串也不會失效，因為 INT 和字符串比較時，MySQL 把字符串轉成數(shù)字
-- 轉換發(fā)生在常量一側，不影響索引列
SELECT * FROM user WHERE id = '123';  -- 走索引

規(guī)則：字符串列傳數(shù)字會失效，數(shù)字列傳字符串不會失效。 因為只有當轉換發(fā)生在索引列一側時，才相當于對索引列做了函數(shù)運算。

怎么判斷是否發(fā)生了隱式類型轉換？用 EXPLAIN 看，如果 Extra 里出現(xiàn)了 Using where 且 type 是 ALL，那么大概率就是。

場景三：隱式字符編碼轉換

這個場景比類型轉換更隱蔽，很多人踩過坑但不知道原因。

-- 庫 A：字符集 utf8mb4，庫 B：字符集 utf8

SELECT * FROM db_a.user a
JOIN db_b.order b ON a.name = b.user_name
WHERE a.name = '張三';

如果兩張表的關聯(lián)列字符集不同，MySQL 在做 JOIN 比較時需要做字符集轉換。轉換發(fā)生在索引列上時，等價于對索引列做了函數(shù)運算，索引失效。原理和場景一相同，只是"變形"的方式從類型轉換變成了編碼轉換。

場景四：LIKE 左模糊匹配

-- 索引：idx_name(name)

-- 走索引：右模糊
SELECT * FROM user WHERE name LIKE '張%';

-- 不走索引：左模糊
SELECT * FROM user WHERE name LIKE '%三';

-- 不走索引：全模糊
SELECT * FROM user WHERE name LIKE '%三%';

回到字典的類比。字典按拼音排序，你可以快速找到"zhang"開頭的所有字。但如果要求找"名字里包含’三’的字"，拼音排序就幫不了你了，因為"三"可能出現(xiàn)在名字的任何位置，它的拼音分布在字典的各個角落。

LIKE '張%' 能走索引，是因為前綴確定時，這些值在 B+ 樹中是連續(xù)的，可以范圍掃描。LIKE '%三' 不行，是因為前綴不確定，值在樹中是分散的，只能逐行檢查。

場景五：OR 連接不同索引列

-- 索引：idx_a(a)，idx_b(b)

-- 失效：OR 連接了不同索引的列
SELECT * FROM t WHERE a = 1 OR b = 2;

a = 1 可以走 idx_a，b = 2 可以走 idx_b，但兩條路徑的結果要合并去重。MySQL 在大多數(shù)情況下只能選一個索引或者直接全表掃描。MySQL 8.0 引入了 Index Merge 優(yōu)化，某些 OR 場景下可以同時使用多個索引，但優(yōu)化器也可能認為全表掃描更快而放棄，不是萬能的。

場景六：違反最左前綴原則

聯(lián)合索引 (a, b, c) 的排序規(guī)則是先按 a 排，a 相同再按 b 排，b 相同再按 c 排。這個話題在我的另一篇文章《MySQL 最左前綴法則：為什么你的聯(lián)合索引總是不生效》里詳細展開過，這里只列核心規(guī)則，不做重復展開。

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

WHERE b = 2;                  -- 跳過了 a，不走索引
WHERE b = 2 AND c = 3;        -- 跳過了 a，不走索引
WHERE a = 1 AND c = 3;        -- 跳過了 b，只用到 a
WHERE a = 1 AND b > 5 AND c = 3;  -- b 范圍之后 c 無序，c 失效

原因和前面的場景一樣：跳過最左列后，后續(xù)列在 B+ 樹中是分散的，無法利用有序性。

場景七：數(shù)據量太小，優(yōu)化器主動放棄

這個場景經常讓人困惑：明明寫法沒問題，索引也建了，為什么 EXPLAIN 顯示全表掃描？

-- 表里只有 100 條數(shù)據
EXPLAIN SELECT * FROM user WHERE age > 20;
-- type: ALL，沒走索引

MySQL 的優(yōu)化器是基于成本做決策的。走索引不是免費的，需要從 B+ 樹根節(jié)點遍歷到葉子節(jié)點，再通過主鍵回表取完整行數(shù)據。當表的數(shù)據量很小時，全表掃描可能只需要讀一兩個數(shù)據頁，走索引反而需要更多 I/O。優(yōu)化器經過成本估算，發(fā)現(xiàn)全表掃描更快，就會主動放棄索引。

同樣的道理，當索引列的區(qū)分度太低時（比如 gender 只有 ‘M’ 和 ‘F’），走索引要回表的行數(shù)接近全表行數(shù)，順序 I/O 比隨機 I/O 快得多，優(yōu)化器也會選擇全表掃描。

這不是 bug，而是合理的優(yōu)化。 可以用 FORCE INDEX 強制走索引來驗證：如果強制走索引反而更慢，說明優(yōu)化器的選擇是對的。

一個公式：索引為什么會失效

看完七個場景，你會發(fā)現(xiàn)它們背后有一個共同的邏輯：

當 MySQL 無法利用 B+ 樹的有序性來減少掃描行數(shù)時，索引就會失效。

拆開來看，有三種情況會導致有序性無法被利用：

遇到索引失效的問題，先用 EXPLAIN 確認是否走了索引，再對照這個表格判斷屬于哪種情況。大多數(shù)時候，我們的改法都是圍繞一個原則：讓索引列保持"原樣"，讓查詢條件能利用 B+ 樹的有序性。

小結

索引失效不是 MySQL 出了問題，而是 B+ 樹有序性的自然推論。索引之所以快，是因為數(shù)據排好了序，可以二分查找。一旦你的查詢寫法讓 MySQL 沒法利用這個排序，索引就等于沒有用處了。

七個場景中，最值得記住的是兩個：對索引列做函數(shù)/運算和隱式類型轉換。這兩個在實際開發(fā)中出現(xiàn)頻率最高，常容易被忽略。

排查索引失效時，EXPLAIN 是第一工具?？?type 是不是 ALL，看 key 是不是 NULL，看 Extra 里有沒有 Using where。確認失效之后，對照本文提到的七個場景逐一排查，基本就能找到具體原因。

以上就是一文系統(tǒng)梳理MySQL數(shù)據庫索引失效的七個場景的詳細內容，更多關于MySQL索引失效常見場景的資料請關注腳本之家其它相關文章！

最新評論