B樹在資料庫索引中的應用剖析

引言

關於資料庫索引，google一個oracle index，mysql index總 有大量的結果，其中很多的使用方法推薦，**索引之n條經典建議云云。筆者認為，較之借鑑，在搞清楚了自己的需求的基礎上，對備選方案的原理有個儘可能深 入全面的瞭解會更有利於我們的選擇和決策。 因為某種方案或者技術呈現出某種優勢（包括可能沒有被介紹到的一定存在的限制），不是定義出來的，而是因為其實現機制決定的。就像LinkedList和 ArrayList分別適用於什麼應用不是docment裡面定義的，是由其本身的結構決定的。資料庫的索引也是一樣，不是廠商的文件這樣規定，而是其原 理決定的。

本文只是重點介紹資料結構中最經典的樹（B樹）在資料庫索引中最經典的的幾種應用，也會涉及到幾種資料庫中對此支援的細微不同，以期比較完整的講明 白其實現原理。最終會發現這幾種被不同資料庫廠商冠以不同名字東西原理上其實差不多，理論上其實是一個東西。本文只是略微空洞的介紹其實現，不涉及有任何 具體的使用建議。

關鍵字：B樹 資料庫索引  索引組織表（Index-Organized Tables） 聚集索引 非聚集索引 oracle mysql mssql

 

一、關於資料庫索引

資料庫索引的在維基中的定義：A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

這個定義看上去挺長，就是為了對一個相對較大的資料結構訪問快速方便，另外的儲存了一個小的資料結構，依照某待檢索個屬性進行排序並且記錄了該屬性的記錄在大的資料結構中的位置，以便快速的在大的資料結構中檢索定位。如果index被翻譯成目錄可能更能抽象出其最本質的結構和作用。和其他很多電腦科學中的概念一樣，index也是現實事物中的一種結構。由目錄最容易想到的是圖書館的書籍管理，如果沒有個目錄，很難想象要從圖書館的那麼多書架上找到一本書是多麼困難的事情。

當然對映的最好的是小時候厚厚的新華字典前面的目錄，一般有兩種，一種是拼音的，一種好像是筆畫還是所謂的四角號碼的。就是對於字典中資料根據兩種屬性不 同的索引。多出來的那麼幾十頁紙（額外的儲存）的好處就是幫助檢索者快速定位到某個字典中某個字儲存的頁碼。如果沒有這個index，要查詢字典的某個字 就只有來挨著翻頁了(對應資料庫索引的全表掃描full table scan)

（寫完上文想著配個圖，在google圖片搜圖片的時候搜到了這篇文章 ，直接摘錄了，併為作者點個贊。我只是想做個資料庫索引和字典索引的類別，作者居然做到了和B樹索引的類別！）

二、關於B樹索引

資料庫中比較常用的索引結構有B樹、 點陣圖等。其中B樹是幾乎所有資料庫的預設索引結構，也是用的最多的索引結構。

索引的基礎作用是用於查詢，資料結構的查詢演算法中最基本的是順序查詢，即從列表上逐個匹配是否找到關鍵字，其時間複雜度是O(n)，當n比較大的時候是不能承受的。然後電腦科學提供的產找效率比較高的演算法，即基於排序樹的查詢。B樹（其實是B+ 樹）是一種樹資料結構，通常用於資料庫和作業系統的檔案系統中。特點是能夠保持資料穩定有序，其插入與修改擁有較穩定的對數時間複雜度。 B+ 樹的創造者 Rudolf Bayer 沒有解釋B代表什麼。最常見的觀點是B代表平衡(balanced)，因為所有的葉子節點在樹中都在相同的級別上。B也可能代表Bayer，或者是波音（Boeing），因為他曾經工作於波音科學研究實驗室。

A simple B+ tree example linking the keys 1–7 to data values d1-d7. The linked list (red) allows rapid in-order traversal

前面講到，B樹是一棵平衡樹。 採用樹的結構是因為其O(logN)的查詢複雜度，而平衡樹是電腦科學中的一類改進的二叉查詢樹。對一棵查詢樹（search tree）進行查詢/新增/刪除 等動作, 所花的時間與樹的高度h 成比例, 並不與樹的容量 n 成比例。在B樹上不管查詢成功與否，每次查詢都是走了一條從根到葉子結點的路徑。關於資料結構中的樹，二叉樹、平衡樹的結構，遍歷方式、節點查詢方式、節 點的刪除、新增等都是很典型的記憶體，不在此做介紹。B樹檢索的虛擬碼如下：

Function:search(k)
  returntree_search(k,root);
 
Function:tree_search(k,node)
  ifnode isaleaf then
    returnnode;
  switchkdo
  casek<k_0
    returntree_search(k,p_0);
  casek_i≤k<k_{i+1}
    returntree_search(k,p_{i+1});
  casek_d≤k
    returntree_search(k,p_{d+1});

關於B樹的一個性質，一個度為d的B樹，設節點為數N，則其樹高h的上限為 logd((N+1)/2)，檢索一個值，其查詢節點個數的時間複雜度為O(logdN)。這樣使得在B樹中檢索一個節點最多需要h個節點，而資料庫系統 中一般將一個節點的大小設定為一個頁，每個節點一次IO。使B樹的根節點常駐記憶體，則一次檢索最多需要h-1次的I/O即可。 在集中資料庫中採用的B樹結構的索引，除了上面平衡樹的公共特徵外，結合資料庫索引使用的需要，都有如下的結構要求。

1. 內節點不儲存data，只儲存key；葉子節點不儲存指標。 (oracle 中稱為branch blocks 和 leaf blocks ；mssql中稱為intermediate level nodes和leaf nodes)。內節點的作用是導航，葉子節點才是存資料data。不同的索引設計型別，葉節點這個data域儲存的東西會有不同，導致查詢也會不同。在後面會對照著詳細介紹。
2. 在葉子節點上都會有個雙向的指標指向相鄰的葉子節點。提高區間訪問的效能。

通常在B樹上有兩個頭指標，一個指向根節點，另一個指向關鍵字最小的葉子節點。因此可以對B樹進行兩種查詢運算：一種是從最小關鍵字起順序查詢，另一種是從根節點開始，進行隨機查詢。

三、B樹在資料庫索引中的幾種應用

結合資料庫實現對B樹結構的不同應用，主要是葉子節點儲存的內容不同，我把B樹其為兩種：一種是葉節點存完整的行資料，一種是葉節點只是存一個指向實際資料行的指標。根據表資料儲存格式不同，指標又分為物理指標和邏輯指標。這樣B樹的結構被我分成了三類：

• B樹葉節點存完整資料的索引結構
• B樹葉節點存物理指標的索引結構
• B樹葉節點存邏輯指標的索引結構

因為幾種資料庫各自對這這幾種種特徵的索引的術語不同，其實歸類不同。至少命名沒有我這麼不高大上的。為了討論方便，且這樣分了。

B樹葉節點存物理指標的索引結構

這是最普通的一種索引結構。資料插入時時儲存位置是隨機的，主要是資料庫內部塊的空閒情況決定。這種表資料的儲存結構稱為堆表（heap table）（本來heap這個概念就是生成時候分配的）。在堆表（heap table）中記錄是無序的，插入速度會比較快。但是查詢一個資料會比較麻煩，需要掃描整個堆表才可以。如下圖表T是最示意的一個簡單表，表上有三列。前 面的十六進位制數字僅僅是示意這一行的儲存位置。

想想我們需要在表中找出c2=43的行，我們需要從第一行開始，逐行的檢查每一行上c2的取值。直到找到第三行找到了。但還是需要掃描接下來的行，因為你不能保證下面在你掃描的前方還有沒有另外一個或者多個c2=43的行存在。即幾乎都要進行全表的掃描，查詢一條記錄的時間複雜度是O(N)，N為記錄行數。想想一個資料量比較大的表，這樣的方式幾乎是不可以接受的。

於是就有了索引的概念，即另外開闢一個儲存結構，按照某個列進行排序，並記錄該列每個行每個取值的行在表中的位置。這恐怕是索引最正宗最純真的意思 了吧。回到我們字典的類比上，想我們的字典目錄個某個筆畫得到某個字在什麼位置，靠的是在目錄中存的頁碼這樣一個到實際資料的指標。

因為前面提到的B樹的優點，幾乎所有的這類索引都採用B樹結構。在葉節點上，葉節點的key是儲存索引列在每行上的值，而對應的data域儲存了該 行的一個引用。也可以理解為指向實際儲存資料的指標。 如圖中在C2上建立索引，記錄按照C2的屬性構建B樹，並對應的一個指標記錄該行資料的儲存位置。則查詢就是最簡單的排序樹的檢索，前面也介紹過，其時間 複雜度為O(logdN)，儘管右下角的堆表上的資料是無序的。同樣要找到c2=43的記錄行，從索引樹上只要經過三個節點即可以找到，並通過指標找到對應的行。

這是幾種資料庫中最典型的索引，結構也基本相同。 oracle中直接根據儲存結構把這種索引稱為B樹索引，索引葉節點儲存(key: rowid)，其中rowid標識了該行的物理儲存位置。

The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked.

對於Mssql來說，當沒有建立聚集索引的時候，即表示表是以堆的形式(heap structure)儲存。同樣葉節點也是儲存（key: RID），其中RID指定資料儲存物理位置的的行和頁。

If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

在Mysql中索引結構和表的儲存方式都是和儲存引擎相關，不同的儲存引擎實現不同。兩種比較常用的儲存引擎中, myisam表上的資料總是按照堆的結果儲存的，在myisam上的索引也都是採用上圖類似的索引結構。詳細點說myisam上的主鍵索引、唯一索引、輔 助索引都是這種結構。不同的是，主鍵索引要求選擇的索引列是表的主鍵，唯一索引要求索引列保持取值的唯一性約束，而輔助索引沒有這些要求。

B樹葉節點存資料的索引結構

B樹構造的另外一種索引，與其說是一種索引方式，倒不如說是以一種表資料的儲存方式（oracle中就稱之為索引組織表（Index-Organized Tables））。 這中結構的一個特點是B樹的葉節點中和索引鍵對應儲存的是實際的資料行。即（Key: Row）的結構。即在葉節點上完整的儲存了資料行。 如圖，在C3上構建索引，則整個表中的資料按照C3的順序來儲存。第一個葉節點上儲存了C3=5和C3=25的完整的行，同時整個表按照列C3取值的順序 在儲存。

 

在oracle中，並不認為該種方式的儲存是索引，而是更形象的稱為索引組織表（Index-Organized Tables）；在mssql中，這種結構正是其所謂的聚集索引（Clustered Index）；在mysql中，因為索引屬於儲存引擎級別的概念，不同儲存引擎對索引的實現方式是不同的，在常用的innodb和myisam儲存引擎中，只有innodb是支援這種結構的，稱之為（clustered index）。 即便三種資料庫分別支援這種索引結構，其相互之間還是有些比較tricky的差別，這正是想對照著強調的。

在Oracle的索引組織表（Index-Organized Tables）根據主鍵排序後的順序進行排列的，即索引的列必須是表的主鍵列，在建表的同時要指定主鍵約束，可以是單欄位主鍵，也可以是複合主鍵約束。建立索引組織表時，必須要設定主鍵，否則報錯。 引用來自Oracle Database Concepts 對Index-Organized Tables的描述：

An index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values.

在mysql的innodb的儲存引擎中，InnoDB的資料檔案本身要按主鍵聚集，按主鍵順序儲存。所以InnoDB要求表必須有主鍵，如果沒有 顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為 主鍵，這個欄位長度為6個位元組，型別為長整形。

引用來自MySQL Manual關於clustered index的描述

• If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
• If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULLcolumns as the primary key and InnoDB uses it as the clustered index.
• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

而在mssql中，關於該索引列的要求就沒有那麼高，並未要求改索引列必須是主鍵，也不要求該列上必須有唯一性約束。如果表上沒有建聚集索引，當在 表上建立主鍵的時候，mssql會自動在該主鍵列上建立一個聚集索引。當在沒有唯一約束的列上建立聚集索引是，mssql會自動的在重複的鍵值上新增一個 4 byte的uniqueifier使得該值唯一，這個對使用者是透明的。

來自technet.microsoft 的Create Clustered Indexes

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

B樹葉節點存邏輯指標的索引結構

根據前面的描述，當表中資料按照傳統堆方式組織的情況時，構造索引（非聚集）的B樹的葉節點上上儲存（key: rowid）這樣的結構，即關聯到資料行的物理指標；當資料本身是按照B樹儲存的時候，資料庫認為有了邏輯標示一個行的標籤，則葉節點上儲存（key:clusterKey）這樣的結構，即關聯到對應的聚集索引鍵，聚集索引鍵扮演了一個邏輯指標。 如圖，前面在C3上建立了聚集索引，C1上建立一個非聚集的索引，則在葉節點處儲存了每行C3取值對應的聚集索引的鍵。如第三個葉子節點，C1對應的值為Inter，而對應的聚集索引在該行的值為151. 即通過151這個cluster key 來關聯到實際資料行，資料行在另外一個B樹上儲存著。

因為幾種資料庫對於聚集索引的要求有細微差別，在存在聚集索引情況下的非聚集索引也相應的有所不同。 在oracle中，該索引稱為輔助索引（Secondary Indexes on Index-Organized Tables）。因為oracle的索引組織表（Index-Organized Tables）的索引鍵必須是主鍵，則該輔助索引相應管理的是一個代表了主鍵的邏輯rowid。

As explained in “Rowid Data Types”, Oracle Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a base64-encoded representation of the table primary key. The logical rowid length depends on the primary key length.

在mysql的Innodb中，和oracle幾乎完全相同，這種索引也稱為輔助索引（ secondary indexes）。因為其聚集索引列也是要求必須是主鍵，相應輔助索引關聯的也是對應的主鍵。

引用來自MySQL Manual關於clustered index的描述

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

在mssql中，這種索引稱為非聚集索引(Nonclustered Index)。 在B樹的頁節點上儲存索引列和聚集索引對應聚集索引鍵（clustered index key）。上面討論聚集索引的時候說到過，mssql的聚集索引的列不要求唯一性，也不要求是主鍵。但是非聚集索引中為了能通過聚集索引鍵唯一定位到一行 資料，在重複的聚集索引鍵上會新增一個唯一標示來使得其唯一。

 

 

如上圖在C3上有重複的值，按照mysql和oracle的要求，在該列上是不能建立聚集索引的，但是在mssql中，在該列上可以建聚集索引，在另外一列上的非聚集索引和聚集索引的關聯如圖。

 

 

保證C1上建立的非聚集索引的每一行資料都能通過聚集索引key唯一關聯到聚集索引上某資料行上。 來自msdn的 no-clustered index

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

四、總結

整理一個列表更直觀的表達其對照，會發現大部分都是相同的，除了術語上，或者某些約定限制的程度上。因為原理是一樣的。因為結構相同，造成使用也是完全相同。如

• 根據聚集索引的檢索方式；
• 有聚集索引的非聚集索引檢索方式；
• 沒有聚集索引的非聚集索引檢索方式

 

資料庫(儲存引擎)/比較專案		oracle	mssql	mysql(innodb)	mysql(myisam)
表資料B樹儲存方式（聚集索引存在）	支援表資料B樹儲存	Yes	Yes	Yes	NO
	表資料B樹儲存名稱	索引組織表（Index-Organized Tables）	聚集索引Clustered Indexes	聚集索引(主鍵索引)clustered index	不支援
	聚集索引鍵要求	必須是主鍵	沒有主鍵要求，也沒有唯一性要求	必須是主鍵	不支援
	B樹葉節點結構	(Key:ROW) 索引key和整行資料	(Key:ROW) 索引key和整行資料	(Key:ROW) 索引key和整行資料	不支援
	根據聚集索引訪問資料行	聚集索引B樹上檢索聚集索引鍵，找到索引葉節點即訪問到正行資料	聚集索引B樹上檢索聚集索引鍵，找到索引葉節點即訪問到正行資料	聚集索引B樹上檢索聚集索引鍵，找到索引葉節點即訪問到正行資料	不支援
	索引（非聚集）名稱	輔助索引	非聚集索引	輔助索引	不支援
	索引（非聚集）B樹葉節點結構	(Key：ClusterKey) 索引（非聚集）鍵和聚集索引鍵的對應關係。	(Key：ClusterKey) 索引（非聚集）鍵和，聚集索引鍵的對應關係。	(Key：ClusterKey) 索引（非聚集）鍵和，聚集索引鍵的對應關係。	不支援
	根據索引（非聚集）訪問資料行	1.檢索索引（非聚集）B樹定位到索引行所在葉節點，得到索引鍵對應的聚集索引鍵 2. 在聚集索引B樹上檢索聚集索引鍵，即訪問到資料行	1.檢索索引（非聚集）B樹定位到索引行所在葉節點，得到索引鍵對應的聚集索引鍵 2. 在聚集索引B樹上檢索聚集索引鍵，即訪問到資料行	1.檢索索引（非聚集）B樹定位到索引行所在葉節點，得到索引鍵對應的聚集索引鍵 2. 在聚集索引B樹上檢索聚集索引鍵，即訪問到資料行	不支援
表資料堆儲存方式heap structure （聚集索引不存在)	索引（非聚集）名稱	B樹索引	非聚集索引	不支援	主鍵索引、唯一索引、輔助索引
	索引（非聚集）B樹葉節點結構	(Key：ROWID) 索引（非聚集）鍵和行儲存物理位置	(Key：ROWID) 索引（非聚集）鍵和行儲存物理位置	不支援	(Key：ROWID) 索引（非聚集）鍵和行儲存物理位置
	根據索引（非聚集）訪問資料行	1. 從索引（非聚集）定位到索引行所在葉節點，即得到資料行的物理儲存位置 2.直接根據物理儲存位置從堆上訪問資料行	1. 從索引（非聚集）定位到索引行所在葉節點，即得到資料行的物理儲存位置 2.直接根據物理儲存位置從堆上訪問資料行	不支援	1. 從索引（非聚集）定位到索引行所在葉節點，即得到資料行的物理儲存位置 2.直接根據物理儲存位置從堆上訪問資料行

 

再根據原理多分析一點，不是使用建議，只是這種結構提示給我們的資訊。只說it is ,不說you should。

知道了聚集索引實現原理後，應該能理解為什麼mysql的innodb不建議長欄位做索引，mssql不大建議在長欄位上面建聚集索引。因為所有輔 助索引都引用主索引，過長的主索引會令輔助索引變得過大。 聚集索引因為索引按照某個列在組織，如果在該列上的查詢，包括範圍查詢會比較高效。因為資料組織有了約束，寫入效能下降，插入/刪除/更新聚集鍵值等，會 導致記錄的物理移動、頁拆分等額外的磁碟操作。同時其他非聚集的索引讀資料時候，如果不能從其索引上未包含全部的查詢列，需要關聯表來查詢，則會有兩次查 詢，一次是從非聚集索引上定位到聚集索引鍵，然後再從聚集索引鍵查到資料。

較之非聚集的索引，資料儲存方式只有一種，聚集索引也就只能有一個，也就顯得相對珍貴些。一般選擇會要比較慎重些。知道了這些原理後，對於到底要不 要建聚集索引，其實就是選擇以B樹的形式組織表資料還是以堆表的方式組織表資料；如果決定是以B樹的方式組織資料，到底在根據哪個列來建立B樹，組織表數 據；其他的應該在哪些列上建立索引，也就不難回答。另外可以結合使用場景結合試驗結果來做出滿足要求的決策。

五、附錄Database System Concepts 關於索引的一個介紹

View Ch11.ppt and other presentations by idouba.

 

原創文章。為了維護文章的版本一致、最新、可追溯，轉載請註明： 轉載自idouba

本文連結地址: B樹在資料庫索引中的應用剖析