一、前言

上一篇中已經(jīng)講過了索引相關(guān)的知識，為什么還要在講一下聯(lián)合索引（二級索引），是因?yàn)檫@個知識點(diǎn)特別重要，不論是在面試中，還是在實(shí)際的使用過程中，理解和掌握聯(lián)合索引，是我們提升數(shù)據(jù)庫查詢優(yōu)化操作或者提升性能的重要手段之一。

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

Mysql從物理存儲上將索引上分為：分為聚簇索引和非聚簇索引

  主鍵索引也被稱為聚簇索引（clustered index），也叫作聚集索引。其余都稱呼為非主鍵索引也被稱為二級索引（secondary index），也叫作輔助索引。

它們區(qū)別就在于葉子節(jié)點(diǎn)存放的是什么數(shù)據(jù)：

• 聚簇索引的葉子節(jié)點(diǎn)存放的是實(shí)際數(shù)據(jù)，所有完整的用戶記錄都存放在聚簇索引的葉子節(jié)點(diǎn)；
• 二級索引的葉子節(jié)點(diǎn)存放的是主鍵值，而不是實(shí)際數(shù)據(jù)。

聯(lián)合索引屬于非聚簇索引的一種，也稱為二級索引，使用多個字段來共同構(gòu)建成一個索引：

KEY idx_abc (a, b, c);

2.1、聚簇索引

InnoDB存儲引擎表是索引組織表，即表中數(shù)據(jù)按照主鍵順序存放。而聚集索引就是按照每張表的主鍵構(gòu)造一棵B+樹，同時葉子節(jié)點(diǎn)中存放的即為整張表的行記錄數(shù)據(jù) 。

我們也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁。聚集索引的這個特性決定了索引組織表中數(shù)據(jù)也是索引的一部分。同B+樹數(shù)據(jù)結(jié)構(gòu)一樣，每個數(shù)據(jù)頁都通過一個雙向鏈表來進(jìn)行鏈接。

由于實(shí)際的數(shù)據(jù)頁只能按照一顆B+樹進(jìn)行排序，因此每張表只能擁有一個聚集索引。在多數(shù)情況下，查詢優(yōu)化器傾向于采用聚集索引。因?yàn)榫奂饕軌蛟贐+樹索引的葉子節(jié)點(diǎn)上直接找到數(shù)據(jù)。此外，由于定義了數(shù)據(jù)的邏輯順序，聚集索引能夠特別快地訪問針對范圍值的查詢。查詢優(yōu)化器能夠快速發(fā)現(xiàn)某一段范圍的數(shù)據(jù)頁需要掃描。

特點(diǎn)：

1）自動建立，一個表只有1個。

2）葉子節(jié)點(diǎn)包含所有用戶記錄（包括隱藏列），record_type為0

3）每層節(jié)點(diǎn)都是按照主鍵從小到大排序

4）內(nèi)節(jié)點(diǎn)（非葉子節(jié)點(diǎn)）：存儲主鍵值以及頁號， record_type為1

注意：

 聚集索引的存儲并不是物理上的連續(xù)，而是邏輯上的連續(xù)。這其中有兩點(diǎn)

a.  前面說過的頁通過雙向鏈表鏈接，頁按照主鍵的順序排序，

b.  每個頁中的記錄是通過單向鏈表進(jìn)行維護(hù)的，物理存儲上可以同樣不按照主鍵存儲。

2.2、聯(lián)合索引

對于聯(lián)合索引，葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)。葉子節(jié)點(diǎn)除了包含鍵值以外，每個葉子節(jié)點(diǎn)中的索引行中還包含了一個書簽（bookmark)。該書簽用來告訴InnoDB存儲引擎哪里可以找到與索引相對應(yīng)的行數(shù)據(jù)。由于InnoDB存儲引擎表時索引組織表，因此InnoDB存儲引擎的輔助索引的書簽就是相應(yīng)行數(shù)據(jù)的聚集索引鍵。

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因此每張表上可以有多個輔助索引。當(dāng)通過輔助索引來尋找數(shù)據(jù)時，InnoDB存儲引擎會遍歷輔助索引并通過葉級別的指針獲得指向主鍵索引的主鍵，然后再通過主鍵索引來找到一個完整的行記錄。

如下圖：

特點(diǎn)：

1）手動創(chuàng)建，可以有多個

2）非葉子節(jié)點(diǎn)包含索引列、主鍵列、頁號（page_no）

3）葉子節(jié)點(diǎn)只包含索引列以及記錄主鍵的值

4）每層節(jié)點(diǎn)都是按照索引列的值從小到大排序（索引列值相同時按照主鍵排序）

三、覆蓋索引-covering index

定義：如果一個索引包含（或者說覆蓋）所有查詢字段所需要的值，稱之為覆蓋索引，則只需要掃描索引而不需要回表。

回表：回表：指從輔助索引（也稱二級索引）查到主鍵值后，再去查主鍵索引（一級索引）然后才拿到數(shù)據(jù)。需要掃描兩次 B + 樹 才能拿到數(shù)據(jù)，而覆蓋索引只需要掃描一次

即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。使用覆蓋索引的一個好處是輔助索引不包含整行記錄的所有信息，故其大小要遠(yuǎn)小于聚集索引，因此可以減少大量的IO操作。

簡單來說，覆蓋索引指的就是只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)，無需回表，速度更快。

覆蓋索引是一種非常強(qiáng)大的工具，能大大提高查詢性能，只需要讀取索引而不用讀取數(shù)據(jù)有以下一些優(yōu)點(diǎn)：

1）索引項(xiàng)通常比記錄要小，所以MySQL訪問更少的數(shù)據(jù)

2）索引都按值的大小順序存儲，相對于隨機(jī)訪問記錄，需要更少的I/O

3）覆蓋索引對于InnoDB表尤其有用，因?yàn)镮nnoDB使用聚集索引組織數(shù)據(jù)，如果二級索引中包含查詢所需的數(shù)據(jù)，就不再需要在聚集索引中查找了。

總結(jié)： 就是把單列的非主鍵 索引 修改為多字段的聯(lián)合索引, 在一棵索引數(shù)上 就找到了想要的數(shù)據(jù), 不需要去主鍵索引樹上,再檢索一遍 這個現(xiàn)象,稱之為 索引覆蓋。

小結(jié)：為什么要使用聯(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)合索引時，MySQL 會根據(jù)索引中的字段順序，從左到右依次匹配查詢條件中的字段。如果查詢條件與索引中的最左側(cè)字段相匹配，那么 MySQL 就會使用索引來過濾數(shù)據(jù)，這樣可以提高查詢效率。

最左匹配原則會一直向右匹配，直到遇到范圍查詢（如 >、<）為止。對于 >=、<=、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;

五、常見問題解答

比前提：test表中有c1,c2,c3這列，建立聯(lián)合索引（c1,c2,c3）

1、 索引的命中與查詢sql中字段的順序有關(guān)嗎？

比如 select * from test where c1=2 and c2=5; 與 select * from test where c2=5 and c1=2？

答案：與查詢的字段順序無關(guān)，因?yàn)椴樵儍?yōu)化器會對搜索字段順序跟索引字段順序不一致的sql進(jìn)行優(yōu)化。

2、最左前綴原則如何理解，它的原理是什么？

最左前綴原是指從索引的有效命中來說，并不是觸發(fā)。只要是索引，或者某個聯(lián)合索引的一部分，就會被觸發(fā)，具體命中了那些索引字段，要根據(jù)key_len來做判斷。

原理：

我們從上面的聯(lián)合索引介紹中看到，它的排序方式是：每層節(jié)點(diǎn)先按照索引中的第1列排序。第1列值相等時，按第2列排序。第2列值相等時，按第3列排序 依次類推，所有列都相等時按照主鍵排序。

所以我們可以這樣來分析：

對于聯(lián)合索引中c1字段是放在最前面的，所以c1是完全有序的，但是c1不知情的條件下，對于c2字段就是無序的，沒辦法排序。因此只有當(dāng)c1相同的時候，c2字段的索引排序才是完全有序的。

因此，在聯(lián)合索引中你只有使用以下的規(guī)則的方式查詢才會使用到索引：

c1

c1 ,c2

c1, c2, c3

我們再思考一下，select * from test where c2>=5 and c3=7;  這條sql語句會命中索引么？

分析：這里使用了c2和c3這兩個字段作為查詢條件，但是沒有使用c1字段，因?yàn)樵赾1不知情的條件下，對于c2是無序的。對于c2>=5的條件可能在很多的c1不同中都有符合條件的出現(xiàn)，所以就沒有辦法使用索引，這也是索引實(shí)現(xiàn)的原因，一定要遵循「查找有序，充分利用索引的有序性」。

再比如 select * from test where c1>=5 and c2=9;  

分析：這個查詢語句中，c1列索引會被命中，c2列卻不會，只有c1列值相等時，才會按c2列排序。但是因?yàn)檫@里的c1>=5，那么c1列是不確定的，后面也就無法按照c2來排序，c2不會被命中。

3、前導(dǎo)模糊查詢?yōu)槭裁磿?dǎo)致索引失效？

比如 select  *  from test where c1  like '%d%'; 

字符串的查詢是對字符串里面的字符一個一個的匹配，「若是字符串最左邊為%表示一個不確定的字符串，那么是沒辦法利用到索引的有序性」。

但是若是修改為 ：where  c1 like 'd%'；就可以使用索引，因?yàn)樽钭筮叺淖址谴_定的，這種稱為「匹配列前綴」。

補(bǔ)充說明：實(shí)際業(yè)務(wù)場景中聯(lián)合索引的創(chuàng)建，「我們應(yīng)該把識別度比較高的字段放在前面，提高索引的命中率，充分的利用索引」。

4、 如何解決數(shù)量占表記錄比重較大，查詢優(yōu)化器放棄索引，直接全表掃描？

通過限制查詢條數(shù)來避免索引失效，比如 select * from test where c2=8 limit  10;

5、為什么MySQL的InnoDB引擎采用B+樹而不是B樹？

1）范圍查詢效率高

B+樹在非葉子節(jié)點(diǎn)只存儲鍵值信息，而不存儲數(shù)據(jù)記錄的具體位置，這使得B+樹在進(jìn)行范圍查詢時更加高效。范圍查詢通常涉及到一系列相鄰的鍵值，B+樹的葉子節(jié)點(diǎn)形成了一個有序鏈表，可以很方便地進(jìn)行范圍掃描。 

2）更適合磁盤存儲

B+樹的葉子節(jié)點(diǎn)形成了一個有序鏈表，便于順序I/O，減少磁盤I/O的次數(shù)。這對于數(shù)據(jù)庫來說非常重要，因?yàn)榇疟PI/O是一個相對較慢的操作，通過減少I/O次數(shù)，可以提高查詢性能。

3）更適合范圍查詢

B+樹中葉子節(jié)點(diǎn)使用雙向指針相連接，形成一條雙休有序鏈表，以及在葉子節(jié)點(diǎn)存儲了所有關(guān)鍵字的信息，使得范圍查詢更為高效。而B樹則需要在非葉子節(jié)點(diǎn)中存儲所有關(guān)鍵字的信息，限制了非葉子節(jié)點(diǎn)的容量，不太適合范圍查詢。

4）更適合內(nèi)存的使用

B+樹的內(nèi)部節(jié)點(diǎn)只存儲鍵值信息，而不存儲具體數(shù)據(jù)，這意味著在同樣的內(nèi)存空間下，B+樹可以容納更多的節(jié)點(diǎn)，提高了緩存命中率，減少了內(nèi)存占用?？傮w而言，B+樹更適合數(shù)據(jù)庫索引的應(yīng)用場景，特別是對于范圍查詢和大數(shù)據(jù)集的情況。因此，InnoDB引擎選擇了B+樹作為其索引結(jié)構(gòu)。

6、二級索引與null值的關(guān)系

值為NULL的二級索引記錄被放在了B+樹的最左邊。這是因?yàn)镮nnoDB的設(shè)計(jì)中有這樣的規(guī)定：

We define the SQL null to be the smallest possible value of a field.

翻譯：我們把SQL中的NULL值認(rèn)為是列中最小的值。

 六、如何挑選索引

1、只為用于搜索、排序或分組的列創(chuàng)建索引

2、考慮列的基數(shù)

列基數(shù)：列值不重復(fù)的數(shù)，基數(shù)越大索引效果越好

3、索引列的類型盡量小

1）數(shù)據(jù)類型越小，在查詢時進(jìn)行的比較操作越快

2）數(shù)據(jù)類型越小，數(shù)據(jù)頁內(nèi)就可以放下更多的記錄，從而減小磁盤I/O帶來的性能損耗

4、索引字符串的前綴

5、索引列在比較表達(dá)式中單獨(dú)出現(xiàn)

6、主鍵順序插入（綜合評估頁分裂、回表、索引樹大小）

七、索引總結(jié)：

1、B+樹索引在空間和時間上都有代價，所以必須合理創(chuàng)建索引

2、B+樹索引適用于下邊這些情況：全值匹配

1）匹配左邊的列

2）匹配范圍值

3）精確匹配某一列并范圍匹配另外一列

4）用于排序

5）用于分組

3、在使用索引時需要注意下邊這些事項(xiàng)

1） 只為用于搜索、排序或分組的列創(chuàng)建索引

2） 為列的基數(shù)大的列創(chuàng)建索引

3） 索引列的類型盡量小

4） 可以只對字符串值的前綴建立索引

5） 只有索引列在比較表達(dá)式中單獨(dú)出現(xiàn)才可以適用索引

6） 為了盡可能少的讓聚簇索引發(fā)生頁面分裂和記錄移位的情況，建議讓主鍵擁有auto_increment屬性

7） 定位并刪除表中的重復(fù)和冗余索引

8）盡量使用覆蓋索引進(jìn)行查詢，避免回表帶來的性能損耗。

這里有幾點(diǎn)要注意下：

1）字符串比較大小：逐字比較，費(fèi)時，效率低，不建議。

2）key_len表示索引中使用的字節(jié)數(shù)，查詢中使用的索引的長度（最大可能長度），并非實(shí)際使用長度，理論上長度越短越好。key_len是根據(jù)表定義計(jì)算而得的，不是通過表內(nèi)檢索出的。

參考文獻(xiàn)：

https://www.51cto.com/article/720535.html

https://www.cnblogs.com/hld123/p/14749217.html

五分鐘告訴你什么是MySQL的覆蓋索引_mysql什么是覆蓋索引-CSDN博客

MySQL 覆蓋索引詳解 - 掘金

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

最新評論