How does the cluster index change when the data is updated?

2022-07-03 0 By

To search for a row corresponding to a primary key id, go to the top index page 88 and do binary search to determine which index page you should go to.If we go to the lower index page 35, there are also some index entries in the lower index page 35, which respectively are the lower index pages (20, 28, 59) and the smallest primary key value in them. If we go to the lower index page 35, we should go to the lower index page to find the key value.It is possible to start from index page 35 and then find the lower index page 59, which also has index entries that hold some data page numbers (such as data pages 2 and 8) and the smallest primary key values in each data page.Continue binary search here, you can locate which data page to look for.For example, if you go to data page 2, there is a page directory containing the primary key values of each row and the actual physical location of the row.Continue binary search here to quickly locate the physical location of the row corresponding to the primary key value to be searched, and then directly locate that data in data page 2.This is the process of finding a primary key based on an index.The lowest level of index pages, all have Pointers to reference data pages, so there are pointer links between index pages and data pages.Within index pages, index pages at the same level form a bidirectional list based on Pointers to each other: if you take index pages and data pages together, they are all joined together, starting at the root index page 88 and working their way up to all data pages, forming a B+ tree.The lowest 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!All the index pages and data pages in the B+ tree are clustered indexes!In InnoDB, when you add or delete data, you put your data pages directly in the cluster index, the data is in the cluster index, and the cluster index contains the data.If you’re inserting data, you’re inserting data into a data page.If your data to pages split, he can adjust the data page internal row data, ensure the primary key values are ordered in the data page, : the next data on a page of all the primary key value > data split all the primary key pages, can also maintain your upper index data structure, maintain your index entries in the upper index page, different data page and minimum primary key value.Then if your data page is more and more, an index page can not be put, it will be pulled out a new index page, and then make a top index page, the index entries stored in the top index page is the lower index page page number and the lowest primary key value.Similarly, if you have more data, there may be more index page levels, but the average index page can have a lot of index items, even if you have a large table, there are basically only three or four index levels in the large table.The cluster index is organized by primary key by default, so when you add, delete, or delete data: the update page will automatically maintain the B+ tree structure of the cluster index, and the new and update page will automatically create the cluster index for you