线上MySQL的自增id用尽怎么办?

发布时间:2023-01-10 13:30

MySQL的自增id都定义了初始值,然后不断加步长。虽然自然数没有上限,但定义了表示这个数的字节长度,计算机存储就有上限。比如,无符号整型(unsigned int)是4个字节,上限就是2^32 - 1。那自增id用完,会怎么样?

线上MySQL的自增id用尽怎么办?_第1张图片

表定义自增值id

表定义的自增值达到上限后的逻辑是:再申请下一个id时,得到的值保持不变。

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)

mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//成功插入一行 4294967295
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

第一个insert成功后,该表的AUTO_INCREMENT还是4294967295,导致第二个insert又拿到相同自增id值,再试图执行插入语句,主键冲突。

2^32 - 1(4294967295)不是一个特别大的数,一个频繁插入删除数据的表是可能用完的。建表时就需要考虑你的表是否有可能达到该上限,若有,就应创建成8字节的bigint unsigned。

InnoDB系统自增row_id

若你创建的InnoDB表未指定主键,则InnoDB会自动创建一个不可见的,6个字节的row_id。InnoDB维护了一个全局的dict_sys->row_id值

线上MySQL的自增id用尽怎么办?_第2张图片

所有无主键的InnoDB表,每插入一行数据,都将当前的dict_sys->row_id作为要插入数据的row_id,然后把dict_sys->row_id加1。

代码实现时row_id是个长度为8字节的无符号长整型(bigint unsigned)。但InnoDB在设计时,给row_id留的只是6个字节的长度,这样写到数据表中时只放了最后6个字节,所以row_id能写到数据表中的值,就有两个特征:

  1. row_id写入表中的值范围,是从0到2^48 - 1
  2. 当dict_sys.row_id=2^48时,如果再有插入数据的行为要来申请row_id,拿到以后再取最后6个字节的话就是0

即写入表的row_id从0~2^48 - 1。达到上限后,下个值就是0,然后继续循环。

2^48 - 1已经很大,但若一个MySQL实例活得久,还是可能达到上限。

InnoDB里,申请到row_id=N后,就将这行数据写入表中;若表中已经存在row_id=N的行,新写入的行就会覆盖原有的行。

验证该结论:通过gdb修改系统的自增row_id。用gdb是为了便于复现问题,只能在测试环境使用。

  • row_id用完的验证序列

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号