前言：

最左匹配原則在我們 MySQL 開發(fā)過程中和面試過程中經(jīng)常遇到，為了加深印象和理解，我在這里把 MySQL 的最左匹配原則詳細(xì)的講解一下，包括它的原理以及是否導(dǎo)致索引失效的場景。

在講解 MySQL 的最左匹配原則之前，我們需要了解一下 MySQL 的聯(lián)合索引（也稱復(fù)合索引），因?yàn)?strong>最左匹配原則是在聯(lián)合索引的基礎(chǔ)上產(chǎn)生的，沒有聯(lián)合索引就沒有最左匹配原則這個概念。

一、聯(lián)合索引

1、什么是聯(lián)合索引

我們知道，單值索引指的是只使用一個字段作為索引字段的索引，而聯(lián)合索引則是使用多個字段來共同構(gòu)建成一個索引：

KEY idx_abc (a, b, c);

2、為什么要使用聯(lián)合索引

2-1、減少開銷

建一個聯(lián)合索引 (a, b, c)，實(shí)際上相當(dāng)于建了 (a)、(a, b)、(a, b, c) 三個索引。這樣我們就不需要創(chuàng)建 (a)、(b)、(c) 三個單值索引了。我們知道，每多一個索引，都會增加數(shù)據(jù)庫寫操作的開銷和磁盤空間的開銷，對于大量數(shù)據(jù)的表，使用聯(lián)合索引會大大的減少開銷！

2-2、覆蓋索引

對聯(lián)合索引 (a, b, c)，如果有如下的 SQL：select a, b, c from test where a=1 and b=2。那么 MySQL 可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，從而減少了很多的隨機(jī) IO 操作。而減少 IO 操作，而減少隨機(jī) IO 是 DBA 主要的優(yōu)化策略，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

2-3、提高效率

聯(lián)合索引的字段越多，通過索引篩選出的數(shù)據(jù)越少。假如有 1000W 條數(shù)據(jù)的表，有如下 sql: select * from table where a=1 and b=2 and c=3，假設(shè)每個條件可以篩選出 10% 的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出 1000W * 10% = 100w 條數(shù)據(jù)，然后再回表從 100w 條數(shù)據(jù)中找到符合 b=2 and c=3 的數(shù)據(jù)，然后再排序，再分頁。

但如果是聯(lián)合索引，則通過索引直接篩選出的數(shù)據(jù)為：1000w * 10% * 10% * 10% = 1w，這效率的提升可想而知！

二、最左匹配原則

1、最左匹配原則的規(guī)則

在聯(lián)合索引當(dāng)中，索引匹配時：最左字段優(yōu)先，以最左邊的字段為起點(diǎn)任何連續(xù)的字段索引都能匹配上，如果遇到范圍查詢 (>、<、between、like) 時就會停止匹配。

2、索引是否生效的場景

是否滿足最左匹配原則是衡量聯(lián)合索引命中與否的依據(jù)。存在的場景比較多，假設(shè)我們創(chuàng)建了以 a, b, c 三個字段的聯(lián)合索引 idx_abc(a, b, c)，下面我們分別展開討論索引是否失效的場景。

2-1、全字段全值匹配

索引的全部字段都在查找條件當(dāng)中，并且都是使用 = 進(jìn)行全值匹配的情況下，索引是命中生效的：

select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'
......

雖然 where 子句幾個搜索條件順序調(diào)換了，但不影響查詢結(jié)果，這是由于 MySQL 的查詢優(yōu)化器會自動調(diào)整 where 子句的條件順序以使用適合的索引，所以 MySQL 不存在 where 子句的順序問題而造成索引失效。

2-2、從左到右按順序匹配

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

只要是按照聯(lián)合索引創(chuàng)建的字段從左到右的順序依次使用，不管使用其中多少個字段，都會命中索引。

2-3、缺失最左邊的字段

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3' 

這種缺失了最左邊 a 字段的情況就是違背最左匹配原則的典型例子，結(jié)果就是沒有用到索引（索引失效）。

因?yàn)槿笔Я俗钭筮叺淖侄?，?dǎo)致索引數(shù)據(jù)結(jié)構(gòu) B+ 樹不知道第一步該查哪個節(jié)點(diǎn)，從而需要去全表掃描了。在建立搜索樹的時候 a 就是第一個比較因子，必須要先根據(jù) a 來搜索，進(jìn)而才能往后繼續(xù)查詢 b 和 c。

2-4、缺失中間的字段

假如去掉中間的字段，保留最左邊和右邊的字段（就是我們說的索引字段不連續(xù)）：

select * from table_name where a = '1' and c = '3' 

結(jié)果就是只用到了 a 列的索引，而 b 列和 c 列都沒有用到。

因?yàn)樵谶@種情況下進(jìn)行數(shù)據(jù)檢索時，B+ 樹可以用 a 來指定第一步的搜索方向，但由于下一個字段 b 的缺失，所以只能先把 a = 1 的數(shù)據(jù)主鍵 ID 都找出來，然后通過查到的主鍵 ID 回表查詢相關(guān)行，再去匹配 c 值的數(shù)據(jù)了。當(dāng)然，這至少把 a = 1 的數(shù)據(jù)篩選出來了，總比直接全表掃描好多了

2-5、匹配范圍值

出現(xiàn)匹配范圍值的情況可能比較復(fù)雜或難以理解，但我們只需要牢記最左匹配原則的規(guī)則：遇到范圍查詢 (>、<、between、like) 時就會停止匹配。

比如下面這種情況：

select * from table_name where  a = 1 and b > 3 and c = 'mm';

這種情況下，由于 a 是等值匹配，所以 B+ 樹走完 a 索引之后 b 還是有序的，但走完 b 索引之后，由于 b 是范圍匹配，所以此時 c 已經(jīng)是無序的了，最終只使用了 (a, b) 兩個索引（由于此時 c 就沒法走索引，所以優(yōu)化器只能根據(jù) a, b 得到數(shù)據(jù)的主鍵 ID 回表查詢，最終影響了執(zhí)行效率）。

再比如下面的情況：

select * from table_name where  a > 1 and b > 1
select * from table_name where  a > 1 and a < 3 and b > 1;

當(dāng)多個列同時進(jìn)行范圍查找時，只有對索引最左邊的那個列進(jìn)行范圍查找才用到 B+ 樹索引，也就是只有 a 用到索引，在 a > 1 和 1 < a < 3 的范圍內(nèi) b 是無序的，所以 b 不能用索引，找到 a 的記錄后，只能根據(jù)條件 b > 1 繼續(xù)逐條過濾。

2-6、like 語句匹配問題

當(dāng)索引列是字符型，并且使用了 like 語句進(jìn)行模糊查詢時，如果通配符 % 不出現(xiàn)在開頭，則可以用到索引，否則將會違背了最左匹配原則，而不會使用索引，走的是全表掃描：

select * from table_name where a like 'As%';   //走索引查詢
select * from table_name where a like '%As';   //全表查詢
select * from table_name where a like '%As%';  //全表查詢

我們先了解一下字符型字段的比較規(guī)則：當(dāng)列是字符型的話，它的比較規(guī)則是先比較字符串的第一個字符，第一個字符小的那個字符串就比較小，如果兩個字符串第一個字符相同，那就再比較第二個字符，依次類推。

所以，如果通配符 % 出現(xiàn)在開頭，B+ 樹則無法進(jìn)行比較匹配，進(jìn)而導(dǎo)致索引失效。

3、解決文件排序的問題

當(dāng)我們對查詢的數(shù)據(jù)進(jìn)行 order by 排序時，一般情況下，我們是先把數(shù)據(jù)記錄加載到內(nèi)存中，再用一些排序算法，比如快速排序，歸并排序等在內(nèi)存中對這些記錄進(jìn)行排序。但有時候查詢的結(jié)果集太大不能在內(nèi)存中進(jìn)行排序時，需要暫時借助磁盤空間存放中間結(jié)果，排序操作完成后再把排好序的結(jié)果返回客戶端。Mysql 把這種在磁盤上進(jìn)行排序的方式稱為文件排序（Filesort）。

文件排序是非常慢非常耗性能的，但如果 order by 子句用到了索引列，就有可能避免文件排序的問題：

select * from table_name order by a, b, c limit 10;

因?yàn)?B+ 樹索引本身就是按照上述規(guī)則排序的，準(zhǔn)確來說就是：索引是有序的，所以得到的結(jié)果集已經(jīng)排好序了，不用再進(jìn)行額外的排序操作。

注意：order by 的子句后面的字段順序也必須按照索引字段的順序給出，不能顛倒順序（MySQL 不會自動調(diào)整排序字段的順序）。

下面這種就是因?yàn)轭嵉鬼樞蚨鴽]有使用索引的情況：

select * from table_name order by b, c, a limit 10;

下面這種是用到部分索引的情況：

select * from table_name order by a limit 10;
select * from table_name order by a, b limit 10;

下面這種情況，由于聯(lián)合索引左邊列為常量，后邊的列排序可以用到索引：

select * from table_name where a =1 order by b, c limit 10;

 到此這篇關(guān)于MySQL聯(lián)合索引與最左匹配原則的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL聯(lián)合索引與最左匹配原則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論