Why is the MySQL auto-increment primary key not continuous?

1. Introduction

The reason for raising this question is that I found at work that the id of the user table in MySQL is auto-incremented by default, but the results stored in the database are not continuous.

User table structure:

CREATE TABLE `user` ( 
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increment id', 
	`name` varchar(20),
	`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time', 
	`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Update time', 
	PRIMARY KEY (`id`), UNIQUE KEY `idx_name` (`name`)) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user table'

The user table stores:

2. Self-increment storage description

1.1 The auto-increment value of the MyISAM engine is stored in the data file.

1.2 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 achieved, 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, auto-increment values ​​are stored in memory. After each restart, when the table is opened for the first time, the maximum value of the auto-increment value max(id) is found, and then max(id) + 1 is used as the current auto-increment value of the 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.

Three self-value-added modification mechanisms

In MySQL, if the field id is defined as AUTO_INCREMENT, when inserting a row of data, the behavior of the auto-increment is as follows:

• If the id field is specified as 0, null, or no value is specified when inserting data, the current AUTO_INCREMENT value of the table is filled into the auto-increment field;
• If a specific value is specified for the id field when inserting data, the value specified in the statement is used directly.

The result of changing the auto-increment value will vary depending on the relationship between the value to be inserted and the current auto-increment value. Suppose the value to be inserted is X and the current auto-increment value is Y.

• If X<Y, then the table's auto-increment value remains unchanged;
• If X≥Y, the current auto-increment value needs to be modified to the new auto-increment value.

The new auto-increment generation algorithm is: starting from auto_increment_offset, with auto_increment_increment as the step length, continue to add until the first value greater than X is found as the new auto-increment. Among them, auto_increment_offset and auto_increment_increment are two system parameters, which are used to represent the initial value and step size of the auto-increment respectively, and the default value is 1.

4. Time to modify the self-valued value

insert into user values(null, '张三');

1 When the above SQL is executed, the executor calls the InnoDB engine interface to write a row. The value of this row passed in is (0,"张三");

2 InnoDB finds that SQL does not specify the value of the auto-increment id, and obtains the current auto-increment value 2 of the user table;

3 Change the value of the incoming row to (2,"张三");

4 Change the table's auto-increment value to 3;

5 Continue to insert data.

5. Reasons for discontinuous self-increment

5.1 Unique Key Conflict

Assume that when the SQL is executed, the user table id = 10, and the auto-increment id in the memory is 11. A unique key conflict occurs and the write to the database fails. The user table does not have the record with id = 10. After that, ids are written starting from 11, so the ids are discontinuous.

5.2 Transaction Rollback

Assume that the user and staff tables need to be written to the database at the same time. When the SQL is executed, the user table id = 10, and the auto-increment id in the memory is 11; the staff table id = 20, and the auto-increment id in the memory is 21. Once the transaction fails, the transaction rolls back and the write operation fails, the user table does not have the record with id = 10, and the staff table does not have the record with id = 20. The user table starts writing from 11, and the staff table starts writing from 21, resulting in discontinuous ids.

5.3 Batch write operation

For statements that insert data in batches, MySQL has a strategy for applying for auto-increment IDs in batches:

1. During statement execution, if you apply for an auto-increment id for the first time, 1 will be allocated;

2. After 1 is used up, this statement applies for the auto-increment id for the second time, and 2 will be allocated;

3. After the two are used up, the same statement is used to apply for the third self-increment id, and 4 will be allocated;

Similarly, when the same statement is used to apply for auto-increment IDs, the number of auto-increment IDs applied for each time is twice the number of the previous one.

Assume that four records are written to the user table in batches, then these four records will be divided into three application IDs.

The first time it is assigned to id = 1, the second time it is assigned to id = 2, 3, and the third time it is assigned to id = 4, 5, 6, 7. After four records are written in batches, id = 1, 2, 3, 4 will be stored, but id = 5, 6, 7 will be discarded, and the next id starts from 8.

6. Reference Documents

https://time.geekbang.org/column/intro/139

This is the end of this article about why MySQL auto-increment primary keys are not continuous. For more relevant content about MySQL auto-increment primary keys, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!