『MySQL』揭開索引神祕面紗

MySQL知識梳理圖，一圖看完整篇文章:

上一篇文章簡單的介紹了MySQL的執行流程，這一篇想詳細介紹一下索引，一直想全面搞懂索引，嘗試寫一篇關於它的博文。

1.索引是什麼？

索引是什麼了，查閱了官方文件。官方文件寫了索引的作用和沒有索引會帶來全表掃描，非常費時間。 Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. 簡單的說索引是提高查詢速度。這個很好理解，就像是以前的英文詞典，找單詞如果沒有前面目錄的話，效率很低，得全文找一遍。

2.索引實現原理

要搞清楚索引的實現原理，先看看索引的底層實現，MySQL索引大部分採用B-Tree實現，B-Tree又有B-樹和B+樹。還有一些使用Hash索引。本文主要介紹B-Tree(Balance Tree)。

2.1 二叉搜尋樹

再說B-Tree之前，先簡單瞭解一下二叉搜尋樹（Binary Search Trees）。

理解二叉搜尋樹，對於後面理解B-和B+樹很有幫助，因為這2種有些特性跟二叉搜尋樹很像。二叉搜尋樹的特點是左孩子的值小於父親節點的值，父親節點的值小於右孩子的值，即按二叉樹的中序遍歷，剛好是一個按小到大排序的。二叉搜尋樹的查詢就可以使用二分查詢，如果要查詢10，因為10比27小，所以往左孩子找，10<14，還在左孩子找。最壞的情況下，查詢的次數等於樹的高度。

2.2 B-樹

通常意義上說B-Tree，一般是指B-樹，也可以叫平衡多路搜尋樹，平衡的意思可以區瞭解一下平衡二叉樹(它是一棵空樹或它的左右兩個子樹的高度差的絕對值不超過1，並且左右兩個子樹都是一棵平衡二叉樹。)，多路的意思就是非葉子節點的孩子至少有2個。 B-Tree的特徵也是非常燒腦，檢視了演算法導論書籍，也是琢磨了很久。下圖為演算法導論書中一張圖，淺陰影部分為查詢字母R時檢查過的結點。

下面演算法導論書中對B-樹的特徵：

1. 每個結點x有如下屬性：
   1. x.n。它表示儲存在 x中的關鍵字的個數；
   2. x.key1,x.key2,...,x.keyn。它們表示x的n個關鍵字，以非降序存放，即x.key1≤x.key2≤...≤x.keyn；
   3. x.leaf。它是一個布林值，如果x是葉結點，它為TRUE；否則為FALSE；
2. x.c1,x.c2,...,x.cn+1。它們是指向自己孩子的指標。如果該結點是葉節點，則沒有這些屬性。
3. 關鍵字x.keyi對儲存在各子樹中的關鍵字範圍進行分割，即滿足：k1≤x.key1≤k2≤x.key2≤...≤x.keyn≤kn+1。其中，ki(i=1,2,....,n+1)表示任意一個儲存在以x.ci為根的子樹中的關鍵字。
4. 每個葉結點具有相同的深度，即葉的高度h。
5. 每個結點所包含的關鍵的個數有上下界。用一個被稱為最小度數的固定整數t(t≥2)來表示這些界：
   1. 下界：除了根結點以外的每個結點必須至少有t−1個關鍵字。因此，除了根結點外的每個內部結點至少有t個孩子。
   2. 上界：每個結點至多包含2t−1個關鍵字。因此，一個內部結點至多可能有2t個孩子。當一個結點恰好有2t−1個關鍵字時，稱該結點為滿的(full)。

是不是還是一頭霧水，接下來根據上圖一一解釋一下這幾個特徵。

第1點是說每一個節點包括的資訊：n表示結點中儲存關鍵字的個數，比如上圖上M的左孩子就存了2個關鍵字，D和H；x.key，說的是具體的關鍵字的資訊，比如D，D實際是有2個部分組成，可以理解為一個map，{key: data}，x.key廣義上就是表示這個map，包括了具體的key和儲存的資料data，通常說是一條記錄；x.leaf是說整個結點是否是葉子結點。

第2點表示如果不是葉子結點，每個結點還有一個屬性，就是指向它n個孩子的指標，比如上圖中的DH結點，有3個孩子，則有3個指標指向自己的孩子。

第3點表示說每個結點的關鍵字按小到大的順序依次排列，同時各個結點之間也滿足上面提到的二叉搜尋樹的特點，左孩子的值<父親節點的值<右孩子的值。

第4點是說每個葉子結點高度一樣，看圖就可以明白，這也是平衡二字的由來。

第5點說的每個結點關鍵字的數量的限制，不可能每個結點可以無限儲存關鍵字。t是最小度數，需要理解這些，可以谷歌一下度數和階數的定義，上圖是4階的B-Tree。上圖中t=2，則每個內部結點可以允許有2、3、4個孩子。孩子數範圍[t, 2t]，每個結點的關鍵字範圍[t-1, 2t-1]。這個要區分。

下面更加形象的給出4階的B-Tree。

由於B-Tree的特性，在B-Tree中按key檢索資料的演算法非常高效：首先從根節點進行二分查詢，如果找到則返回對應節點的data，否則對相應區間的指標指向的節點遞迴進行查詢，直到找到節點或找到null指標，前者查詢成功，後者查詢失敗。

2.3 B+樹

終於寫完了B-樹，B+樹其實是B-樹變種。 與B-樹最大的區別是內部結點不儲存data，只儲存key。如下圖：

一般資料庫系統中使用的B+樹再上圖經典的基礎上再進行了優化，變成了帶順序訪問指標的B+樹， 如下圖。這樣就提高區間訪問的效能，例如如果要查詢key為從18到49的所有資料記錄，當找到18後，只需順著節點和指標順序遍歷就可以一次性訪問到所有資料節點，極大提到了區間查詢效率。

3. 為什麼是B-Tree(B+)來實現資料庫索引

3.1 磁碟存取原理

資料導論書中開頭就是說： B樹是為磁碟或其他直接存取的輔助儲存裝置而設計的一種平衡搜尋樹。上面提到了輔助儲存裝置，那我們就來看看其中原理，到底由來是什麼？ 計算機系統有主存和基於磁碟的輔存，主存通常就是我們說的RAM，也就是記憶體，這裡不展開說它。索引檔案本身很大，一般不會存在記憶體裡，因此索引往往是以檔案的形式儲存在磁碟裡，所以索引檢索需要磁碟I/O操作。下圖為一個典型的磁碟驅動器。

磁碟讀取資料靠的是磁碟的機械運動。每次磁碟讀取的時間有三部分：尋道時間、旋轉延遲、傳輸時間。尋道時間指的是磁臂移動到指定磁軌所需要的時間，主流磁碟一般在5ms以下；旋轉延遲就是我們經常聽說的磁碟轉速，比如一個磁碟7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms；傳輸時間指的是從磁碟讀出或將資料寫入磁碟的時間，一般在零點幾毫秒，相對於前兩個時間可以忽略。那麼訪問一次磁碟讀取資料的時間，即一次磁碟I/O操作的時間約9ms左右，這相對於主存儲存時間50ns高出5個數量級。看著還不錯的，但是一臺500 -MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令，資料庫動輒十萬百萬乃至千萬級資料，每次9毫秒的時間，顯然是個災難。

為了縮短磁碟讀取的時間，計算機做了一些優化：磁碟預讀。磁碟預讀是基於區域性性原理：當一個資料被用到時，其附近的資料也通常會馬上被使用。所以磁碟I/O操作時不光把當前磁碟地址的資料，而是把相鄰的資料也都讀取到記憶體緩衝區內，因為區域性性原理告訴我們，當計算機訪問一個地址的資料的時候，與其相鄰的資料也會很快被訪問到。

預讀的長度一般為頁（page）的整倍數。頁是計算機管理儲存器的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁得大小通常為4k），主存和磁碟以頁為單位交換資料。

說了那麼多，總結一下：

• 檔案很大，不可能全部儲存在記憶體中，故要儲存到磁碟上。
• 索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數，因為每次磁碟I/O消耗時間都是非常多的。
• 區域性性原理與磁碟預讀，預讀的長度一般為頁（page）的整倍數。

3.2 B-/B+的查詢效能

資料庫系統巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。B-樹也利用這一點，每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也儲存在一個頁裡，加之計算機儲存分配都是按頁對齊的，就實現了一次磁碟I/O就讀取了一頁的資料。下面是B-樹的示例圖：

根據B-Tree的定義，可知檢索一次最多需要訪問h個節點（h個樹的高度）。B-Tree中一次檢索最多需要h-1次I/O（根節點常駐記憶體），漸進複雜度為O(h)=O(logdN)。一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小（通常不超過3）。所以B-Tree作為索引效率是非常高，相比平衡二叉樹、紅黑樹要高很多，因為這些樹的h一般都比較深。

下面附一張B+樹的直觀圖:

B+樹比B-樹更加適合作為磁碟的索引資料結構，原因是B+樹的內部結點不儲存data，內部結點的出度d越大，那麼漸進複雜度越小。出度d的上限取決於節點內key和data的大小： dmax=floor(pagesize/(keysize+datasize+pointsize))

一般3層B+樹可以儲存上百萬的資料，也就是讀取上百萬的資料，只需要3次磁碟I/O，可見這效率，大大提升了。如果沒有索引，那每次查詢一次資料項，都需要一次I/O，幾百萬次，可怕。

4. 不同引擎的索引實現原理

4.1 MyISAM索引實現

MyISAM的索引採用B+樹實現，MyISAM的索引和資料時分開的，葉子節點data存取的是資料的地址。如下主鍵索引的示例圖：

由圖可以看出，要根據索引找到資料，先根據索引找到葉子節點，再根據葉子節點找到資料的地址，然後再根據資料地址取出資料。

MyIASM的輔助索引的實現與主鍵索引沒有區別，如下圖：

單獨出來說，是為了待會跟InnoDB作區分。

4.2 InnoDB索引實現

InnoDB，在實際專案接觸是非常多的，索引的實現也是使用B+樹，但是實現原理跟MyISAM不同。 第一個區別是InnoDB的資料檔案本身就是索引檔案。MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。如下圖：

這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，型別為長整形。

第二個區別就是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。如下圖所示：

4.3 意義

瞭解不同的引擎的實現原理，對於我們日常做資料庫的設計是非常有幫助的。

1. InnoDB輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄，從而能夠明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。
2. 不建議用非單調的欄位作為InnoDB的主鍵，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時，資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，所以一般使用自增欄位作為主鍵。

這一篇先到這裡吧，下一篇總結索引的優化策略。

參考:

 1. http://blog.codinglabs.org/articles/theory-of-mysql-index.html
 2. 演算法導論（第三版）
複製程式碼

更多精彩文章，請關注公眾號：「 天澄技術雜談 」