MySQL实战45讲学习笔记：第四十五讲 自增id用完怎么办

MySQL⾥有很多⾃增的id，每个⾃增id都是定义了初始值，然后不停地往上加步⻓。虽然⾃然数是没有上限的，但是在计算机 ⾥，只要定义了表示这个数的字节⻓度，那它就有上限。⽐如，⽆符号整型(unsigned int)是4个字节，上限就是2 -1。

既然⾃增id有上限，就有可能被⽤完。但是，⾃增id⽤完了会怎么样呢？ 今天这篇⽂章，我们就来看看MySQL⾥⾯的⼏种⾃增id，⼀起分析⼀下它们的值达到上限以后，会出现什么情况。

表定义⾃增值id

说到⾃增id，你第⼀个想到的应该就是表结构定义⾥的⾃增字段，也就是我在第39篇⽂章《⾃增主键为什么不是连续的？》中 和你介绍过的⾃增主键id。

表定义的⾃增值达到上限后的逻辑是：再申请下⼀个id时，得到的值保持不变。

我们可以通过下⾯这个语句序列验证⼀下：

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
//成功插⼊⼀⾏ 4294967295

show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

insert into t values(null);
//Duplicate entry '4294967295' for key '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值，所有⽆主键的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改变量这个操作是为了便于我们复现问 题，只能在测试环境使⽤。

 图1 row_id⽤完的验证序列

 图2 row_id⽤完的效果验证

可以看到，在我⽤gdb将dict_sys.row_id设置为2^48 之后，再插⼊的a=2的⾏会出现在表t的第⼀⾏，因为这个值的row_id=0。之 后再插⼊的a=3的⾏，由于row_id=1，就覆盖了之前a=1的⾏，因为a=1这⼀⾏的row_id也是1。

从这个⻆度看，我们还是应该在InnoDB表中主动创建⾃增主键。因为，表⾃增id到达上限后，再插⼊数据时报主键冲突错误， 是更能被接受的。

毕竟覆盖数据，就意味着数据丢失，影响的是数据可靠性；报主键冲突，是插⼊失败，影响的是可⽤性。⽽⼀般情况下，可靠 性优先于可⽤性。

Xid

在第15篇⽂章《答疑⽂章（⼀）：⽇志和索引相关问题》中，我和你介绍redo log和binlog相配合的时候，提到了它们有⼀个 共同的字段叫作Xid。它在MySQL中是⽤来对应事务的。

那么，Xid在MySQL内部是怎么⽣成的呢？

MySQL内部维护了⼀个全局变量global_query_id，每次执⾏语句的时候将它赋值给Query_id，然后给这个变量加1。如果当前 语句是这个事务执⾏的第⼀条语句，那么MySQL还会同时把Query_id赋值给这个事务的Xid。

⽽global_query_id是⼀个纯内存变量，重启之后就清零了。所以你就知道了，在同⼀个数据库实例中，不同事务的Xid也是有 可能相同的。

但是MySQL重启之后会重新⽣成新的binlog⽂件，这就保证了，同⼀个binlog⽂件⾥，Xid⼀定是惟⼀的。

虽然MySQL重启不会导致同⼀个binlog⾥⾯出现两个相同的Xid，但是如果global_query_id达到上限后，就会继续从0开始计 数。从理论上讲，还是就会出现同⼀个binlog⾥⾯出现相同Xid的场景。

因为global_query_id定义的⻓度是8个字节，这个⾃增值的上限是2^64 -1。要出现这种情况，必须是下⾯这样的过程：

1. 执⾏⼀个事务，假设Xid是A；

2. 接下来执⾏2^64 次查询语句，让global_query_id回到A；

3. 再启动⼀个事务，这个事务的Xid也是A。

不过，2^64 这个值太⼤了，⼤到你可以认为这个可能性只会存在于理论上。

Innodb trx_id

Xid和InnoDB的trx_id是两个容易混淆的概念。

Xid是由server层维护的。InnoDB内部使⽤Xid，就是为了能够在InnoDB事务和server之间做关联。但是，InnoDB⾃⼰的 trx_id，是另外维护的。

其实，你应该⾮常熟悉这个trx_id。它就是在我们在第8篇⽂章《事务到底是隔离的还是不隔离的？》中讲事务可⻅性时，⽤到 的事务id（transaction id）。

InnoDB内部维护了⼀个max_trx_id全局变量，每次需要申请⼀个新的trx_id时，就获得max_trx_id的当前值，然后并将 max_trx_id加1。

InnoDB数据可⻅性的核⼼思想是：每⼀⾏数据都记录了更新它的trx_id，当⼀个事务读到⼀⾏数据的时候，判断这个数据是否 可⻅的⽅法，就是通过事务的⼀致性视图与这⾏数据的trx_id做对⽐。

对于正在执⾏的事务，你可以从information_schema.innodb_trx表中看到事务的trx_id。

我在上⼀篇⽂章的末尾留给你的思考题，就是关于从innodb_trx表⾥⾯查到的trx_id的。现在，我们⼀起来看⼀个事务现场：

 图3 事务的trx_id

session B⾥，我从innodb_trx表⾥查出的这两个字段，第⼆个字段trx_mysql_thread_id就是线程id。显示线程id，是为了说明 这两次查询看到的事务对应的线程id都是5，也就是session A所在的线程。

可以看到，T2时刻显示的trx_id是⼀个很⼤的数；T4时刻显示的trx_id是1289，看上去是⼀个⽐较正常的数字。这是什么原因 呢？

实际上，在T1时刻，session A还没有涉及到更新，是⼀个只读事务。⽽对于只读事务，InnoDB并不会分配trx_id。也就是 说：

1. 在T1时刻，trx_id的值其实就是0。⽽这个很⼤的数，只是显示⽤的。⼀会⼉我会再和你说说这个数据的⽣成逻辑。

2. 直到session A 在T3时刻执⾏insert语句的时候，InnoDB才真正分配了trx_id。所以，T4时刻，session B查到的这个trx_id 的值就是1289。

需要注意的是，除了显⽽易⻅的修改类语句外，如果在select 语句后⾯加上for update，这个事务也不是只读事务。 在上⼀篇⽂章的评论区，有同学提出，实验的时候发现不⽌加1。这是因为：

1. update 和 delete语句除了事务本身，还涉及到标记删除旧数据，也就是要把数据放到purge队列⾥等待后续物理删除，这个操作也会把max_trx_id+1， 因此在⼀个事务中⾄少加2；

2. InnoDB的后台操作，⽐如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx_id值并不是按照加 1递增的。

那么，T2时刻查到的这个很⼤的数字是怎么来的呢？

其实，这个数字是每次查询的时候由系统临时计算出来的。它的算法是：把当前事务的trx变量的指针地址转成整数，再加上 2^48 。使⽤这个算法，就可以保证以下两点：

1. 因为同⼀个只读事务在执⾏期间，它的指针地址是不会变的，所以不论是在 innodb_trx还是在innodb_locks表⾥，同⼀个 只读事务查出来的trx_id就会是⼀样的。

2. 如果有并⾏的多个只读事务，每个事务的trx变量的指针地址肯定不同。这样，不同的并发只读事务，查出来的trx_id就是 不同的。

那么，为什么还要再加上2^48 呢？

在显示值⾥⾯加上2^48 ，⽬的是要保证只读事务显示的trx_id值⽐较⼤，正常情况下就会区别于读写事务的id。但是，trx_id跟 row_id的逻辑类似，定义⻓度也是8个字节。因此，在理论上还是可能出现⼀个读写事务与⼀个只读事务显示的trx_id相同的情 况。不过这个概率很低，并且也没有什么实质危害，可以不管它。

另⼀个问题是，只读事务不分配trx_id，有什么好处呢？

⼀个好处是，这样做可以减⼩事务视图⾥⾯活跃事务数组的⼤⼩。因为当前正在运⾏的只读事务，是不影响数据的可⻅性 判断的。所以，在创建事务的⼀致性视图时，InnoDB就只需要拷⻉读写事务的trx_id。

另⼀个好处是，可以减少trx_id的申请次数。在InnoDB⾥，即使你只是执⾏⼀个普通的select语句，在执⾏过程中，也是要 对应⼀个只读事务的。所以只读事务优化后，普通的查询语句不需要申请trx_id，就⼤⼤减少了并发事务申请trx_id的锁冲 突。

由于只读事务不分配trx_id，⼀个⾃然⽽然的结果就是trx_id的增加速度变慢了。

但是，max_trx_id会持久化存储，重启也不会重置为0，那么从理论上讲，只要⼀个MySQL服务跑得⾜够久，就可能出现 max_trx_id达到2^48-1的上限，然后从0开始的情况。

当达到这个状态后，MySQL就会持续出现⼀个脏读的bug，我们来复现⼀下这个bug。

⾸先我们需要把当前的max_trx_id先修改成2^48 -1。注意：这个case⾥使⽤的是可重复读隔离级别。具体的操作流程如下：

 

 

 图 4 复现脏读

由于我们已经把系统的max_trx_id设置成了2^48 -1，所以在session A启动的事务TA的低⽔位就是2^48-1。

在T2时刻，session B执⾏第⼀条update语句的事务id就是2^48 -1，⽽第⼆条update语句的事务id就是0了，这条update语句执⾏ 后⽣成的数据版本上的trx_id就是0。

在T3时刻，session A执⾏select语句的时候，判断可⻅性发现，c=3这个数据版本的trx_id，⼩于事务TA的低⽔位，因此认为 这个数据可⻅。

但，这个是脏读。

由于低⽔位值会持续增加，⽽事务id从0开始计数，就导致了系统在这个时刻之后，所有的查询都会出现脏读的。

并且，MySQL重启时max_trx_id也不会清0，也就是说重启MySQL，这个bug仍然存在。

那么，这个bug也是只存在于理论上吗？

假设⼀个MySQL实例的TPS是每秒50万，持续这个压⼒的话，在17.8年后，就会出现这个情况。如果TPS更⾼，这个年限⾃ 然也就更短了。但是，从MySQL的真正开始流⾏到现在，恐怕都还没有实例跑到过这个上限。不过，这个bug是只要MySQL 实例服务时间够⻓，就会必然出现的。

当然，这个例⼦更现实的意义是，可以加深我们对低⽔位和数据可⻅性的理解。你也可以借此机会再回顾下第8篇⽂章《事务 到底是隔离的还是不隔离的？》中的相关内容。

thread_id

接下来，我们再看看线程id（thread_id）。其实，线程id才是MySQL中最常⻅的⼀种⾃增id。平时我们在查各种现场的时 候，show processlist⾥⾯的第⼀列，就是thread_id。

thread_id的逻辑很好理解：系统保存了⼀个全局变量thread_id_counter，每新建⼀个连接，就将thread_id_counter赋值给这 个新连接的线程变量。

thread_id_counter定义的⼤⼩是4个字节，因此达到2^32 -1后，它就会重置为0，然后继续增加。但是，你不会在show processlist⾥看到两个相同的thread_id。

这，是因为MySQL设计了⼀个唯⼀数组的逻辑，给新线程分配thread_id的时候，逻辑代码是这样的：

do {
new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

这个代码逻辑简单⽽且实现优雅，相信你⼀看就能明⽩。

⼩结

今天这篇⽂章，我给你介绍了MySQL不同的⾃增id达到上限以后的⾏为。数据库系统作为⼀个可能需要7*24⼩时全年⽆休的 服务，考虑这些边界是⾮常有必要的。

每种⾃增id有各⾃的应⽤场景，在达到上限后的表现也不同：

1. 表的⾃增id达到上限后，再申请时它的值就不会改变，进⽽导致继续插⼊数据时报主键冲突的错误。

2. row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。

3. Xid只需要不在同⼀个binlog⽂件中出现重复值即可。虽然理论上会出现重复值，但是概率极⼩，可以忽略不计。

4. InnoDB的max_trx_id 递增值每次MySQL重启都会被保存起来，所以我们⽂章中提到的脏读的例⼦就是⼀个必现的bug， 好在留给我们的时间还很充裕。

5. thread_id是我们使⽤中最常⻅的，⽽且也是处理得最好的⼀个⾃增id逻辑了。

当然，在MySQL⾥还有别的⾃增id，⽐如table_id、binlog⽂件序号等，就留给你去验证和探索了。

不同的⾃增id有不同的上限值，上限值的⼤⼩取决于声明的类型⻓度。⽽我们专栏声明的上限id就是45，所以今天这篇⽂章也 是我们的最后⼀篇技术⽂章了。

既然没有下⼀个id了，课后也就没有思考题了。

今天，我们换⼀个轻松的话题，请你来说说，读完专栏以后有什么感想吧。

这个“感想”，既可以是你读完专栏前后对某⼀些知识点的理解发⽣的变化，也可以是你积累的学习专栏⽂章的好⽅法，当然也 可以是吐槽或者对未来的期望。