Shulou(Shulou.com)06/01 Report--

Why does MySQL's primary key increase? This problem may be our daily study or work often see. I hope you learned something from this question. The following is the reference content brought to you by Xiaobian. Let's take a look together!

Features of InnoDB engine tables

1. InnoDB engine table is an index organization table (IOT) based on B+ tree

About B+ trees

B+ tree characteristics:

All keywords appear in the linked list of leaf nodes (dense index), and the keywords in the linked list happen to be ordered;

It is impossible to hit on non-leaf nodes;

Non-leaf nodes correspond to indexes (sparse indexes) that are leaf nodes, and leaf nodes correspond to data layers that store (keyword) data;

2. If we define a primary key (PRIMARY KEY), InnoDB will select the primary key as the clustered index. If there is no explicit primary key definition, InnoDB will select the first unique index that does not contain NULL values as the primary key index. If there is no such unique index, InnoDB will select the built-in 6-byte ROWID as the implicit clustered index (ROWID increases with the writing of row records, and this ROWID is not as referenceable as ORACLE ROWID, and is implicit).

The data records themselves are stored on leaf nodes of the main index (a B+Tree). This requires that all data records in the same leaf node (size of a memory page or disk page) be stored in primary key order, so whenever a new record is inserted, MySQL inserts it into the appropriate node and location according to its primary key, and if the page reaches the load factor (InnoDB default is 15/16), a new page (node) is opened.

4. If the table uses a self-increasing primary key, each time a new record is inserted, the record will be added to the subsequent position of the current index node in sequence. When a page is full, a new page will be automatically opened.

5. If you use a non-self-increasing primary key (If ID number or student number, etc.), because the value of each inserted primary key is approximately random, each time a new record is inserted into a certain position in the middle of the existing index page, MySQL has to move the data in order to insert the new record into the appropriate position, and even the target page may have been written back to disk and cleared from the cache. At this time, it has to be read back from disk, which increases a lot of overhead. At the same time, frequent movement and paging operations cause a lot of fragmentation. The resulting index structure was not compact enough, and subsequently had to be optimized by OPTIMIZE TABLE to rebuild the table and optimize the fill page.

To sum up, if the data writing order of InnoDB table can be consistent with the leaf node order of B+ tree index, the access efficiency is the highest at this time, that is, the access efficiency is the highest in the following cases:

Use self-increasing column (INT/BIGINT type) as the main key, at this time the writing order is self-increasing, and the B+ number leaf node splitting order is consistent;

The table does not specify self-adding columns as the primary key, and there is no unique index that can be selected as the primary key (the above condition). In this case, InnoDB will select the built-in ROWID as the primary key, and the writing order is consistent with the ROWID growth order;

In addition, if an InnoDB table does not display a primary key and has a unique index that can be selected as a primary key, but the unique index may not be an incremental relationship (e.g., string, UUID, multi-field union unique index), the table access efficiency will be poor.

Thank you for reading! After reading the above content, do you roughly understand the reasons for MySQL primary key self-increment? I hope the content of this article is helpful to everyone. If you want to know more about related articles, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.