我們在學習的過程中常能聽到人們談論到MySQL的索引失效了。那么為什么??索引會失效呢？

一、為什么索引會失效

我們需要知道索引的本質(zhì)是以空間換時間的一種結(jié)構(gòu)，“排好序的數(shù)據(jù)結(jié)構(gòu)（例如InnoDB+樹）”，能夠幫助數(shù)據(jù)庫快速定位數(shù)據(jù)。但是如果查詢條件破壞了索引的有序性或者查詢優(yōu)化器判斷“全表掃描比走索引更快”，就會放棄使用索引，導致索引失效。

接下來我們來以一些具體的場景來看！

二、索引失效的場景

2.1 對索引字段做“函數(shù)/運算操作”，破壞索引的有序性

索引存儲的是字段原始值，一旦對字段做函數(shù)處理（如SUBSTR()，DATE()）或者運算（如+，-），數(shù)據(jù)庫無法直接使用索引定位，只能全表掃描。

-- 對索引字段name做函數(shù)處理，索引失效
SELECT * FROM user WHERE SUBSTR(name,1,3)='哈基米';

-- 對索引字段age做運算，索引失效
SELECT * FROM user WHERE age+3=24;

對于第二個SQL進行優(yōu)化：把函數(shù)/運算移到等號右邊

-- 以下兩種都會走索引
SELECT * FROM user WHERE age=21;

SELECT * FROM user WHERE age=24-3;

2.2 隱式類型轉(zhuǎn)換，導致索引字段被“隱式處理”

當查詢條件中，字段類型與傳入值類型不匹配時，MySQL會自動做類型轉(zhuǎn)化（相當于隱式函數(shù)操作），導致索引失效

-- age是INT類型，傳入字符串'21'，會被轉(zhuǎn)為INT（相當于CAST(age as CHAR)）
SELECT * FROM user WHERE age='21'; -- 索引失效

對于這個SQL進行優(yōu)化：保證傳入的參數(shù)類型與字段類型相同

SELECT * FROM user WHERE age=21

2.3 LIKE查詢以%開頭，無法利用索引有序性

B+樹索引是按照字段前綴排序的，LIKE '%XXX'表示“后綴匹配”，無法通過索引的有序性定位，只能全表掃描，而LIKE 'XXX%'(前綴匹配）可以走索引。

-- %在開頭，索引失效
SELECT * FROM user WHERE name LIKE '%基米';

對于該SQL進行優(yōu)化：不使用后綴匹配，如果業(yè)務必需后綴匹配，可以考慮“倒序存儲+前綴索引”（如存name_reverse='米基哈'，查詢LIKE '米%'）

2.4 組合索引不滿足“最左前綴原則”

組合索引(a,b,c)的B+樹是按照a->b->c的順序排序的，查詢條件必需包含最左列(a)，否則不誤利用索引。

-- 組合索引(a,b,c),缺少最左列a，索引失效
SELECT * FROM table1 WHERE b=2 AND c=2;

-- 雖然有a，但是中間列b缺失，只能用到a的索引，b和c無法利用
SELECT * FROM table1 WHERE a=2 AND c=2;

對上述的SQL進行優(yōu)化：按最左前綴原則設計查詢條件，或調(diào)整組合索引順序（將高頻字段放左邊）

2.5 OR連接的條件中，存在未建索引的字段

OR的邏輯是“滿足任意一個條件即可”，如果其中一個字段沒索引，數(shù)據(jù)庫無法通過索引快速定位所有滿足條件的行（會查詢到不滿足非索引條件的行），只能放棄索引走全表掃描。

-- age有索引，name無索引，OR導致age索引失效
SELECT * FROM user WHERE age=21 OR name='哈基米';

對上述SQL進行優(yōu)化：給OR連接的所有字段都建立索引，或改用UNION拆分查詢：

SELECT * FROM user WHERE age=21
UNION
SELECT * FROM user WHERE name='哈基米'; -- 分別走各自的索引

注意??：

假設字段age和name都有自己的索引

執(zhí)行：SELECT * FROM user WHERE age=21 OR name='哈基米' ;

即使age和name分別有單獨的索引，這個查詢大概率不會走任何索引，會進行全表掃描

原因：

OR的邏輯是“滿足任意一個條件即可”，而數(shù)據(jù)庫的索引是單個字段排序的：

• age索引只能快速定位age=21的行；
• name索引只能快速定位到name='哈基米'的行；
• 數(shù)據(jù)庫無法通過一個索引同事定位兩個條件的結(jié)果，若分別使用兩個索引再合并結(jié)果，開銷可能比全表掃描更大（尤其是當兩個條件的結(jié)果集都比較大時）

執(zhí)行：SELECT * FROM user WHERE age=21 AND name='哈基米';

假設name和age都只有單獨的索引，沒有兩者的組合索引時，數(shù)據(jù)庫會選擇其中一個過濾效果更好的索引（例如age=21能篩選出更少的行，則優(yōu)先用age索引），定位后再在結(jié)果中過濾name='哈基米'的行。

2.6 查詢優(yōu)化器判斷“全表掃描更快”

當數(shù)據(jù)量很少（例如幾百行），或查詢結(jié)果占表數(shù)據(jù)的大部分（如WHERE age=21返回90%的數(shù)據(jù)），查詢優(yōu)化器會認為“全表掃描比走索引更快”（索引也需要IO開銷），此時會主動放棄索引。這種是“合理失效”，無效優(yōu)化，數(shù)據(jù)庫會自動選擇最優(yōu)方案。

2.7 其他場景

• NOT IN/<>''!=：這些操作可能導致索引失效（視版本和數(shù)據(jù)分布而定），建議使用NOT EXISTS代替NOT IN
• IS NULL/IS NOT NULL：早期MySQL版本對NULL處理不佳可能失效，新版本已優(yōu)化，但扔建議字段盡量設置NOT NULL
• USE INDEX等強制索引語句被優(yōu)化器忽略：如果強制走索引但優(yōu)化器判斷效率更低，會忽略強制指令

三、如何避免索引失效

總結(jié):

1. 索引字段不做函數(shù)/運算，避免隱式類型轉(zhuǎn)化
2. 遵循組合索引的“最左前綴原則”
3. LIKE查詢盡量用前綴匹配（xxxx%）
4. 用EXPLAIN分析SQL，關(guān)注type（是否為ALL）和Extra（是否有Using where）
5. 結(jié)合業(yè)務場景設計索引

到此這篇關(guān)于MySQL索引失效的幾種常見場景的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論