MySQL auto_increment介绍及自增键断层的原因分析

约定不等于承诺〃 2022-04-08 08:26 433阅读 0赞

MySQL里面有一个列特性，auto\_increment，自增长列，可以通过auto\_increment\_increment设置递增间隔，auto\_increment\_offset设置初始值。

理论上，如果列容量足够大，那么auto\_increment可以无限的伸长，可谓是男人的榜样。什么？听见了发动机的声音？不不不，我们是正规的城市公交，上车别忘了打卡。

下面，有请auto\_increment同学来表演一下它的伸长绝技。

mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` int(11) NOT NULL AUTO_INCREMENT,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`col1`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into test values (null,1),(null,2),(null,3);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from test;
    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |    1 |
    |    2 |    2 |
    |    3 |    3 |
    +------+------+
    3 rows in set (0.00 sec)

只要自增列是null或是0，他就会自己递增。那么，递增的规则是什么呢？递增初始值是什么？递增区间是什么？怎么确定下一个数值是什么？

递增初始值，是可以通过auto\_increment\_offset来指定的，如上例子，默认是1;递增间隔是通过auto\_increment\_increment来指定，默认也是1。这两个属于系统变量，有全局和会话两个级别。

mysql> show variables like 'auto_increment%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 1     |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+

所以设置这两个属性，通过常规的变量设置方式，

set global auto_increment_increment=N;
    set session auto_increment_increment=N；

不能通过alter table test auto\_increment\_increment=N的方式进行设置。  
需要注意的是，当auto\_increment\_offset小于auto\_increment\_increment时，auto\_increment\_offset会被忽略，采用默认值1。

那么，MySQL怎么确定自增列的下一个数值是什么呢？  
首先，MySQL硬性规定自增列必须存在索引。

mysql> create table test2 (id int not null auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

为什么？因为MySQL是通过select max(auto\_increment\_column) from table的方式来确定自增列的最大值，然后按照自增间隔，确定下一个自增值，有索引才能保证无论表数据量多大都能快速找到自增最大值，具体原理可以研究一下索引二叉树原理。

例如前面的test表，我们可以通过show create table的方式查看下一个自增数值：

同时也可以通过tables系统表去查看：

mysql> select auto_increment from information_schema.tables where table_schema='sam' and table_name='test';
    +----------------+
    | auto_increment |
    +----------------+
    |              4 |
    +----------------+
    1 row in set (0.00 sec)

但在MySQL8.0以后，tables表信息可能会不准确，具体原因及解决方法请移步往期博文《MySQL8.0的information\_schema.tables信息不准确怎么办》。

auto\_increment有个倔脾气，能伸不能屈，就算数据没插入成功。

mysql> select * from test;
    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |    1 |
    |    2 |    2 |
    |    3 |    3 |
    |    4 |    1 |
    |    5 |    1 |
    |    6 |    1 |
    |    7 |    3 |
    +------+------+
    7 rows in set (0.00 sec)
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` int(11) NOT NULL AUTO_INCREMENT,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`col1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into test values (null,4),(7,5),(null,6);
    ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` int(11) NOT NULL AUTO_INCREMENT,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`col1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into test values (null,7);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from test;
    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |    1 |
    |    2 |    2 |
    |    3 |    3 |
    |    4 |    1 |
    |    5 |    1 |
    |    6 |    1 |
    |    7 |    3 |
    |   11 |    7 |
    +------+------+
    8 rows in set (0.00 sec)

如上例子，插入三条记录(null,4),(7,5),(null,6)，但由于7发生了主键冲突，所以三条记录都被rollback了，数据当然是没插入成功，但auto\_increment仍然是递增到了11，当下一次正常插入时，递增列就是11了，出现了断层。同样，如果是delete了数据，auto\_increment也是不会自动回收的。

mysql> delete from test where col1>5;
    Query OK, 3 rows affected (0.01 sec)
    
    mysql> select * from test;
    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |    1 |
    |    2 |    2 |
    |    3 |    3 |
    |    4 |    1 |
    |    5 |    1 |
    +------+------+
    5 rows in set (0.00 sec)
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` int(11) NOT NULL AUTO_INCREMENT,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`col1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> truncate table test;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` int(11) NOT NULL AUTO_INCREMENT,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`col1`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into test values (null,10);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test;
    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |   10 |
    +------+------+
    1 row in set (0.00 sec)

如上例子，delete了部分数据，auto\_increment还是12，只有通过truncate方式，才能初始化auto\_increment。

我们可能会遇到某些表出现自增列递增到了一个异常大，亿级别的数值，远远超过表数据量，如果排除了以上的两个例子，还有什么原因呢？这就涉及到MySQL的auto\_inc锁类型了。

这个锁类型由参数innodb\_autoinc\_lock\_mode决定，取值为0|1|2，分别代表表级类型锁，连续类型锁，交叉锁。一脸懵逼？

先讲一讲相关概念：类插入语句（insert-like statement）  
类插入语句也就是那些会在表中产生新数据行的语句，例如insert,insert…select,replace,replace…select,load data等等，而MySQL将类插入语句分为三个类型：  
1.简单插入  
简单插入也就是插入的行数是能提前预知，例如单行或多行的insert，replace不带子查询，但insert into …on duplicate key update不属于简单插入；  
2.离散插入（bulk insert，就这么叫吧）  
离散插入就是那些不能提前预知行数的插入，例如insert into …select,replace…select,load data等；  
3.混合插入  
只有两种插入属于这种类型：  
`1.insert into table values (null,1),(null,4),(6,3),(null,3);`，就是auto\_increment部分指定具体值，部分不指定；  
`2.insert...on duplicate key update`

那么，  
当innodb\_autoinc\_lock\_mode=0表示表级锁，也就是说，以上三种类型的插入，加入有两个事物A,B，事物A在插入时会申请一个表级锁，只有获取到了所有需要的自增值才会释放（但不需要等待事物结束），事物B这时才能执行插入，也就是说，事物A的语句所产生的自增值是连续，而不会在中间被事物B插一个值进来，例如事物A执行insert…select插入多行，事物B也执行insert…select插入多行，事物A先获取一段连续的自增值，然后再到事物B去获取一段连续的自增值，这样对于statement-base的binlog是安全的。

当innodb\_autoinc\_lock\_mode=1表示连续锁，对于简单插入，会按照插入的行数分配所需的自增值，不会产生表锁；对于混合插入，也是直接分配一定的行数，不产生表锁，但由于一些行指定auto\_increment的值，而一个行未指定，而分配的自增列值必须是连续，所以分配的自增区间可能会比实际插入行数大，例如向一个空表执行离散插入：

insert into table values (null,1),(null,4),(6,3),(null,3);

插入(null,1),(null,4)后，下一行自增值应该是3，但(6,3),制定了自增值是6，所以最后的(null,3)实际插入的数据应该是（7,3），这样所分配的自增区间是1-7了，而插入的数据只有4行；  
对于离散插入，会产生表锁，因为MySQL在连续锁的模式下，需要保证一个语句内产生的自增值是连续的，这样才能保证statement-base格式binlog的安全。而对于离散插入，MySQL并不知道将要插入的行数是多少，也就不能提前分配自增区间，所以索性采用表锁，让离散插入取够了自增值，再让其他语句继续插入。

当innodb\_autoinc\_lock\_mode=2表示交叉锁，即不做任何限制，允许不同语句之间交叉插入，例如在事务A执行insert…select的时候，事务B可以任意执行插入，这样自增值的产生就存在不确定性，对于statement-base格式的binlog是不安全。

在5.7，innodb\_autoinc\_lock\_mode默认为1；在8.0以后，innodb\_autoinc\_lock\_mode默认是2，所以建议binlog\_format用row格式。

auto\_inc锁类型的缘故，导致在表行数未改变的情况下，自增值不断地变大：  
1.replace

mysql> show create table tab\G
    *************************** 1. row ***************************
           Table: tab
    Create Table: CREATE TABLE `tab` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `col` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col` (`col`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into tab values (null,1);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into tab values (null,2);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into tab values (null,3);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from tab;
    +----+------+
    | id | col  |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> replace into tab values (null,3);
    Query OK, 2 rows affected (0.01 sec)
    
    mysql> select * from tab;
    +----+------+
    | id | col  |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  4 |    3 |
    +----+------+
    3 rows in set (0.00 sec)

如上例子，实验环境是5.7，innodb\_autoinc\_lock\_mode是1。col列是唯一键，replace into tab values (null,3)发现表里已经有了重复的数据，所以内部的操作是先删除原来的数据，然后再执行插入。数据行没变，id却变了。

2.insert…on duplicate key update

mysql> insert into test1 values (null,1,1),(null,2,2),(null,3,3);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `col1` int(11) DEFAULT NULL,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col1` (`col1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into test1 values (null,3,3) on duplicate key update col2=col2+1; 
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +----+------+------+
    | id | col1 | col2 |
    +----+------+------+
    |  1 |    1 |    1 |
    |  2 |    2 |    2 |
    |  3 |    3 |    4 |
    +----+------+------+
    3 rows in set (0.00 sec)
    
    mysql> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `col1` int(11) DEFAULT NULL,
      `col2` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col1` (`col1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> insert into test1 values (null,4,4);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test1;
    +----+------+------+
    | id | col1 | col2 |
    +----+------+------+
    |  1 |    1 |    1 |
    |  2 |    2 |    2 |
    |  3 |    3 |    4 |
    |  5 |    4 |    4 |
    +----+------+------+

如上例子，实验环境是5.7，innodb\_autoinc\_lock\_mode是1。col1列是唯一键，insert into test1 values (null,3,3) on duplicate key update col2=col2+1发现col1=3已经存在，所以放弃插入，而是更新col2列为4。同时，我们可以看到，在数据行没变的情况下，auto\_increment增加至了5，再插入新数据的时候，就出现了id列的断层。  
insert into test1 values (null,3,3) on duplicate key update col2=col2+1只是执行update操作，并没有实际的新数据插入，但在innodb\_autoinc\_lock\_mode=1的模式下，对于这种混合插入是按需分配，在这种情况下，MySQL以为这种语句会插入一行，所以就分配了一个递增值，但这个语句并没有实际的插入，所以这个递增值就白白浪费了。  
其实这种情况下，只要将innodb\_autoinc\_lock\_mode设为0，使得insert into test1 values (null,3,3) on duplicate key update col2=col2+1执行时按照实际需要去获取自增值，就不会造成递增值浪费，但缺点是会产生表锁，降低并发性能。

到此，对于自增列断层以及auto\_inc锁，我们可以总结些经验：  
1.binlog用row格式，MySQL8.0以后，innodb\_autoinc\_lock\_mode已经默认是2了；  
2.对于不是简单插入的语句，考虑一下是否会引起自增列的暴涨；  
3.自增列根据实际情况，赋予容量足够的数据类型，因为自增列达到最大值后不会重置。