)的時候無法使用索引會導(dǎo)致全表掃描3.不在索引列上做任何操作(計算、函數(shù)(自動或手動)類型轉(zhuǎn)換),會使索引失效轉(zhuǎn)為全表掃描全值匹配意思就是聯(lián)立的復(fù)合索引的順序和個數(shù)要和檢索的條件順序和個數(shù)相同。...">
瀏覽量:95次
面試的時候被問到了:導(dǎo)致索引失效的原因有哪些?頓時啞口無言,平時不多注意,而支支吾吾的回答了幾點(diǎn),雖然問題不大,但是表達(dá)還是欠缺。今天在這里做一個總結(jié),給自己長點(diǎn)記性。也可以當(dāng)做是面試后總結(jié)的經(jīng)驗(yàn)與筆記吧,保證下次不再犯錯,同樣在開發(fā)過程中能注意到這些問題。希望也能幫助到大家!
下面結(jié)合一些示例來給大家講解
首先先新建臨時表,這個表有四個字段 主鍵 、名字、年齡、職位
CREATETABLE`sys_user`(`id`varchar(64)NOTNULLCOMMENT'主鍵',`name`varchar(64)DEFAULTNULLCOMMENT'名字',`age`int(64)DEFAULTNULLCOMMENT'年齡',`pos`varchar(64)DEFAULTNULLCOMMENT'職位',PRIMARYKEY(`id`),KEY`idx_sys_user_nameAgePos`(`name`,`age`,`pos`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用戶表';1.全值匹配
全值匹配意思就是聯(lián)立的復(fù)合索引的順序和個數(shù)要和檢索的條件順序和個數(shù)相同。
2.最佳左前綴法則(重要) 最佳左前綴法則是指,如果索引了多列,要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列
下面我們給這個表建立一個復(fù)合索引
SELECT*FROMsys_userWHEREname='小明'ANDage=22ANDpos='java';以下是我們的檢索語句:
SELECT*FROMsys_userWHEREname='小明'ANDage=22ANDpos='java';我們通過在檢索語句前面加關(guān)鍵字 EXLAIN,可以知道是否使用的索引
(1)EXPLAINSELECT*FROMsys_userWHEREname='小明'ANDage=22ANDpos='java';(2)EXPLAINSELECT*FROMsys_userWHEREname='小明'ANDage=22;(3)EXPLAINSELECT*FROMsys_userWHEREname='小明'ANDpos='java';通過上面的結(jié)果我們可以知道,第一個復(fù)合索引的三個字段我們都用了,第二個復(fù)合索引我們只用到兩個字段,第三個復(fù)合索引我們只用到一個字段。三個語句我們都用到索引,顯然第一種是最優(yōu)的。
我們再看看哪種情況會失效:
(4)EXPLAINSELECT*FROMsys_userWHEREage=22;(5)EXPLAINSELECT*FROMsys_userWHEREpos='java';(6)EXPLAINSELECT*FROMsys_userWHEREage=22ANDpos='java';以上三種情況都變成了全表掃描,原因是違反了最左左前綴原則,因?yàn)閺?fù)合索引最左邊的是name,當(dāng)檢索條件name沒在前面索引將失效,第一種情況滿足了全值匹配,第二種滿足了兩個字段name和age,第三種因?yàn)橹粷M足了name,所以索引只用到name。
3.不在索引列上做任何操作(計算、函數(shù)(自動或手動)類型轉(zhuǎn)換),會使索引失效轉(zhuǎn)為全表掃描
(7)EXPLAINSELECT*FROMsys_userWHERELEFT(name,1)='小明';第七種情況失效是因?yàn)樗饕凶隽擞嬎慊蛘吆瘮?shù)的操作,導(dǎo)致了全表掃描。
4.存儲引擎不能使用索引中范圍條件右邊的列 可能大家關(guān)看上面的文字不知道是什么意思,下面我們執(zhí)行一下查詢語句就清楚了
(8)EXPLAINSELECT*FROMsys_userWHEREname='小明'ANDage<22ANDpos='java';從上圖我們可以知道type變成了范圍級別,也就是說age<22之后的pos字段的索引失效了。< p="">
5.盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致),減少select * 的使用查詢具體的字段比查詢*效率更高,下面我們做一下對比
(9)EXPLAINSELECT*FROMsys_userWHEREname='小明'ANDage=22ANDpos='java';(10)EXPLAINSELECTname,age,posFROMsys_userWHEREname='小明'ANDage=22ANDpos='java';6.mysql在使用不等于(!= 或者<>)的時候無法使用索引會導(dǎo)致全表掃描
(11)EXPLAINSELECT*FROMsys_userWHEREname!='小明'結(jié)果顯示索引失效導(dǎo)致了全表掃描
7.is null,is not null 也無法使用索引
(12)EXPLAINSELECT*FROMsys_userWHEREnameisnotnull8.like以通配符開頭(’?c…’)mysql索引會失效變成全表掃描的操作,(%寫右邊則可以避免索引失效,如果業(yè)務(wù)實(shí)在需要’?c…%'則可以用覆蓋索引避免索引失效)
(13)EXPLAINSELECT*FROMsys_userWHEREnamelike'%明%'(14)EXPLAINSELECT*FROMsys_userWHEREnamelike'明%'(15)EXPLAINSELECTname,age,posFROMsys_userWHEREnamelike'%明%'從上面的結(jié)果,第一種索引失效,第二種只寫右邊的%則可以避免索引失效,第三種如果業(yè)務(wù)實(shí)在需要‘?c…%’這種sql,則可以用覆蓋索引解決索引失效的問題
9.字符串不加單引號索引會失效
(16)EXPLAINSELECT*FROMsys_userWHEREname=222;因?yàn)闄z索字符串是必須加單引號,上面用用了222是int類型,mysql在檢索的時候會判斷name是varchar的類型會將222轉(zhuǎn)換為’222’進(jìn)行檢索,索引列發(fā)生了類型轉(zhuǎn)換,故索引失效。
10.少用or,用它連接時會索引失效
(17)EXPLAINSELECT*FROMsys_userWHEREname='小明'orage=22;以上是本文的全部內(nèi)容,希望對大家的學(xué)習(xí)有幫助,也希望大家多多支持php自學(xué)中心
官網(wǎng)優(yōu)化
整站優(yōu)化
渠道代理
400-655-5776