• notice
  • Congratulations on the launch of the Sought Tech site

How does MySQL's clustered index change when data is updated?

To search for a row corresponding to a primary key id, first go to the top-level index page 88 and perform a binary search to locate which index page you should go to to continue searching.

If you locate the lower index page 35 now, there are also some index entries in the index page 35, which are the lower index pages (20, 28, 59) and the smallest primary key value in them. Continue the binary search in the index entry of 35, it is easy to locate, you should go to the lower index page to find it.

It is possible to find the lower index page 59 from the index page 35. At this time, the index page 59 also has index entries, which store the page numbers of some data pages (such as data pages 2 and 8) and the smallest primary key value in each data page. Continue the binary search here, and you can locate which data page to look for.

For example, when entering data page 2, there is a page directory, which stores the primary key value of each row of data and the actual physical location of the row. Continue the binary search here, you can quickly locate the physical location of the row corresponding to the primary key value to be searched, and then directly find that piece of data in data page 2. This is the process of finding the primary key based on the index.

The index pages at the lowest level all have pointers to refer to the data pages, so there are pointer connections between the index pages and the data pages. Inside the index page, index pages at the same level also form a doubly linked list based on pointers:

Suppose you look at the index page and the data page together, they are all connected together, starting from the root index page 88, all the way to all the data pages, forming a B+ tree. The bottom layer is the data page, which is the leaf node in the B+ tree.

Therefore, if the B+ tree index data structure, the leaf node is the data page itself, that is, the clustered index! That is, the B+ tree composed of all index pages + data pages in the above figure is a clustered index!

Under InnoDB, when adding, deleting or modifying data, you directly put your data pages in the clustered index, the data is in the clustered index, and the clustered index contains the data. For example, you insert data, that is, insert data in the data page.

If your data page starts to be split, it will adjust the row data in each data page at this time to ensure that the primary key values in the data page are in order:

All primary key values of the next data page > all primary key values of the previous data page

When the page is split, it will also maintain your upper-level index data structure, maintain your index entries, different data pages and minimum primary key values in the upper-level index page.

Then if you have more and more data pages, and one index page cannot fit, you will pull out a new index page and create an upper-level index page at the same time. The index entries stored in the upper-level index page are the lower-level index page pages. number and the lowest primary key value.

In the same way, if the amount of data you have is larger, there may be more index page levels at this time, but in general, you can put many index entries in the index page. The level of the index is also three or four.

Clustered indexes are organized by primary key by default, so when you add, delete or modify data:

  • will update the data page

  • It will automatically maintain the clustered index of the B+ tree structure for you, and add and update index pages. This clustered index will be created for you by default.


Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin 10mg drug & lt;a href="https://lipiws.top/"& gt;order lipitor 10mg pill& lt;/a& gt; where to buy lipitor without a preion

Wvityw

2024-03-07

Leave a Reply

+