優化MySQL的理論基礎是什么？

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

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

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

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

1、索引的本質

索引的本質是一種排好序的數據結構。這個我相信其實大家并不陌生，因為談到索引很多人自然而然的就會聯想到字典中的目錄。

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

2、索引的分類

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

2.1、Hash 索引

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

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

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

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

2.2、二叉樹

另外，常見的索引使用的數據結構是樹結構，首先我們來介紹下最經典的二叉樹。

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

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

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

左子節點 小于 本節點，右子節點 大于 本節點

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

但是在極端情況下會出現鏈化的情況，即節點一直在某一邊增加。如下圖

二叉樹中，有一種特殊的結構——平衡二叉樹，平衡二叉樹的特點：

根節點會隨著數據的改變而變更

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

2.4、B樹（二三樹）

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

從B樹的結構圖中可以看到每個節點中不僅包含數據的 key 值，還有 data 值。

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

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

2.5、B+樹

MySQL 中最常用的索引的數據結構是 B+ 樹，他有以下特點：

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

B+ 樹葉子節點的關鍵字從小到大有序排列，左邊結尾數據都會保存右邊節點開始數據的指針。

B+ 樹的層級更少：相較于 B 樹 B+ 每個非葉子節點存儲的關鍵字數更多，樹的層級更少所以查詢數據更快

B+ 樹查詢速度更穩定：B+ 所有關鍵字數據地址都存在葉子節點上，所以每次查找的次數都相同所以查詢速度要比B樹更穩定;

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

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

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

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

3、主鍵目錄

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

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

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

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

4、索引頁

但是現在假設有很多很多的是數據頁，那是不是對應的主鍵目錄會很大很大呢？

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

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

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

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

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

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

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

5、索引頁的分層

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

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

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

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

聚簇索引是 MySQL 基于主鍵索引結構創建的

6、非主鍵索引

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

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

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

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

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

SELECTnameFROMstudentWHEREname='wx'

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

7.回表

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

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

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

SELECT*FROMstudentWHEREname='wx'

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

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

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

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

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

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

文章出處：【微信公眾號：數據分析與開發】歡迎添加關注！文章轉載請注明出處。

責任編輯：haq