Go: Using B-tree and B + trees: A detailed introduction to data search and database indexing

Original address: http://www.jb51.net/article/36184.htm

B-Tree

1. B-Tree definition

A B-tree is a balanced, multi-path lookup tree that is useful in a file system.

Definition: A B-Tree of M-order, or an empty tree, or a M-fork tree that satisfies the following characteristics:
Each node in the ⑴ tree has a maximum of M subtrees trees;
⑵ joghen node is not a leaf node, there are at least two subtrees trees;

All non-terminal nodes outside the ⑶ root node have at least [M/2] subtrees trees;
⑷ all non-terminal nodes contain the following information data:

(N,a0,k1,a1,k2,...,kn,an)
Where: Ki (i=1,2,..., N) is the key code, and Ki<ki+1,

The Ai is a pointer to the subtree node (i=0,1,..., N), and the key code for all nodes in the subtree of the pointer Ai-1 is less than Ki (i=1,2,..., N), and the key codes for all nodes in the sub-tree are greater than kn.

n is the number of key codes.
⑸ all leaf nodes appear at the same level without information (which can be seen as an external node or a failed node, in fact these nodes do not exist and pointers to these nodes are empty).

That is, all leaf nodes have the same depth, equal to the tree height.

such as a four-order B-tree with a depth of 4.

B-Tree lookup is similar to binary sorting tree lookup, the difference is that B-tree each node is a multi-key code of the ordered table, in the arrival of a node, the first in the ordered table, if found, then find success; otherwise, to the corresponding pointer information pointed to the subtree to find, when reaching the leaf node, There is no corresponding key code in the tree.

The process of finding the keyword 47 on the B-tree is as follows:

1) First from the beginning, according to the root node pointer to find the * node, because there is only one keyword in the *a node, and given the value of > keyword 35, if there must be in the pointer A1 in the subtree.

2) The *c node is found by the pointer, the node has two keywords (43 and 78), and the < x < 78, if there is more than the pointer A1 refers to the subtree.

3) Similarly, the CIS pointer locates the *G node, where the keyword 47 is found and the lookup succeeds.

2. Finding algorithms

Copy the Code code as follows:
typedef int KEYTYPE;
#define M 5/*b tree, tentatively set to 5*/
typedef struct node{
int keynum; /* The number of key codes in the node, that is, the node size */
struct Node *parent; /* Point to parent node */
KeyType key[m+1]; /* Key code vector, Unit No. 0 not used */
struct Node *ptr[m+1]; /* Sub-tree pointer vector */
Record *recptr[m+1]; /* Record pointer vector */
}nodetype; /*b tree Node Type */

typedef struct{
NodeType *pt; /* point to the Found node */
int i; /* Key code ordinal in node, node ordinal range [1...m]*/
int tag; /* 1: Find successful, 0: Find failed */
}result; Search result type for/*b tree */

Result Searchbtree (NodeType *t,keytype kx)
{
/* Find key code KX on M-Order B-tree T, reverse back (Pt,i,tag). If the lookup succeeds, the eigenvalue tag=1,*/
/* The key code in the node referred to by PT is equal to KX; otherwise, the eigenvalue tag=0 is equal to the KX key code record */
/* should be inserted between the first and the I+1 key codes in the point where the pointer pt refers to */
p=t;q=null;found=false;i=0; /* Initialize, p points to the unknown origin node, Q points to P's parents */
while (P&&!found)
{N=p->keynum;i=search (P,KX); /* Find in P-->key[1...keynum] */
if (i>0&&p->key[i]= =kx) found=true; /* Find */
else {q=p;p=p->ptr[i];}
}
if (found) return (p,i,1); /* Find success */
else return (q,i,0); /* Find unsuccessful, reverse KX insertion Position Information */
}

Analysis of B-Tree lookup algorithm

As you can see from the lookup algorithm, there are two basic operations for finding in a B-tree:

(1) Finding nodes in B-tree;

(2) Find the keyword in the node.

Since B-trees are usually stored on disk, the previous lookup operation is done on disk, and the next lookup operation is in memory, after the node is found on the disk, the information in the node is read into memory, and then in order to find or binary the keyword with the query equals K. Obviously, it is much more expensive to make a lookup on disk than to make one lookup in memory.

Therefore, the number of lookups on disk, which is the node in the B-tree where the key is to be found, is the primary factor in determining the efficiency of the B-tree lookup.

So how deep is the worst case for M-order B-trees with n key codes? Similar analysis can be performed on a two-fork balance tree. First, we discuss the minimum number of nodes on the M-order B-count layers.

Defined by the B-tree: A B-tree contains n keywords. So there are n+1 leaves all in the j+1 layer.

1) The first layer is the root, at least one node, the root has at least two children, so there are at least two nodes in the second layer.

2) root and leaves, other nodes have at least [M/2] children, so the third layer has at least 2*[M/2] nodes, on the fourth floor at least 2*[m/2]2 nodes ...

3) Then the first j+1 layer has at least 2*[m/2]j-1 nodes, and the J+1 layer node is the leaf node, so the number of leaf nodes n+1. Yes:

That is, the number of nodes involved in the B-tree lookup of the N keyword, from the root node to the node where the keyword is located, does not exceed:

Insertion of the 3.b-tree

The generation of B-trees is also generated from the empty tree, inserting the keywords one by one. However, because the number of keywords in the B-tree node must be ≥ceil (M/2)-1, so that each insertion of a keyword is not to add a leaf node in the tree, but first in the lowest layer of a non-terminal node to add a keyword, if the node's number of keywords does not exceed m-1, the insert is completed, Otherwise, you have to create a "split" of the knot.

(a) is a 3-order B-Tree (the diagram omits F-nodes (i.e. leaf nodes)), assuming that the keyword 30,26,85 should be inserted sequentially.

1) first determine the location of the insertion by looking up. A lookup is made from the root *a, which determines that 30 should be inserted in the *d node. Because the number of keywords in *d does not exceed 2 (that is, m-1), the first keyword is inserted to complete: (b)

2) Similarly, the search to determine the keyword 26 should also be inserted into the *d. Because the number of *d node keywords exceeds 2, you need to split the *d into two nodes, the keyword 26 and its front and back pointers remain in the *d node, and the keyword 37 and its front and back pointers are stored in the new resulting node *d '. Also insert a pointer to the keyword 30 and indicate node *d ' into its parent's node. Because the number of keywords in the *b node does not exceed 2, the insert is complete. (c) (d)

3) (e)-(g) for insertion after 85;

Insert algorithm:

Copy CodeThe code is as follows:
int Inserbtree (NodeType **t,keytype kx,nodetype *q,int i) {
/* Insert a key code between the key[i],key[i+1 of *q on the M-Order B-Tree *t kx*/
/* If the node is too large, then the necessary node splitting adjustment along the parent chain, so that the *t is still m-order B-Tree */
X=kx;ap=null;finished=false;
while (q&&!finished)
{
Insert (Q,I,X,AP); /* Insert x and AP separately into q->key[i+1] and q->ptr[i+1]*/
if (q->keynum<m) finished=true; /* Insert Complete */
Else
{/* split junction *p*/
S=m/2;split (Q,AP);x=q->key[s];
/* Move Q-&GT;KEY[S+1...M],Q-&GT;PTR[S...M] and Q-&GT;RECPTR[S+1...M] into the new node *ap*/
q=q->parent;
if (q) i=search (Q,KX); /* Find the insertion position of KX in the parent node *q * *
}
}
if (!finished)/* (*T) is an empty tree or root node has been split into *q* and ap*/
Newroot (T,Q,X,AP); /* Generate new root node with information (T,X,AP) *t, original *t and AP as subtree pointer */
}

4. B-Tree deletion

Conversely, if you delete a keyword on the B-tree, you should first find the node where the key is located and delete it, if the node is the lowest non-terminal node, and where the number of keywords is not less than ceil (M/2), the deletion is completed, or the "merge" node operation. If the deleted keyword is ki in a non-terminal node, you can replace ki with the minimum keyword y in the subtree of the pointer ai, and then delete y in the corresponding node. For example, in Figure 4.1 (a) on the B-tree Delete 45, you can *f 50 in the node to replace 45, and then delete 50 in the *f node.

Figure 4.1 (a)

So, let's just talk about removing the keywords from the lowest-level non-terminal node. The following three possibilities are available:

(1) The number of keywords in the node where the keyword is deleted is not less than ceil (M/2), then simply delete the keyword ki and the corresponding pointer ai from the node, and the rest of the tree will be the same, for example, delete the keyword 12 from the B-tree shown in Figure 4.1 (a), as shown in the B-Tree diagram 4.2 (a):

Figure 4.2 (a)

(2) The number of keywords in the node of the deleted keyword equals ceil (M/2)-1, while the number of keywords in the right sibling (or left sibling) node adjacent to the node is greater than Ceil (M/2)-1, the smallest (or largest) keyword in its sibling node must be moved up to the parent node. The keywords that are less than (or greater than) in the parent node are moved to the node where the keyword is deleted.

[For example], by deleting 50 from Tutu 4.2 (a), you need to move 61 up in the right sibling node to the *e node, and 53 in the *e node to *f, so that the number of keywords in *f and *g is not less than ceil (m-1)-1, and the number of keys in the parent node is the same, as shown in Figure 4.2 (b).

Figure 4.2 (b)

(3) The number of keywords in the node and adjacent sibling nodes of the deleted keyword is equal to Ceil (M/2)-1. Suppose the node has a right brother, and the right sibling node address is referred to by the pointer Ai in the parent node, then after deleting the keyword, the remaining keywords and pointers in its node, together with the Keyword Ki in the parent node, are merged into the AI's sibling node (if there is no right sibling, merge to the left sibling node).

[For example], by deleting 53 from the B-tree shown in Figure 4.2 (b), the *f node should be deleted and the remaining information in *f (the pointer "empty") and 611 from the parent *e node will be merged into the right sibling node *g. Delete the tree as shown in 4.2 (c).

Figure 4.2 (c)

If so the number of keywords in the parent node is less than Ceil (M/2)-1, then so on.

[For example], after deleting the keyword 37 in the B-Tree of Figure 4.2 (c), the remaining information in the parent B node ("Pointer C") should be merged with the keyword 451 in the parent *a node to the right sibling node *e, as shown in the deleted B-Tree 4.2 (d).

Figure 4.2 (d)

B-trees are primarily used in file systems

In order to store large database files on the hard disk to reduce the number of accesses to the hard disk, this paper presents a kind of balanced multi-path search tree--b-tree structure by its performance analysis, its retrieval efficiency is quite high in order to improve the B-tree performance ' There are many kinds of B-tree variants to improve

B + Tree B + Tree is a B-tree deformation tree that is produced as required by the file system. A M-order B + tree and M-order
The difference between trees is:
⑴ n subtrees tree nodes contain n key codes;
⑵ all of the leaf nodes contain information about all key codes, and pointers to the records containing these key codes, and
The leaf nodes themselves are linked by the size of the key yards from a large order.
⑶ all non-terminal nodes can be regarded as the index part, and the nodes contain only the largest (or smallest) key codes in their sub-root nodes.

A 3-order B + Tree: There are usually two head pointers on the B + tree, one pointing to the root node and the other to the smallest leaf node of the keyword. You can therefore perform two search operations on the B + tree: one from the smallest keyword, and the other from the root node for random lookups. The process of random lookups, insertions, and deletions on B + trees is essentially similar to that of a B. tree. Just when looking for, not the key code on the terminal node equals the given value, does not terminate, but continues downward until the leaf node. Therefore, in the B +
Tree, regardless of whether the search succeeds or not, each search is a path from the root to the leaf node.

The application of B + tree in database

1. The role of indexes in the database

In the process of using the database system, the data query is one of the most frequently used data operations.

The most basic query algorithm, of course, is the sequential lookup (linear search), which iterates through the table and then matches row-by-row values equal to the keyword to be searched, with a time complexity of O (n).  But the time complexity is O (n) Algorithm small scale table, load light database, also can have good performance. But when the data grows, the algorithm with time complexity O (n) is obviously bad, and performance drops quickly.

Fortunately, the development of computer science provides a number of better search algorithms, such as binary search, binary tree search, binary search, and so on. If you look at it a little bit, you will find that each lookup algorithm can only be applied to a particular data structure, such as a binary lookup requires an orderly retrieval of data, while a binary tree lookup can only be applied to a binary lookup tree, but the data itself cannot be fully organized to meet a variety of data structures (for example, It is theoretically impossible to organize both columns sequentially, so in addition to the data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

An index is a structure that sorts the values of one or more columns in a database table. Compared to searching all rows in a table, the index uses pointers to data values stored in the specified columns in the table, and then arranges the pointers in the order specified to help get information faster. Typically, you need to create an index on a table only when you frequently query the data in an indexed column. The index consumes disk space and affects the speed of data updates. However, in most cases, the data retrieval speed advantage of index is much more than its disadvantage.

2. The application of B + tree in database index

At present, most database systems and file systems use B-tree or its variant B+tree as the index structure.

1) Application in database indexing

In the application of database indexing, the B + tree is organized in the following ways:

① the way the leaf nodes are organized. The lookup key for a B + tree is the primary key of the data file, and the index is dense. In other words, in the leaf node, the first record of the data file has a key, refers to, the data file can be sorted by the primary key or not by the primary key, the data file is sorted by the primary key, and the B + tree is a sparse index, in the leaf node for each block of data files with a key, refers to; And this property is the B + Tree lookup key, the leaf node in the data file appears in each attribute K has a key, refers to, where the pointer performs a sort key value of K in the first of the record.

② the organization of non-leaf nodes.  The non-leaf nodes in B + Trees form a multi-level sparse index on the leaf nodes. There are at least ceil (M/2) pointers in each non-leaf node, with a maximum of M pointers.

2) B + Tree index insertion and deletion

① when inserting new data into the database, you also need to insert the corresponding index key value into the database index, you need to insert a new key value into the B + tree. That is, the B-tree insertion algorithm we mentioned above.

② When you delete data from a database and you also need to remove the corresponding index key value from the database index, you need to remove the key value from the B + tree. That is, the B-Tree deletion algorithm

Why use B-tree (B+tree)

Binary search tree Evolutionary varieties of red and black trees and other data structures can also be used to implement the index, but the file system and database system generally adopt b-/+tree as the index structure.

In general, the index itself is large and cannot be stored in memory, so the index is often stored as an index file on the disk. In this way, the index lookup process will generate disk I/O consumption, relative to memory access, I/O access to the consumption of a few orders of magnitude, so the evaluation of a data structure as an index is the most important indicator of the number of disk I/O operations in the process of incremental complexity. In other words, the structural organization of the index minimizes the number of disk I/O accesses during the lookup process. Why using B-/+tree is also related to the principle of disk access.

principle of locality and disk pre-reading

Due to the characteristics of the storage media, the disk itself is much slower than main memory, coupled with mechanical movement, disk access speed is often one of the hundreds of of main memory, so in order to improve efficiency, to minimize disk I/O. To do this, the disk is often not read strictly on-demand, but is read-ahead every time, even if only one byte is required, and the disk starts from this location, sequentially reading a certain length of data into memory. The rationale for this is the well-known local principle in computer science:

When a data is used, the data around it is usually used immediately.

The data that is required during the program run is usually relatively centralized.

Due to the high efficiency of disk sequential reads (no seek time required and minimal rotational time), pre-reading can improve I/O efficiency for programs with locality.

The length of the read-ahead is generally the integer multiple of the page. Page is the logical block of Computer Management memory, hardware and operating system tend to divide main memory and disk storage area into contiguous size equal blocks, each storage block is called a page (in many operating systems, the page size is usually 4k), main memory and disk in the page to exchange data. When the program to read the data is not in main memory, will trigger a page fault, the system will send a read signal to the disk, the disk will find the starting position of the data and sequentially read one or several pages back into memory, and then return unexpectedly, the program continues to run.

The above analysis B-/+tree retrieves the maximum number of nodes to be accessed at a time:

h =

The database system skillfully utilizes the principle of disk pre-reading, setting the size of a node equal to one page, so that each node can be fully loaded with only one I/O. To achieve this, the following techniques are required to implement B-tree in practice:

Each time you create a new node, request a page space directly, so that a node is physically stored in a page, and the computer storage allocation is page-aligned, the implementation of a node only one time I/O.

B-tree requires a maximum of h-1 I/O (root node resident memory) in a single retrieval, and a progressive complexity of O (h) =o (LOGMN). In general practical applications, M is a very large number, usually more than 100, so H is very small (usually not more than 3).

In summary, using B-tree as index structure efficiency is very high.

And the red-black tree structure, H is obviously much deeper. Because the logically close node (parent-child) may be far away physically, it is not possible to take advantage of locality, so the I/O asymptotic complexity of the red-black tree is also O (h), and the efficiency is significantly worse than B-tree.

MySQL B-tree index (technically speaking b+tree)

In MySQL, there are four main types of indexes: B-tree index, Hash Index, Fulltext Index, and R-tree index. We mainly analyze the B-tree index.

The B-tree index is the most frequently used index type in a MySQL database, and all storage engines except the Archive storage engine support B-tree indexes. The Archive engine does not support indexing until MySQL 5.1, and only supports indexing a single auto_increment column.

Not only in MySQL, but in many other database management systems, the B-tree index is also the most important index type, mainly because the storage structure of the B-tree index has a very good performance in data retrieval of the database.

In general, the physical files of the B-tree index in MySQL are mostly stored in the structure of the Balance tree, that is, all the data that is actually needed is stored in the leaf node of the tree, and the shortest path to any of the leaf nodes is long Are exactly the same, so we all call it the B-tree index. Of course, it is possible that various databases (or MySQL's various storage engines) will slightly transform the storage structure when storing their own b-tree indexes. such as the B-tree index of the INNODB storage engine actually uses the storage structure is b+tree, that is, on the basis of the B-TREE data structure has made a small transformation, on each Leaf Node above the index key stored in the relevant information, but also stored a point to the leaf Nod E The next Leafnode pointer information (added sequential access pointers), mainly to speed up the efficiency of retrieving multiple neighboring Leaf Node.

The following is a discussion of the index implementations of the MyISAM and InnoDB two storage engines:

1. MyISAM Index Implementation:

1) primary key index:

The MyISAM engine uses B+tree as the index structure, and the data domain of the leaf node holds the address of the record. Is the schematic diagram of the MyISAM primary key index :

(Figure MYISAM1)

There are three columns in the table, assuming that we Col1 the primary key, figure myisam1 is a MyISAM table's main index (Primary key) schematic. You can see that the index file of MyISAM only stores the address of the data record.

2) Secondary index (secondary key)

In MyISAM, the primary index and secondary index (secondary key) are structurally indistinguishable, except that the primary index requires that key be unique, and the secondary index key can be duplicated. If we establish a secondary index on Col2, the structure of this index is as follows:

It is also the address of a b+tree,data field that holds data records. Therefore, the algorithm of index retrieval in MyISAM is to search the index according to the B+tree search algorithm first, if the specified key exists, the value of its data field is fetched, then the data record is read with the value of the data field.

The index of MyISAM is also called "non-aggregation", and the reason for this is to differentiate it from InnoDB's clustered index.

2. InnoDB Index Implementation

However InnoDB also uses B+tree as the index structure, but the concrete implementation way is different from MyISAM.

1) primary key index:

The MyISAM index file and the data file are detached, and the index file only holds the address of the data record. In InnoDB, the table data file itself is an index structure organized by B+tree, and the tree's leaf node data field holds the complete data record. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

(Figure Inndb Primary key index)

(Figure Inndb Primary key index) is the InnoDB primary index (also the data file), and you can see that the leaf node contains the complete data record. This index is called a clustered index. Because the InnoDB data file itself is clustered by the primary key, the INNODB requires that the table must have a primary key (MyISAM can not), and if it is not explicitly specified, the MySQL system automatically selects a column that uniquely identifies the data record as the primary key, and if no such column exists, Then MySQL automatically generates an implicit field for the InnoDB table as the primary key, which is 6 bytes long and has a length of type.

2). Secondary index of the InnoDB

All secondary indexes of InnoDB refer to the primary key as the data domain. For example, to define a secondary index on the Col3:

InnoDB tables are built on clustered indexes. Therefore, the InnoDB index provides a very fast primary key lookup performance. However, its secondary indexes (secondary index, or non-primary key indexes) also contain primary key columns, so if the primary key definition is larger, the other indexes will be large. If you want to define, many indexes on the table, try to make the primary key as small as possible. InnoDB does not compress the index.

The ASCII code of the text character as the comparison criterion. Clustered index This implementation makes search by primary key efficient, but secondary index search needs to retrieve two times index: first retrieves the secondary index to obtain the primary key, and then retrieves the record with the primary key to the primary index.

The way the index implementations of different storage engines is useful for proper use and optimization of indexes, such as knowing the InnoDB index implementation, makes it easy to understand why it is not recommended to use too-long fields as primary keys, because all secondary indexes refer to the primary index, and the long primary index makes the secondary index too large. For example, using non-monotonic fields as primary keys is not a good idea in InnoDB because the InnoDB data file itself is a b+tree, and a non-monotonic primary key causes the data file to be frequently split in order to maintain b+tree characteristics when inserting new records, which is inefficient, Using the self-increment field as the primary key is a good choice.

InnoDB the difference between index and MYISAM index:

The first is the difference between the primary index, the InnoDB data file itself is the index file. The index and data of MyISAM are separate.

The second is the difference between secondary indexes: the secondary index of the InnoDB data field stores the value of the corresponding record primary key instead of the address. The secondary and primary indexes of MyISAM are not much different.

Go: Using B-tree and B + trees: A detailed introduction to data search and database indexing