為什么MySQL字符串不加引號索引會失效？這個答案是我見過最靠譜

作為一名程序員，在求職面試時，不知你有沒有遇到類似這樣的問題。

成都創(chuàng)新互聯(lián)是專業(yè)的翁源網(wǎng)站建設(shè)公司，翁源接單;提供成都網(wǎng)站建設(shè)、網(wǎng)站制作,網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行翁源網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!

張工是一名java程序員，最近到一家軟件公司應(yīng)聘軟件開發(fā)崗位，面試官問了他關(guān)于MySql索引這樣的一個問題。

對于這個問題張工之前在做項(xiàng)目時也曾遇到，那時候字段明明是加了索引，可不明白為什么還是很慢。后加上引號就正常了，為了趕項(xiàng)目進(jìn)度，張工也沒有再去留意。

現(xiàn)在面試官突然這么一問，張工也說不出個所以然來。

面試官讓他回去等通知。

我們知道MySql索引可以加快數(shù)據(jù)檢索速度，這也是使用的索引的最主要原因。但有時候使用不當(dāng)就會遇到索引失效問題，譬如在MySQL字符串類型查詢時不加引號索引會失效，是因?yàn)镸ySQL內(nèi)部進(jìn)行了隱式轉(zhuǎn)換。

那為什么會發(fā)生隱式轉(zhuǎn)換？又是怎么轉(zhuǎn)換的呢？

今天我們來聊聊關(guān)于MySql索引失效的話題。

先來看看一般導(dǎo)致索引失效的有哪些？

如果一張表的索引有多個，要遵守最佳左前綴法則，即查詢從索引的最左前列開始并且不跳過索引中的列。

用戶表tb_user字段 id，name，age，sex

創(chuàng)建索引為idx_user_name

執(zhí)行語句：

這時候就會導(dǎo)致索引失效

在索引列上做加工操作，查詢時會導(dǎo)致索引失效，從而導(dǎo)致全表掃描。所以，建議不要在索引列上做任何操作。

舉個例子，例如訂單表tb_order有個索引是dt（日期）， 字段數(shù)據(jù)存放的格式是這樣的2021-12-10 這樣的，如果有個需求需要根據(jù)dt，格式是20220207這樣的來查詢，這時候就不要對dt進(jìn)行格式轉(zhuǎn)換了，

這樣索引就失效了。

而是應(yīng)該對 20220207做格式處理

這樣dt索引才不會失效。

例如我們在訂單表tb_order建立了索引idx_order_id，order_id字段類型為varchar

在查詢時如果使用where order_id= 20220207123654100，這樣的查詢方式會直接造成索引失效。

要讓索引生效，正確的用法為

假如有張用戶表tb_user，創(chuàng)建的索引為idx_user_name_age_sex_phone 其中name、age、sex都加了索引。

執(zhí)行語句

上面這條sql語句只會命中name和age索引，sex索引會失效，復(fù)合索引失效需要查看key_len的長度。

再來看一個例子：

從這兩條SQL執(zhí)行的結(jié)果我們可以看出，執(zhí)行第一條SQL沒有使用到索引，而執(zhí)行第二條SQL時使用到了索引。這是為什么呢？

我們需要先了解下mysql索引優(yōu)化器工作的原理。選擇索引是優(yōu)化器工作，優(yōu)化器工作有自己的一套規(guī)則，如果等號兩邊的數(shù)據(jù)類型不一致，則會發(fā)生隱式轉(zhuǎn)換。

基于這條規(guī)則，我們回過頭看看

這條SQL語句執(zhí)行時就會變?yōu)?/p>

由于對索引列進(jìn)行了函數(shù)操作，所以才導(dǎo)致索引失效，從而全表掃描了。

那么問題來了，細(xì)心的你不知有沒有留意到為什么是把左側(cè)的列轉(zhuǎn)為int類型，而不是把右側(cè)的值轉(zhuǎn)成字符串類型呢？

什么情況下把數(shù)字轉(zhuǎn)為字符串，什么情況下把字符串轉(zhuǎn)為數(shù)字，優(yōu)化器它是根據(jù)什么規(guī)則來進(jìn)行判斷的？其實(shí)規(guī)則也并不復(fù)雜。

根據(jù)這個規(guī)則，我們再回過頭看看之前的查詢語句

select '12345678936' = 12345678936

返回1 所以這時候就把左側(cè)的列值12345678936轉(zhuǎn)成數(shù)字。

關(guān)于MySql索引失效的問題先簡單寫到這，建議平時在做項(xiàng)目時還是要多了解下原理，如果你了解其背后的原理，求職面試時和面試官交流起來就會很舒服了，相信能為這次面試加分，提高被錄用的概率。

為什么MySQL字符串類型查詢時不加引號索引會失效？這是因?yàn)橐樵兊淖址侄螞]有加引號時，MySQL內(nèi)部進(jìn)行了隱式轉(zhuǎn)換，此次查詢會導(dǎo)致全表掃描，所以慢了。

總結(jié)：

在索引列上進(jìn)行了函數(shù)操作，MySQL內(nèi)部會進(jìn)行了隱式轉(zhuǎn)換，導(dǎo)致索引失效，從而產(chǎn)生全表掃描。

由于筆者知識及水平有限,文中錯漏之處在所難免,如有不足之處，歡迎交流。

拓展

索引創(chuàng)建

1、主鍵索引：

2、唯一索引：

3、普通索引：

4、全文索引：

alter table table_name add fulltext (column)

5、聯(lián)合索引：

索引刪除

mysql高并發(fā)導(dǎo)致索引失效

具體原因是：

1、索引列值為null，此時會索引失效。

2、sql的語句中寫了or，如果or后的字段不全是帶索引字段，此時索引失效。

3、模糊查詢是like以%XX開頭，就是說左模糊不太行，右模糊可以。

4、存在類型轉(zhuǎn)換，比如你的索引字段是varchar型，但是你搜索條件卻是userid=333，那這樣索引不生效。

5、數(shù)據(jù)量極少時，Mysql不會使用索引，因?yàn)槿頀呙杷俣雀臁?/p>

6、where條件中的索引運(yùn)算設(shè)計(jì)計(jì)算時，索引失效。索引列不要使用函數(shù)。

mysql 避免索引失效

where條件==order by 條件==group by 條件 按順序遵守 最佳左前綴法則

假設(shè)創(chuàng)建了復(fù)合索引：a,b,c

不在索引列上做任何的操作（計(jì)算、函數(shù)、顯式或隱式的類型轉(zhuǎn)換），否則會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

1、字符不加單引號會導(dǎo)致索引失效

name字段為varchar類型

這條sql發(fā)生了隱式的類型轉(zhuǎn)換：數(shù)值==字符串。所以導(dǎo)致了全表掃描，索引失效

應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

mysql中的范圍條件有：in/not in、 like、 、BETWEEN AND ；

后面的索引失效

in會導(dǎo)致索引全部失效！??！

BETWEEN AND 范圍條件不會導(dǎo)致索引失效?。?！

盡量讓索引列和查詢列一致；減少select * 的使用

1、查詢表結(jié)構(gòu)

2、查詢表的索引結(jié)構(gòu)

聯(lián)合索引：name,age,post；說明add_time字段沒有添加索引

3、查看select * 的執(zhí)行計(jì)劃

4、查看 select name,age,pos的執(zhí)行計(jì)劃

5、如果select只用一部分索引

like以通配符開頭（’%abc…’）mysql索引失效會變成全表掃描的操作。

解決：可以使用 覆蓋索引 來解決這個問題！

1、先查看表上的索引

id、name、age、pos 四個字段上都有索引； 注意：name是聯(lián)合索引中的第一個，帶頭大哥！

2、查看表結(jié)構(gòu)

有個add_time字段沒有用到索引

3、查看執(zhí)行計(jì)劃

使用UNION ALL

假設(shè)創(chuàng)建了聯(lián)合索引 x(a,b,c)

ps：like雖然也是范圍查詢但是區(qū)別于、，%用在最前面就只用到索引a了；%用在最后面可以用到a+b+c！

下面的sql幾乎違背了上面的所有原則，索引依然全部生效。因?yàn)閟elect是索引覆蓋的，select里不包含沒有建立索引的字段。因此總是用到索引的?？梢钥闯鰜硭饕采w在sql優(yōu)化中的作用性

mysql 索引失效總結(jié)

首先我們還是先把表結(jié)構(gòu)說下：用戶表tb_user結(jié)構(gòu)如下：

1、 不要在索引列上進(jìn)行運(yùn)算操作， 索引將失效。

手機(jī)號phone字段有唯一索引，當(dāng)根據(jù)phone字段進(jìn)行函數(shù)運(yùn)算操作之后，索引失效:

2、 字符串類型字段使用時，不加引號，索引將失效。

如果字符串不加單引號，對于查詢結(jié)果，沒什么影響，但是數(shù) 據(jù)庫存在隱式類型轉(zhuǎn)換，索引將失效。

3、 如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

接下來，我們來看一下這三條SQL語句的執(zhí)行效果，查看一下其執(zhí)行計(jì)劃：

由于下面查詢語句中，都是根據(jù)profession（專業(yè)）字段查詢，profession字段是一個普通的索引， 我們主要看一下，模糊查詢時，%加在關(guān)鍵字之前，和加在關(guān)鍵字之后的影響。

經(jīng)過上述的測試，我們發(fā)現(xiàn)，在like模糊查詢中，在關(guān)鍵字后面加%，索引可以生效。而如果在關(guān)鍵字 前面加了%，索引將會失效。

4、 用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會 被用到。

由于age沒有索引，所以即使id有索引，索引也會失效。所以需要針對于age也要建立索引。

5、 數(shù)據(jù)分布影響：如果MySQL評估使用索引比全表更慢，則不使用索引。