线上MySQL的自增id用尽怎么办？
码农编程进阶笔记
共 8005字，需浏览 17分钟
·
2022-08-08 15:16
表定义自增值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值
row_id用完的验证序列
row_id用完的效果验证
Xid
每次执行语句时，将它赋值给query_id，然后给该变量+1：
若当前语句是该事务执行的第一条语句，则MySQL还会同时把query_id赋值给该事务的Xid：
Innodb trx_id
Xid由server层维护
InnoDB数据可见性的核心思想
S2 的执行记录：mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 421972504382792 | 70 |
+-----------------+---------------------+
1 row in set (0.00 sec)
mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+---------+---------------------+
| trx_id | trx_mysql_thread_id |
+---------+---------------------+
| 1355623 | 70 |
+---------+---------------------+
1 row in set (0.01 sec)
t1时，trx_id的值就是0。而这个很大的数，只是显示用 直到S1在t3时执行insert，InnoDB才真正分配trx_id。所以t4时，S2查到该trx_id的值就是1289。
update 和 delete语句除了事务本身，还涉及到标记删除旧数据，即要把数据放到purge队列里等待后续物理删除，这个操作也会把max_trx_id+1， 因此在一个事务中至少加2 InnoDB的后台操作，比如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx_id值并不是按照加1递增的。
t2时查到的很大数字是怎么来的？
每次查询时，由系统临时计算：
当前事务的trx变量的指针地址转成整数，再加上248
这样可以保证：
因为同一只读事务在执行期间，它的指针地址不会变，所以无论在 innodb_trx还是在innodb_locks表里，同一个只读事务查出来的trx_id就会是一样的 若有并行只读事务，每个事务的trx变量的指针地址肯定不同。这样，不同并发只读事务，查出来的trx_id就是不同的。
为什么要加248？
为何只读事务不分配trx_id？
减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务，不影响数据的可见性判断。所以，在创建事务的一致性视图时，InnoDB就只需要拷贝读写事务的trx_id
减少trx_id的申请次数。InnoDB执行一个普通的select语句，也要对应一个只读事务。所以只读事务优化后，普通查询语句无需申请trx_id，大大减少并发事务申请trx_id的锁冲突
因为系统的max_trx_id被设置成2^48 - 1，所以在session A启动的事务TA的低水位就是2^48 - 1。
t2时:
session B执行第一条update语句的事务id=2^48 - 1
第二条事务id就是0了，这条update执行后生成的数据版本上的trx_id=0
t3时：
thread_id
系统保存了一个全局变量thread_id_counter
给新线程分配thread_id时的逻辑：
总结
评论