?有位粉絲面試高開(kāi)的時(shí)候被問(wèn)到，為什么SQL語(yǔ)句命中索引比不命中索引要快？雖然自己也知道答案，但被問(wèn)到的瞬間，就不知道如何組織語(yǔ)言了。今天，我給大家深度分析一下。

1.索引的作用

想象一下，現(xiàn)在有一本包含幾十萬(wàn)字的字典，有幾百頁(yè)厚，同時(shí)里面的字是無(wú)序排列的。如果在不使用目錄的情況下，我們?nèi)绾螐淖值渲姓页鲂枰淖謥?lái)呢？毫無(wú)疑問(wèn)，我們只能一頁(yè)一頁(yè)地翻，顯然，這是一項(xiàng)反人類(lèi)的的工作。

我們必然想的是先看目錄，然后，找到相關(guān)的字或者偏旁，然后，找到對(duì)應(yīng)的頁(yè)碼再去查找想要找的文字，這樣，效率就大大提高了。而事實(shí)上，目錄就是一種索引，我們說(shuō)的數(shù)據(jù)庫(kù)索引思想和目錄的思想一脈相承。

數(shù)據(jù)庫(kù)索引最主要的作用就是幫助我們快速檢索到想要的數(shù)據(jù)，從而不至于每次查詢(xún)都做全局掃描。

假設(shè)不使用任何算法的情況下，我們要查詢(xún)10萬(wàn)條記錄中的某一條，在最壞的情況下需要遍歷10萬(wàn)次。

但如果使用二分查找算法，則只需要進(jìn)行l(wèi)og2 20000次，也就是14.287712次即可。這意味著我們只需對(duì)排序后的值進(jìn)行14次搜索，就可以使用二分查找到想要的唯一值，常見(jiàn)的索引數(shù)據(jù)結(jié)構(gòu)有B樹(shù)和B+樹(shù)。

下面我們，以MySQL的InnoDB引擎為例，分析一下索引的工作原理。

2.索引執(zhí)行原理

我們知道MySQL的InnoDB引擎采用的是B+樹(shù)數(shù)據(jù)結(jié)構(gòu)，當(dāng)我們?nèi)?zhí)行SELECT語(yǔ)句查詢(xún)數(shù)據(jù)的時(shí)候，InnoDB需要從磁盤(pán)上去讀取數(shù)據(jù)，而這個(gè)過(guò)程會(huì)涉及到磁盤(pán) 以及磁盤(pán)的隨機(jī)IO ，我們來(lái)看這么一個(gè)圖：

系統(tǒng)會(huì)把數(shù)據(jù)的邏輯地址傳給磁盤(pán)，磁盤(pán)控制線(xiàn)路按照尋址邏輯把邏輯地址翻譯成物理地址。也就是確定要讀取的數(shù)據(jù)在哪個(gè)磁道、哪個(gè)扇區(qū)。為了讀取這個(gè)扇區(qū)的數(shù)據(jù)，需要把磁頭放在這個(gè)扇區(qū)上面，為了實(shí)現(xiàn)這樣一個(gè)點(diǎn)，磁盤(pán)會(huì)不斷地去旋轉(zhuǎn)。把目標(biāo)扇區(qū)旋轉(zhuǎn)到磁頭下面，使得磁頭能夠去找到對(duì)應(yīng)的磁道。這里還會(huì)涉及到尋道的時(shí)間以及旋轉(zhuǎn)時(shí)間的一個(gè)損耗。很明顯磁盤(pán)IO這個(gè)過(guò)程的性能開(kāi)銷(xiāo)是非常大的，尤其是查詢(xún)的數(shù)據(jù)量比較多的情況下。

所以InnotDB里面，干脆對(duì)存儲(chǔ)在磁盤(pán)上的數(shù)據(jù)建立一個(gè)索引，然后把索引數(shù)據(jù)以及索引列對(duì)應(yīng)的磁盤(pán)地址以B+樹(shù)的方式進(jìn)行存儲(chǔ)。來(lái)看這么一個(gè)圖：

當(dāng)我們需要查找目標(biāo)數(shù)據(jù)的時(shí)候，根據(jù)索引從B+樹(shù)中去查找目標(biāo)數(shù)據(jù)就行了。由于B+樹(shù)的子樹(shù)比較多，所以，只需要較少次數(shù)的磁盤(pán)IO就能夠查找到目標(biāo)數(shù)據(jù)。

至于B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)，在這里就不分析了。大家可以去我的個(gè)人主頁(yè)看往期視頻有講到。

3.索引的弊端

雖然，使用索引能減少磁盤(pán)IO次數(shù)，提高查詢(xún)效率，但是，索引也不能建立太多。如果一個(gè)表中所有字段的索引很大，也會(huì)導(dǎo)致性能 l下降。想象一下，如果一個(gè)索引和一個(gè)表一樣長(zhǎng)，那么它將再次成為一個(gè)需要檢查的開(kāi)銷(xiāo)。這就好比字典的目錄非常詳細(xì)，但是其長(zhǎng)度已經(jīng)和所有的文字一樣長(zhǎng)，這個(gè)時(shí)候目錄本身的效率就大大下降了。

那索引有弊端嗎？肯定是有的，索引可以提高查詢(xún)讀取性能，而它會(huì)將降低寫(xiě)入性能。當(dāng)有索引時(shí)，如果更改一條記錄，或者在數(shù)據(jù)庫(kù)中插入一條新的記錄，它將執(zhí)行兩個(gè)寫(xiě)入操作（一個(gè)操作是寫(xiě)入記錄本身，另一個(gè)操作是將更新索引）。

因此，在定義索引時(shí)，必須牢記以下幾點(diǎn)：

• 索引表中的每個(gè)字段將降低寫(xiě)入性能。
• 建議使用表中的唯一值為字段編制索引。
• 在關(guān)系數(shù)據(jù)庫(kù)中充當(dāng)外鍵的字段必須建立索引，因?yàn)樗鼈冇兄诳缍鄠€(gè)表進(jìn)行復(fù)雜查詢(xún)。
• 索引還使用磁盤(pán)空間，因此在選擇要索引的字段時(shí)要小心。