Notes

Exploring note

0%

自增主键

发表于 更新于 分类于
本文字数： 4.2k 阅读时长 ≈ 7 分钟
自增主键不连续

自增主键可以让主键索引尽量的保持递增顺序插入，避免了页分裂，索引更紧凑。但自增主键不能保证连续递增，业务设计不能依赖自增主键的连续性。 

1
2
3
4
5
6
7
8
9
10
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;

-- 在空表t中插入一行数据
insert into t value(null,1,1);

自增值保存位置

show create table t; 

1
2
3
4
5
6
table: t
create table: create table 't' (
    ...
) engine=innodb auto_increment=2 default charset=latin1  

-- auto_increment=2，表示下一次插入数据时，如果需要自动生成自增值，会生成id=2。

表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。不同引擎对于自增值的保存策略：

  • MyISAM引擎的自增值保存在数据文件中。
  • InnoDB引擎的自增值，其实是保存在内存里，MySQL8.0版本后，才有了“自增值持久化”的能力，实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”：
    • MySQL5.7以前，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。MySQL重启可能会修改一个表的auto_increment的值。(如果一个表当前数据行的最大的id是10，auto_increment=11。删除id=10的行，auto_increment还是11。马上重启实例，重启后这个表的auto_increment就会变成10。)
    • MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增值修改机制

在MySQL里面，如果字段id被定义为auto_increment，在插入一行数据的时候，自增值的行为如下：

  1. 如果插入数据时id字段指定为0、null或者未指定值，那么就把这个表当前的auto_increment值填到自增字段；
  2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设某次要插入的值是X，当前的自增值是Y。

  1. 如果X<Y，那么这个表的自增值不变；
  2. 如果X>=Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：从auto_increment_offset开始，以auto_increment_increment为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值。两个系统参数，分别用来表示自增的初始值和步长，默认值都是1。

在一些场景下，使用的不全是默认值。双M的主备结构里要求双写的时候，会设置成auto_increment_increment=2，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突。

当auto_increment_offset和auto_increment_increment都是1的时候，新的自增值生成逻辑：

  1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；
  2. 否则，自增值不变。

自增值修改时机

唯一键冲突-自增主键id不连续原因1

1
2
--假设表t里面已经有了(1,1,1)这条记录，这时执行一条插入数据命令：  
insert into t values(null,1,1);

执行流程：

  1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1)；
  2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；
  3. 将传入的行的值改成(2,1,1)；
  4. 将表的自增值改成3；
  5. 继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

这个表的自增值改成3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但是也没有将自增值再改回去。在这之后，再插入新的数据行时，拿到的自增id就是3。出现了自增主键不连续的情况。

事务回滚-自增主键id不连续原因2

1
2
3
4
5
6
insert into t values(null,1,1);  
begin;  
insert into t values(null,1,1);  
rollback;  
insert into t values(null,2,2);  
//插入的行是(3,2,2)

出现唯一键冲突或者回滚的时候，MySQL都没有把表t的自增值改回去。这么设计是为了提升性能。

自增值不能回退原因

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定要加锁，然后顺序申请。

  1. 假设事务A申请到了id=2，事务B申请到id=3，那么这时候表t的自增值是4，之后继续执行。
  2. 事务B正确提交了，但事务A出现了唯一键冲突。
  3. 如果允许事务A把自增id回退，把表t的当前自增值改回2，那么就会出现：表里面已经有id=3的行，而当前的自增id值是2。
  4. 接下来，继续执行的其他事务就会申请到id=2，然后再申请到id=3。就会出现插入语句报错“主键冲突”。

解决主键冲突：

  1. 每次申请id之前，先判断表里面是否已经存在这个id。如果存在，就跳过这个id。但是这个方法的成本很高。
  2. 把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增id。锁的粒度太大，系统并发能力大大下降。

InnoDB放弃了“允许自增id回退”的设计，只保证了自增id是递增的，但不保证是连续的。

自增锁的优化

自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

MySQL5.0版本，自增锁的范围是语句级别的。如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放，影响并发度。

MySQL5.1.22版本，引入参数innodb_autoinc_lock_mode，默认值是1。

  1. 这个参数的值被设置为0时，表示采用之前5.0版本的策略，即语句执行结束后才释放锁；
  2. 这个参数的值被设置为1时：
    • 普通insert语句，自增锁在申请之后马上释放；
    • 类似insert…select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
  3. 这个参数的值被设置为2时，所有的申请自增主键的动作都是申请后就释放锁。

普通批量insert批量申请后释放

在普通的insert语句里面包含多个value值的情况下，即使innodb_autoinc_lock_mode设置为1，也不会等语句执行完成才释放锁。因为这类语句在申请自增id的时候，是可以精确计算出需要多少给id的，然后一次性申请，申请完成后锁就可以释放了。

insert…select、replace…select、load data语句级锁执行完释放

sessionA sessionB
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 values(null, 5, 5); insert into t2(c,d) select c,d from t;

示例中，往表t1中插入了4行数据，然后创建了一个相同结构的表t2，然后两个session同时执行向表t2中插入数据的操作。

如果sessionB申请了自增值以后马上就释放自增锁，那么可能出现情况：

  • sessionB先插入了两个记录，(1,1,1)、(2,2,2)；
  • 然后，sessionA来申请自增id得到id=3，插入了(3,5,5)；
  • 之后，sessionB继续执行，插入两条记录(4,3,3)、(5,4,4)。

如果binlog_format=statement，由于两个session是同时执行插入数据命令的，所以binlog里面对表t2的更新日志只有两种情况：要么先记sessionA的，要么先记sessionB的。但是不论是哪一种，这个binlog拿去从库执行，或者用来恢复临时实例，备库和临时实例里面，sessionB这个语句执行出来，生成的结果里面，id都是连续的。这个库就发生了数据不一致。

因为原库sessionB的insert语句，生成的id不连续。这个不连续的id，用statement格式的binlog来串行执行，是执行不出来的。解决的两种思路：

  1. 一种思路是，让原库的批量插入数据语句，固定生成连续的id值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的。
  2. 另一种思路是，在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。这种情况，其实就是innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row。

在生产上，尤其是有insert…select、replace…select、load data这种批量插入数据的场景时，从并发插入数据性能的角度考虑，设置：innodb_autoinc_lock_mode=2，并且binlog_format=row。技能提升并发性，又不会出现数据一致性问题。

批量申请id策略-自增主键id不连续原因3

批量插入数据的语句，若不知道要预先申请多少个id(普通批量insert知道要申请多少个id)，需要一个时申请一个，但是在大批量插入数据的情况下，不但速度慢，还会影响并发插入的性能。批量申请自增id策略：

  1. 语句执行过程中，第一次申请自增id，会分配1个；
  2. 1个用完以后，这个语句第二次申请自增id，会分配2个；
  3. 2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
  4. 依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。(但不一定会被用完，未使用的会被浪费掉)