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

最新推荐文章于 2024-03-28 00:04:00 发布

遇星

最新推荐文章于 2024-03-28 00:04:00 发布

阅读量3.1k

点赞数 5

分类专栏： MySQL 文章标签： MySQL 开源数据库 auto_inrement 锁

本文链接：https://blog.csdn.net/weixin_39004901/article/details/84871909

版权

MySQL 专栏收录该内容

99 篇文章 3 订阅

订阅专栏

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的方式查看下一个自增数值：

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=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

同时也可以通过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.自增列根据实际情况，赋予容量足够的数据类型，因为自增列达到最大值后不会重置。

遇星

关注

5
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL auto_increment介绍及自增键断层的原因分析

MySQL里面有一个列特性，auto_increment，自增长列，可以通过auto_increment_increment设置递增间隔，auto_increment_offset设置初始值。理论上，如果列容量足够大，那么auto_increment可以无限的伸长，可谓是男人的榜样。什么？听见了发动机的声音？不不不，我们是正规的城市公交，上车别忘了打卡。下面，有请auto_increment同...
复制链接

扫一扫