MySQL索引背后的數據結構及算法原理（一）

MySQL索引背后的數據結構及算法原理(1)2011年07月11日15:29 來源： 張洋博客 作者：張洋 編輯： 胡銘婭 評論： 0 條 本文Tag： 開源數據庫 MySQL 數據庫

【IT168 技術】 在編程領域有一句人盡皆知的法則“程序 = 數據結構 + 算法”，我個人是不太贊同這句話(因為我覺得程序不僅僅是數據結構加算法)，但是在日常的學習和工作中我確認深深感受到數據結構和算法的重要性，很多東西，如果你愿意稍稍往深處挖一點，那么撲面而來的一定是各種數據結構和算法知識。例如幾乎每個程序員都要打交道的數據庫，如果僅僅是用來存個數據、建建表、建建索引、做做增刪改查，那么也許覺得數據結構和這東西沒什么關系。不過要是哪天心血來潮，想知道的多一點，想研究一下如何優化數據庫，那么一定避免不了研究索引的原理，如果想要真正明白索引是怎么工作的，如何合理的使用索引以優化數據庫，那么就免不了糾結于一堆數據結構與算法之間了。所以，如果說“程序的核心基礎 = 數據結構 + 算法”我是十分贊同的，而一個想成為高手的程序員，一定會去學習程序的核心基礎。

　　好吧，說了這么多，其實我的意思是如果想把數據庫索引學個明明白白，就必須將數據結構和算法作為切入點去學習，遺憾的是我目前還沒有在網上找到從原理層面去介紹數據庫索引的資料(這里僅指在通俗資料領域沒找到，不包括學術論文)，倒不是說沒有高水平的程序員，就只在我們公司范圍內能把這一點講透徹講明白的數據庫大牛也海了去了，只是由于工作的忙碌和個人興趣原因，這些大牛們沒有時間或沒有興趣去寫這方面的文章。由于工作的需要，我這個半桶水的程序員這段時間也草草研究一些關于MySQL數據庫索引的東西，雖然對這方面的理解相比那些大牛差的太遠了，不過這里我還是將這些淺薄的知識總結成文吧。

　　本文以MySQL數據庫為研究對象，討論與數據庫索引相關的一些話題。特別需要說明的是，MySQL支持諸多存儲引擎，而各種存儲引擎對索引的支持也各不相同，因此MySQL數據庫支持多種索引類型，如BTree索引，哈希索引，全文索引等等。為了避免混亂，本文將只關注于BTree索引，因為這是平常使用MySQL時主要打交道的索引，至于哈希索引和全文索引本文暫不討論。

摘 要

　　文章主要內容分為四個部分。

　　第一部分主要從數據結構及算法理論層面討論MySQL數據庫索引的數理基礎。

　　第二部分結合MySQL數據庫中MyISAM和InnoDB數據存儲引擎中索引的架構實現討論聚集索引、非聚集索引及覆蓋索引等話題。

　　第三部分根據上面的理論基礎，討論MySQL中高性能使用索引的策略。

　　數據結構及算法基礎

　　索引的本質

　　MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取數據的數據結構。提取句子主干，就可以得到索引的本質：索引是數據結構。

　　我們知道，數據庫查詢是數據庫的最主要功能之一，例如下面的SQL語句：

　　view sourceprint?SELECT * FROM my_table WHERE col2 = '77'

　　可以從表“my_table”中獲得“col2”為“77”的數據記錄。

　　我們都希望查詢數據的速度能盡可能的快，因此數據庫系統的設計者會從查詢算法的角度進行優化。最基本的查詢算法當然是順序查找(linear search)，遍歷“my_table”然后逐行匹配“col2”的值是否是“77”，這種復雜度為O(n)的算法在數據量很大時顯然是糟糕的，好在計算機科學的發展提供了很多更優秀的查找算法，例如二分查找(binary search)、二叉樹查找(binary tree search)等。如果稍微分析一下會發現，每種查找算法都只能應用于特定的數據結構之上，例如二分查找要求被檢索數據有序，而二叉樹查找只能應用于二叉查找樹上，但是數據本身的組織結構不可能完全滿足各種數據結構(例如，理論上不可能同時將兩列都按順序進行組織)，所以，在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用(指向)數據，這樣就可以在這些數據結構上實現高級查找算法。這種數據結構，就是索引。

　　看一個例子：

　　上圖展示了一種可能的索引方式。左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的物理地址(注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的)。為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在O(log2n)的復雜度內獲取到相應數據。

　　雖然這是一個貨真價實的索引，但是實際的數據庫系統幾乎沒有使用二叉查找樹或其進化品種紅黑樹(red-black tree)實現的，原因會在下文介紹。

　　B-Tree和B+Tree

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

　　B-Tree

　　為了描述B-Tree，首先定義一條數據記錄為一個二元組[key, data]，key為記錄的鍵值，對于不同數據記錄，key是互不相同的;data為數據記錄除key外的數據。那么B-Tree是滿足下列條件的數據結構：

　　d為大于1的一個正整數，稱為B-Tree的度。

　　h為一個正整數，稱為B-Tree的高度。

　　每個非葉子節點由n-1個key和n個指針組成，其中d<=n<=2d。

　　每個葉子節點最少包含一個key和兩個指針，最多包含2d-1個key和2d個指針，葉節點的指針均為null 。

　　所有葉節點具有相同的深度，等于樹高h。

　　key和指針互相間隔，節點兩端是指針。

　　一個節點中的key從左到右非遞減排列。

　　所有節點組成樹結構。

　　每個指針要么為null，要么指向另外一個節點。

　　如果某個指針在節點node最左邊且不為null，則其指向節點的所有key小于v(key1)，其中v(key1)為node的第一個key的值。

　　如果某個指針在節點node最右邊且不為null，則其指向節點的所有key大于v(keym)，其中v(keym)為node的最后一個key的值。

　　如果某個指針在節點node的左右相鄰key分別是keyi和keyi+1且不為null，則其指向節點的所有key小于v(keyi+1)且大于v(keyi)。

　　圖2是一個d=2的B-Tree示意圖。

　　由于B-Tree的特性，在B-Tree中按key檢索數據的算法非常直觀：首先從根節點進行二分查找，如果找到則返回對應節點的data，否則對相應區間的指針指向的節點遞歸進行查找，直到找到節點或找到null指針，前者查找成功，后者查找失敗。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-Tree有一系列有趣的性質，例如一個度為d的B-Tree，設其索引N個key，則其樹高h的上限為logd((N+1)/2)，檢索一個key，其查找節點個數的漸進復雜度為O(logdN)。從這點可以看出，B-Tree是一個非常有效率的索引數據結構。

　　另外，由于插入刪除新的數據記錄會破壞B-Tree的性質，因此在插入刪除時，需要對樹進行一個分裂、合并、轉移等操作以保持B-Tree性質，本文不打算完整討論B-Tree這些內容，因為已經有許多資料詳細說明了B-Tree的數學性質及插入刪除算法，有興趣的朋友可以在本文末的參考文獻一欄找到相應的資料進行閱讀。

　　B+Tree

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

　　與B-Tree相比，B+Tree有以下不同點：

　　每個節點的指針上限為2d而不是2d+1。

　　內節點不存儲data，只存儲key;葉子節點不存儲指針。

　　下圖是一個簡單的B+Tree示意。

　　由于并不是所有節點都具有相同的域，因此B+Tree中葉節點和內節點一般大小不同。這點與B-Tree不同，雖然B-Tree中不同節點存放的key和指針可能數量不一致，但是每個節點的域和上限是一致的，所以在實現中B-Tree往往對每個節點申請同等大小的空間。

　　一般來說，B+Tree比B-Tree更適合實現外存儲索引結構，具體原因與外存儲器原理及計算機存取原理有關，將在下面討論。

　　帶有順序訪問指針的B+Tree

　　一般在數據庫系統或文件系統中使用的B+Tree結構都在經典B+Tree的基礎上進行了優化，增加了順序訪問指針。

　　如圖所示，在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指針，就形成了帶有順序訪問指針的B+Tree。做這個優化的目的是為了提高區間訪問的性能，例如圖中如果要查詢key為從18到49的所有數據記錄，當找到18后，只需順著節點和指針順序遍歷就可以一次性訪問到所有數據節點，極大提到了區間查詢效率。

　　這一節對B-Tree和B+Tree進行了一個簡單的介紹，下一節結合存儲器存取原理介紹為什么目前B+Tree是數據庫系統實現索引的首選數據結構。

　　為什么使用B-Tree(B+Tree)

　　上文說過，紅黑樹等數據結構也可以用來實現索引，但是文件系統及數據庫系統普遍采用B-/+Tree作為索引結構，這一節將結合計算機組成原理相關知識討論B-/+Tree作為索引的理論基礎。

　　一般來說，索引本身也很大，不可能全部存儲在 內存 中，因此索引往往以索引文件的形式存儲的磁盤上。這樣的話，索引查找過程中就要產生磁盤I/O消耗，相對于 內存 存取，I/O存取的消耗要高幾個數量級，所以評價一個數據結構作為索引的優劣最重要的指標就是在查找過程中磁盤I/O操作次數的漸進復雜度。換句話說，索引的結構組織要盡量減少查找過程中磁盤I/O的存取次數。下面先介紹內存和磁盤存取原理，然后再結合這些原理分析B-/+Tree作為索引的效率。

　　主存存取原理

　　目前計算機使用的主存基本都是隨機讀寫存儲器(RAM)，現代RAM的結構和存取原理比較復雜，這里本文拋卻具體差別，抽象出一個十分簡單的存取模型來說明RAM的工作原理。

　　從抽象角度看，主存是一系列的存儲單元組成的矩陣，每個存儲單元存儲固定大小的數據。每個存儲單元有唯一的地址，現代主存的編址規則比較復雜，這里將其簡化成一個二維地址：通過一個行地址和一個列地址可以唯一定位到一個存儲單元。圖5展示了一個4 x 4的主存模型。

　　主存的存取過程如下：

　　當系統需要讀取主存時，則將地址信號放到地址總線上傳給主存，主存讀到地址信號后，解析信號并定位到指定存儲單元，然后將此存儲單元數據放到數據總線上，供其它部件讀取。

　　寫主存的過程類似，系統將要寫入單元地址和數據分別放在地址總線和數據總線上，主存讀取兩個總線的內容，做相應的寫操作。

　　這里可以看出，主存存取的時間僅與存取次數呈線性關系，因為不存在機械操作，兩次存取的數據的“距離”不會對時間有任何影響，例如，先取A0再取A1和先取A0再取D3的時間消耗是一樣的。

　　磁盤存取原理

　　上文說過，索引一般以文件形式存儲在磁盤上，索引檢索需要磁盤I/O操作。與主存不同，磁盤I/O存在機械運動耗費，因此磁盤I/O的時間消耗是巨大的。

　　下圖是磁盤的整體結構示意圖。

　　一個磁盤由大小相同且同軸的圓形 盤片 組成，磁盤可以轉動(各個磁盤必須同步轉動)。在磁盤的一側有磁頭支架，磁頭支架固定了一組磁頭，每個磁頭負責存取一個磁盤的內容。磁頭不能轉動，但是可以沿磁盤半徑方向運動(實際是斜切向運動)，每個磁頭同一時刻也必須是同軸的，即從正上方向下看，所有磁頭任何時候都是重疊的(不過目前已經有多磁頭獨立技術，可不受此限制)。

　　下圖是磁盤結構的示意圖。

盤片 被劃分成一系列同心環，圓心是盤片中心，每個同心環叫做一個磁道，所有半徑相同的磁道組成一個柱面。磁道被沿半徑線劃分成一個個小的段，每個段叫做一個扇區，每個扇區是磁盤的最小存儲單元。為了簡單起見，我們下面假設磁盤只有一個盤片和一個磁頭。

　　當需要從磁盤讀取數據時，系統會將數據邏輯地址傳給磁盤，磁盤的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址，即確定要讀的數據在哪個磁道，哪個扇區。為了讀取這個扇區的數據，需要將磁頭放到這個扇區上方，為了實現這一點，磁頭需要移動對準相應磁道，這個過程叫做尋道，所耗費時間叫做尋道時間，然后磁盤旋轉將目標扇區旋轉到磁頭下，這個過程耗費的時間叫做旋轉時間。

　　局部性原理與磁盤預讀

　　由于存儲介質的特性，磁盤本身存取就比主存慢很多，再加上機械運動耗費，磁盤的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁盤I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁盤也會從這個位置開始，順序向后讀取一定長度的數據放入內存。這樣做的理論依據是計算機科學中著名的局部性原理：

　　當一個數據被用到時，其附近的數據也通常會馬上被使用。

　　程序運行期間所需要的數據通常比較集中。

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

　　預讀的長度一般為頁(page)的整倍數。頁是計算機管理存儲器的邏輯塊，硬件及 操作系統 往往將主存和磁盤存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁(在許多 操作系統 中，頁得大小通常為4k)，主存和磁盤以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信號，磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中，然后異常返回，程序繼續運行。

　　B-/+Tree索引的性能分析

　　到這里終于可以分析B-/+Tree索引的性能了。

　　上文說過一般使用磁盤I/O次數評價索引結構的優劣。先從B-Tree分析，根據B-Tree的定義，可知檢索一次最多需要訪問h個節點。數據庫系統的設計者巧妙利用了磁盤預讀原理，將一個節點的大小設為等于一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，在實際實現B-Tree還需要使用如下技巧：

　　每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了一個node只需一次I/O。

　　B-Tree中一次檢索最多需要h-1次I/O(根節點常駐內存)，漸進復雜度為O(h)=O(logdN)。一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小(通常不超過3)。

　　綜上所述，用B-Tree作為索引結構效率是非常高的。

　　而紅黑樹這種結構，h明顯要深的多。由于邏輯上很近的節點(父子)物理上可能很遠，無法利用局部性，所以紅黑樹的I/O漸進復雜度也為O(h)，效率明顯比B-Tree差很多。

　　上文還說過，B+Tree更適合外存索引，原因和內節點出度d有關。從上面分析可以看到，d越大索引的性能越好，而出度的上限取決于節點內key和data的大小：

　　dmax = floor(pagesize / (keysize + datasize + pointsize)) (pagesize – dmax >= pointsize)

　　或

　　dmax = floor(pagesize / (keysize + datasize + pointsize)) - 1 (pagesize – dmax < pointsize)

　　floor表示向下取整。由于B+Tree內節點去掉了data域，因此可以擁有更大的出度，擁有更好的性能。

　　這一章從理論角度討論了與索引相關的數據結構與算法問題，下一章將討論B+Tree是如何具體實現為MySQL中索引，同時將結合MyISAM和InnDB存儲引擎介紹非聚集索引和聚集索引兩種不同的索引實現形式。

MySQL索引背后的數據結構及算法原理（一）