Reading notes-mysql operation and Maintenance 08-Index Implementation Principle 2

We already know the structure of the B + tree and how the different layers relate to each other.

Now we simulate how a B + tree is from small to large, from scratch, from simple to complex process.

Let's start by making some assumptions:

1, each page including the inner node and leaf node can be inserted up to three records, insert fourth, it will lead to division.

2, the inserted data is a key-value pair, but we only focus on the key, the value can be used without attention, simply as the data represented.

3, insert the data sequence as: 10,20,5,8,23,22,50,21,53,40,9

4, to be concise, key is a number of simple int types

5, assuming that the page number of the root node is 100

The first time the insertion process. At this point, there is no data in the index, so the B + tree is an empty root node.

Because the page can only store three keys, first insert the 10,20,5 into the page, and then do the database sorting, index three key after inserting, B + tree should be the appearance.

According to the hypothesis, with the page has been inserted full, but there is more data to be inserted, if you continue to insert, you need to split with the face leaf, the classification process is as follows:

1, first create a new leaf node, assuming that the amount of the requested page is number 101th. In the process of splitting, the root node always does not become, no matter how big the tree, the root node of the page is consistent.

2, then copy all the records of the root node to the new page, the smallest record of the original root page points to the new leaf node, and all the records of the root page are deleted.

3. Finally, the Min pointer of the root page is pointed to the new leaf node, page 101th.

After this transformation, the new B + tree structure is shown in

At this point, the classification of the root node is complete, but the key--8 we want to insert is not yet inserted, so we still need to insert it.

Insert 8, you can think of, through the location directly back to find page 101th, in this page inserted, found still no space, this time the page belongs to the leaf node, so there is a leaf node involved in the division.

The steps are as follows:

1, first create a leaf node, assuming the page number is not 102

2, move half of the data on number 101th to page 102th, where you can assume that each migration of the past 1

Page 3,101 and page 102th are leaf nodes, commonly called brotherhood relationships, and they need to form a doubly linked list.

4, after migrating half of the data to page 102th, page 102th has only 1 records with key 20. Note that page 102th is also a leaf node, so this page also needs to be linked to the root node. All you need to do is take the key out of the 20 record,

Then add a pointer to the information, that is, the 102th page here, a new record inserted into the root page, then this record even point to the corresponding son node 102th page.

In this way, the leaf nodes are split and re-adjusted after the B + tree as shown:

It is very simple to insert key (8) until it is split.

Start with the page search, because 8:20 small, so still from min this record to find the corresponding leaf node, found the number 101th page. Then insert on this page, this time has been satisfied with the insertion condition.

In the same way, starting from the root node, you can simply insert the key (22) and the two lines of record. The B + at this point is as follows:

At this point continue to insert key (50) This row of data, because it is greater than 20, the index found 102 sub-page, but the 102 page is full, then continue to split it. This classification remains the division of the leaf nodes.

Also first apply for a leaf node page, set page number is page 103th. Then move the half Data key (23) on page 102th to page 103th and then the parent-child relationship on page 103th and the root page.

This creates a new leaf node, page 103th. Page 103th is also the sibling node of page 102th, which needs to be connected by a doubly linked list.

After this transformation, the new B + tree structure is entered:

After splitting this, the same method is used to start the search from the root node, it is convenient to insert key (a), key (+), key (53) three rows of data.

Then continue to insert key (40), because it is larger than key (23), so it should be inserted into page 103th, but the 103th page is full, so the leaf node split again.

Suppose the application is on page 104th, splitting and inserting a B + tree after key (40) such as:

Continue inserting the next Data key (9). In the root page, because key (9) is smaller than key (20), you should find page 101th to insert the data, but the page is already full and needs to be split again.

Similarly create a new leaf node page, set the page number is 105. Move the data so that it becomes the sibling node of page 101th.

When you're done, create a parent-child relationship with the root node, a new index record (key:10,pageno:105), and insert it into the root node page by the only Key (10) on page 105th, which is moved from page 101th.

Now the discovery and the page is full, it is clear that the root page of the split.

Let's first look at the current B + tree (not actually called a full B + tree) as shown in:

can see page number 105th is no father, need to divide for him to find a father.

The page of the new application should be the Inner node page, because the data to be stored is the index, not the leaf page. Suppose this page is number 106th.

It is important to note that since the root node is always the node, it is necessary to move all the data on the root node to page 106th, which is the equivalent of 5 pages from 101 to 105 as the parent node becomes number 106th. The smallest node of page 100th also points to page 106th.

The B + Tree is now a 3-storey structure, as shown in:

At this point, the number 105th node still cannot write the index information to the parent node, because number 106th is still full. Then go on splitting.

Indicates that this test split is not a page node nor a root node, but a splitting of the inner node.

The split of the inner node is similar to that of the leaf node, it is still applying for a new node, assuming it is page 107th, and then as the sibling page of page 106th, moving a piece of data to page 107th, the index record (key:53,pageno:107) is inserted into the root page.

At this point, the new B + tree structure looks like this:

At this point, the index data (key:10,pageno:105) continues to be inserted on page 106th. At this point, after two divisions,

At this point, page 106th includes key (10,20,23) three records, pointing to Page 105,102,103, respectively.

Finally number 105th also found its own parent node, at this time the B + tree into the show:

Wait, don't forget the business, our data Insertion task is not finished yet.

Insert Data Key (9). Starting from the root search, you will still find page 101th, you can directly write the data to page 101th.

Insert all data into the complete B + tree after inserting it as shown:

At this point, we look back, the sequence of data to be inserted is: 10,20,5,23,22,50,21,53,40,9.

At this point, we can look at the leaf node of the last graph, and read all the records from left to right, the sequence is: 5,8,9,10,20,21,22,23,40,50,53.

It is obvious that it is equivalent to ordering the original sequence once and containing all the data.

If you want to query the full table, inside the InnoDB, it will be positioned directly to the left-most leaf node, then all the data is read from left to right, and this is formally the characteristic of the B + tree we are introducing.

Reading notes-mysql operation and Maintenance 08-Index Implementation Principle 2