索引，可能讓好很多人望而生畏，畢竟每次面試時候 MySQL 的索引一定是必問內(nèi)容，哪怕先撇開面試，就在平常的開發(fā)中，對于 SQL 的優(yōu)化也而是重中之重。

可以毫不夸張的說，系統(tǒng)中 SQL 的好壞，是能直接決定你系統(tǒng)的快慢的。但是在優(yōu)化之前大家是否想過一個問題？那就是：我們優(yōu)化的原則是什么？優(yōu)化SQL的理論基礎(chǔ)是什么？

雖然說實踐出真知，但是我更相信理論是支撐實踐的基礎(chǔ)，因為我們不可能毫無目的的去盲目的實踐，因為這樣往往事倍功半。

所以說了這么多只想告訴大家，在真正的開始索引優(yōu)化之前，我們需要徹底搞明白索引的原理。這樣再談優(yōu)化你將覺得更絲滑~

1、索引的本質(zhì)

索引的本質(zhì)是一種排好序的數(shù)據(jù)結(jié)構(gòu)。這個我相信其實大家并不陌生，因為談到索引很多人自然而然的就會聯(lián)想到字典中的目錄。

沒錯，這樣的類比是很形象的，但是如果再往深處說，恐怕很多小伙伴就有點張口結(jié)舌了，那既然你已經(jīng)知道了索引的本質(zhì)，那么您就已經(jīng)有了看這篇文章的基礎(chǔ)，相信讀文本文的你，一定會對索引的原理有一個全新的了解。

2、索引的分類

在數(shù)據(jù)庫中，索引是分很多種類的（千萬不要狹隘的認為索引只有 B+ 樹，那是因為我們平時使用的基本都是 MySQL）。而不同的種類很顯然是為了應(yīng)付不同的場合，那索引到底有那些種類呢？下面就讓我們來大致的了解下。

2.1、Hash 索引

Hash 索引是比較常見的一種索引，他的單條記錄查詢的效率很高，時間復(fù)雜度為1。但是，Hash索引并不是最常用的數(shù)據(jù)庫索引類型，尤其是我們常用的Mysql Innodb引擎就是不支持hash索引的。主要有以下原因：

Hash索引適合精確查找，但是范圍查找不適合

因為存儲引擎都會為每一行計算一個hash碼，hash碼都是比較小的，并且不同鍵值行的hash碼通常是不一樣的，hash索引中存儲的就是Hash碼，hash 碼彼此之間是沒有規(guī)律的，且 Hash 操作并不能保證順序性，所以值相近的兩個數(shù)據(jù)，Hash值相差很遠，被分到不同的桶中。這就是為什么hash索引只能進行全職匹配的查詢，因為只有這樣，hash碼才能夠匹配到數(shù)據(jù)。

對于 hash 索引，小伙伴們只需要了解到這里就可以了。

2.2、二叉樹

另外，常見的索引使用的數(shù)據(jù)結(jié)構(gòu)是樹結(jié)構(gòu)，首先我們來介紹下最經(jīng)典的二叉樹。

先來介紹下二叉樹的特點：

二叉樹的時間復(fù)雜度為 O(n)

一個節(jié)點只能有兩個子節(jié)點。即度不超過2

左子節(jié)點 小于 本節(jié)點，右子節(jié)點 大于 本節(jié)點

首先來看一下二叉樹的樣子

但是在極端情況下會出現(xiàn)鏈化的情況，即節(jié)點一直在某一邊增加。如下圖

二叉樹中，有一種特殊的結(jié)構(gòu)——平衡二叉樹，平衡二叉樹的特點：

根節(jié)點會隨著數(shù)據(jù)的改變而變更

數(shù)據(jù)量越多，遍歷次數(shù)越多，IO次數(shù)就越多，就越慢（磁盤的IO由樹高決定）

2.4、B樹（二三樹）

了解了二叉樹之后，可以進一步談一下什么是B樹了。B 樹大概是這樣子的：

從B樹的結(jié)構(gòu)圖中可以看到每個節(jié)點中不僅包含數(shù)據(jù)的 key 值，還有 data 值。

而每頁的存儲空間是有限的，如果 data 比較大，會導(dǎo)致每個節(jié)點的 key 存儲的較少，當數(shù)據(jù)量較大的時候，同樣會導(dǎo)致B樹很深，從而增加了磁盤 IO 的次數(shù)，進而影響查詢效率。

好了，說到這里，常見的索引的種類也說完了，上面的內(nèi)容僅僅是作為一個鋪墊，下面我們正式開始 MySQL 的 B+ 樹。

2.5、B+樹

MySQL 中最常用的索引的數(shù)據(jù)結(jié)構(gòu)是 B+ 樹，他有以下特點：

在 B+ 樹中，所有數(shù)據(jù)記錄節(jié)點都是按照鍵值的大小存放在同一層的葉子節(jié)點上，而非葉子結(jié)點只存儲key的信息，這樣可以大大減少每個節(jié)點的存儲的key的數(shù)量，降低B+ 樹的高度

B+ 樹葉子節(jié)點的關(guān)鍵字從小到大有序排列，左邊結(jié)尾數(shù)據(jù)都會保存右邊節(jié)點開始數(shù)據(jù)的指針。

B+ 樹的層級更少：相較于 B 樹 B+ 每個非葉子節(jié)點存儲的關(guān)鍵字數(shù)更多，樹的層級更少所以查詢數(shù)據(jù)更快

B+ 樹查詢速度更穩(wěn)定：B+ 所有關(guān)鍵字數(shù)據(jù)地址都存在葉子節(jié)點上，所以每次查找的次數(shù)都相同所以查詢速度要比B樹更穩(wěn)定;

B+ 樹天然具備排序功能：B+ 樹所有的葉子節(jié)點數(shù)據(jù)構(gòu)成了一個有序鏈表，在查詢大小區(qū)間的數(shù)據(jù)時候更方便，數(shù)據(jù)緊密性很高，緩存的命中率也會比B樹高。

B+ 樹全節(jié)點遍歷更快：B+ 樹遍歷整棵樹只需要遍歷所有的葉子節(jié)點即可，，而不需要像 B 樹一樣需要對每一層進行遍歷，這有利于數(shù)據(jù)庫做全表掃描。

好了說了這么多的 B+ 樹的特點，我們來張圖看看 B+ 樹到底長什么樣子（如果看不懂，也沒有關(guān)系，下文會一步一步解釋說明的）

上面的數(shù)據(jù)頁就是實際存放數(shù)據(jù)頁的地方，且數(shù)據(jù)頁之間是通過雙向鏈表進行連接的，好了到這里我們就將各個索引的類型快速了解了下，下面我們就開始正式B+樹的分析。

3、主鍵目錄

我們將上圖中的數(shù)據(jù)頁拿出來再細化下，就成了下面的這張圖

我們都知道 MySQL 在存儲數(shù)據(jù)的時候是以數(shù)據(jù)頁為最小單位的，且數(shù)據(jù)在數(shù)據(jù)頁中的存儲是連續(xù)的，數(shù)據(jù)頁中的數(shù)據(jù)是按照主鍵排序的（沒有主鍵是由 MySQL自己維護的 ROW_ID 來排序的），數(shù)據(jù)頁和數(shù)據(jù)頁之間是通過雙向鏈表來關(guān)聯(lián)的，數(shù)據(jù)與數(shù)據(jù)時間是通過單向鏈表來關(guān)聯(lián)的。

也就是說有一個在每個數(shù)據(jù)頁中，他必然就有一個最小的主鍵，然后每個數(shù)據(jù)頁的頁號和最小的主鍵會組成一個主鍵目錄（就像上圖中的左邊部分），假設(shè)現(xiàn)在要查找主鍵為 2 的數(shù)據(jù)，通過二分查找法最后確定下主鍵為 2 的記錄在數(shù)據(jù)頁 1 中，此時就會定位到數(shù)據(jù)頁 1 接著再去定位主鍵為 2 的記錄，我們先知道大致的流程，細節(jié)先不要深究，先從宏觀看結(jié)構(gòu)原理，再到微觀看實現(xiàn)原理。

剛剛上面是說的其實可以理解為是主鍵索引，主鍵索引也是最簡單的最基礎(chǔ)的索引。這個時候大家應(yīng)該知道為什么你建立了主鍵查詢就能變快了吧？

4、索引頁

但是現(xiàn)在假設(shè)有很多很多的是數(shù)據(jù)頁，那是不是對應(yīng)的主鍵目錄會很大很大呢？

那假設(shè)有1000萬條記錄、5000萬條記錄呢？是不是就算是二分法查找，其效率也依舊是很低的，所以為了解決這種問題 MySQL 又設(shè)計出了一種新的存儲結(jié)構(gòu)—索引頁。例如有下面這樣情況，

假設(shè)上面的主鍵目錄中的記錄是非常非常多的，此時上面的結(jié)構(gòu)是演變成這樣子的，MySQL 會將里面的記錄拆分到不同的索引頁中，也就是下面這樣子的

索引頁中記錄的是每頁數(shù)據(jù)頁的頁號和該數(shù)據(jù)頁中最小的主鍵的記錄，也就是說最小主鍵和數(shù)據(jù)頁號不是單純的維護在主鍵目錄中了，而是演變成了索引頁，索引頁和數(shù)據(jù)頁類似，一張不夠存就分裂到下一張。

假如現(xiàn)在要查找 id=20 的這條記錄，咦？那我應(yīng)該到哪個索引頁中查找該條記錄呢？所以這個時候肯定是需要去維護索引頁的。

沒錯，MySQL 也是這么設(shè)計的，也就是說 MySQL 同時也設(shè)計出了用于維護索引頁的數(shù)據(jù)結(jié)構(gòu)，其實也還叫索引頁，只不過他們是在不同的層級，類似下面這樣子的：

也就是說維護索引頁的索引頁是在真正存儲記錄和數(shù)據(jù)頁的索引頁的上一層，現(xiàn)在如果你想查找 id=20 的這條記錄，那就是從最上層的索引頁開始查找，通過二分法查找，很快就能夠定位到 id=20 s這條記錄是在索引頁 2 上，然后到就索引頁 2 上面查找，接著就是和之前一樣了（注意，索引頁中的記錄也是通過單向鏈表連接的），根據(jù)各個最小的主鍵能夠定位到 id=20 是在數(shù)據(jù)頁5上，假設(shè)數(shù)據(jù)頁5是這樣子的

那這個時候你是不是能夠想明白數(shù)據(jù)是怎么定位的了呢？

5、索引頁的分層

好，既然你已經(jīng)知道到索引頁太多會往上一層擴散，那現(xiàn)在假設(shè)上一層的索引頁記錄也太多了，那該怎么辦？很簡單，繼續(xù)分裂，再往上一層繼續(xù)，不廢話，我來畫圖幫助大家理解

我看明白了，你看明白了嗎？我們來模擬一個查找的過程，假設(shè)你要查找 37 這條記錄，說實話我根本不知道這條記錄在哪里。好，現(xiàn)在我們就來模擬 MySQL 的查找過程，首先從最頂層的索引頁開始查找，因為 id=37，因此定位到了索引頁16，然后到索引頁 16 中繼續(xù)查找，此時同樣能夠定位到 id=37 在索引頁 3 中，然后繼續(xù)查找，最終能夠定位到數(shù)據(jù)實在數(shù)據(jù)頁 8 中，假設(shè)數(shù)據(jù)頁 8 是這樣子的

是不是很完美？如果非要我把上面的圖畫完整，那....小弟義不容辭（圖太大了，索引頁中數(shù)據(jù)的鏈表結(jié)構(gòu)就不畫出來了）

這個時候機智的你是不是已經(jīng)發(fā)現(xiàn)了什么小秘密？他是不是很像一顆二叉樹？實際上這就是一顆 B+ 樹的結(jié)構(gòu)，這也是數(shù)據(jù)在磁盤中真正存儲的物理結(jié)構(gòu)。B+樹的特性是什么呢？B+樹，也是二叉搜索樹的一種，但是他的數(shù)據(jù)僅僅存儲在葉子節(jié)點（在這里就是數(shù)據(jù)頁），像這種索引頁+數(shù)據(jù)頁組成的組成的B+樹就是聚簇索引(這句話很重要)。

聚簇索引是 MySQL 基于主鍵索引結(jié)構(gòu)創(chuàng)建的

6、非主鍵索引

但是現(xiàn)在問題又來了，既然這里強調(diào)的是主鍵索引，那我們平時開發(fā)中除了主鍵索引其他的索引也用的不少，這時候該怎么辦？假設(shè)你現(xiàn)在對name、age建立索引。現(xiàn)在回顧下主鍵索引，是不是在插入數(shù)據(jù)的時候基于主鍵的順序去維護一個 B+ 樹的？

而實際上非主鍵索引其原理是一樣的，MySQL 都是去維護一顆 B+ 樹，說白了，你建立多少個索引，MySQL 就會幫你維護多少的B+樹（這下是不是也突然想明白了為什么索引不能建立太多了？以前就知道不能建立太多索引，因為索引也會占用空間，實際上這就是根本原因）

假如現(xiàn)在真的對name+age建立索引，那此時是存放的呢？此時 MySQL 根據(jù)會 name+age 維護一個單獨的 B+ 樹結(jié)構(gòu)，數(shù)據(jù)依舊是存放在數(shù)據(jù)頁中的，只不過是原來數(shù)據(jù)中的每條記錄寫的是 id=xx，現(xiàn)在寫的是name=xx，age=xx，id=xx，不管怎么樣，主鍵肯定會存放的，先來張圖壓壓驚

在插入數(shù)據(jù)的時候，MySQL 首先會根據(jù) name 進行排序，如果 name 一樣，就根據(jù)聯(lián)合索引中的 age 去排序，如果還一樣，那么就會根據(jù) 主鍵 字段去排序。插入的原理就是這樣子的。

此時每個數(shù)據(jù)頁中的記錄存放的實際是索引字段和主鍵字段，而其他字段是不存的（為什么不存放？一樣的數(shù)據(jù)到處存放很浪費空間的，也沒必要，所以才會有下面的索引優(yōu)化），至于查找，原理和過程跟聚簇索引一樣，這里就不再贅述，但是，下面說的內(nèi)容卻是至關(guān)重要的：假設(shè)現(xiàn)在執(zhí)行這樣的SQL：

SELECTnameFROMstudentWHEREname='wx'

那么此時的查詢是完美的，使用到了索引且不需要回表

7.回表

是這樣子的，現(xiàn)在要根據(jù) name 查找到該條記錄，且查詢的字段（即 select 后面的查詢字段）也僅僅有 name（只要是在 name,age,id 這三個字段中都可以）這個時候是能夠直接獲取到最終的記錄的

換句話說，因為聯(lián)合索引中的記錄也僅僅有 name，age，id，所以在查詢的如果也僅僅查詢這三個字段，那么在該B+樹中就能夠查詢到想要的結(jié)果了。

那現(xiàn)在假設(shè)查詢的 SQL 是這樣子的（我們假設(shè) student 中還有除了name，age，id 其他的字段 ）

SELECT*FROMstudentWHEREname='wx'

那這下子就完蛋了，因為你現(xiàn)在雖然根據(jù) name 很快的定位到了該條記錄，但是因為 name+age 不是聚簇索引，此時的 B+ 樹的數(shù)據(jù)頁中存放的僅僅是自己關(guān)聯(lián)的索引和主鍵索引字段，并不會存其他的字段，所以這個時候其他的屬性值是獲取不到的，這時候該怎么辦？

這種情況下，MySQL 就需要進行回表查詢了。此時 MySQL 就會根據(jù)定位到的某條記錄中的 id 再次進行聚簇索引查找，也就是說會根據(jù) id 去維護 id 的那么 B+ 樹中查找。因為聚簇索引中數(shù)據(jù)頁記錄的是一條記錄的完整的記錄，這個過程就叫回表。

再強調(diào)下回表的含義：根據(jù)非主鍵索引查詢到的結(jié)果并沒有查找的字段值，此時就需要再次根據(jù)主鍵從聚簇索引的根節(jié)點開始查找，這樣再次查找到的記錄才是完成的。

最后，讓我一起看下 MySQL 對于非主鍵索引的維護過程：

對于非主鍵索引（一般都是聯(lián)合索引），在維護 B+ 樹的時候，會根據(jù)聯(lián)合索引的字段依次去判斷，假設(shè)聯(lián)合索引為：name + address + age，那么 MySQL 在維護該索引的 B+ 樹的時候，首先會根據(jù) name 進行排序，name 相同的話會根據(jù)第二個 address 排序，如果 address 也一樣，那么就會根據(jù) age 去排序，如果 age 也一樣，那么就會根據(jù)主鍵字段值去排序，且對于非主鍵索引，MySQL 在維護 B+ 樹的時候，僅僅是維護索引字段和主鍵字段。

原文標題：MySQL索引原理，一篇從頭到尾講清楚

文章出處：【微信公眾號：數(shù)據(jù)分析與開發(fā)】歡迎添加關(guān)注！文章轉(zhuǎn)載請注明出處。

責(zé)任編輯：haq