MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理（一）

MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理(1)2011年07月11日15:29 來(lái)源： 張洋博客 作者：張洋 編輯： 胡銘婭 評(píng)論： 0 條 本文Tag： 開(kāi)源數(shù)據(jù)庫(kù) MySQL 數(shù)據(jù)庫(kù)

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

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

　　本文以MySQL數(shù)據(jù)庫(kù)為研究對(duì)象，討論與數(shù)據(jù)庫(kù)索引相關(guān)的一些話題。特別需要說(shuō)明的是，MySQL支持諸多存儲(chǔ)引擎，而各種存儲(chǔ)引擎對(duì)索引的支持也各不相同，因此MySQL數(shù)據(jù)庫(kù)支持多種索引類(lèi)型，如BTree索引，哈希索引，全文索引等等。為了避免混亂，本文將只關(guān)注于BTree索引，因?yàn)檫@是平常使用MySQL時(shí)主要打交道的索引，至于哈希索引和全文索引本文暫不討論。

摘 要

　　文章主要內(nèi)容分為四個(gè)部分。

　　第一部分主要從數(shù)據(jù)結(jié)構(gòu)及算法理論層面討論MySQL數(shù)據(jù)庫(kù)索引的數(shù)理基礎(chǔ)。

　　第二部分結(jié)合MySQL數(shù)據(jù)庫(kù)中MyISAM和InnoDB數(shù)據(jù)存儲(chǔ)引擎中索引的架構(gòu)實(shí)現(xiàn)討論聚集索引、非聚集索引及覆蓋索引等話題。

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

　　數(shù)據(jù)結(jié)構(gòu)及算法基礎(chǔ)

　　索引的本質(zhì)

　　MySQL官方對(duì)索引的定義為：索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。提取句子主干，就可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。

　　我們知道，數(shù)據(jù)庫(kù)查詢(xún)是數(shù)據(jù)庫(kù)的最主要功能之一，例如下面的SQL語(yǔ)句：

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

　　可以從表“my_table”中獲得“col2”為“77”的數(shù)據(jù)記錄。

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

　　看一個(gè)例子：

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

　　雖然這是一個(gè)貨真價(jià)實(shí)的索引，但是實(shí)際的數(shù)據(jù)庫(kù)系統(tǒng)幾乎沒(méi)有使用二叉查找樹(shù)或其進(jìn)化品種紅黑樹(shù)(red-black tree)實(shí)現(xiàn)的，原因會(huì)在下文介紹。

　　B-Tree和B+Tree

　　目前大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)及文件系統(tǒng)都采用B-Tree或其變種B+Tree作為索引結(jié)構(gòu)，在本文的下一節(jié)會(huì)結(jié)合存儲(chǔ)器原理及計(jì)算機(jī)存取原理討論為什么B-Tree和B+Tree在被如此廣泛用于索引，這一節(jié)先單純從數(shù)據(jù)結(jié)構(gòu)角度描述它們。

　　B-Tree

　　為了描述B-Tree，首先定義一條數(shù)據(jù)記錄為一個(gè)二元組[key, data]，key為記錄的鍵值，對(duì)于不同數(shù)據(jù)記錄，key是互不相同的;data為數(shù)據(jù)記錄除key外的數(shù)據(jù)。那么B-Tree是滿(mǎn)足下列條件的數(shù)據(jù)結(jié)構(gòu)：

　　d為大于1的一個(gè)正整數(shù)，稱(chēng)為B-Tree的度。

　　h為一個(gè)正整數(shù)，稱(chēng)為B-Tree的高度。

　　每個(gè)非葉子節(jié)點(diǎn)由n-1個(gè)key和n個(gè)指針組成，其中d<=n<=2d。

　　每個(gè)葉子節(jié)點(diǎn)最少包含一個(gè)key和兩個(gè)指針，最多包含2d-1個(gè)key和2d個(gè)指針，葉節(jié)點(diǎn)的指針均為null 。

　　所有葉節(jié)點(diǎn)具有相同的深度，等于樹(shù)高h(yuǎn)。

　　key和指針互相間隔，節(jié)點(diǎn)兩端是指針。

　　一個(gè)節(jié)點(diǎn)中的key從左到右非遞減排列。

　　所有節(jié)點(diǎn)組成樹(shù)結(jié)構(gòu)。

　　每個(gè)指針要么為null，要么指向另外一個(gè)節(jié)點(diǎn)。

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

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

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

　　圖2是一個(gè)d=2的B-Tree示意圖。

　　由于B-Tree的特性，在B-Tree中按key檢索數(shù)據(jù)的算法非常直觀：首先從根節(jié)點(diǎn)進(jìn)行二分查找，如果找到則返回對(duì)應(yīng)節(jié)點(diǎn)的data，否則對(duì)相應(yīng)區(qū)間的指針指向的節(jié)點(diǎn)遞歸進(jìn)行查找，直到找到節(jié)點(diǎn)或找到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);

　　關(guān)于B-Tree有一系列有趣的性質(zhì)，例如一個(gè)度為d的B-Tree，設(shè)其索引N個(gè)key，則其樹(shù)高h(yuǎn)的上限為logd((N+1)/2)，檢索一個(gè)key，其查找節(jié)點(diǎn)個(gè)數(shù)的漸進(jìn)復(fù)雜度為O(logdN)。從這點(diǎn)可以看出，B-Tree是一個(gè)非常有效率的索引數(shù)據(jù)結(jié)構(gòu)。

　　另外，由于插入刪除新的數(shù)據(jù)記錄會(huì)破壞B-Tree的性質(zhì)，因此在插入刪除時(shí)，需要對(duì)樹(shù)進(jìn)行一個(gè)分裂、合并、轉(zhuǎn)移等操作以保持B-Tree性質(zhì)，本文不打算完整討論B-Tree這些內(nèi)容，因?yàn)橐呀?jīng)有許多資料詳細(xì)說(shuō)明了B-Tree的數(shù)學(xué)性質(zhì)及插入刪除算法，有興趣的朋友可以在本文末的參考文獻(xiàn)一欄找到相應(yīng)的資料進(jìn)行閱讀。

　　B+Tree

　　B-Tree有許多變種，其中最常見(jiàn)的是B+Tree，例如MySQL就普遍使用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)。

　　與B-Tree相比，B+Tree有以下不同點(diǎn)：

　　每個(gè)節(jié)點(diǎn)的指針上限為2d而不是2d+1。

　　內(nèi)節(jié)點(diǎn)不存儲(chǔ)data，只存儲(chǔ)key;葉子節(jié)點(diǎn)不存儲(chǔ)指針。

　　下圖是一個(gè)簡(jiǎn)單的B+Tree示意。

　　由于并不是所有節(jié)點(diǎn)都具有相同的域，因此B+Tree中葉節(jié)點(diǎn)和內(nèi)節(jié)點(diǎn)一般大小不同。這點(diǎn)與B-Tree不同，雖然B-Tree中不同節(jié)點(diǎn)存放的key和指針可能數(shù)量不一致，但是每個(gè)節(jié)點(diǎn)的域和上限是一致的，所以在實(shí)現(xiàn)中B-Tree往往對(duì)每個(gè)節(jié)點(diǎn)申請(qǐng)同等大小的空間。

　　一般來(lái)說(shuō)，B+Tree比B-Tree更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)，具體原因與外存儲(chǔ)器原理及計(jì)算機(jī)存取原理有關(guān)，將在下面討論。

　　帶有順序訪問(wèn)指針的B+Tree

　　一般在數(shù)據(jù)庫(kù)系統(tǒng)或文件系統(tǒng)中使用的B+Tree結(jié)構(gòu)都在經(jīng)典B+Tree的基礎(chǔ)上進(jìn)行了優(yōu)化，增加了順序訪問(wèn)指針。

　　如圖所示，在B+Tree的每個(gè)葉子節(jié)點(diǎn)增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的指針，就形成了帶有順序訪問(wèn)指針的B+Tree。做這個(gè)優(yōu)化的目的是為了提高區(qū)間訪問(wèn)的性能，例如圖中如果要查詢(xún)key為從18到49的所有數(shù)據(jù)記錄，當(dāng)找到18后，只需順著節(jié)點(diǎn)和指針順序遍歷就可以一次性訪問(wèn)到所有數(shù)據(jù)節(jié)點(diǎn)，極大提到了區(qū)間查詢(xún)效率。

　　這一節(jié)對(duì)B-Tree和B+Tree進(jìn)行了一個(gè)簡(jiǎn)單的介紹，下一節(jié)結(jié)合存儲(chǔ)器存取原理介紹為什么目前B+Tree是數(shù)據(jù)庫(kù)系統(tǒng)實(shí)現(xiàn)索引的首選數(shù)據(jù)結(jié)構(gòu)。

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

　　上文說(shuō)過(guò)，紅黑樹(shù)等數(shù)據(jù)結(jié)構(gòu)也可以用來(lái)實(shí)現(xiàn)索引，但是文件系統(tǒng)及數(shù)據(jù)庫(kù)系統(tǒng)普遍采用B-/+Tree作為索引結(jié)構(gòu)，這一節(jié)將結(jié)合計(jì)算機(jī)組成原理相關(guān)知識(shí)討論B-/+Tree作為索引的理論基礎(chǔ)。

　　一般來(lái)說(shuō)，索引本身也很大，不可能全部存儲(chǔ)在 內(nèi)存 中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤(pán)上。這樣的話，索引查找過(guò)程中就要產(chǎn)生磁盤(pán)I/O消耗，相對(duì)于 內(nèi)存 存取，I/O存取的消耗要高幾個(gè)數(shù)量級(jí)，所以評(píng)價(jià)一個(gè)數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過(guò)程中磁盤(pán)I/O操作次數(shù)的漸進(jìn)復(fù)雜度。換句話說(shuō)，索引的結(jié)構(gòu)組織要盡量減少查找過(guò)程中磁盤(pán)I/O的存取次數(shù)。下面先介紹內(nèi)存和磁盤(pán)存取原理，然后再結(jié)合這些原理分析B-/+Tree作為索引的效率。

　　主存存取原理

　　目前計(jì)算機(jī)使用的主存基本都是隨機(jī)讀寫(xiě)存儲(chǔ)器(RAM)，現(xiàn)代RAM的結(jié)構(gòu)和存取原理比較復(fù)雜，這里本文拋卻具體差別，抽象出一個(gè)十分簡(jiǎn)單的存取模型來(lái)說(shuō)明RAM的工作原理。

　　從抽象角度看，主存是一系列的存儲(chǔ)單元組成的矩陣，每個(gè)存儲(chǔ)單元存儲(chǔ)固定大小的數(shù)據(jù)。每個(gè)存儲(chǔ)單元有唯一的地址，現(xiàn)代主存的編址規(guī)則比較復(fù)雜，這里將其簡(jiǎn)化成一個(gè)二維地址：通過(guò)一個(gè)行地址和一個(gè)列地址可以唯一定位到一個(gè)存儲(chǔ)單元。圖5展示了一個(gè)4 x 4的主存模型。

　　主存的存取過(guò)程如下：

　　當(dāng)系統(tǒng)需要讀取主存時(shí)，則將地址信號(hào)放到地址總線上傳給主存，主存讀到地址信號(hào)后，解析信號(hào)并定位到指定存儲(chǔ)單元，然后將此存儲(chǔ)單元數(shù)據(jù)放到數(shù)據(jù)總線上，供其它部件讀取。

　　寫(xiě)主存的過(guò)程類(lèi)似，系統(tǒng)將要寫(xiě)入單元地址和數(shù)據(jù)分別放在地址總線和數(shù)據(jù)總線上，主存讀取兩個(gè)總線的內(nèi)容，做相應(yīng)的寫(xiě)操作。

　　這里可以看出，主存存取的時(shí)間僅與存取次數(shù)呈線性關(guān)系，因?yàn)椴淮嬖跈C(jī)械操作，兩次存取的數(shù)據(jù)的“距離”不會(huì)對(duì)時(shí)間有任何影響，例如，先取A0再取A1和先取A0再取D3的時(shí)間消耗是一樣的。

　　磁盤(pán)存取原理

　　上文說(shuō)過(guò)，索引一般以文件形式存儲(chǔ)在磁盤(pán)上，索引檢索需要磁盤(pán)I/O操作。與主存不同，磁盤(pán)I/O存在機(jī)械運(yùn)動(dòng)耗費(fèi)，因此磁盤(pán)I/O的時(shí)間消耗是巨大的。

　　下圖是磁盤(pán)的整體結(jié)構(gòu)示意圖。

　　一個(gè)磁盤(pán)由大小相同且同軸的圓形 盤(pán)片 組成，磁盤(pán)可以轉(zhuǎn)動(dòng)(各個(gè)磁盤(pán)必須同步轉(zhuǎn)動(dòng))。在磁盤(pán)的一側(cè)有磁頭支架，磁頭支架固定了一組磁頭，每個(gè)磁頭負(fù)責(zé)存取一個(gè)磁盤(pán)的內(nèi)容。磁頭不能轉(zhuǎn)動(dòng)，但是可以沿磁盤(pán)半徑方向運(yùn)動(dòng)(實(shí)際是斜切向運(yùn)動(dòng))，每個(gè)磁頭同一時(shí)刻也必須是同軸的，即從正上方向下看，所有磁頭任何時(shí)候都是重疊的(不過(guò)目前已經(jīng)有多磁頭獨(dú)立技術(shù)，可不受此限制)。

　　下圖是磁盤(pán)結(jié)構(gòu)的示意圖。

盤(pán)片 被劃分成一系列同心環(huán)，圓心是盤(pán)片中心，每個(gè)同心環(huán)叫做一個(gè)磁道，所有半徑相同的磁道組成一個(gè)柱面。磁道被沿半徑線劃分成一個(gè)個(gè)小的段，每個(gè)段叫做一個(gè)扇區(qū)，每個(gè)扇區(qū)是磁盤(pán)的最小存儲(chǔ)單元。為了簡(jiǎn)單起見(jiàn)，我們下面假設(shè)磁盤(pán)只有一個(gè)盤(pán)片和一個(gè)磁頭。

　　當(dāng)需要從磁盤(pán)讀取數(shù)據(jù)時(shí)，系統(tǒng)會(huì)將數(shù)據(jù)邏輯地址傳給磁盤(pán)，磁盤(pán)的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址，即確定要讀的數(shù)據(jù)在哪個(gè)磁道，哪個(gè)扇區(qū)。為了讀取這個(gè)扇區(qū)的數(shù)據(jù)，需要將磁頭放到這個(gè)扇區(qū)上方，為了實(shí)現(xiàn)這一點(diǎn)，磁頭需要移動(dòng)對(duì)準(zhǔn)相應(yīng)磁道，這個(gè)過(guò)程叫做尋道，所耗費(fèi)時(shí)間叫做尋道時(shí)間，然后磁盤(pán)旋轉(zhuǎn)將目標(biāo)扇區(qū)旋轉(zhuǎn)到磁頭下，這個(gè)過(guò)程耗費(fèi)的時(shí)間叫做旋轉(zhuǎn)時(shí)間。

　　局部性原理與磁盤(pán)預(yù)讀

　　由于存儲(chǔ)介質(zhì)的特性，磁盤(pán)本身存取就比主存慢很多，再加上機(jī)械運(yùn)動(dòng)耗費(fèi)，磁盤(pán)的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁盤(pán)I/O。為了達(dá)到這個(gè)目的，磁盤(pán)往往不是嚴(yán)格按需讀取，而是每次都會(huì)預(yù)讀，即使只需要一個(gè)字節(jié)，磁盤(pán)也會(huì)從這個(gè)位置開(kāi)始，順序向后讀取一定長(zhǎng)度的數(shù)據(jù)放入內(nèi)存。這樣做的理論依據(jù)是計(jì)算機(jī)科學(xué)中著名的局部性原理：

　　當(dāng)一個(gè)數(shù)據(jù)被用到時(shí)，其附近的數(shù)據(jù)也通常會(huì)馬上被使用。

　　程序運(yùn)行期間所需要的數(shù)據(jù)通常比較集中。

　　由于磁盤(pán)順序讀取的效率很高(不需要尋道時(shí)間，只需很少的旋轉(zhuǎn)時(shí)間)，因此對(duì)于具有局部性的程序來(lái)說(shuō)，預(yù)讀可以提高I/O效率。

　　預(yù)讀的長(zhǎng)度一般為頁(yè)(page)的整倍數(shù)。頁(yè)是計(jì)算機(jī)管理存儲(chǔ)器的邏輯塊，硬件及 操作系統(tǒng) 往往將主存和磁盤(pán)存儲(chǔ)區(qū)分割為連續(xù)的大小相等的塊，每個(gè)存儲(chǔ)塊稱(chēng)為一頁(yè)(在許多 操作系統(tǒng) 中，頁(yè)得大小通常為4k)，主存和磁盤(pán)以頁(yè)為單位交換數(shù)據(jù)。當(dāng)程序要讀取的數(shù)據(jù)不在主存中時(shí)，會(huì)觸發(fā)一個(gè)缺頁(yè)異常，此時(shí)系統(tǒng)會(huì)向磁盤(pán)發(fā)出讀盤(pán)信號(hào)，磁盤(pán)會(huì)找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁(yè)或幾頁(yè)載入內(nèi)存中，然后異常返回，程序繼續(xù)運(yùn)行。

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

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

　　上文說(shuō)過(guò)一般使用磁盤(pán)I/O次數(shù)評(píng)價(jià)索引結(jié)構(gòu)的優(yōu)劣。先從B-Tree分析，根據(jù)B-Tree的定義，可知檢索一次最多需要訪問(wèn)h個(gè)節(jié)點(diǎn)。數(shù)據(jù)庫(kù)系統(tǒng)的設(shè)計(jì)者巧妙利用了磁盤(pán)預(yù)讀原理，將一個(gè)節(jié)點(diǎn)的大小設(shè)為等于一個(gè)頁(yè)，這樣每個(gè)節(jié)點(diǎn)只需要一次I/O就可以完全載入。為了達(dá)到這個(gè)目的，在實(shí)際實(shí)現(xiàn)B-Tree還需要使用如下技巧：

　　每次新建節(jié)點(diǎn)時(shí)，直接申請(qǐng)一個(gè)頁(yè)的空間，這樣就保證一個(gè)節(jié)點(diǎn)物理上也存儲(chǔ)在一個(gè)頁(yè)里，加之計(jì)算機(jī)存儲(chǔ)分配都是按頁(yè)對(duì)齊的，就實(shí)現(xiàn)了一個(gè)node只需一次I/O。

　　B-Tree中一次檢索最多需要h-1次I/O(根節(jié)點(diǎn)常駐內(nèi)存)，漸進(jìn)復(fù)雜度為O(h)=O(logdN)。一般實(shí)際應(yīng)用中，出度d是非常大的數(shù)字，通常超過(guò)100，因此h非常小(通常不超過(guò)3)。

　　綜上所述，用B-Tree作為索引結(jié)構(gòu)效率是非常高的。

　　而紅黑樹(shù)這種結(jié)構(gòu)，h明顯要深的多。由于邏輯上很近的節(jié)點(diǎn)(父子)物理上可能很遠(yuǎn)，無(wú)法利用局部性，所以紅黑樹(shù)的I/O漸進(jìn)復(fù)雜度也為O(h)，效率明顯比B-Tree差很多。

　　上文還說(shuō)過(guò)，B+Tree更適合外存索引，原因和內(nèi)節(jié)點(diǎn)出度d有關(guān)。從上面分析可以看到，d越大索引的性能越好，而出度的上限取決于節(jié)點(diǎn)內(nèi)key和data的大小：

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

　　或

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

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

　　這一章從理論角度討論了與索引相關(guān)的數(shù)據(jù)結(jié)構(gòu)與算法問(wèn)題，下一章將討論B+Tree是如何具體實(shí)現(xiàn)為MySQL中索引，同時(shí)將結(jié)合MyISAM和InnDB存儲(chǔ)引擎介紹非聚集索引和聚集索引兩種不同的索引實(shí)現(xiàn)形式。

MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理（一）