為什麼MySQL字串不加引號索引失效?《死磕MySQL系列 十一》

發表於2021-11-17

群裡一個小夥伴在問為什麼MySQL字串不加單引號會導致索引失效,這個問題估計很多人都知道答案。沒錯,是因為MySQL內部進行了隱式轉換。

本期文章就聊聊什麼是隱式轉換,為什麼會發生隱式轉換。

系列文章

字串可以這樣加索引,你知嗎?《死磕MySQL系列 七》

無法復現的“慢”SQL《死磕MySQL系列 八》

什麼?還在用delete刪除資料《死磕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時也會導致隱式字符集轉換,導致索引失效。

堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助,我是咔咔,下期見。

相關文章