MySQL最左匹配原則詳細分析

闡述

通常我們在建立聯合索引的時候，相信建立過索引的同學們會發現，無論是Oracle 還是 MySQL 都會讓我們選擇索引的順序，比如我們想在 a,b,c 三個欄位上建立一個聯合索引，我們可以選擇自己想要的優先順序，(a、b、c)，或是 (b、a、c) 或者是 (c、a、b) 等順序。

為什麼資料庫會讓我們選擇欄位的順序呢？

不都是三個欄位的聯合索引麼？

這裡就引出了資料庫索引的最重要的原則之一，最左匹配原則。

在我們開發中經常會遇到這種問題，明明這個欄位建了聯合索引，但是SQL查詢該欄位時卻不會使用這個索引。難道這索引是假的？

比如索引 abc_index:(a,b,c) 是 a,b,c 三個欄位的聯合索引，下列 sql 執行時都無法命中索引 abc_index；

select * from table where c = '1';
select * from table where b ='1' and c ='2';

以下三種情況卻會走索引:

select * from table where a = '1';
select * from table where a = '1' and b = '2';
select * from table where a = '1' and b = '2'  and c='3';

從上面兩個例子大家有木有看出點眉目呢？

是的，索引 abc_index:(a,b,c)，只會在 where 條件中帶有 (a)、(a,b)、(a,b,c) 的三種型別的查詢中使用。

其實這裡說的有一點歧義，其實當 where 條件只有 (a,c) 時也會走，但是隻走a欄位索引，不會走 c 欄位。

那麼這都是為什麼呢？

我們一起來看看其原理吧。

一、最左匹配原則的原理

MySQL 建立多列索引（聯合索引）有最左匹配的原則，即最左優先：

如果有一個 2 列的索引 (a, b)，則已經對 (a)、(a, b) 上建立了索引； 如果有一個 3 列索引 (a, b, c)，則已經對 (a)、(a, b)、(a, b, c) 上建立了索引；

假設資料表 LOL (id,sex,price,name) 的物理位置（表中的無序資料）如下：

（注：下面資料是測試少量資料選用的，只為了方便大家看清楚。實際操作中，應按照使用頻率、資料區分度來綜合設定索引順序喔~）

主鍵id  sex(a)   price(b)      name(c)    
(1)     1         1350         AAA安妮
(2)     2         6300         MMM盲僧
(3)     1         3150         NNN奈德麗
(4)     2         6300         CCC錘石
(5)     1         6300         LLL龍女
(6)     2         3150         EEE伊澤瑞爾
(7)     2         6300         III艾克
(8)     1         6300         BBB暴走蘿莉
(9)     1         4800         FFF發條魔靈
(10)    2         3150         KKK卡牌大師
(11)    1         450          HHH寒冰射手
(12)    2         450          GGG蓋倫
(13)    2         3150         OOO小提莫
(14)    2         3150         DDD刀鋒之影
(15)    2         6300         JJJ疾風劍豪
(16)    2         450          JJJ劍聖

當你在 LOL 表建立一個聯合索引 abc_index:(sex,price,name) 時，生成的 索引檔案邏輯上等同於下表內容（分級排序）：

sex(a)   price(b)       name(c)         主鍵id
1        450            HHH寒冰射手      (11)
1        1350           AAA安妮          (1)
1        3150           NNN奈德麗        (3)
1        4800           FFF發條魔靈       (9)
1        6300           BBB暴走蘿莉       (8)
1        6300           LLL龍女          (5)
2        450            GGG蓋倫          (12)
2        450            JJJ劍聖          (16)
2        3150           DDD刀鋒之影       (14)
2        3150           EEE伊澤瑞爾       (6)
2        3150           KKK卡牌大師       (10)
2        3150           OOO小提莫         (13)
2        6300           CCC錘石          (4)
2        6300           III艾克          (7)
2        6300           JJJ疾風劍豪       (15)
2        6300           MMM盲僧          (2)

小夥伴兒們有沒有發現B+樹聯合索引的規律？

感覺還有點模糊的話，那咱們再來看一張索引儲存資料的結構圖，或許更明瞭一些。

B+樹中的聯合索引，每級索引都是排好序的。

聯合索引 bcd_index:(b,c,d) , 在索引樹中的樣子如圖 ， 在比較的過程中 ，先判斷 b 再判斷 c 然後是 d 。

由上圖可以看出，B+ 樹的資料項是複合的資料結構，同樣，對於我們這張表的聯合索引 (sex,price,name)來說 ，B+ 樹也是按照從左到右的順序來建立搜尋樹的，當SQL如下時：

select sex,price,name from LOL 
where sex = 2 
and price = 6300 
and name = 'JJJ疾風劍豪'; 

B+ 樹會優先比較 sex 來確定下一步的指標所搜方向，如果 sex 相同再依次比較 price 和 name，最後得到檢索的資料；

二、違背最左原則導致索引失效的情況

（下面以聯合索引 abc_index:(a,b,c) 來進行講解，便於理解）

1、查詢條件中，缺失優先順序最高的索引 “a”

當 where b = 6300 and c = ‘JJJ疾風劍豪’ 這種沒有以 a 為條件來檢索時；B+樹就不知道第一步該查哪個節點，從而需要去全表掃描了（即不走索引）。

因為建立搜尋樹的時候 a 就是第一個比較因子，必須要先根據 a 來搜尋，進而才能往後繼續查詢b 和 c，這點我們通過上面的儲存結構圖可以看明白。

2、查詢條件中，缺失優先順序居中的索引 “b”

當 where a =1 and c =“JJJ疾風劍豪” 這樣的資料來檢索時；B+ 樹可以用 a 來指定第一步搜尋方向，但由於下一個欄位 b 的缺失，所以只能把 a = 1 的資料主鍵ID都找到，通過查到的主鍵ID回表查詢相關行，再去匹配 c = ‘JJJ疾風劍豪’ 的資料了，當然，這至少把 a = 1 的資料篩選出來了，總比直接全表掃描好多了。

這就是MySQL非常重要的原則，即索引的最左匹配原則。

三、查詢優化器偷偷幹了哪些事兒

當對索引中所有列通過 “=” 或 “IN” 進行精確匹配時，索引都可以被用到。

1、如果建的索引順序是 (a, b)。而查詢的語句是 where b = 1 AND a = ‘aaa’; 為什麼還能利用到索引？

理論上索引對順序是敏感的，但是由於 MySQL 的查詢優化器會自動調整 where 子句的條件順序以使用適合的索引，所以 MySQL 不存在 where 子句的順序問題而造成索引失效。當然了，SQL書寫的好習慣要保持，這也能讓其他同事更好地理解你的SQL。

2、還有一個特殊情況說明下，下面這種型別的SQL， a 與 b 會走索引，c不會走。

select * from LOL 
where a = 2 and b > 1000  
and c='JJJ疾風劍豪';

對於上面這種型別的sql語句；

mysql會一直向右匹配直到遇到範圍查詢 (>、<、between、like) 就停止匹配（包括like '陳%'這種）。

在a、b走完索引後，c已經是無序了，所以c就沒法走索引，優化器會認為還不如全表掃描c欄位來的快。所以只使用了(a,b)兩個索引，影響了執行效率。

其實，這種場景可以通過修改索引順序為 abc_index:(a,c,b)，就可以使三個索引欄位都用到索引，建議小夥伴們不要有問題就想著新增索引哦，浪費資源還增加伺服器壓力。

綜上，如果通過調整順序，就可以解決問題或少維護一個索引，那麼這個順序往往就是我們DBA人員需要優先考慮採用的。

四、知識點

1、如何通過有序索引排序，避免冗餘執行 order by

order by 用在 select 語句中，具備排序功能。如：

SELECT sex, price, name FROM LOL ORDER BY sex;

是將表 LOL 中的資料按 “sex” 一列排序。

而只有當 order by 與 where 語句同時出現，order by 的排序功能無效。

換句話說，order by 中的欄位在執行計劃中利用了索引時，不用排序操作。如下SQL時，不會按 sex 一列排序，因為 sex 本身已經是有序的了。

SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;

所以，只有 order by 欄位出現在 where 條件中時,才會利用該欄位的索引而避免排序。

對於上面的語句，資料庫的處理順序是：

第一步：根據 where 條件和統計資訊生成執行計劃，得到資料。

第二步：將得到的資料排序。當執行處理資料（order by）時，資料庫會先檢視第一步的執行計劃，看 order by 的欄位是否在執行計劃中利用了索引。如果是，則可以利用索引順序而直接取得已經排好序的資料。如果不是，則排序操作。

第三步：返回排序後的資料。

2、like 語句的索引問題

如果萬用字元 % 不出現在開頭，則可以用到索引，但根據具體情況不同可能只會用其中一個字首，在 like “value%” 可以使用索引，但是 like “%value%” 違背了最左匹配原則，不會使用索引，走的是全表掃描。

3、不要在列上進行運算

如果查詢條件中含有函數或表示式，將導致索引失效而進行全表掃描

例如 ：

select * from user where YEAR(birthday) < 1990

可以改造成：

select * from users where birthday <'1990-01-01′

4、索引不會包含有 NULL 值的列

只要列中包含有 NULL 值都將不會被包含在索引中，複合索引中只要有一列含有 NULL 值，那麼這一列對於此複合索引就是無效的。所以在資料庫設計時不要讓欄位的預設值為 NULL

5、儘量選擇區分度高的列作為索引

區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是 1，而一些狀態、性別欄位可能在巨量資料面前區分度就是 0。一般需要 join 的欄位都要求區分度 0.1 以上，即平均 1 條掃描 10 條記錄

6、覆蓋索引的好處

如果一個索引包含所有需要的查詢的欄位的值，我們稱之為覆蓋索引。覆蓋索引是非常有用的工具，能夠極大的提高效能。因為，只需要讀取索引，而無需讀表，極大減少資料存取量，這也是不建議使用Select * 的原因。

到此這篇關於MySQL最左匹配原則詳細分析的文章就介紹到這了,更多相關MySQL最左匹配內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！