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

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;

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

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

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

4. Optimization of lock increase

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.

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.

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

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

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

• 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);

Copy after login

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);

Copy after login

第一个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!