索引失效底層原理是什么

這篇文章主要講解了“索引失效底層原理是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“索引失效底層原理是什么”吧！

單值索引B+樹圖

單值索引在B+樹的結構里，一個節點只存一個鍵值對

聯合索引

開局一張圖，由數據庫的a字段和b字段組成一個聯合索引。

從本質上來說，聯合索引也是一個B+樹，和單值索引不同的是，聯合索引的鍵值對不是1，而是大于1個。

a, b 排序分析

a順序：1，1，2，2，3，3

b順序：1，2，1，4，1，2

大家可以發現a字段是有序排列，b字段是無序排列（因為B+樹只能選一個字段來構建有序的樹）

一不小心又會發現，在a相等的情況下，b字段是有序的。

大家想想平時編程中我們要對兩個字段排序，是不是先按照第一個字段排序，如果第一個字段出現相等的情況，就用第二個字段排序。這個排序方式同樣被用到了B+樹里。

分析最佳左前綴原理

先舉一個遵循最佳左前綴法則的例子

select * from testTable where a=1 and b=2

分析如下：

首先a字段在B+樹上是有序的，所以我們可以通過二分查找法來定位到a=1的位置。

其次在a確定的情況下，b是相對有序的，因為有序，所以同樣可以通過二分查找法找到b=2的位置。

再來看看不遵循最佳左前綴的例子

select * from testTable where b=2

分析如下：

我們來回想一下b有順序的前提：在a確定的情況下。

現在你的a都飛了，那b肯定是不能確定順序的，在一個無序的B+樹上是無法用二分查找來定位到b字段的。

所以這個時候，是用不上索引的。大家懂了嗎？

范圍查詢右邊失效原理

舉例

select * from testTable where a>1 and b=2

分析如下：

首先a字段在B+樹上是有序的，所以可以用二分查找法定位到1，然后將所有大于1的數據取出來，a可以用到索引。

b有序的前提是a是確定的值，那么現在a的值是取大于1的，可能有10個大于1的a，也可能有一百個a。

大于1的a那部分的B+樹里，b字段是無序的（開局一張圖），所以b不能在無序的B+樹里用二分查找來查詢，b用不到索引。

like索引失效原理

where name like "a%"  where name like "%a%"  where name like "%a"

我們先來了解一下%的用途

•  %放在右邊，代表查詢以"a"開頭的數據，如：abc

•  兩個%%，代表查詢數據中包含"a"的數據，如：cab、cba、abc

•  %放在左邊，代表查詢以"a"為結尾的數據，如cba

為什么%放在右邊有時候能用到索引

•  %放右邊叫做：前綴

•  %%叫做：中綴

•  %放在左邊叫做：后綴

沒錯，這里依然是最佳左前綴法則這個概念

大家可以看到，上面的B+樹是由字符串組成的。

字符串的排序方式：先按照第一個字母排序，如果第一個字母相同，就按照第二個字母排序。。。以此類推

開始分析

一、%號放右邊（前綴）

由于B+樹的索引順序，是按照首字母的大小進行排序，前綴匹配又是匹配首字母。所以可以在B+樹上進行有序的查找，查找首字母符合要求的數據。所以有些時候可以用到索引。

二、%號放左邊

是匹配字符串尾部的數據，我們上面說了排序規則，尾部的字母是沒有順序的，所以不能按照索引順序查詢，就用不到索引。

三、兩個%%號

這個是查詢任意位置的字母滿足條件即可，只有首字母是進行索引排序的，其他位置的字母都是相對無序的，所以查找任意位置的字母是用不上索引的。

感謝各位的閱讀，以上就是“索引失效底層原理是什么”的內容了，經過本文的學習后，相信大家對索引失效底層原理是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！