闡述

通常我們?cè)诮⒙?lián)合索引的時(shí)候，相信建立過(guò)索引的同學(xué)們會(huì)發(fā)現(xiàn)，無(wú)論是Oracle 還是 MySQL 都會(huì)讓我們選擇索引的順序，比如我們想在 a,b,c 三個(gè)字段上建立一個(gè)聯(lián)合索引，我們可以選擇自己想要的優(yōu)先級(jí)，(a、b、c)，或是 (b、a、c) 或者是 (c、a、b) 等順序。

為什么數(shù)據(jù)庫(kù)會(huì)讓我們選擇字段的順序呢？

不都是三個(gè)字段的聯(lián)合索引么？

這里就引出了數(shù)據(jù)庫(kù)索引的最重要的原則之一，最左匹配原則。

在我們開(kāi)發(fā)中經(jīng)常會(huì)遇到這種問(wèn)題，明明這個(gè)字段建了聯(lián)合索引，但是SQL查詢(xún)?cè)撟侄螘r(shí)卻不會(huì)使用這個(gè)索引。難道這索引是假的？

比如索引 abc_index:(a,b,c) 是 a,b,c 三個(gè)字段的聯(lián)合索引，下列 sql 執(zhí)行時(shí)都無(wú)法命中索引 abc_index；

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

以下三種情況卻會(huì)走索引:

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';

從上面兩個(gè)例子大家有木有看出點(diǎn)眉目呢？

是的，索引 abc_index:(a,b,c)，只會(huì)在 where 條件中帶有 (a)、(a,b)、(a,b,c) 的三種類(lèi)型的查詢(xún)中使用。

其實(shí)這里說(shuō)的有一點(diǎn)歧義，其實(shí)當(dāng) where 條件只有 (a,c) 時(shí)也會(huì)走，但是只走a字段索引，不會(huì)走 c 字段。

那么這都是為什么呢？

我們一起來(lái)看看其原理吧。

一、最左匹配原則的原理

MySQL 建立多列索引（聯(lián)合索引）有最左匹配的原則，即最左優(yōu)先：

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

假設(shè)數(shù)據(jù)表 LOL (id,sex,price,name) 的物理位置（表中的無(wú)序數(shù)據(jù)）如下：

（注：下面數(shù)據(jù)是測(cè)試少量數(shù)據(jù)選用的，只為了方便大家看清楚。實(shí)際操作中，應(yīng)按照使用頻率、數(shù)據(jù)區(qū)分度來(lái)綜合設(shè)定索引順序喔~）

主鍵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發(fā)條魔靈
(10)    2         3150         KKK卡牌大師
(11)    1         450          HHH寒冰射手
(12)    2         450          GGG蓋倫
(13)    2         3150         OOO小提莫
(14)    2         3150         DDD刀鋒之影
(15)    2         6300         JJJ疾風(fēng)劍豪
(16)    2         450          JJJ劍圣

當(dāng)你在 LOL 表創(chuàng)建一個(gè)聯(lián)合索引 abc_index:(sex,price,name) 時(shí)，生成的 索引文件邏輯上等同于下表內(nèi)容（分級(jí)排序）：

sex(a)   price(b)       name(c)         主鍵id
1        450            HHH寒冰射手      (11)
1        1350           AAA安妮          (1)
1        3150           NNN奈德麗        (3)
1        4800           FFF發(fā)條魔靈       (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疾風(fēng)劍豪       (15)
2        6300           MMM盲僧          (2)

小伙伴兒們有沒(méi)有發(fā)現(xiàn)B+樹(shù)聯(lián)合索引的規(guī)律？

感覺(jué)還有點(diǎn)模糊的話(huà)，那咱們?cè)賮?lái)看一張索引存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)圖，或許更明了一些。

B+樹(shù)中的聯(lián)合索引，每級(jí)索引都是排好序的。

聯(lián)合索引 bcd_index:(b,c,d) , 在索引樹(shù)中的樣子如圖 ， 在比較的過(guò)程中 ，先判斷 b 再判斷 c 然后是 d 。

由上圖可以看出，B+ 樹(shù)的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，同樣，對(duì)于我們這張表的聯(lián)合索引 (sex,price,name)來(lái)說(shuō) ，B+ 樹(shù)也是按照從左到右的順序來(lái)建立搜索樹(shù)的，當(dāng)SQL如下時(shí)：

select sex,price,name from LOL 
where sex = 2 
and price = 6300 
and name = 'JJJ疾風(fēng)劍豪'; 

B+ 樹(shù)會(huì)優(yōu)先比較 sex 來(lái)確定下一步的指針?biāo)逊较?，如?sex 相同再依次比較 price 和 name，最后得到檢索的數(shù)據(jù)；

二、違背最左原則導(dǎo)致索引失效的情況

（下面以聯(lián)合索引 abc_index:(a,b,c) 來(lái)進(jìn)行講解，便于理解）

1、查詢(xún)條件中，缺失優(yōu)先級(jí)最高的索引 “a”

當(dāng) where b = 6300 and c = ‘JJJ疾風(fēng)劍豪’ 這種沒(méi)有以 a 為條件來(lái)檢索時(shí)；B+樹(shù)就不知道第一步該查哪個(gè)節(jié)點(diǎn)，從而需要去全表掃描了（即不走索引）。

因?yàn)榻⑺阉鳂?shù)的時(shí)候 a 就是第一個(gè)比較因子，必須要先根據(jù) a 來(lái)搜索，進(jìn)而才能往后繼續(xù)查詢(xún)b 和 c，這點(diǎn)我們通過(guò)上面的存儲(chǔ)結(jié)構(gòu)圖可以看明白。

2、查詢(xún)條件中，缺失優(yōu)先級(jí)居中的索引 “b”

當(dāng) where a =1 and c =“JJJ疾風(fēng)劍豪” 這樣的數(shù)據(jù)來(lái)檢索時(shí)；B+ 樹(shù)可以用 a 來(lái)指定第一步搜索方向，但由于下一個(gè)字段 b 的缺失，所以只能把 a = 1 的數(shù)據(jù)主鍵ID都找到，通過(guò)查到的主鍵ID回表查詢(xún)相關(guān)行，再去匹配 c = ‘JJJ疾風(fēng)劍豪’ 的數(shù)據(jù)了，當(dāng)然，這至少把 a = 1 的數(shù)據(jù)篩選出來(lái)了，總比直接全表掃描好多了。

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

三、查詢(xún)優(yōu)化器偷偷干了哪些事兒

當(dāng)對(duì)索引中所有列通過(guò) “=” 或 “IN” 進(jìn)行精確匹配時(shí)，索引都可以被用到。

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

理論上索引對(duì)順序是敏感的，但是由于 MySQL 的查詢(xún)優(yōu)化器會(huì)自動(dòng)調(diào)整 where 子句的條件順序以使用適合的索引，所以 MySQL 不存在 where 子句的順序問(wèn)題而造成索引失效。當(dāng)然了，SQL書(shū)寫(xiě)的好習(xí)慣要保持，這也能讓其他同事更好地理解你的SQL。

2、還有一個(gè)特殊情況說(shuō)明下，下面這種類(lèi)型的SQL， a 與 b 會(huì)走索引，c不會(huì)走。

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

對(duì)于上面這種類(lèi)型的sql語(yǔ)句；

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

在a、b走完索引后，c已經(jīng)是無(wú)序了，所以c就沒(méi)法走索引，優(yōu)化器會(huì)認(rèn)為還不如全表掃描c字段來(lái)的快。所以只使用了(a,b)兩個(gè)索引，影響了執(zhí)行效率。

其實(shí)，這種場(chǎng)景可以通過(guò)修改索引順序?yàn)?abc_index:(a,c,b)，就可以使三個(gè)索引字段都用到索引，建議小伙伴們不要有問(wèn)題就想著新增索引哦，浪費(fèi)資源還增加服務(wù)器壓力。

綜上，如果通過(guò)調(diào)整順序，就可以解決問(wèn)題或少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是我們DBA人員需要優(yōu)先考慮采用的。

四、知識(shí)點(diǎn)

1、如何通過(guò)有序索引排序，避免冗余執(zhí)行 order by

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

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

是將表 LOL 中的數(shù)據(jù)按 “sex” 一列排序。

而只有當(dāng) order by 與 where 語(yǔ)句同時(shí)出現(xiàn)，order by 的排序功能無(wú)效。

換句話(huà)說(shuō)，order by 中的字段在執(zhí)行計(jì)劃中利用了索引時(shí)，不用排序操作。如下SQL時(shí)，不會(huì)按 sex 一列排序，因?yàn)?sex 本身已經(jīng)是有序的了。

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

所以，只有 order by 字段出現(xiàn)在 where 條件中時(shí),才會(huì)利用該字段的索引而避免排序。

對(duì)于上面的語(yǔ)句，數(shù)據(jù)庫(kù)的處理順序是：

第一步：根據(jù) where 條件和統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃，得到數(shù)據(jù)。

第二步：將得到的數(shù)據(jù)排序。當(dāng)執(zhí)行處理數(shù)據(jù)（order by）時(shí)，數(shù)據(jù)庫(kù)會(huì)先查看第一步的執(zhí)行計(jì)劃，看 order by 的字段是否在執(zhí)行計(jì)劃中利用了索引。如果是，則可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。如果不是，則排序操作。

第三步：返回排序后的數(shù)據(jù)。

2、like 語(yǔ)句的索引問(wèn)題

如果通配符 % 不出現(xiàn)在開(kāi)頭，則可以用到索引，但根據(jù)具體情況不同可能只會(huì)用其中一個(gè)前綴，在 like “value%” 可以使用索引，但是 like “%value%” 違背了最左匹配原則，不會(huì)使用索引，走的是全表掃描。

3、不要在列上進(jìn)行運(yùn)算

如果查詢(xún)條件中含有函數(shù)或表達(dá)式，將導(dǎo)致索引失效而進(jìn)行全表掃描

例如 ：

select * from user where YEAR(birthday) < 1990

可以改造成：

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

4、索引不會(huì)包含有 NULL 值的列

只要列中包含有 NULL 值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有 NULL 值，那么這一列對(duì)于此復(fù)合索引就是無(wú)效的。所以在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為 NULL

5、盡量選擇區(qū)分度高的列作為索引

區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是 1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是 0。一般需要 join 的字段都要求區(qū)分度 0.1 以上，即平均 1 條掃描 10 條記錄

6、覆蓋索引的好處

如果一個(gè)索引包含所有需要的查詢(xún)的字段的值，我們稱(chēng)之為覆蓋索引。覆蓋索引是非常有用的工具，能夠極大的提高性能。因?yàn)?，只需要讀取索引，而無(wú)需讀表，極大減少數(shù)據(jù)訪(fǎng)問(wèn)量，這也是不建議使用Select * 的原因。

到此這篇關(guān)于MySQL最左匹配原則詳細(xì)分析的文章就介紹到這了,更多相關(guān)MySQL最左匹配內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論