為什么MySQL字符串不加引號(hào)索引會(huì)失效？這個(gè)答案是我見過最靠譜

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

創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括仁壽網(wǎng)站建設(shè)、仁壽網(wǎng)站制作、仁壽網(wǎng)頁制作以及仁壽網(wǎng)絡(luò)營(yíng)銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，仁壽網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到仁壽省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

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

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

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

面試官讓他回去等通知。

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

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

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

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

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

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

創(chuàng)建索引為idx_user_name

執(zhí)行語句：

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

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

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

這樣索引就失效了。

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

這樣dt索引才不會(huì)失效。

例如我們?cè)谟唵伪韙b_order建立了索引idx_order_id，order_id字段類型為varchar

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

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

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

執(zhí)行語句

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

再來看一個(gè)例子：

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

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

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

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

由于對(duì)索引列進(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ù)這個(gè)規(guī)則，我們?cè)倩剡^頭看看之前的查詢語句

select '12345678936' = 12345678936

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

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

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

總結(jié)：

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

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

拓展

索引創(chuàng)建

1、主鍵索引：

2、唯一索引：

3、普通索引：

4、全文索引：

alter table table_name add fulltext (column)

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

索引刪除

Mysql索引失效的底層原理

1.索引失效的原因

聯(lián)合索引排序的原理：先對(duì)第一個(gè)字段進(jìn)行排序，在第一個(gè)字段相同的情況下考慮第二個(gè)字段，然后在第二個(gè)字段相同的情況下才考慮第三個(gè)字段...

CREATE TABLE 'test_user'(

'id' int(11) not null auto_increment comment '主鍵id',

‘user_id’ varchar(36) not null comment '用戶id',

'phone' varchar(20) not null comment '用戶名稱',

'lan_id' int(9) not null comment '本地網(wǎng)',

'region_id' int(9) not null comment '區(qū)域'

)ENGINE=InnoDB Auto_increment=4057960 Default charset=utf8mb4;

假設(shè)將('phone', 'lan_id', 'region_id')組成的聯(lián)合索引

Explain select * from test_user where lan_id = 1;

此時(shí)的索引是失效的，因?yàn)槁?lián)合索引是遵循最左前綴法則即第一個(gè)字段有序的情況下lan_id才有序?，F(xiàn)在是跳過phone,直接搜索lan_id相當(dāng)于在一個(gè)無序的B+樹上搜索，所以只能全表掃描。

例1下例范圍查找的右邊索引會(huì)失效

Explain select * from test_user where a 1 and b = 1;

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

因?yàn)槲覀兛梢哉业絘 1的所有的節(jié)點(diǎn)，但是此時(shí)的b索引是無序的，仍然不可以通過二分查找法來查找

例2. like查詢中，如果%放在兩邊或者放到左邊，它都是不走索引的。只有%放到右邊，它某些情況才會(huì)走這個(gè)索引。這是什么原因？

字符串在B+樹里面存儲(chǔ)的時(shí)候，它也是按照字母的大小去排序。首先按照第一個(gè)字母去比較，如果第一個(gè)字母相同則按照第二個(gè)字母去比較和最佳左前綴法則相似。如果左邊用了%，那后面的字符是無序的，此時(shí)就不能使用二分查找來定位元素還是退化為了全表掃描。

3.Mysql中的索引查詢?yōu)槭裁词褂昧薆+樹結(jié)構(gòu)，而不使用哈希索引或者B樹？

首先哈希值是無序的，不能夠進(jìn)行范圍查找。

平衡二叉樹的缺點(diǎn)是當(dāng)數(shù)據(jù)量非常大的時(shí)候，其深度也會(huì)非常深這樣也會(huì)導(dǎo)致查找效率慢。其次其存在回旋查找的問題。比如說當(dāng)存在范圍查詢5的時(shí)候定位到該元素之后還得回溯到前面的節(jié)點(diǎn)元素6，7

B樹的最大特點(diǎn)是一個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)值，這樣可以使得樹的高度變矮，從而使得樹的查找速度變快。但是其也存在回旋查找的問題。

B+樹則解決了這個(gè)問題，它的非葉子節(jié)點(diǎn)存儲(chǔ)的是key,其葉子節(jié)點(diǎn)既存儲(chǔ)了key也存儲(chǔ)了value并且其葉子節(jié)點(diǎn)是有序的，節(jié)點(diǎn)之間用指針相連也正是因?yàn)檫@一點(diǎn)使得B+樹在范圍查詢的時(shí)候不存在回旋問題。

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

具體原因是：

1、索引列值為null，此時(shí)會(huì)索引失效。

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

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

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

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

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