我們?cè)趯W(xué)習(xí)的過(guò)程中常能聽(tīng)到人們談?wù)摰組ySQL的索引失效了。那么為什么??索引會(huì)失效呢？

一、為什么索引會(huì)失效

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

接下來(lái)我們來(lái)以一些具體的場(chǎng)景來(lái)看！

二、索引失效的場(chǎng)景

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

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

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

-- 對(duì)索引字段age做運(yùn)算，索引失效
SELECT * FROM user WHERE age+3=24;

對(duì)于第二個(gè)SQL進(jìn)行優(yōu)化：把函數(shù)/運(yùn)算移到等號(hào)右邊

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

SELECT * FROM user WHERE age=24-3;

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

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

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

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

SELECT * FROM user WHERE age=21

2.3 LIKE查詢以%開(kāi)頭，無(wú)法利用索引有序性

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

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

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

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

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

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

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

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

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

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

-- age有索引，name無(wú)索引，OR導(dǎo)致age索引失效
SELECT * FROM user WHERE age=21 OR name='哈基米';

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

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

注意??：

假設(shè)字段age和name都有自己的索引

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

即使age和name分別有單獨(dú)的索引，這個(gè)查詢大概率不會(huì)走任何索引，會(huì)進(jìn)行全表掃描

原因：

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

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

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

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

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

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

2.7 其他場(chǎng)景

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

三、如何避免索引失效

總結(jié):

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

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

最新評(píng)論