自增主键相关-AUTO_INCREMENT

Posted on | Edited on | In ,

自增主键的原理

首先创建测试表

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

我们MySql的主键往往设置成auto_incrment

  • 原理是什么呢？
  • 为什么会有不连续的情况？

自增字值保存在哪里

不同的表引擎对于auto_incrment处理是不同的

  • MyIsam表是保存在数据结构中
  • Innodb表是保存在内存中的
    • 5.7以及之前的版本是保存在内存中，每次数据库启动时候会查找表的max(id)，然后+1。如果这时候有auto_incrment=11,id=10,如果这条id=10的数据被删除后从起mysql，Auto_incrment的值又变成10
    • 在Mysql 8.0中auto_incrment的值保存在redolog中，依靠redolog来恢复

自增值的修改方式

如果一个值被设置为auto_incrment，当一条插入语句时候

  1. 如果这个值是null或者0，会把当前的auto_incrment值设置进去
  2. 如果手动设置了值，就是用设置的值

这时候auto_incrment的变化如下，插入值是X，auto_incrment的值是Y，

  1. 如果X < Y，自增值不变
  2. 如果X ≥ Y，自增值变为新的自增值，计算方法是：当前的auto_incrment_offset+auto_incrment_increment知道比X大。

自增值的修改时机来看为什么自增值不连续

1
2
3
4
5
# 1
insert into t values(null, 1, 1);

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

主要看第2条语句的执行流程

  1. Innodb调用引擎写入一条数据(null, 1, 1)
  2. 因为id是null所以获取当前的auto_incrment，现在是2
  3. 改变auto_incrment，变为3
  4. 这时候插入（2,1,1）;
  5. 所以C冲突插入失败
  6. 语句结束

可以看到这时候auto_incrment是没有变回2。原因是，因为有可能这时候并发写入，如果回滚了后续的写入都会报主键冲突，同理事物的回滚也类似。

自增锁的优化

为了增加并发能力，Mysql为自增字段加锁不是一个事务锁，每次申请完了都会释放。但是在Mysql5.1之前不是这样的。

在5.1.22版本中增加了一个新策略inoodb_autoinc_lock_mode,默认值是1

  • 0，类似之前的设计直到每个语句执行结束才会释放
  • 1，普通的insert会立刻释放，select。。。。insert ，load等批量操作需要等语句执行完
  • 2，都会立刻释放

推荐设置成2，同事binlog格式设置成row，既能保证并发度，又能保证主备数据不一致。原理是

当binlog的格式是statement时候，select。。。。insert 在并发阶段很可能造成主从数据不一致。比如下图：

avator

  • 很可能涉及sessionB的binlog拿到从库执行由于id都是null，是没法保证执行顺序的造成主备不一致。