You may not know these things about Mysql auto-increment id

Introduction:

When using MySQL to create a table, we usually create an auto-increment field (AUTO_INCREMENT) and use this field as the primary key. This article will tell you everything about auto-increment ID in the form of questions and answers.

Note: This article is all based on the Innodb storage engine.

Let’s not talk too much, let’s take a look at the detailed introduction together.

1.Why does MySQL recommend setting the auto-increment column id as the primary key?

• If we define a primary key (PRIMARY KEY), InnoDB will select the primary key as the clustered index. If the primary key is not explicitly defined, 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 as row records are written. This ROWID is not referenceable like ORACLE's ROWID, but is implicit).
• The data records themselves are stored in the leaf nodes 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 disk page) is stored in the order of the primary key. Therefore, whenever a new record is inserted, MySQL will insert it into the appropriate node and position according to its primary key. If the page reaches the load factor (InnoDB defaults to 15/16), a new page (node) is opened.
• If the table uses an auto-increment primary key, each time a new record is inserted, the record will be added sequentially to the subsequent position of the current index node. When a page is full, a new page will be automatically opened.
• If a non-auto-incrementing primary key is used (such as ID number or student ID number, etc.), each new record will be inserted into the middle of the existing index page because the value of the primary key is nearly random. At this time, MySQL has to move data to insert the new record into the appropriate position. The target page may have been written back to the disk and cleared from the cache, and then it has to be read back from the disk. This adds a lot of overhead. At the same time, frequent movement and paging operations cause a large amount of fragmentation, resulting in an index structure that is not compact enough. OPTIMIZE TABLE has to be used to rebuild the table and optimize the filling page.

In summary: when we use the auto-increment column as the primary key, the access efficiency is the highest.

2. Are the auto-increment column IDs necessarily continuous?

The increment of the auto-increment ID is not necessarily continuous.

Let's first look at MySQL's storage strategy for auto-increment values:

The self-increment value of the InnoDB engine is actually stored in the memory. It was not until MySQL 8.0 that the ability of "self-increment persistence" was realized, that is, "if a restart occurs, the self-increment value of the table can be restored to the value before MySQL restarted". The specific situation is:
In MySQL 5.7 and earlier versions, the auto-increment value is stored in memory and is not persisted. After each restart, when the table is opened for the first time, the maximum value of the auto-increment value max(id) will be found, and then max(id)+1 will be used as the current auto-increment value of the table.
For example, if the largest id in the current data row of a table is 10, AUTO_INCREMENT=11. At this time, we delete the row with id=10, and AUTO_INCREMENT is still 11. But if you restart the instance immediately, the AUTO_INCREMENT of this table will become 10 after the restart.
That is, a MySQL restart may modify the AUTO_INCREMENT value of a table.
In MySQL 8.0, the changes of the auto-increment value are recorded in the redo log. When restarting, the redo log is used to restore the value before the restart.

The situations that cause the auto-increment ID to be discontinuous may be:

1. Unique key conflict

2. Transaction rollback

3. insert ... select statement to batch apply for auto-increment id

3.Is there an upper limit for the auto-increment ID?

The auto-increment id is an integer field. We often use the int type to define the growth id, and the int type has an upper limit, which means the growth id also has an upper limit.
The following table lists the ranges of int and bigint field types:

It can be seen from the above table that when the auto-increment field uses the int signed type, the maximum value can reach 2147483647, which is more than 2.1 billion; when using the int unsigned type, the maximum value can reach 4294967295, which is more than 4.2 billion. Of course, bigint can represent a wider range.

Next, we test what happens when the auto-increment id reaches the maximum and inserts data again:

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
// Successfully inserted a row of 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

From the experiment, we can see that when the auto-increment ID reaches the maximum, it cannot be expanded. After the first insert statement successfully inserts the data, the AUTO_INCREMENT of this table does not change (it is still 4294967295), which causes the second insert statement to get the same auto-increment ID value. When trying to execute the insert statement again, a primary key conflict error is reported.

4. How should we maintain the auto-increment column?

The following two suggestions are provided for maintenance:

1. Field type selection: It is recommended to use the int unsigned type. If it is predicted that the amount of data in the table will be very large, the bigint unsigned type can be used instead.

2. Pay more attention to the auto-increment value of large tables to prevent primary key overflow.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.