乾貨：mysql索引的資料結構

EasonTyler發表於2019-04-17
MySql索引資料結構

索引

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取資料的資料結構。

我們知道，資料庫查詢是資料庫的最主要功能之一。我們都希望查詢資料的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行優化。最基本的查詢演算法當然是順序查詢（linear search），這種複雜度為O(n)的演算法在資料量很大時顯然是糟糕的，好在電腦科學的發展提供了很多更優秀的查詢演算法，例如二分查詢（binary search）、二叉樹查詢（binary tree search）等。如果稍微分析一下會發現，每種查詢演算法都只能應用於特定的資料結構之上，例如二分查詢要求被檢索資料有序，而二叉樹查詢只能應用於二叉查詢樹上，但是資料本身的組織結構不可能完全滿足各種資料結構（例如，理論上不可能同時將兩列都按順序進行組織），所以，在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。
看一個例子：

乾貨：mysql索引的資料結構
圖1.png

圖1展示了一種可能的索引方式。左邊是資料表，一共有兩列七條記錄，最左邊的是資料記錄的實體地址（注意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的）。為了加快Col2的查詢，可以維護一個右邊所示的二叉查詢樹，每個節點分別包含索引鍵值和一個指向對應資料記錄實體地址的指標，這樣就可以運用二叉查詢在O(log2n)的複雜度內獲取到相應資料。
雖然這是一個貨真價實的索引，但是實際的資料庫系統幾乎沒有使用二叉查詢樹或其進化品種紅黑樹（red-black tree）實現的，原因會在下文介紹。

B-Tree和B+Tree

目前大部分資料庫系統及檔案系統都採用B-Tree或其變種B+Tree作為索引結構，在本文的下一節會結合儲存器原理及計算機存取原理討論為什麼B-Tree和B+Tree在被如此廣泛用於索引，這一節先單純從資料結構角度描述它們。

B-Tree

是一種多路搜尋樹（並不是二叉的）：
1.定義任意非葉子結點最多隻有M個兒子；且M>2；
2.根結點的兒子數為[2, M]；
3.除根結點以外的非葉子結點的兒子數為[M/2, M]；
4.每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）
5.非葉子結點的關鍵字個數=指向兒子的指標個數-1；
6.非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非葉子結點的指標：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小於K[1]的
子樹，P[M]指向關鍵字大於K[M-1]的子樹，其它P[i]指向關鍵字屬於(K[i-1], K[i])的子樹；
8.所有葉子結點位於同一層；
9.每個k對應一個data。
如：（M=3）相當於一個2–3樹，2–3樹是一個這樣的一棵樹， 它的每個節點要麼有2個孩子和1個資料元素，要麼有3個孩子和2個資料元素，葉子節點沒有孩子，並且有1個或2個資料元素。


乾貨：mysql索引的資料結構

B-樹的搜尋，從根結點開始，對結點內的關鍵字（有序）序列進行二分查詢，如果命中則結束，否則進入查詢關鍵字所屬範圍的兒子結點；重複，直到所對應的兒子指標為空，或已經是葉子結點；B-Tree上查詢演算法的虛擬碼如下：

BTree_Search(node, key) { if(node == null) return null; foreach(node.key) { if(node.key[i] == key) return node.data[i]; if(node.key[i] > key) return BTree_Search(point[i]->node); } return BTree_Search(point[i+1]->node); } data = BTree_Search(root, my_key);

  • B-樹的特性：
    1.關鍵字集合分佈在整顆樹中；
    2.任何一個關鍵字出現且只出現在一個結點中；
    3.搜尋有可能在非葉子結點結束；
    4.其搜尋效能等價於在關鍵字全集內做一次二分查詢；
    5.自動層次控制；

  • B-樹的自控制:
    B樹中每一個內部節點會包含一定數量的鍵值。通常，鍵值的數量被選定在d和2d之間。在實際中，鍵值佔用了節點中大部分的空間。因數2將保證節點可以被拆分或組合。如果一個內部節點有2d個鍵值，那麼新增一個鍵值給此節點的過程，將會拆分2d鍵值為2個d鍵值的節點，並把此鍵值新增給父節點。每一個拆分的節點需要最小數目的鍵值。相似地，如果一個內部節點和他的鄰居兩者都有d個鍵值，那麼將通過它與鄰居的合併來刪除一個鍵值。刪除此鍵值將導致此節點擁有d-1個鍵值;與鄰居的合併則加上d個鍵值，再加上從鄰居節點的父節點移來的一個鍵值。結果為完全填充的2d個鍵值。

  • B-樹的構造過程：
    下面是往B樹中依次插入
    6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4


    乾貨：mysql索引的資料結構
    btreebuild.gif

B+Tree

B-Tree有許多變種，其中最常見的是B+Tree，例如MySQL就普遍使用B+Tree實現其索引結構。

  • 與B-Tree相比，B+Tree有以下不同點：
    1.非葉子結點的子樹指標與關鍵字個數相同；
    2.非葉子結點的子樹指標P[i]，指向關鍵字值屬於[K[i], K[i+1])的子樹（B-樹是開區間）；
    3.為所有葉子結點增加一個鏈指標；
    4.所有關鍵字都在葉子結點出現；
    5.內節點不儲存data，只儲存key
    如：（M=3）


    乾貨：mysql索引的資料結構
    Paste_Image.png

B+的搜尋與B-樹也基本相同，區別是B+樹只有達到葉子結點才命中（B-樹可以在非葉子結點命中），其效能也等價於在關鍵字全集做一次二分查詢；

  • B+的特性：
    1.所有關鍵字都出現在葉子結點的連結串列中（稠密索引），且連結串列中的關鍵字恰好是有序的；
    2.不可能在非葉子結點命中；
    3.非葉子結點相當於是葉子結點的索引（稀疏索引），葉子結點相當於是儲存（關鍵字）資料的資料層；
    4.更適合檔案索引系統；

  • B+樹的構造過程：
    下面是往B+樹中依次插入
    6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4


    乾貨：mysql索引的資料結構
    Bplustreebuild.gif

索引的物理儲存

一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引查詢過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高几個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在查詢過程中磁碟I/O操作次數的漸進複雜度。換句話說，索引的結構組織要儘量減少查詢過程中磁碟I/O的存取次數。

B-tree

乾貨：mysql索引的資料結構
Paste_Image.png

假如每個盤塊可以正好存放一個B樹的結點（正好存放2個檔名）。那麼一個BTNODE結點就代表一個盤塊，而子樹指標就是存放另外一個盤塊的地址。

  • 下面，我們們來模擬下查詢檔案29的過程：
    1.根據根結點指標找到檔案目錄的根磁碟塊1，將其中的資訊匯入記憶體。【磁碟IO操作 1次】
    2.此時記憶體中有兩個檔名17、35和三個儲存其他磁碟頁面地址的資料。根據演算法我們發現：17<29<35，因此我們找到指標p2。
    3.根據p2指標，我們定位到磁碟塊3，並將其中的資訊匯入記憶體。【磁碟IO操作 2次】
    4.此時記憶體中有兩個檔名26，30和三個儲存其他磁碟頁面地址的資料。根據演算法我們發現：26<29<30，因此我們找到指標p2。
    5.根據p2指標，我們定位到磁碟塊8，並將其中的資訊匯入記憶體。【磁碟IO操作 3次】
    6.此時記憶體中有兩個檔名28，29。根據演算法我們查詢到檔名29，並定位了該檔案記憶體的磁碟地址。
    分析上面的過程，發現需要3次磁碟IO操作和3次記憶體查詢操作。關於記憶體中的檔名查詢，由於是一個有序表結構，可以利用折半查詢提高效率。至於IO操作是影響整個B樹查詢效率的決定因素。

當然，如果我們使用平衡二叉樹的磁碟儲存結構來進行查詢，磁碟4次，最多5次，而且檔案越多，B樹比平衡二叉樹所用的磁碟IO操作次數將越少，效率也越高。

B+tree

乾貨：mysql索引的資料結構
Paste_Image.png
  • B+tree的優點：
  1. B+-tree的磁碟讀寫代價更低
    ****B+-tree****的內部結點並沒有指向關鍵字具體資訊的指標。因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查詢的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
    舉個例子，假設磁碟中的一個盤塊容納16bytes，而一個關鍵字2bytes，一個關鍵字具體資訊指標2bytes。一棵9階B-tree(一個結點最多8個關鍵字)的內部結點需要2個盤快。而****B+
    ****樹內部結點只需要1個盤快。當需要把內部結點讀入記憶體中的時候，B 樹就比****B+ ****樹多一次盤塊查詢時間(在磁碟中就是碟片旋轉的時間)。
  2. B+-tree的查詢效率更加穩定
    由於非終結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當。

mysql的兩種儲存引擎的索引儲存機制

MyISAM索引實現

MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是資料記錄的地址。下圖是MyISAM索引的原理圖：


乾貨：mysql索引的資料結構
Paste_Image.png

這裡設表一共有三列，假設我們以Col1為主鍵，則上圖是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如下圖所示：


乾貨：mysql索引的資料結構
Paste_Image.png

同樣也是一顆B+Tree，data域儲存資料記錄的地址。因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。
MyISAM的索引方式也叫做“非聚集”的。

InnoDB索引實現

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的資料檔案本身就是索引檔案。從上文知道，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。


乾貨：mysql索引的資料結構
Paste_Image.png

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

第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，定義在Col3上的一個輔助索引：


乾貨：mysql索引的資料結構
Paste_Image.png

這裡以英文字元的ASCII碼作為比較準則。聚集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

瞭解不同儲存引擎的索引實現方式對於正確使用和優化索引都非常有幫助，例如知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好主意，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。




相關文章