MySQL自增id溢出

标签: 数据库  mysql  数据库

前言

线上推送业务的数据库出现连接数激增,同时出现推送内容丢失。综合两种情况,查询推送表发现自增id已经增加到21亿+。初步判断应该是自增id过多影响连接数,但是有多个问题不太理解(如自增id为什么不连续、int(11)会在多大数据时溢出、为什么溢出后会出现mysql连接数激增),所以写下这篇文章加深理解。

自增id为什么不是连续的

  1. 唯一键冲突
  2. 事务回滚
  3. 批量插入,分配自增id不连续
对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:
* 语句执行过程中,第一次申请自增 id,会分配 1 个;
* 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
* 2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;
* 依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。
如果一次性批量插入4组数据,此时会申请7个自增id,多余的3个就浪费了,所以会出现不连续的情况。

int(11)会在多大数据时溢出

如果根据int(11),认为自增id超过99999999999才会溢出是有问题的。整数型和字符型不同,长度是固定的,也就是说int(1)和int(11)储存的最大值是一样的。数据的最大值只和类型有关(tinyint, int, bigint)和括号内的数字无关。各类型的最大长度如下

类型 字节数 范围
TINYINT 1 -128~127
SMALLINT 2 -32 768~32 767
MEDIUMINT 3 -8 388 608~8 388 607
INT 4 -2 147 483 648~2 147 483 647
BIGINT 8 -9 223 372 036 854 775 808~9 223 372 036 854 775 807

溢出后为什么会出现mysql连接数迅速增加

表定义的自增值达到上限后的逻辑是:再申请下一个 id 时,得到的值保持不变。那如果多个线程拿到多个相同的自增id去INSERT会怎么样呢?这里参考MySQL官方文档
例子如下:

-- table structure
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

-- Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

-- Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

-- Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

-- Session 1:
ROLLBACK;

session1拿到了exclusive锁(X锁),session2和session3发生duplicate-key错的时候,同时去请求shared锁。当session1回滚,它释放X锁,此时session2和session3同时获得shared锁(S锁),并同时去请求X锁。引起了死锁。我们看一下这个持有和竞争的关系:

锁冲突图片

S X
S 不冲突 冲突
x 冲突 冲突

因为X和S锁是互斥的,session2想要X锁,必须等待session3的S锁释放, session3想要获得X锁也要session2释放S锁,这个时候构成了环路等待,引起了死锁。

版权声明:本文为sjy8207380原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sjy8207380/article/details/104108815