Why is the InnoDB table in MySQL recommended to use the self-increment column key

Features of the InnoDB engine table

1, InnoDB engine table is a B + tree-based index organization table (IOT)

About B + Trees

(Image from the Internet)

Features of B + trees:

(1) All keywords appear in the list of leaf nodes (dense index), and the key words in the list are in order;

(2) It is impossible to hit the non-leaf node;

(3) The non-leaf node is equivalent to the index of the leaf node (sparse index), and the leaf node is equivalent to the data layer of storing (key word) data;

2, if we define the primary key (PRIMARY key), then InnoDB will select the primary key as the clustered index, if the primary key is not explicitly defined, then InnoDB will select the first non-inclusive null value of a unique index as the primary key index, if there is no such a unique index, The InnoDB will select the built-in 6-byte long rowid as an implied clustered index (ROWID is incremented with the write of the row record and the ROWID is not as referenced as the ROWID of Oracle, which is implied).

3. The data record itself is stored on the leaf node of the primary index (a b+tree). This requires that each data record in the same leaf node (the size of a memory page or a disk page) be stored in the primary key order, so that whenever a new record is inserted, MySQL inserts it into the appropriate node and position according to its primary key, if the page reaches the load factor (InnoDB defaults to 15/16). Opens a new page (node)

4, if the table uses the self-increment primary key, then each time the new record is inserted, the record will be added sequentially to the subsequent position of the current index node, when a page is full, it will automatically open a new page

5, if the use of non-self-increment primary key (if the social Security number or school number, etc.), because each time the value of the primary key is approximately random, so each new record will be inserted into the existing index page in the middle of a position, when MySQL had to plug the Fresh records into the appropriate location and move the data, Even the target page may have been written back to disk and cleared from the cache, and then read back from the disk, which adds a lot of overhead, while the frequent movement, paging operations caused a lot of fragmentation, the lack of compact index structure, the subsequent forced by optimize Table to rebuild the tables and refine the fill page.

In summary, if the data write order of the InnoDB table is consistent with the leaf node order of the B + Tree index, then the access efficiency is the highest, that is, the following conditions are the highest access efficiency:

1, using the Self-increment column (int/bigint type) The master key, this time the write order is self-increment, and B + number leaf node splitting sequence consistent;

2, the table does not specify the self-increment the main key, and there is no unique index can be selected as the primary key (the above conditions), this time InnoDB will choose the built-in ROWID as the primary key, the write order and ROWID growth sequence consistent;
In addition, if a InnoDB table does not display a primary key, and there is a unique index that can be selected as the primary key, the unique index may be less efficient when it is not an incremental relationship (for example, a string, UUID, multi-field federated unique index).

The exact words in high-performance MySQL

Reference: https://ruby-china.org/topics/26352

Why is the InnoDB table in MySQL recommended to use the self-increment column key