mysql索引原理剖析

標籤：訪問   關於   磁碟讀寫   支援   儲存結構   平衡二叉樹   字元   線性   特殊   

一、索引的原理

　　所謂索引，即是快速定位與尋找，那麼索引的結構組織要盡量減少尋找過程中磁碟I/O的存取次數(B+樹相比B樹，其非葉子節點佔用更小的空間，可以有更多非葉子節點存放在再記憶體中，減少大量的IO)

　　1、索引原理

　　2、局部性原理和磁碟預讀

　　局部性原理：

• 當一個資料被用到時，其附近的資料也通常會馬上被使用。
• 程式運行期間所需要的資料通常比較集中。

　　由於磁碟順序讀取的效率很高(不需要尋道時間，只需很少的旋轉時間)，因此對於具有局部性的程式來說，預讀可以提高I/O效率。

　　預讀的長度一般為頁(page)的整倍數。頁是電腦管理儲存空間的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁(在許多作業系統中，頁得大小通常為4k)，主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。

　　資料庫系統巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。

二、索引的資料結構

　　大規模資料存放區中，實現索引查詢這樣一個實際背景下，樹節點儲存的元素數量是有限的（如果元素數量非常多的話，尋找就退化成節點內部的線性尋找了），這樣導致二叉尋找樹結構由於樹的深度過大而造成磁碟I/O讀寫過於頻繁，進而導致查詢效率低下（為什麼會出現這種情況，待會在外部儲存空間-磁碟中有所解釋），那麼如何減少樹的深度（當然是不能減少查詢的資料量），一個基本的想法就是：採用多叉樹結構（由於樹節點元素數量是有限的，自然該節點的子樹數量也就是有限的）。

　　也就是說，因為磁碟的操作費時費資源，如果過於頻繁的多次尋找勢必效率低下。那麼如何提高效率，即如何避免磁碟過於頻繁的多次尋找呢？根據磁碟尋找存取的次數往往由樹的高度所決定，所以，只要我們通過某種較好的樹結構減少樹的結構盡量減少樹的高度，那麼是不是便能有效減少磁碟尋找存取的次數呢？那這種有效樹結構是一種怎樣的樹呢？

　　這樣我們就提出了一個新的尋找樹結構——多路尋找樹。根據平衡二叉樹的啟發，自然就想到平衡多路尋找樹結構，也就是這篇文章所要闡述的第一個主題B~tree，即B樹結構(後面，我們將看到，B樹的各種操作能使B樹保持較低的高度，從而達到有效避免磁碟過於頻繁的尋找存取操作，從而有效提高尋找效率)。

　　1.外存-磁碟

　　電腦存放裝置一般分為兩種：記憶體儲器(main memory)和外儲存空間(external memory)。 記憶體存取速度快，但容量小，價格昂貴，而且不能長期儲存資料(在不通電情況下資料會消失)。

　　外儲存空間—磁碟是一種直接存取的存放裝置(DASD)。它是以存取時間變化不大為特徵的。可以直接存取任何字元組，且容量大、速度較其它外存裝置更快。

　　當磁碟機執行讀/寫功能時。碟片裝在一個主軸上，並繞主軸高速旋轉，當磁軌在讀/寫頭(又叫磁頭) 下通過時，就可以進行資料的讀 / 寫了。

　　磁碟上資料必須用一個三維地址唯一標示：柱面號、盤面號、塊號(磁軌上的盤塊)。

　　讀/寫磁碟上某一指定資料需要下面3個步驟：

　　(1)  首先移動臂根據柱面號使磁頭移動到所需要的柱面上，這一過程被稱為定位或尋找 。

　　(2)  如11.3中所示的6盤組中，所有磁頭都定位到了10個盤面的10條磁軌上(磁頭都是雙向的)。這時根據盤面號來確定指定盤面上的磁軌。

　　(3) 盤面確定以後，碟片開始旋轉，將指定塊號的磁軌段移動至磁頭下。

　　經過上面三個步驟，指定資料的儲存位置就被找到。這時就可以開始讀/寫操作了。

　　訪問某一具體資訊，由3部分時間組成：

　　● 尋找時間(seek time) Ts: 完成上述步驟(1)所需要的時間。這部分時間代價最高，最大可達到0.1s左右。

　　● 等待時間(latency time) Tl: 完成上述步驟(3)所需要的時間。由於碟片繞主軸旋轉速度很快，一般為7200轉/分(電腦硬碟的效能指標之一, 家用的普通硬碟的轉速一般有5400rpm(筆記本)、7200rpm幾種)。因此一般旋轉一圈大約0.0083s。

　　● 傳輸時間(transmission time) Tt: 資料通過系統匯流排傳送到記憶體的時間，一般傳輸一個位元組(byte)大概0.02us=2*10^(-8)s

　　磁碟讀取資料是以盤塊(block)為基本單位的。位於同一盤塊中的所有資料都能被一次性全部讀取出來。而磁碟IO代價主要花費在尋找時間Ts上。因此我們應該盡量將相關資訊存放在同一盤塊，同一磁軌中。或者至少放在同一柱面或相鄰柱面上，以求在讀/寫資訊時盡量減少磁頭來回移動的次數，避免過多的尋找時間Ts。

　　所以，在大規模資料存放區方面，大量資料存放區在外存磁碟中，而在外存磁碟中讀取/寫入塊(block)中某資料時，首先需要定位到磁碟中的某塊，如何有效地尋找磁碟中的資料，需要一種合理高效的外存資料結構，就是下面所要重點闡述的B-tree結構，以及相關的變種結構：B+-tree結構和B*-tree結構。

　　1、B樹

　　B-樹，即為B樹。因為B樹的原英文名稱為B-tree，而國內很多人喜歡把B-tree譯作B-樹，其實，這是個非常不好的直譯，很容易讓人產生誤解。如人們可能會以為B-樹是一種樹，而B樹又是一種一種樹。而事實上是，B-tree就是指的B樹。

　　B 樹是為了磁碟或其它存放裝置而設計的一種多叉（下面你會看到，相對於二叉，B樹每個內結點有多個分支，即多叉）平衡尋找樹。

　　B樹與紅/黑樹狀結構最大的不同在於，B樹的結點可以有許多子女，從幾個到幾千個。那為什麼又說B樹與紅/黑樹狀結構很相似呢?因為與紅/黑樹狀結構一樣，一棵含n個結點的B樹的高度也為O（lgn），但可能比一棵紅/黑樹狀結構的高度小許多，應為它的分支因子比較大。所以，B樹可以在O（logn）時間內，實現各種如插入（insert），刪除（delete）等動態集合操作。

　　如所示，即是一棵B樹，一棵關鍵字為英語中輔音字母的B樹，現在要從樹種尋找字母R（包含n[x]個關鍵字的內結點x，x有n[x]+1]個子女（也就是說，一個內結點x若含有n[x]個關鍵字，那麼x將含有n[x]+1個子女）。所有的葉結點都處於相同的深度，帶陰影的結點為尋找字母R時要檢查的結點）：

　　　　　　

　　相信，從你能輕易的看到，一個內結點x若含有n[x]個關鍵字，那麼x將含有n[x]+1個子女。如含有2個關鍵字D H的內結點有3個子女，而含有3個關鍵字Q T X的內結點有4個子女。

　　B 樹又叫平衡多路尋找樹。

1. 樹中每個結點最多含有m個孩子（m>=2）；
2. 除根結點和葉子結點外，其它每個結點至少有[ceil(m / 2)]個孩子（其中ceil(x)是一個取上限的函數）；
3. 若根結點不是葉子結點，則至少有2個孩子（特殊情況：沒有孩子的根結點，即根結點為葉子結點，整棵樹只有一個根節點）；
4. 所有葉子結點都出現在同一層，葉子結點不包含任何關鍵字資訊
5. 每個非終端結點中包含有n個關鍵字資訊： (n，P0，K1，P1，K2，P2，......，Kn，Pn)。其中：
          a)   Ki (i=1...n)為關鍵字，且關鍵字按順序升序排序K(i-1)< Ki。 
          b)   Pi為指向子樹根的接點，且指標P(i-1)指向子樹種所有結點的關鍵字均小於Ki，但都大於K(i-1)。 
          c)   關鍵字的個數n必須滿足： [ceil(m / 2)-1]<= n <= m-1。

　　B樹中的每個結點根據實際情況可以包含大量的關鍵字資訊和分支(當然是不能超過磁碟塊的大小，根據磁碟驅動(disk drives)的不同，一般塊的大小在1k~4k左右)；這樣樹的深度降低了，這就意味著尋找一個元素只要很少結點從外存磁碟中讀入記憶體，很快訪問到要尋找的資料。

　　　　　　　　

　　檔案尋找的具體過程：

　　為了簡單，這裡用少量資料構造一棵3叉樹的形式，實際應用中的B樹結點中關鍵字很多的。上面的圖中比如根結點，其中17表示一個磁碟檔案的檔案名稱；小紅方塊表示這個17檔案內容在硬碟中的儲存位置；p1表示指向17左子樹的指標。

　　其結構可以簡單定義為：

typedef struct {    /*檔案數*/    int  file_num;    /*檔案名稱(key)*/    char * file_name[max_file_num];    /*指向子節點的指標*/     BTNode * BTptr[max_file_num+1];     /*檔案在硬碟中的儲存位置*/     FILE_HARD_ADDR offset[max_file_num];}BTNode;

View Code

　　假如每個盤塊可以正好存放一個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操作次數將越少，效率也越高。

　　樹的高度：

　　根據上面的例子我們可以看出，對於輔存做IO讀的次數取決於B樹的高度。而B樹的高度由什麼決定的呢？若B樹某一非葉子節點包含N個關鍵字，則此非葉子節點含有N+1個孩子結點，而所有的葉子結點都在第I層，我們可以得出：

1. 因為根至少有兩個孩子，因此第2層至少有兩個結點。
2. 除根和葉子外，其它結點至少有┌m/2┐個孩子，
3. 因此在第3層至少有2*┌m/2┐個結點，
4. 在第4層至少有2*(┌m/2┐^2)個結點，
5. 在第 I 層至少有2*(┌m/2┐^(l-2) )個結點，於是有： N+1 ≥ 2*┌m/2┐I-2；
6. 考慮第L層的結點個數為N+1，那麼2*(┌m/2┐^(l-2)）≤N+1，也就是L層的最少結點數剛好達到N+1個，即： I≤ log┌m/2┐((N+1)/2 )+2；

　　所以當B樹包含N個關鍵字時，B樹的最大高度為l-1（因為計算B樹高度時，葉結點所在層不計算在內），即：l - 1 =  log┌m/2┐((N+1)/2 )+1。

　　樹中每個結點含有最多含有m個孩子，即m滿足：ceil(m/2)<=m<=m。而樹中每個結點含孩子數越少，樹的高度則越大，故如此

　　2、B+樹

　　　　B+-tree：是應檔案系統所需而產生的一種B-tree的變形樹。

　　　　一棵m階的B+樹和m階的B樹的異同點在於：

　　　　　　所有的葉子結點中包含了全部關鍵字的資訊，及指向含有這些關鍵字記錄的指標，且葉子結點本身依關鍵字的大小自小而大的順序連結。 (而B 樹的葉子節點並沒有包括全部需要尋找的資訊)

　　　　　　所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。 (而B 樹的非終節點也包含需要尋找的有效資訊)

　　　　　　

 

　　3、B樹與B+樹區別

　　關鍵字的數量不同；B+樹中分支結點有m個關鍵字，其葉子結點也有m個，其關鍵字只是起到了一個索引的作用，但是B樹雖然也有m個子結點，但是其只擁有m-1個關鍵字。

　　儲存的位置不同；B+樹中的資料都儲存在葉子結點上，也就是其所有葉子結點的資料群組合起來就是完整的資料，但是B樹的資料存放區在每一個結點中，並不僅僅儲存在葉子結點上。

　　分支結點的構造不同；B+樹的分支結點僅僅儲存著關鍵字資訊和兒子的指標（這裡的指標指的是磁碟塊的位移量），也就是說內部結點僅僅包含著索引資訊。

　　查詢不同；B樹在找到具體的數值以後，則結束，而B+樹則需要通過索引找到葉子結點中的資料才結束，也就是說B+樹的搜尋過程中走了一條從根結點到葉子結點的路徑。

三、為什麼Mysql使用B+樹作索引，而不使用B樹？

　　結構上：

• B樹中關鍵字集合分布在整棵樹中，分葉節點中不包含任何關鍵字資訊，而B+樹關鍵字集合分布在葉子結點中，非分葉節點只是葉子結點中關鍵字的索引；
• B樹中任何一個關鍵字只出現在一個結點中，而B+樹中的關鍵字必須出現在分葉節點中，也可能在非葉結點中重複出現；

　　效能上：

• 不同於B樹只適合隨機檢索，B+樹同時支援隨機檢索和順序檢索；
• B+樹的磁碟讀寫代價更低。B+樹的內部結點並沒有指向關鍵字具體資訊的指標，其內部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入記憶體中可以尋找的關鍵字也就越多，相對的，IO讀寫次數也就降低了。而IO讀寫次數是影響索引檢索效率的最大因素。(舉個例子，假設磁碟中的一個盤塊容納16bytes，而一個關鍵字2bytes，一個關鍵字具體資訊指標2bytes。一棵9階B-tree(一個結點最多8個關鍵字)的內部結點需要2個盤快。而B+ 樹內部結點只需要1個盤快。當需要把內部結點讀入記憶體中的時候，B 樹就比B+ 樹多一次盤塊尋找時間(在磁碟中就是碟片旋轉的時間))

• B+樹的查詢效率更加穩定。B樹搜尋有可能會在非葉子結點結束，越靠近根節點的記錄尋找時間越短，只要找到關鍵字即可確定記錄的存在，其效能等價於在關鍵字全集內做一次二分尋找。而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關鍵字的尋找都必須走一條從根節點到分葉節點的路，所有關鍵字的尋找路徑長度相同，導致每一個關鍵字的查詢效率相當。（由於非終結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的尋找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當）
• （資料庫索引採用B+樹的主要原因是，）B-樹在提高了磁碟IO效能的同時並沒有解決元素遍曆的效率低下的問題。B+樹的葉子節點使用指標順序串連在一起，只要遍曆葉子節點就可以實現整棵樹的遍曆。而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹不支援這樣的操作（或者說效率太低）。

　　原因：

　　    （1）B+樹空間利用率更高，可減少I/O次數，

　　　　 一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引尋找過程中就要產生磁碟I/O消耗。而因為B+樹的內部節點只是作為索引使用，而不像B-樹那樣每個節點都需要儲存硬碟指標。
         也就是說：B+樹中每個非分葉節點沒有指向某個關鍵字具體資訊的指標，所以每一個節點可以存放更多的關鍵字數量，即一次性讀入記憶體所需要尋找的關鍵字也就越多，減少了I/O操作。
    　　 e.g.假設磁碟中的一個盤塊容納16bytes，而一個關鍵字2bytes，一個關鍵字具體資訊指標2bytes。一棵9階B-tree(一個結點最多8個關鍵字)的內   部結點需要2個盤快。而B+ 型樹狀結構內部結點只需要1個盤快。當需要把內部結點讀入記憶體中的時候，B 樹就比B+ 型樹狀結構多一次盤塊尋找時間(在磁碟中就是碟片旋轉的時間)。

　　　（2）增刪檔案（節點）時，效率更高，
         因為B+樹的葉子節點包含所有關鍵字，並以有序的鏈表結構儲存，這樣可很好提高增刪效率。
    　　（3）B+樹的查詢效率更加穩定，
    因為B+樹的每次查詢過程中，都需要遍曆從根節點到葉子節點的某條路徑。所有關鍵字的查詢路徑長度相同，導致每一次查詢的效率相當。

　　B+樹還有一個最大的好處，方便掃庫，B樹必須用中序遍曆的方法按序掃庫，而B+樹直接從葉子結點挨個掃一遍就完了，B+樹支援range-query非常方便，而B樹不支援。這是資料庫選用B+樹的最主要原因

　　B-樹和B+樹最重要的一個區別就是B+樹只有分葉節點存放資料，其餘節點用來索引，而B-樹是每個索引節點都會有Data域。這就決定了B+樹更適合用來儲存外部資料，也就是所謂的磁碟資料。

　　從Mysql（Inoodb）的角度來看，B+樹是用來充當索引的，一般來說索引非常大，尤其是關係性資料庫這種資料量大的索引能達到億層級，所以為了減少記憶體的佔用，索引也會被儲存在磁碟上。那麼Mysql如何衡量查詢效率呢？磁碟IO次數，B-樹（B類樹）的特定就是每層節點數目非常多，層數很少，目的就是為了就少磁碟IO次數，當查詢資料的時候，最好的情況就是很快找到目標索引，然後讀取資料，使用B+樹就能很好的完成這個目的，但是B-樹的每個節點都有data域（指標），這無疑增大了節點大小，說白了增加了磁碟IO次數（磁碟IO一次讀出的資料量大小是固定的，單個資料變大，每次讀出的就少，IO次數增多，一次IO多耗時啊！），而B+樹除了葉子節點其它節點並不儲存資料，節點小，磁碟IO次數就少。這是優點之一。

　　另一個優點是什麼，B+樹所有的Data域在葉子節點，一般來說都會進行一個最佳化，就是將所有的葉子節點用指標串起來。這樣遍曆葉子節點就能獲得全部資料，這樣就能進行區間訪問啦。

　　

 

本文主要參考：http://blog.csdn.net/v_JULY_v/article/details/6530142/

mysql索引原理剖析