自增主键

自增值存在哪？

不同的引擎对于自增值的保存策略不同：

  • MyISAM引擎的自增值保存在数据文件中。
  • InnoDB引擎的自增值，其实是保存在了内存里（重启重新去找自增值的最大值max(id)），到了MySQL 8.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是两个系统参数，分别用来表示自增的初始值和步长，默认值都是1。

自增值的修改时机

例如：表t里面已经有了(1,1,1)这条记录并且c有唯一索引，再执行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，语句返回。

主键id不连续的原因

  • 唯一键冲突
  • 事务回滚
  • 批量插入后自增值的浪费

InnoDB为了性能放弃自增值回退的功能（因为回退需要判断id是否存在、要不就把自增id的锁范围扩大）

自增锁的优化

MySQL 5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1。

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

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

批量插入数据，包含的语句类型是insert … select、replace … select和load data语句。

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