群裡一個小夥伴在問為什麼MySQL字串不加單引號會導致索引失效,這個問題估計很多人都知道答案。沒錯,是因為MySQL內部進行了隱式轉換。
本期文章就聊聊什麼是隱式轉換,為什麼會發生隱式轉換。
系列文章
MySQL統計總數就用count(*),別花裡胡哨的《死磕MySQL系列 十》
一、幾大索引失效原因
你肯定在網上看到過非常多關於索引失效原因的文章,但是一定要自己親手嘗試一下,因為版本不同引發的結果不會一致。
1.帶頭大哥不能死
這局經典語句是說建立索引要符合最左側原則。
例如表結構為u_id,u_name,u_age,u_sex,u_phone,u_time
建立索引為idx_user_name_age_sex
。
查詢條件必須帶上u_name這一列。
2.不在索引列上做任何操作
不在索引列上做任何計算、函式、自動或者手動的型別轉換,否則會進行全表掃描。簡而言之不要在索引列上做任何操作。
3.倆邊型別不等
例如建立了索引idx_user_name,name欄位型別為varchar
在查詢時使用where name = kaka,這樣的查詢方式會直接造成索引失效。
正確的用法為where name = "kaka"。
4.不適當的like查詢會導致索引失效
建立索引為idx_user_name
執行語句為select * from user where name like "kaka%";可以命中索引。
執行語句為select name from user where name like "%kaka";可以使用到索引(僅在8.0以上版本)。
執行語句為select * from user where name like ''%kaka";會直接導致索引失效
5.範圍條件之後的索引會失效
建立索引為idx_user_name_age_sex
執行語句select * from user where name = 'kaka' and age > 11 and sex = 1;
上面這條sql語句只會命中name和age索引,sex索引會失效。
複合索引失效需要檢視key_len的長度即可。
總結:%在後邊會命令索引,當使用了覆蓋索引時任何查詢方式都可命中索引。
以上就是咔咔關於索引失效會出現的原因總結,在很多文章中沒有標註MySQL版本,所以你有可能會看到is null 、or索引會失效的結論。
二、從規則方面說明索引失效的原因
問題的答案就是第3點,兩邊型別不一致導致索引失效。
下圖是表結構,目前這個表存在兩個索引,一個主鍵索引,一個普通索引phone。
分別執行以下兩條SQL語句
explain select * from evt_sms where phone = 13020733815;
explain select * from evt_sms where phone = '13020733815';
從上圖可看出,執行第一條SQL沒有使用到索引,第二條SQL卻使用到了索引。
不錯,你也發現了兩條SQL的不同,第二條SQL跟第一條SQL邏輯一致,不同的是一個查詢條件有引號,一個沒有。
問題:為什麼邏輯相同的SQL卻是用不了索引
選擇索引是優化器大哥的工作,大哥做事肯定輪不到我們們去教,因為大哥有自己的一套規則。
對於優化器來說,如果等號兩邊的資料型別不一致,則會發生隱式轉換。
例如,explain select * from evt_sms where phone = 13020733815;
這條SQL語句就會變為explain select * from evt_sms where cast(phone as signed int) = 13020733815;
由於對索引列進行了函式操作,從而導致索引失效。
問題:為什麼會把左側的列轉為int型別呢?
優化器大哥就是根據這個規則進行判斷,是把字串轉為數字,還是把數字轉為字串。
若返回1,則把字串轉為數字。
若返回0,則把數字轉為字串。
問題:select * from evt_sms where id = "193014410456945216"這條SQL語句能用上索引嗎?
如果你忘記了表結構,可以翻到文章開頭再看下錶evt_sms的索引。
可以知道列id新增了主鍵索引,型別為int型別。
根據規則得到,MySQL8.0以上的版本是將字串轉為數字。
所以說,函式操作的是等號右邊的資料,跟索引列沒有關係,所以可以用上索引。
那麼來到資料庫驗證一下結論,你答對了嗎?
三、從索引結構說明索引失效原因
有這樣一個需求,要統計每年雙11註冊使用者數量。
可以看到在evt_sms表中是沒有給create_time建立索引的,於是你會執行alter table evt_sms add index idx_ctime(create_time)
,給create_time新增上索引。
接著你就執行了下面的SQL語句。
explain select count(*) from evt_sms where month(create_time) = 11;
上線沒一會資料庫出現了大量的慢查詢,導致非常多的SQL返回失敗。
此時公司大牛肯定會直接指出問題,索引列進行函式操作。
問題:為什麼索引列使用函式就用不上索引了呢?
你現在看到的create_time索引結構圖。
若此時執行的是where create_time = '2021-11-16',那麼MySQL就會非常快的等位到對應位置,並返回結果。
但是,做了函式操作,例如month(2021-11-16)得到的值是11。
當MySQL拿到返回的這個11時,在索引結構中根據就不知道怎麼辦。MySQL之所以能使用快速定位,是因為B+樹的有序性。
而使用了函式對索引列進行操作後就會破壞索引的有序性,因此優化器大哥會選擇執行代價最低的索引來繼續執行。
四、結論
本期文章給大家介紹了兩個案例,一個隱式轉換,一個對索引列進行函式操作。
兩種情況的本質是一樣的,都是在索引列上進行了函式操作,導致全表掃描。
類似於這兩種情況的還是字符集問題,不過一般這個問題會會很少發生,如有新業務需要新建立表,都會設定為之前的字符集。
兩張表的字符集不同在進行join時也會導致隱式字符集轉換,導致索引失效。
“堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助,我是咔咔,下期見。
”