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

張工是一名JAVA程序員，最近到一家軟件公司應聘軟件開發崗位，面試官問了他關于MySQL索引這樣的一個問題。

面試官：為什么MySQL字符串類型查詢時不加引號索引會失效？

張工：MySQL內部進行了隱式轉換。

面試官：那為什么會發生隱式轉換？

張工：……

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

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

面試官讓他回去等通知。

我們知道MySql索引可以加快數據檢索速度，這也是使用的索引的最主要原因。但有時候使用不當就會遇到索引失效問題，譬如在MySQL字符串類型查詢時不加引號索引會失效，是因為MySQL內部進行了隱式轉換。

那為什么會發生隱式轉換？又是怎么轉換的呢？

今天我們來聊聊關于MySql索引失效的話題。

先來看看一般導致索引失效的有哪些？

1. 最佳左前綴法則

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

2.like查詢使用不當會導致索引失效

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

創建索引為idx_user_name

執行語句：

select * from tb_user where name like ''%xiaoai";

這時候就會導致索引失效

3.在索引列上做加工操作

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

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

select * from tb_order where DATE_FORMAT(dt,'%Y%m%d') ='20220207'

這樣索引就失效了。

而是應該對 20220207做格式處理

select * from tb_order  where  dt=DATE_FORMAT('20220207','%Y-%m-%d')

這樣dt索引才不會失效。

4.查詢條件兩邊數據類型不一致

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

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

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

where order_id=’20220207123654100’

5.范圍條件之后的索引會失效

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

執行語句

select * from tb_user where name = 'xiaoai' and age > 18 and sex =0;

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

再來看一個例子：

explain select * from tb_user  where phone = 12345678936

explain select * from tb_user  where phone = '12345678936'

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

我們需要先了解下mysql索引優化器工作的原理。選擇索引是優化器工作，優化器工作有自己的一套規則，如果等號兩邊的數據類型不一致，則會發生隱式轉換。

基于這條規則，我們回過頭看看

explain select * from tb_user where phone = 12345678936;

這條SQL語句執行時就會變為

explain select * from tb_user where cast(phone as signed int) = 12345678936;

由于對索引列進行了函數操作，所以才導致索引失效，從而全表掃描了。

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

什么情況下把數字轉為字符串，什么情況下把字符串轉為數字，優化器它是根據什么規則來進行判斷的？其實規則也并不復雜。

• 若返回1，則把字符串轉為數字。
• 若返回0，則把數字轉為字符串。

根據這個規則，我們再回過頭看看之前的查詢語句

select * from tb_user where phone = 12345678936

select '12345678936' = 12345678936

返回1 所以這時候就把左側的列值12345678936轉成數字。

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

為什么MySQL字符串類型查詢時不加引號索引會失效？這是因為要查詢的字符串字段沒有加引號時，MySQL內部進行了隱式轉換，此次查詢會導致全表掃描，所以慢了。

總結：

在索引列上進行了函數操作，MySQL內部會進行了隱式轉換，導致索引失效，從而產生全表掃描。

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

拓展

索引創建

1、主鍵索引：

 alter table table_name add primary key (column)

2、唯一索引：

    alter table table_name add unique (column)

3、普通索引：

   alter table table_name add index index_name (column)

4、全文索引：

alter table table_name add fulltext (column)

5、聯合索引：

alter table table_name add index index_name (column1,column2,column3)

索引刪除

alter table table_name drop index index_name;