解決mysql模糊查詢索引失效問題的幾種方法

我們?cè)谑褂胠ike %通配符時(shí)常常會(huì)引起索引失效的問題。
這里，我們討論一下like使用%的幾種情況：

下列例子用到的索引(VC_STUDENT_NAME)

一、like ‘xx%'

EXPLAIN select * from t_student where VC_STUDENT_NAME like ‘王%'

我們發(fā)現(xiàn)使用%不放在開頭的時(shí)候，索引是有效的

二、like ‘%xx'

EXPLAIN select * from t_student where VC_STUDENT_NAME like ‘%王'

我們發(fā)現(xiàn)%放在開頭時(shí)，索引便失效了。

上述兩個(gè)例子說明，在使用通配符時(shí)，%放在開頭會(huì)引起索引失效。
但在現(xiàn)實(shí)問題里，往往需要把%放在開頭作模糊匹配。這是我們可以通過覆蓋索引的方式即索引覆蓋查詢，來解決索引失效的問題。

EXPLAIN select * from t_student where VC_STUDENT_NAME like ‘%王'

把查詢列換成索引與之相應(yīng)的列，索引失效問題得以解決。

補(bǔ)充：

1.索引不存儲(chǔ)null值

更準(zhǔn)確的說，單列索引不存儲(chǔ)null值，復(fù)合索引不存儲(chǔ)全為null的值。索引不能存儲(chǔ)Null，所以對(duì)這列采用is null條件時(shí)，因?yàn)樗饕细?/p>

沒Null值，不能利用到索引，只能全表掃描。

為什么索引列不能存Null值？

將索引列值進(jìn)行建樹，其中必然涉及到諸多的比較操作。Null值的特殊性就在于參與的運(yùn)算大多取值為null。

這樣的話，null值實(shí)際上是不能參與進(jìn)建索引的過程。也就是說，null值不會(huì)像其他取值一樣出現(xiàn)在索引樹的葉子節(jié)點(diǎn)上。

2.不適合鍵值較少的列（重復(fù)數(shù)據(jù)較多的列）

假如索引列TYPE有5個(gè)鍵值，如果有1萬條數(shù)據(jù)，那么 WHERE TYPE = 1將訪問表中的2000個(gè)數(shù)據(jù)塊。

再加上訪問索引塊，一共要訪問大于200個(gè)的數(shù)據(jù)塊。

如果全表掃描，假設(shè)10條數(shù)據(jù)一個(gè)數(shù)據(jù)塊，那么只需訪問1000個(gè)數(shù)據(jù)塊，既然全表掃描訪問的數(shù)據(jù)塊

少一些，肯定就不會(huì)利用索引了。

3.前導(dǎo)模糊查詢不能利用索引(like '%XX'或者like '%XX%')

假如有這樣一列code的值為'AAA','AAB','BAA','BAB' ,如果where code like '%AB'條件，由于前面是

模糊的，所以不能利用索引的順序，必須一個(gè)個(gè)去找，看是否滿足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃

描。如果是這樣的條件where code like 'A % '，就可以查找CODE中A開頭的CODE的位置，當(dāng)碰到B開頭的

數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿足要求。這樣就可以利用索引了。

4.索引失效的幾種情況

1.如果條件中有or，即使其中有條件帶索引也不會(huì)使用(這也是為什么盡量少用or的原因)

要想使用or，又想讓索引生效，只能將or條件中的每個(gè)列都加上索引

2.對(duì)于多列索引，不是使用的第一部分，則不會(huì)使用索引

3.like查詢以%開頭

4.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來,否則不使用索引

5.如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引

5.MySQL主要提供2種方式的索引：B-Tree索引，Hash索引

B樹索引具有范圍查找和前綴查找的能力，對(duì)于有N節(jié)點(diǎn)的B樹，檢索一條記錄的復(fù)雜度為O(LogN)。相當(dāng)于二分查找。

哈希索引只能做等于查找，但是無論多大的Hash表，查找復(fù)雜度都是O(1)。

顯然，如果值的差異性大，并且以等值查找（=、 、>、in）為主，Hash索引是更高效的選擇，它有O(1)的查找復(fù)雜度。

如果值的差異性相對(duì)較差，并且以范圍查找為主，B樹是更好的選擇，它支持范圍查找。

到此這篇關(guān)于解決mysql模糊查詢索引失效問題的幾種方法 的文章就介紹到這了,更多相關(guān)mysql模糊查詢索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！