一文講清，MySQL中的二級(jí)索引

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

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

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

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

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

圖1 二級(jí)索引

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

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

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

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

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

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

索引的利弊

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

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

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

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

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

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

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

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

聯(lián)合索引查詢原理

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

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

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

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

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

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

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

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

圖2?查找到索引頁

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

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

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

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

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