[MySQL FAQ] Series-Why the InnoDB table is recommended to use the self-increment column master key

[MySQL FAQ] Series-Why the InnoDB table is recommended to use the self-increment column master key

Http://imysql.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml

Let's start by understanding some of the key features of the InnoDB engine table:

• InnoDB engine table is an index organization table (IOT) based on the B + tree;
• Each table needs to have a clustered index (clustered index);
• All row records are stored in the leaf nodes of the B + tree (leaf pages of the tree);
• The efficiency of increment, delete, change and check based on clustered index is the highest;
• If we define the primary key (PRIMARY key), then InnoDB will be the selector as a clustered index;
• If you do not explicitly define a primary key, InnoDB selects the first unique index that does not include a null value as the primary key index;
• If there is no such unique index, then 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 quoted as the rowID of Oracle, which is implied).

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:

• Using the Self-increment column (int/bigint type) The master key, when the write order is self-increment, and B + number leaf node splitting sequence consistent;
• The table does not specify the self-increment key, and there is no unique index (above) that can be selected as the primary key, when InnoDB chooses the built-in ROWID as the primary key, and the write order is consistent with the ROWID growth sequence;
• 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).

What is the actual situation? After a simple TPCC benchmark, modified to use the self-increment column as the primary key and the original table structure of the TPCC test, the former TPMC results 9% times higher than the latter, it is evident that the use of self-increment column to do innodb table primary key benefits, other more different scenarios using the self-added column performance can be self-contrast test.

Figures:

1. B + Tree Typical structure

2. INNODB PRIMARY KEY Logical Structure

Extended reading:

1. Tpcc-mysql User Manual

2, B+TREE index structures in InnoDB

3. B+tree Indexes and Innodb–percona

4. Official MySQL Handbook: Clustered and secondary Indexes

[MySQL FAQ] Series-Why the InnoDB table is recommended to use the self-increment column master key