索引失效底層原理分析，這麼多年終於講清楚了

前言

吊打面試官 又來啦，今天我們講講MySQL 索引為什麼會失效 ，很多文章和培訓機構的教學，都只會告訴你，在什麼情況下索引會失效。

比如：沒遵循最佳左字首法則、範圍查詢的右邊會失效、like查詢用不到索引等等

但是沒有一個人告訴你， 索引失效的原理 是什麼， 老哥 今天就告訴大家，讓你們 知其然，還要 知其所以然 。

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

image

開局一張圖，由資料庫的 a 欄位和 b 欄位組成一個 聯合索引 。

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

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用不到索引。

where name like "a%"

where name like "%a%"

where name like "%a"

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

%放在右邊
兩個%%
%放在左邊

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

字首
中綴
字尾

沒錯，這裡依然是最佳左字首法則這個概念

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

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

開始分析

一、%號放右邊（字首）

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

二、%號放右邊

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

三、兩個%%號

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

這裡把一些經典的索引失效案例給大家分析了，希望能引發大家的思考，能夠通過這些案例，明白其他情況索引失效的原理。