MySQL細數發生索引失效的情況

索引的儲存結構

首先了解一下索引的儲存結構，知道了索引的儲存結構，才方便我們更好地理解索引失效的問題。

索引的儲存結構跟MySQL的儲存引擎有關，儲存引擎的不同採用的結構也會不同。

MySQL預設的儲存引擎InnoDB採用B+Tree作為索引的資料結構，在建立表時，InnoDB會預設建立一個主鍵索引，這是一個聚簇索引，其他索引都屬於二級索引。

MyISAM儲存引擎在建立表時，預設是用的是B+樹索引。

雖然和InnoDB一樣都支援B+樹索引，但是他們儲存資料的方式不同；

InnoDB是聚簇索引（B+樹索引的葉子結點儲存資料本身）

MyISAM是非聚集索引（B+樹的葉子結點儲存資料的實體地址）

如下圖所示：

InnoDB儲存引擎可以分為【聚簇索引】和【二級索引】，它們的區別在於聚簇索引的葉子結點存放的是實際資料，所有完整的資料都存放在聚簇索引的葉子結點，二級索引的葉子結點存放的是主鍵值。

在使用二級索引欄位作為查詢條件，查詢資料在聚簇索引上的時候，

會先根據條件在二級索引上找到對應的葉子結點得到主鍵值，

再根據主鍵值去聚簇索引上找到對應的葉子結點然後查詢到對應的資料，

這個過程叫回表

使用二級索引作為查詢條件，查詢的資料在二級索引的葉子結點上的時候，那麼只需找到二級索引的B+樹對應的葉子結點，讀取資料，這個過程叫覆蓋索引

上面這些查詢條件都用到了索引列，但並不表示用到索引列索引就一定會生效，我們再來看一看索引失效的情況

不合理的模糊查詢條件

使用左或左右模糊查詢的時候，也就是like "%張"或like "%張%"這兩種模糊查詢方式都會導致索引失效

因為B+樹是根據索引值進行排列的，字首不確定的時候可能是，“小張”，"二張"之類的所有的情況，就只能通過全表掃描的方式來查詢

對索引使用函數

例如：SELECT * FROM sys_user WHERE LENGTH(user_id) = 3 ;

因為索引儲存的是索引欄位的原始值，而不是經過函數計算後的值，所以使用函數的時候就不會走索引了

不過從MySQL8.0開始，索引特性增加了函數索引，也就是針對該函數計算後的值建立一個索引，這樣就可以通過掃描索引來查詢資料了；

alter table t_user add key idx_name_length ((length(name)));

對索引進行表示式計算

例如：select * from sys_user where user_id+1 =3;

但是如果是SELECT * FROM sys_user WHERE user_id = 1+1 ;這樣的不在索引欄位上進行計算，就又會走索引了

原因跟對索引使用函數差不多，索引儲存的是索引欄位的原始值，而不是運算後的值，所以無法走索引

對索引使用隱式轉換

這裡的phone欄位是二級索引，且是varchar型別的

使用整型作為查詢引數的時候，執行計劃中type為ALL，也就是通過全表掃描查詢的，但如果是字串型別，還是走索引查詢的

我們再看一個例子

這裡user_id是bigint型別，但是使用字串作為查詢引數還是走了索引的

為什麼第一個例子導致了索引失效，而第二個不會呢？

這裡就要了解一下MySQL的字元轉換規則了，看是數位轉字串，還是字串轉數位

我們可以用select "10">9來測試一下

如果是數位轉字串，那麼就相當於select "10">"9"結果應該是0

如果是字串轉數位，那麼就相當於select 10>9，結果是1

在MySQL中的執行結果如下：

這就說明，MySQL在遇到數位與字串的比較的時候，會自動把字串轉換為數位，然後進行比較

也就是說，在第一個例子中

SELECT * FROM sys_user WHERE phone = 18200000000 ;

相當於

SELECT * FROM sys_user WHERE CAST(phone AS UNSIGNED) = 18200000000 ;

這就在索引欄位上使用了函數，所以導致索引失效

而在第二個例子中

SELECT * FROM sys_user WHERE user_id = "1" ;

相當於

SELECT * FROM sys_user WHERE user_id = CAST("1" AS UNSIGNED) ;

函數式作用在查詢引數上的，並沒有作用在索引欄位上，所以還是走索引的

聯合索引非最左匹配

多個普通欄位組合在一起建立的索引叫做聯合索引（組合索引）

在使用聯合索引的時候，一定要注意順序問題，聯合索引的使用需要遵循最左匹配原則，也就是按照最左優先的方式進行索引匹配。

例如，建立了一個(a,b,c)聯合索引，那麼如果查詢條件是一下幾種，就可以匹配上聯合索引

where a = 1
where a = 1 and b = 2
where a = 1 and b = 2 and c = 3

需要注意的是，因為有查詢優化器，所以a欄位在where子句中的順序不重要

但是必須要有a欄位，如果像下面幾種，因為不符合最左匹配原則，就無法匹配上聯合索引，聯合索引就會失效：

where b = 2
where c = 3
where b = 2 and c = 3

還有一個比較特殊的查詢條件：where a = 1 and c = 3

在MySQL5.5的話，前面的a 會走索引，在聯合索引找到主鍵值，然後回表，到主鍵索引讀取資料行，然後在比對c欄位的值

在MySQL5.6之後，有一個索引下推的功能，

下推就是將部分上層(服務層)負責的事情，交給了下層(引擎層)處理

儲存引擎直接在聯合索引裡按照c=3過濾，按照過濾後的資料在進行回表掃描，減少了回表的次數，從而提升了效能

在執行計劃中Extra = Using index condition就表示使用了索引下推

聯合索引不遵循最左匹配原則的原因：在聯合索引中，資料按照第一列索引進行排序，第一列資料相同時，才會按照第二列進行排序，以此類推，所以直接使用第二列進行查詢的時候，聯合索引就會失效

where子句中的or

where子句中or的條件列有不是索引列會導致索引失效

例如：下圖中id是索引列，email不是索引列，從執行計劃來看，進行了全文掃描並沒有使用到索引

因為or關鍵字只滿足一個條件就可以，因此只要有一個列不是索引列，其他索引列也就沒有意義了，就會進行全表掃描

在email列上建立索引之後，可以看到執行計劃中使用到了兩個索引

type = index_merge表示對id 和email都進行了掃描，然後進行了合併

總結

導致索引失效的情況有：

不合理的使用模糊查詢：like "%張"或like %張%

對索引列使用函數

對索引使用表示式計算

對索引使用隱式轉換，這三個都是引起了索引列值的變化導致索引失效

聯合索引非最左匹配

where子句中or條件列沒有使用索引

到此這篇關於MySQL細數發生索引失效的情況的文章就介紹到這了,更多相關MySQL索引失效內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！