线上MySQL的自增id用尽怎么办?
表定义自增值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数据可见性的核心思想
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时的逻辑:
总结
评论