Shulou(Shulou.com)05/31 Report--

This article mainly introduces why the InnoDB table must use the self-increasing column as the primary key, the article introduces in great detail, has a certain reference value, interested friends must read it!

0. Guide reading

Let's first take a look at some key features of the InnoDB engine table:

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

Each table needs to have a clustered index (clustered index)

All row records are stored in the leaf node (leaf pages of the tree) of the B+ tree.

The efficiency of adding, deleting, modifying and querying based on clustered index is relatively the highest.

If we define the primary key (PRIMARY KEY), then InnoDB will choose it as the clustered index

If the primary key is not explicitly defined, InnoDB selects the first unique index that does not contain a null value as the primary key index

If there is no such unique index, InnoDB chooses the built-in 6-byte ROWID as the implicit clustered index (ROWID increments the primary key as row records are written, and this ROWID is not as referable as ORACLE's ROWID and is implied).

To sum up, 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 access efficiency is the highest in the following cases:

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

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 have an explicit primary key, but has a unique index that can be selected as the primary key, and the unique index may not be an incremental relationship (for example, in the case of strings, UUID, multi-field federated unique indexes), the access efficiency of the table will be particularly poor.

What is the actual situation? After a simple TPCC benchmark test, it is modified to use the self-increment column as the primary key and the original table structure to conduct TPCC tests respectively. The TpmC result of the former is 9 times higher than that of the latter, which shows the obvious benefits of using the self-increment column as the primary key of the InnoDB table. The performance improvement of using the self-increment column in more different scenarios can be tested by itself.

Attached picture:

1. Typical structure of B + tree.

2. Logical structure of InnoDB primary key

The above is all the contents of the article "Why the InnoDB table must use the self-increasing column as the primary key". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.