MySQL btree索引概述

MySQL btree索引概述

今天研究下，mysql中的B-tree索引，通過這篇文章你可以瞭解到，mysql中的btree索引的原理，檢索資料的過程，innodb和myisam引擎中btree索引的不同，以及btree索引的好處和限制。

B-Tree 索引是 MySQL 資料庫中使用最為頻繁的索引類型，除了 Archive 儲存引擎之外的其他所有的儲存引擎都支援 B-Tree 索引。不僅僅在 MySQL 中是如此，實際上在其他的很多資料庫管理系統中B-Tree 索引也同樣是作為最主要的索引類型，這主要是因為B-Tree 索引的儲存結構在資料庫的資料檢索中有非常優異的表現，值得注意的是mysql中innodb和myisam引擎中的B-tree索引使用的是B+tree（即每一個葉子節點都包含指向下一個葉子節點的指標，從而方便葉子節點的範圍遍曆，並且除葉子節點外其他節點只儲存索引值和指標）。

一般來說， MySQL 中的 B-Tree 索引的物理檔案大多都是以 B+tree的結構來儲存的，也就是所有實際需要的資料都存放於 Tree 的 Leaf Node，而且到任何一個 Leaf Node 的最短路徑的長度都是完全相同的，可能各種資料庫（或 MySQL 的各種儲存引擎）在存放自己的 B-Tree 索引的時候會對儲存結構稍作改造。如 Innodb 儲存引擎的 B-Tree 索引實際使用的儲存結構實際上是 B+Tree ，也就是在 B-Tree 資料結構的基礎上做了很小的改造，在每一個Leaf Node 上面出了存放索引索引值和主鍵的相關資訊之外，B+Tree還儲存了指向與該 Leaf Node 相鄰的後一個 LeafNode 的指標資訊，這主要是為了加快檢索多個相鄰 Leaf Node 的效率考慮。

一：下面重點講解下在mysql中innodb和myisam的b-tree索引的不同實現原理；

1）MyISAM索引實現

MyISAM引擎使用B+Tree作為索引結構，分葉節點的data域僅僅存放的是指向資料記錄的地址（也叫行指標），在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。

2）InnoDB索引實現

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

前面說過了，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料行記錄的地址（行指標）。但是在innodb引擎中，btree索引分為兩種，1，叢集索引（主鍵索引），2.二級索引，或者說叫輔助索引。InnoDB中的主鍵索引是叢集索引，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的分葉節點data域儲存了完整的資料記錄（整行資料）。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主鍵索引。但是innodb的二級索引，儲存的是索引列值以及指向主鍵的指標，所以我們使用覆蓋索引的做最佳化處理就是針對mysql的innodb的索引而言的。

總結起來就是：

MyISAM引擎中leaf node儲存的內容:

主鍵索引 ：僅僅儲存行指標；

二級索引：儲存的也僅僅是行指標；

InnoDB引擎中leaf node儲存的內容

主鍵索引 ：叢集索引儲存完整的資料（整行資料）

二級索引：儲存索引列值+主鍵資訊

下面這張圖顯示mysql中innodb和myisam引擎的索引實現的原理

二：接下來說下通過btree索引檢索資料的過程：

myisam和innodb引擎都是使用B+tree實現btree索引，檢索資料的過程中，從根節點到子節點，然後找到葉子節點，接著找到葉子節點中儲存的data的過程是一樣的，只不過因為myisam和innodb引擎中的葉子節點中的data中儲存的內容是不一樣的（前文介紹了），所以找到葉子節點中的data之後再找真正資料的過程是不一樣的，然後根據前文介紹的不同儲存引擎中葉子節點data中儲存的不同資料，例如innodb中的主鍵索引葉子節點儲存的是完整資料行，所以根據innodb中的主鍵索引遍曆資料時，找到了葉子節點的data，就可以找到資料，至於myisam中葉子節點data儲存的是行指標，也就是找到葉子節點的data後，再根據行指標去找到真正的資料行。

下面重點去說由根節點找葉子節點中的data域的過程：

為了對比，可以先看下B-tree實現原理：

B+tree實現原理如：

通過兩張圖可以看出來，相對於B-tree來說，B+Tree根節點和子節點只儲存了索引值和指標，所有資料記錄節點都是按照索引值大小順序存放在同一層的葉子節點上，這樣可以大大加大每個節點儲存的key值數量，降低B+Tree的高度，並且B+Tree中的葉子節點比B-tree多儲存了指向下一個葉子節點的指標，這樣更方便葉子節點的範圍遍曆。

每個節點佔用一個磁碟塊的磁碟空間，一個節點上有n個升序排序的關鍵字和（n+1)個指向子樹根節點的指標，這個指標儲存的是子節點所在磁碟塊的地址（注意這裡的n是建立索引的時候，根據資料量計算出來的，如果資料量太大了，三層的可能就滿足不了，就需要四層的B+tree，或者更多層）,然後n個關鍵字劃分成（n+1)個範圍域，然後每個範圍域對應一個指標，來指向子節點，子節點又從新根據關鍵字再次劃分，然後指標指向葉子節點。

針對具體解釋下B+tree索引的實現原理（修改自網路）：

針對，每個節點佔用一個盤塊的磁碟空間，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指標，兩個關鍵詞劃分成的三個範圍域對應三個指標指向的子樹的資料的範圍域。以根節點為例，關鍵字為17和35，P1指標指向的子樹的資料範圍為小於17，P2指標指向的子樹的資料範圍為17~35，P3指標指向的子樹的資料範圍為大於35。

然後針對類比下 where id=29的具體過程：（首先mysql讀取資料是以塊（page）為單位的）。

首先根據根節點找到磁碟塊1，讀入記憶體。【磁碟I/O操作第1次】

比較關鍵字29在區間（17,35），找到磁碟塊1的指標P2。

根據P2指標找到磁碟塊3，讀入記憶體。【磁碟I/O操作第2次】

比較關鍵字29在區間（26,30），找到磁碟塊3的指標P2。

根據P2指標找到磁碟塊8，讀入記憶體。【磁碟I/O操作第3次】

在磁碟塊8中的關鍵字列表中找到關鍵字29。

分析上面過程，發現需要3次磁碟I/O操作，和3次記憶體尋找操作。由於記憶體中的關鍵字是一個有序表結構，可以利用二分法尋找提高效率。而3次磁碟I/O操作是影響整個B-Tree尋找效率的決定因素。B-Tree相對於AVLTree縮減了節點個數，使每次磁碟I/O取到記憶體的資料都發揮了作用，從而提高了查詢效率。

相對於B-tree來說，B+Tree根節點和子節點只儲存了索引值和指標，

查看mysql中的頁的大小：

MySQL [meminfo]> show variables like 'innodb_page_size';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| innodb_page_size | 16384 |

+------------------+-------+

1 row in set (0.00 sec)

InnoDB儲存引擎中頁的大小為16KB，一般表的主鍵類型為INT（佔用4個位元組）或BIGINT（佔用8個位元組），指標類型也一般為4或8個位元組，也就是說一個頁（B+Tree中的一個節點）中大概儲存16KB/(8B+8B)=1K個索引值（因為是估值，為方便計算，這裡的K取值為〖10〗^3）。也就是說一個深度為3的B+Tree索引可以維護10^3 * 10^3 * 10^3 = 10億 條記錄。

實際情況中每個節點可能不能填充滿，因此在資料庫中，B+Tree的高度一般都在2~4層。MySQL的InnoDB儲存引擎在設計時是將根節點常駐記憶體的，也就是說尋找某一索引值的行記錄時最多隻需要1~3次磁碟I/O操作。

三：下面說下mysql中innodb引擎中聚簇表和myisam中非聚簇表的遍曆資料的不同

如（來自網路）：

看上去聚簇索引的效率明顯要低於非聚簇索引，因為每次使用輔助索引檢索都要經過兩次B+樹尋找，這不是多此一舉嗎？聚簇索引的優勢在哪？

1 由於行資料和葉子節點儲存在一起，這樣主鍵和行資料是一起被載入記憶體的，找到葉子節點就可以立刻將行資料返回了，如果按照主鍵Id來組織資料，獲得資料更快。

2 輔助索引使用主鍵作為"指標" 而不是使用行地址值作為指標的好處是，減少了當出現行移動或者資料頁分裂時輔助索引的維護工作，使用主索引值當作指標會讓輔助索引佔用更多的空間，換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指標"，使用聚簇索引可以保證不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響。

四：最後說下mysql中的B+tree索引的好處和限制（摘自高效能mysql第三版）

（一）可以使用的情況：

可以使用btree索引的查詢類型，btree索引使用用於全索引值、索引值範圍、或者鍵首碼尋找，其中鍵首碼尋找只適合用於根據最左首碼的尋找。前面樣本中建立的多列索引對如下類型的查詢有效：

1）全值匹配

全值匹配指的是和索引中的所有列進行匹配，即可用於尋找姓名和出生日期

2）匹配最左首碼

如：只尋找姓，即只使用索引的第一列

3）匹配列首碼

也可以只匹配某一列值的開頭部分，如：匹配以J開頭的姓的人（like 'J%')，這裡也只是使用了索引的第一列，且是第一列的一部分

4）匹配範圍值

如尋找姓在allen和barrymore之間的人，這裡也只使用了索引的第一列

5）精確匹配某一列並範圍匹配另外一列

如尋找所有姓為allen，並且名字字母是K開頭的，即，第一列last_name精確匹配，第二列first_name範圍匹配

6）只訪問索引的查詢

btree通常可以支援只訪問索引的查詢，即查詢只需要訪問索引，而無需訪問資料行，即，這個就是覆蓋索引的概念。需要訪問的資料直接從索引中取得，這個是針對innodb中btree索引而言的。

因為索引樹中的節點是有序的，所以除了按值尋找之外，索引還可以用於查詢中的order by操作，一般來說，如果btree可以按照某種方式尋找的值，那麼也可以按照這種方式用於排序，所以，如果order by子句滿足前面列出的幾種查詢類型，則這個索引也可以滿足對應的排序需求。

(二）下面是關於btree索引的限制：

1）如果不是按照索引的最左���開始尋找的，則無法使用索引(注意，這裡不是指的where條件的順序，即where條件中，不管條件順序如何，只要where中出現的列在多列索引中能夠從最左開始連貫起來就能使用到多列索引)

2）不能跳過索引中的列，如建立了多列索引（姓，名，出生日期）：查詢條件為姓和出生日期，跳過了名字列，這樣，多列索引就只能使用到姓這一列。

3）如果查詢中有某個列的範圍查詢，則其右邊所有列都無法使用索引最佳化查詢，如：where last_name=xxx and first_name like ‘xxx%’ and dob=’xxx’;這樣，first_name列可以使用索引，這列之後的dob列無法使用索引。

總結：mysql中常用的引擎有innodb和myisam,這兩個引擎中建立的預設索引都是B-tree索引，而都是B+tree結構實現的，並且innodb和myisam具體葉子節點儲存的內容有所不同，然後覆蓋索引是針對innodb引擎的索引而言的，因為myisam引擎中b-tree索引的葉子節點儲存的僅僅是行指標。