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

The main content of this article is to explain "what is the reason why the InnoDB table in MySql uses a self-increasing column as the primary key?" interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the reason why the InnoDB table in MySql uses a self-increasing column as the primary key?"

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

On B + Tree

The characteristics of B + tree:

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 at a non-leaf node.

A non-leaf node is equivalent to an index (sparse index) of a leaf node, and a leaf node is a data layer that stores (keywords) data.

2. Primary key (PRIMARY KEY)

If we define a primary key (PRIMARY KEY), then InnoDB will select the primary key as the clustered index, if there is no explicit definition of the primary key, InnoDB will select the first unique index that does not contain null values as the primary key index, and if there is no such unique index, InnoDB will choose the built-in 6-byte ROWID as the implicit clustered index (ROWID increments as the row records are written, this ROWID is not as referable as ORACLE's ROWID Is implied).

3. Main index

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

4. Self-increasing primary key

If the table uses a self-incrementing primary key, each time a new record is inserted, the record will be sequentially added to the subsequent position of the current index node, and when a page is full, a new page will be automatically opened

5. Non-self-increasing primary key

If you use a non-self-increasing primary key (such as ID number or student number, etc.), because the value of each inserted primary key is approximately random, each new record will be inserted somewhere in the middle of the existing index page, and MySQL will have to move the data in order to insert the new record to the appropriate location, and even the target page may have been written back to disk and cleared from the cache, and then read back from the disk. This increases a lot of overhead, while frequent movement and paging operations cause a large number of fragments, resulting in an index structure that is not compact enough. Later, we have to use OPTIMIZE TABLE to rebuild the table and optimize the fill page.

Summary

If the data writing order of the InnoDB table is the same as that of the leaf nodes of the B+ tree index, the access efficiency is the highest, that is, the following situations have the highest access efficiency:

1. Use the self-incrementing column (INT/BIGINT type) as the primary key, and the writing order is self-increasing, which is consistent with the splitting order of B+ leaf nodes.

2. The table does not specify a self-incrementing column 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 choose the built-in ROWID as the primary key, and the write order is the same as the ROWID growth order.

In addition, if an InnoDB table does not show the primary key and has a unique index that can be selected as the primary key, but the unique index may not be an incremental relationship (for example, in the case of string, UUID, multi-field union unique index), the access efficiency of the table will be less efficient.

At this point, I believe that everyone on the "MySql InnoDB table to use self-increment column as the primary key is why" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.