MySQL InnoDB table-Basic BTree Data Structure

MySQL InnoDB table-Basic BTree Data Structure

MySQL InnoDB table is an index organization table. This should be the first thing that anyone who learns MySQL will learn. This means that the data in this table is stored in the primary key order, that is to say, the leaf node of the BTree stores all the data of this row.

I started with Oracle. When I first came into contact with MySQL, the default engine was MyISAM. At that time, I saw that all the InnoDB tables created by the company would add a business-independent auto-incrementing primary key in the first column. I think it is not necessary. I asked some people to do this, the answer is also confusing. In fact, it is said that the efficiency will be better. Therefore, this form is not widely used in the construction of data warehouse projects. Most tables do not specify primary keys.

As a matter of fact, the people who first designed those tables have indeed studied MySQL InnoDB, and they are very considerate in Table creation. So what does B-tree look like?

I drew a picture, but this figure is a bit problematic. The leaf node should be a two-way linked list, and I only drew a one-way linked list. In this way, we can also see that all values are stored in the leaf node. As for how much data can be stored on each page, I have not made any research. If you know, let me know. Every time I see this figure, I think of the exciting times of my college age. I draw binary trees every day to calculate the cost of traversal. At that time, I really don't know what it is like. Here the blue square refers to the pointer. I have no distinction between the pointer pointing to the Child page and the pointer pointing to the next leaf page.

The basic data structure is already described in this article. The next time we have time to update and delete data, how can we perform this operation.

This article permanently updates the link address: