mysql 批量insert_MySQL自增id溢出

作者：Penguin

前言

线上推送业务的数据库出现连接数激增，同时出现推送内容丢失。综合两种情况，查询推送表发现自增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）和括号内的数字无关。各类型的最大长度如下：

溢出后为什么会出现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;

第1处，锁等待

session 1 执行的 insert 语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁（读锁）。session 2和session 3在记录上加写锁的时候会出现锁等待，直到session 1回滚。

第2处，死锁

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

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