Learn more about auto-incrementing primary keys in MySQL

This article will take you to understand the auto-increment primary key in MySQL, introduce the auto-increment value modification mechanism, auto-increment value modification timing, auto-increment lock optimization methods, etc. Friends in need can learn about it~

1. Where is the self-increased value stored?

Different engines have different strategies for saving auto-incremented values

1. The auto-incremented value of the MyISAM engine is stored in the data file

2.InnoDB engine The self-incremented value is stored in memory in MySQL 5.7 and earlier versions and is not persisted. After each restart, when you open the table for the first time, you will find the maximum value of the auto-increment max(id), and then use the max(id) step size as the current auto-increment value of the table

select max(ai_col) from table_name for update;

In MySQL8 .0 version records the changes in the self-increasing value in the redo log. When restarting, rely on the redo log to restore the value before restarting

2. Self-increasing value modification mechanism

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

1. If the id field is specified as 0, null or unspecified value when inserting data, then this The current AUTO_INCREMENT value of the table is filled in the auto-increment field

2. If the id field specifies a specific value when inserting data, use the value specified in the statement directly

Assume that a certain value is to be inserted The value is X, and the current auto-increment value is Y

1. If The self-increment value is modified to a new self-increment value

The new self-increment value generation algorithm is: starting from auto_increment_offset (initial value), taking auto_increment_increment (step size) as the step size, and continuing to superpose until the first value is found. A value greater than Field, c is the only index. The table creation statement is as follows:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)) ENGINE=InnoDB;

Assume that there is already a record (1,1,1) in table t, and then execute another insert data command:

insert into t values(null, 1, 1);

The execution process is as follows:

1. The executor calls the InnoDB engine interface to write a row. The value of the passed row is (0,1,1)

2.InnoDB Find the value for which the auto-increment id is not specified, and obtain the current auto-increment value of table t 2

3. Change the value of the incoming row to (2,1,1)

4 .Change the auto-increment value of the table to 3

5. Continue to insert data. Since the record of c=1 already exists, a Duplicate key error (unique key conflict) is reported, and the statement returns

The corresponding execution flow chart is as follows:

After that, when inserting a new data row, the auto-incremented ID obtained is 3. There is a situation where the auto-incrementing primary key is not continuous

Unique key conflicts and transaction rollbacks will lead to the situation where the auto-incrementing primary key id is not continuous

4. Optimization of lock increase

The auto-increment id lock is not a transaction lock, but is released immediately after each application, so as to allow other transactions to apply again

But in MySQL5. In version 0, the scope of self-increasing locks is statement level. In other words, if a statement applies for a table auto-increment lock, the lock will not be released until the statement is executed.

MySQL version 5.1.22 introduces a new strategy, a new parameter innodb_autoinc_lock_mode, the default value is 1

1. This parameter is set to 0, which means the strategy of the previous MySQL5.0 version is adopted, that is, the lock is released only after the statement is executed.

2. This parameter is set to 1

In ordinary insert statements, the self-increasing lock is released immediately after the application.

For statements such as insert...select that insert data in batches, the self-increasing lock still has to wait until the statement is completed before being released

3. This parameter is set to 2. All actions for applying for an auto-incremented primary key are to release the lock after application.

For the sake of data consistency, the default setting is 1

• If sessionB releases the auto-increment lock immediately after applying for the auto-increment value, then the following situation may occur:

sessionB first inserts two rows of data (1,1,1), (2,2,2)

sessionA applied for the auto-increment id and got id=3. After inserting (3,5,5)

, sessionB continued to execute and inserted two records ( 4,3,3), (5,4,4)

When binlog_format=statement, the two sessions execute the insert data command at the same time, so the binlog faces the update log of table t2 There are only two situations: either record sessionA first, or record sessionB first. No matter which one it is, this binlog is executed from the slave database, or used to restore a temporary instance. In the standby database and temporary instance, the sessionB statement is executed, and the IDs in the generated results are continuous. At this time, data inconsistency occurred in this library.

• Ideas to solve this problem:
• 1) Let the original library insert data statements in batches to generate continuous id values. Therefore, the self-increasing lock is not released until the statement is executed, just to achieve this purpose

2）在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。也就是把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row

如果有批量插入数据（insert … select、replace … select和load data）的场景时，从并发插入数据性能的角度考虑，建议把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row，这样做既能并发性，又不会出现数据一致性的问题

对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

1.语句执行过程中，第一次申请自增id，会分配1个

2.1个用完以后，这个语句第二次申请自增id，会分配2个

3.2个用完以后，还是这个语句，第三次申请自增id，会分配4个

4.依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7

由于这条语句实际上只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2 values(null, 5,5)，实际上插入了的数据就是(8,5,5)

这是主键id出现自增id不连续的第三种原因

五、自增主键用完了

自增主键字段在达到定义类型上限后，再插入一行记录，则会报主键冲突的错误

以无符号整型（4个字节，上限就是$2^{32}-1$）为例，通过下面这个语句序列验证一下：

CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);

第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误

相关学习推荐：mysql教程(视频)

The above is the detailed content of Learn more about auto-incrementing primary keys in MySQL. For more information, please follow other related articles on the PHP Chinese website!