主鍵索引是InnoDB存儲(chǔ)引擎默認(rèn)給我們創(chuàng)建的一套索引結(jié)構(gòu)，我們表里的數(shù)據(jù)也是直接放在主鍵索引里，作為葉子節(jié)點(diǎn)的數(shù)據(jù)頁(yè)。

但我們?cè)陂_(kāi)發(fā)的過(guò)程中，往往會(huì)根據(jù)業(yè)務(wù)需要在不同的字段上建立索引，這些索引就是二級(jí)索引，今天我們就給大家講講二級(jí)所有的原理。

比如，你給name字段加了一個(gè)索引，你插入數(shù)據(jù)的時(shí)候，就會(huì)重新搞一棵B+樹(shù)，B+樹(shù)的葉子節(jié)點(diǎn)，也是數(shù)據(jù)頁(yè)，但是這個(gè)數(shù)據(jù)頁(yè)里僅僅放了主鍵字段和name字段。

葉子節(jié)點(diǎn)的數(shù)據(jù)頁(yè)的name值，跟主鍵索引一樣的，都是按照大小排序的。同一個(gè)數(shù)據(jù)頁(yè)里的name字段值都是大于上一個(gè)數(shù)據(jù)頁(yè)里的name字段值。

name字段的B+樹(shù)也會(huì)構(gòu)建多層索引頁(yè)，這個(gè)索引頁(yè)里放的是下一層的頁(yè)號(hào)和最小name字段值。就像這樣：

圖1 二級(jí)索引

假設(shè)你要根據(jù)name字段來(lái)搜索數(shù)據(jù)，比如：select * from user where name=‘xxx'，過(guò)程與主鍵索引一樣的。從name索引的根節(jié)點(diǎn)開(kāi)始找，一層一層的向下找，一直找到葉子節(jié)點(diǎn)，定位到name字段值對(duì)應(yīng)的主鍵值。

但此時(shí)葉子節(jié)點(diǎn)的數(shù)據(jù)頁(yè)沒(méi)有完整所有字段，就需要根據(jù)主鍵到主鍵索引里去查找，從主鍵索引的根節(jié)點(diǎn)一路找到葉子節(jié)點(diǎn)，就可以找到這行數(shù)據(jù)的所有字段了，這個(gè)過(guò)程就叫回表。

二級(jí)索引，可以對(duì)多個(gè)字段建立聯(lián)合索引，比如，name + age + sex

此時(shí)聯(lián)合索引與單個(gè)字段的索引原理是一樣的，只不過(guò)葉子節(jié)點(diǎn)的數(shù)據(jù)頁(yè)里放的是id + name + age + sex，然后默認(rèn)按照name排序，name一樣就按age排序，age一樣就按sex排序。

每個(gè)name + age +sex的索引頁(yè)里，放的就是下層節(jié)點(diǎn)的頁(yè)號(hào)和最小的name + age + sex值。當(dāng)你用name + age + sex搜索的時(shí)候，就會(huì)走name + age + sex聯(lián)合索引這棵樹(shù)，再回表查詢。

以上就是innoDB二級(jí)索引的原理了，有沒(méi)有感覺(jué)也不過(guò)如此？

索引的利弊

隨著我們不停的在表里插入數(shù)據(jù)，就會(huì)不停的在數(shù)據(jù)頁(yè)里插入數(shù)據(jù)，然后一個(gè)數(shù)據(jù)頁(yè)放滿了就會(huì)分裂成多個(gè)數(shù)據(jù)頁(yè)，這個(gè)時(shí)候就需要索引頁(yè)去指向各個(gè)數(shù)據(jù)頁(yè)。

如果數(shù)據(jù)頁(yè)太多了，那么索引頁(yè)里的數(shù)據(jù)頁(yè)指針也就會(huì)太多了，索引頁(yè)也必然會(huì)放滿的，此時(shí)索引頁(yè)也會(huì)分裂成多個(gè)，再形成更上層的索引頁(yè)。

這個(gè)過(guò)程跟主鍵索引是一模一樣的，所以你如果搞懂了主鍵索引，二級(jí)索引也很簡(jiǎn)單的。

索引的好處是顯而易見(jiàn)的，查找數(shù)據(jù)的時(shí)候不需要全表掃描，性能是很高的。

但索引也有其缺點(diǎn)，如果用的不好，反而對(duì)會(huì)有副作用。

首先，要?jiǎng)?chuàng)建索引，就要占用存儲(chǔ)空間。我們每創(chuàng)建一個(gè)索引，MySQL就會(huì)搞出一個(gè)B+樹(shù)，每棵B+樹(shù)都要占用很多的磁盤空間啊，所以搞太多索引，也是很耗費(fèi)磁盤空間的。

其次，你在進(jìn)行增刪改查的時(shí)候，每次都需要維護(hù)各個(gè)索引的數(shù)據(jù)有序性，因?yàn)槊總€(gè)B+樹(shù)都要求頁(yè)內(nèi)是按照值大小來(lái)排序的，頁(yè)之間也是有序的。所以你不停的增刪改查，各個(gè)索引的數(shù)據(jù)頁(yè)要不停的分裂、增加新的索引頁(yè)，如果你一個(gè)表里搞太多索引，增刪改的性能就會(huì)比較差

所以綜合上面兩個(gè)原因，我們不建議給一張表搞太多索引的。

聯(lián)合索引查詢?cè)?/h3>

之所以要講聯(lián)合索引的查詢?cè)?，是想帶著讀者們更清晰的理解索引的工作原理，我們平時(shí)設(shè)計(jì)索引也大多是設(shè)計(jì)的聯(lián)合索引。

假如有一個(gè)索引KEY(class, name, course），對(duì)學(xué)生班級(jí)、姓名、科目名稱建立的聯(lián)合索引。聯(lián)合索引的示意圖如下：

每個(gè)數(shù)據(jù)頁(yè)都包含了聯(lián)合索引的三個(gè)字段值和主鍵值，數(shù)據(jù)頁(yè)內(nèi)部也是按照順序來(lái)排序的。

首先按照班級(jí)值來(lái)排序，如果一樣則按照學(xué)生姓名來(lái)排序，如果一樣，則按照科目名稱來(lái)排序，所以數(shù)據(jù)頁(yè)內(nèi)部都是按照這三個(gè)字的值來(lái)排序的。

數(shù)據(jù)頁(yè)內(nèi)部與數(shù)據(jù)頁(yè)之間也是有序的，數(shù)據(jù)頁(yè)內(nèi)部組成單向鏈表，數(shù)據(jù)頁(yè)之間組成雙向鏈表。

圖中索引頁(yè)分別指向兩個(gè)數(shù)據(jù)頁(yè)，索引頁(yè)放的是數(shù)據(jù)頁(yè)里最小的那個(gè)數(shù)據(jù)值。

假如我們要執(zhí)行語(yǔ)句：select * from student where class='1班‘ and student_name='張強(qiáng)' and course_name='數(shù)學(xué)'。

查詢時(shí)先到索引頁(yè)里去找，索引頁(yè)里有多個(gè)數(shù)據(jù)頁(yè)的最小值記錄，此時(shí)直接在索引頁(yè)里基于二分查找方法來(lái)找就可以了，先根據(jù)班級(jí)名來(lái)找1班這個(gè)值對(duì)應(yīng)的數(shù)據(jù)頁(yè)，直接可以定位到所在的數(shù)據(jù)頁(yè)。

圖2 查找到索引頁(yè)

然后就可以找到索引指向的那個(gè)數(shù)據(jù)頁(yè)就可以了，在數(shù)據(jù)頁(yè)內(nèi)部是一個(gè)單向鏈表， 你也是基于二分查找就可以了，先按1班這個(gè)值查詢，你發(fā)現(xiàn)有幾條數(shù)據(jù)都是1班，然后按照張強(qiáng)這個(gè)學(xué)生姓名查找，發(fā)現(xiàn)也有多條數(shù)據(jù)，接著按照科目名稱來(lái)二分查找。

很快就定位到一條數(shù)據(jù)了，對(duì)應(yīng)的就是圖中的id=127的數(shù)據(jù)。

圖3 查找到數(shù)據(jù)

然后根據(jù)主鍵id=127回表查找完整的字段，在主鍵索引開(kāi)始二分查找迅速定位到各層級(jí)的索引頁(yè)，再逐步向下定位到id=127的那條數(shù)據(jù)，就可以拿到所有字段的值了。

上面的過(guò)程就是聯(lián)合索引的查找過(guò)程。對(duì)于聯(lián)合索引，就是一次安裝各個(gè)字段來(lái)進(jìn)行二分查找，先定位到第一個(gè)字段對(duì)應(yīng)的值在哪個(gè)頁(yè)，如果第一個(gè)字段值一樣，就按第二個(gè)字段值來(lái)查找，以此類推，就找到最終的數(shù)據(jù)了。