You may not know about Mysql increasing id

Introduction:

When using MySQL to build a table, we usually create a self-increasing field (AUTO_INCREMENT) and use this field as the primary key. This article will talk about 1 cut of self-increasing id in the form of question and answer.

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

The following words are not much to say, let's take a look at the detailed introduction with this site

1. Why does 1. MySQL recommend setting self-added id as the primary key?

If we define the primary key (PRIMARY KEY), InnoDB selects the primary key as the clustered index, and if no primary key is explicitly defined, InnoDB selects the first one-only index that does not contain the value of NULL as the primary key index, and if there is no such one-only index, InnoDB selects the built-in 6-byte ROWID as the implicit clustered index (ROWID increments the primary key with the writing of row records, and this ROWID is not as referential as ROWID of ORACLE and is implicit). The data record itself is stored on the leaf node of the main index (1 B + Tree). This requires that all data records in the same leaf node (size is 1 memory page or disk page) be stored in primary key order, so whenever 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 defaults to 15/16), a new page (node) will be opened If the table uses a self-increasing primary key, every time a new record is inserted, the records are sequentially added to the subsequent position of the current index node, and when a page is full, a new page is automatically opened If you use a non-self-increasing primary key (such as ID number or student number, etc.), Because the value of the primary key is approximately random every time it is inserted, So every time a new record is inserted somewhere in the middle of the existing index page, At this point, MySQL has to move the data in order to insert the new record into the appropriate position, Even 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, which increases a lot of overhead. At the same time, frequent movement and paging operations cause a lot of fragments, resulting in a compact index structure. Subsequently, we have to rebuild the table and optimize the filling page through OPTIMIZE TABLE.

To sum up, when we use self-increasing columns as primary keys, the access efficiency is the highest.

2. Is the self-added id1 necessarily continuous?

Self-increasing id is an indefinite continuity of growth.

Let's first look at MySQL's preservation strategy for self-appreciation:

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

Discontinuity of self-increasing id may be caused by:

1. Only 1 key conflict

2. Transaction rollback

3. insert... select statement batch application self-increment id

3. Is there an upper limit for self-increasing id?

Self-increasing id is an integer field. We often use int type to define increasing id, while int type has an upper limit, that is, increasing id also has an upper limit.
The following table lists the range of int and bigint field types:

类型	大小	范围（有符号）	范围（无符号）
int	4字节	(-2147483648,2147483647)	(0,4294967295)
bigint	8字节	(-9223372036854775808,9223372036854775807)	(0,18446744073709551615)

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

Let's test what happens when the self-increasing 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);
//  Successful insertion 1 Row  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'

It can be seen from the experiment that when the self-increasing id reaches the maximum, it will not be expanded. After the first insert statement inserts data successfully, the AUTO_INCREMENT of this table does not change (still 4294967295), which leads to the second insert statement getting the same self-increasing id value again, and then tries to execute the insertion statement and reports the primary key conflict error.

4. How should we maintain the self-addition?

In terms of maintenance, the following two suggestions are mainly provided:

1. Field type selection: int unsigned type is recommended. If it can be predicted that the data volume of this table will be very large, bigint unsigned type can be used instead.

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

Summarize