建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
索引失效通常發(fā)生在全表掃描、使用函數(shù)操作、通配符前綴不明確等情況下，導(dǎo)致數(shù)據(jù)庫(kù)性能下降。
MySQL調(diào)優(yōu)之索引在什么情況下會(huì)失效詳解
索引是數(shù)據(jù)庫(kù)中用于快速查找數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)，在MySQL中，合理地使用索引可以顯著提高查詢(xún)性能，在某些情況下，即使存在索引，查詢(xún)優(yōu)化器也可能選擇不使用它，導(dǎo)致索引失效，本文將詳細(xì)解釋索引在哪些情況下會(huì)失效，并提供相應(yīng)的解決方案。
1、使用了函數(shù)或表達(dá)式的列
當(dāng)查詢(xún)條件中包含函數(shù)或表達(dá)式時(shí)，MySQL無(wú)法使用索引，以下查詢(xún)中的DATE(create_time)會(huì)導(dǎo)致索引失效：
SELECT * FROM users WHERE DATE(create_time) = '2022-01-01';
解決方案：盡量避免在查詢(xún)條件中使用函數(shù)或表達(dá)式，如果必須使用，可以考慮將計(jì)算結(jié)果存儲(chǔ)為一個(gè)新的列，并為該列創(chuàng)建索引。
2、使用了LIKE ‘%xxx’的通配符查詢(xún)
當(dāng)使用LIKE進(jìn)行模糊查詢(xún)時(shí)，如果以%開(kāi)頭，MySQL無(wú)法使用索引。
SELECT * FROM users WHERE username LIKE '%張三';
解決方案：盡量避免使用以%開(kāi)頭的通配符查詢(xún)，如果必須使用，可以考慮全文索引或者使用搜索引擎（如Elasticsearch）進(jìn)行模糊查詢(xún)。
3、隱式類(lèi)型轉(zhuǎn)換
當(dāng)查詢(xún)條件中的數(shù)據(jù)類(lèi)型與列的數(shù)據(jù)類(lèi)型不匹配時(shí)，MySQL會(huì)嘗試進(jìn)行隱式類(lèi)型轉(zhuǎn)換，這可能導(dǎo)致索引失效，如果age列的數(shù)據(jù)類(lèi)型為INT，以下查詢(xún)可能導(dǎo)致索引失效：
SELECT * FROM users WHERE age = '30';
解決方案：確保查詢(xún)條件中的數(shù)據(jù)類(lèi)型與列的數(shù)據(jù)類(lèi)型一致，可以使用CAST()或CONVERT()函數(shù)進(jìn)行顯式類(lèi)型轉(zhuǎn)換。
4、使用了OR的查詢(xún)條件
當(dāng)查詢(xún)條件中包含多個(gè)條件并用OR連接時(shí)，MySQL可能無(wú)法使用索引。
SELECT * FROM users WHERE age = 30 OR city = '北京';
解決方案：盡量將OR條件拆分成多個(gè)查詢(xún)，然后使用UNION將結(jié)果合并，這樣可以讓MySQL分別使用各個(gè)條件的索引。
5、索引列參與了計(jì)算
當(dāng)查詢(xún)條件中對(duì)索引列進(jìn)行了計(jì)算操作時(shí)，MySQL無(wú)法使用索引。
SELECT * FROM users WHERE YEAR(create_time) = 2022;
解決方案：盡量避免在查詢(xún)條件中對(duì)索引列進(jìn)行計(jì)算，如果必須使用，可以考慮將計(jì)算結(jié)果存儲(chǔ)為一個(gè)新的列，并為該列創(chuàng)建索引。
6、索引列使用了不等于操作符
當(dāng)查詢(xún)條件中使用了不等于操作符（<>或!=）時(shí)，MySQL可能無(wú)法使用索引。
SELECT * FROM users WHERE age <> 30;
解決方案：盡量避免在查詢(xún)條件中使用不等于操作符，可以考慮使用其他操作符（如>、>=、<、<=）或者使用BETWEEN進(jìn)行范圍查詢(xún)。
相關(guān)問(wèn)題與解答
1、問(wèn)題：為什么在查詢(xún)條件中使用函數(shù)或表達(dá)式會(huì)導(dǎo)致索引失效？
答：因?yàn)槭褂煤瘮?shù)或表達(dá)式后，MySQL無(wú)法直接通過(guò)索引定位到數(shù)據(jù)，需要對(duì)每一行數(shù)據(jù)進(jìn)行計(jì)算后再進(jìn)行比較，這樣就失去了索引的優(yōu)勢(shì)。
2、問(wèn)題：為什么以%開(kāi)頭的通配符查詢(xún)會(huì)導(dǎo)致索引失效？
答：因?yàn)橐?code>%開(kāi)頭的通配符表示匹配任意長(zhǎng)度的字符串，MySQL無(wú)法確定需要匹配的范圍，所以無(wú)法使用索引。
3、問(wèn)題：為什么隱式類(lèi)型轉(zhuǎn)換會(huì)導(dǎo)致索引失效？
答：因?yàn)殡[式類(lèi)型轉(zhuǎn)換會(huì)導(dǎo)致MySQL無(wú)法直接通過(guò)索引定位到數(shù)據(jù)，需要對(duì)每一行數(shù)據(jù)進(jìn)行類(lèi)型轉(zhuǎn)換后再進(jìn)行比較，這樣就失去了索引的優(yōu)勢(shì)。
4、問(wèn)題：為什么使用了OR的查詢(xún)條件可能導(dǎo)致索引失效？
答：因?yàn)槭褂昧薕R的查詢(xún)條件可能導(dǎo)致多個(gè)索引之間的選擇，MySQL需要對(duì)所有可能的索引組合進(jìn)行評(píng)估，這可能導(dǎo)致索引失效，將OR條件拆分成多個(gè)查詢(xún)可以避免這個(gè)問(wèn)題。
網(wǎng)站欄目：MySQL調(diào)優(yōu)之索引在什么情況下會(huì)失效詳解
文章URL：http://www.5511xx.com/article/cdoohog.html
