一文講清,MySQL中的二級(jí)索引
主鍵索引是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ù)了。