Do you know why you choose B + tree as the database index structure? Talk about your understanding


Do you know why you choose B + tree as the database index structure? Talk about your understanding


First, let’s talk about the B tree. Why use a B-tree? We need to understand the following two facts:

[fact 1]

The access speed of memory with different capacity varies greatly. Taking disk and memory as an example, the time to access disk is about Ms level, and the time to access memory is about ns level. There is a vivid metaphor. If a memory access takes 1 second, an external memory access takes 1 day. Therefore, today’s storage systems are organized hierarchically.

The most commonly used data shall be placed in higher-level and smaller memory as far as possible. Only if it cannot be found in the current layer, it can be found in lower layer and larger memory. This explains that when processing large-scale data (meaning that the data cannot be stored in memory at one time), the actual running time of the algorithm often depends on the IO times of the data between different storage levels. Therefore, to improve the speed, the key is to reduce io.

[fact 2]

Disk read data is based on data block (or page). All data in the same data block can be read out at one time.

In other words, reading 1b from disk is almost as fast as reading 1KB! Therefore, if you want to improve the speed, you should make use of the characteristics of external memory batch access. In some articles, it is also called disk pre reading. The design of the system is based on a famous locality principle:

When a data is used, the data near it is usually used immediately. The data required during program operation is usually concentrated

B tree

Suppose there are 1 billion records (1000)10001000), if the balanced binary search tree (BBSt) is used, in the worst case, the search requires log (2, 10 ^ 9) = 30 I / O operations, and only one keyword can be read out at a time (that is, if the keyword read out this time is not what I want to find, I / O must be carried out again to read the data). What would happen if we replaced it with a B-tree?

B-tree is a multi fork balanced search tree designed for disk or other auxiliary storage devices.B-tree is used in multi-level storage system, which can be used for external search and greatly reduce the number of I / O. Through the B-tree, we can make full use of the efficient support of external memory for batch access and turn this feature into advantages.For each descending layer, take the super node as the unit (super node means that a node contains multiple keywords) and read a group of keywords from the disk. So, how big is the group?

How much data a node stores depends on the data block size of the disk. For example, the size of 1 block in the disk is 1024KB. Assuming that the size of each keyword is 4 bytes, you can set the size of each group M = 1024 KB / 4 byte = 256. At present, most database systems use M = 200 ~ 300. Assuming M = 256, the height of the B tree storing 100 million pieces of data is about log (256, 10 ^ 9) = 4, that is, the number of I / OS required for a single query does not exceed 4, which greatly reduces the number of I / OS.

Generally speaking, the root node of the B tree resides in memory. The search process of the B tree is as follows: first, since a node contains multiple (for example, 256) keys, it needs to be searched in sequence / binary first. If it is found, the search is successful; If it fails, read the node data of the next layer from the disk according to the corresponding reference (this involves a disk I / O), and perform the same sequential search in the node. In fact, a large part of the time consumed by B-tree search is spent on I / O, so it is very important to reduce the number of I / O.

Definition of B-tree

B-tree is a balanced multi-channel search tree, the so-called m-order B-tree, that is, M-channel balanced search tree. According to the definition of Wikipedia, an m-order B-tree must meet the following requirements:

  • Each node contains at most M branch nodes (M > = 2).
  • Each non leaf node except the root node contains at least ┌ M / 2 branches.
  • If the root node is not a leaf node, there are at least 2 children.
  • A non leaf node with K Children contains k-1 keywords( Keywords in each node are arranged in ascending order)
  • All leaf nodes appear on the same layer. In fact, these nodes do not exist and can be regarded as external nodes.

According to the upper and lower limits of the branch of a node, it can also be called (┌ M / 2, m) tree. For example, when the order M = 4, such a B tree can also be called a (2,4) tree( In fact, (2,4) tree is a special B tree. It has a special origin with red and black trees! I’ll talk about red and black trees later.)

Moreover, the keyword of each internal node is used as the separated value of its subtree. For example, a node contains two keywords (assuming A1 and A2), that is, the node contains three subtrees. Then, the keywords of the leftmost subtree are less than A1; The keyword of the middle subtree is between A1 and A2; The keywords of the rightmost subtree are greater than A2.

For example, a level 3 B tree looks like this:

Do you know why you choose B + tree as the database index structure? Talk about your understanding

Height of B tree (understand)

Suppose a B-tree is not empty, has n keywords, has a height of H (let the root node be the first layer) and has an order of M, what are the maximum and minimum heights of the B-tree?

Maximum height

When the height of the tree is the maximum, the number of key words in each node should be as few as possible. According to the definition, the root node has at least 2 children (i.e. 1 keyword), and the non leaf node other than the root node has at least ┌ M / 2 children (i.e. ┌ M / 2 ┌ – 1 keyword). For convenience of description, let P = ┌ M / 2 ┌.

  • Layer 1: 1 node (including 1 keyword)
  • Two nodes in layer 2 (including 2 * (p-1) keywords)
  • 2p nodes in layer 3 (including 2p * (p-1) ^ 2 keywords)
  • Layer h 2p ^ (H-2) nodes

Therefore, the total number of nodes n

≥ 1+(p-1)*[2+2p+2p^2+...+2p^(h-2)]
≥ 2p^(h-1)-1

Thus, H ≤ log is derived_ P [(n + 1) / 2] + 1 (where p is the base, P = ┌ M / 2)

Minimum height

When the height of the tree is the lowest, the keywords of each node contain at most M children (i.e. M-1 keywords)

n ≤ (m-1)*(1 + m + m^2 +...+ m^(h-1)) = m^h - 1

Thus, H ≥ log is derived_ M (n + 1) (where m is the base)

B + tree

Definition of B + tree

B + tree is a variant of B tree. The biggest difference between B + tree and B tree is:

  • Leaf nodes contain all keywords and pointers to corresponding records, and the keywords in leaf nodes are arranged in size order, and adjacent leaf nodes are connected by pointers.
  • Non leaf nodes only store the maximum (or minimum) keywords of their subtrees, which can be regarded as indexes.

Example of a third-order B + tree: (appreciate the difference between B + tree and B + tree. The keywords of the two are the same)

Do you know why you choose B + tree as the database index structure? Talk about your understanding

Q: why is B + tree more suitable for file index and database index of operating system in practical application than B tree?


  • B + tree is more suitable for external storage. Because the inner node does not store real data (only the largest or smallest keywords of its subtree are stored as indexes), a node can store more keywords, and each node can index a wider and more accurate range, which also means that the amount of information in a single disk IO of B + tree is greater than that of B tree, and the number of I / O is relatively reduced.
  • MySQL is a relational database, and interval access is a common situation. The chain pointer added to the B + leaf node enhances interval access, which can be used in the scenario of interval query; However, interval search cannot be performed using B-tree.

Write at the end

Welcome to my official account.Calm as a yard】, a large number of Java related articles and learning materials will be updated in it, and the sorted materials will also be put in it.

If you think it’s good, just like it and pay attention! Pay attention, don’t get lost, keep updating!!!