MySQL中有很多的自增id,对于不同的自增id,MySQL的处理策略是不同的,而且对于所有的策略,都是定义了一个初始值,然后不停的往上增加步长,虽然自然数是没有上限的,但是在计算机中,只要一个数有字节上限,就会有达到的一天,比如一个int就是 2的32次方-1
既然自增id有上限,那么就会用完,今天看下自增id的处理策略
1.表定义的自增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’ |
在插入第一个达到最大值的时候,其AUTO_INCREMENT没有改变,但是插入第二个时候又拿到了相同的自增id值,在试图插入语句,会爆出主键冲突错误
2的32次方-1 不是一个特别大的数,对于一个频繁插入删除数据的表来说,很可能被用完的,因此在使用主键id的时候可以创建为8字节的bigint unsigned
2.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字节的无符号整型,但是在InnoDB设计的时候,row_id留的只是一个6字节的长度,这样只有最后6个字节放了进去
所以
1.row_id写入表中值的范围,是从0到2的48次方-1
2.当dict_sys.row_id=2的48次方的时候,如果在有插入的行为申请row_id时候,下一个值就0
这个值会循环0到2的48次方-1
当然这个2的48次方本身是很大的,但是一个MySQL如果跑的够久,是可能出现的,如果出现了重复的row_id,就会让新的行覆盖到原来的行
可以利用gdb来修改系统的row_id来模拟覆盖的问题
可以看出新插入的行的row_id已经变为了0和1
于是覆盖了第一行的value
从这个角度的话,还是在InnoDB表中设置一个自增主键,在表自增id中到达上限后,在进行插入的话,是会报错的
因为,覆盖数据的情况,会更加的可怕
3.Xid
在redo log和binlog相配合的时候,有一个相同字段叫做Xid
Xid是怎么生成的呢?内部维护了一个global_query_id,每次执行的时候将其赋予给Query_id 然后进行加一
如果这个语句是第一条语句,那么还是会把Query_id赋值给这个事务的Xid
但是MySQL重启后生成一个新的binlog文件,保证了一个同一个binlog中Xid是唯一的
但是global_query_id还是可能达到上限的,所以有相同Xid的情况,但可能性不高
4.InnoDB trx_id
InnoDB这个 trx_id是为了维护事务可见性的,InnoDB内部维护了一个max_trx_id的全局变量,每次需要更新新的trx_id时候,就会获取这个值,然后进行加1
当一个事务读到一个数据的时候,判断这个数据是否可见,就通过这个trx_id
可以通过information_schema.innodb_trx表看到这个trx_id
从sessionB来看innodb_trx表里查出这两个字段,两个字段trx_mysql_thread.id就是线程id,标定了session A所在线程
可以看出 T2时刻的trx_id是一个很大的值,T4是一个很小的值
对于T1时刻,只是一个读取语句,不涉及更新,对于T1时刻,trx_id值没有被赋予
T2时刻,才会真正的分配了trx_id,所以T4时刻,sessionB查到了trx_id值是1289
这就是只读语句不会进行赋予trx_id
而且很多时候表的内部事务,比如索引信息统计,可能不会按照1递增的
或者删除数据的时候,可能数据放在purge队列等待物理删除,所以事务id也会增加
那么T2时刻这个很大的值是怎么回事的呢
其实这个数字是系统临时计算的,其算法为,把这个事务的trx变量的指针的地址变为了整数,再加上2的48次方,
这样就能保证了
1.同一个事务中,内存指针是不会变的,于是能够保证trx_id是一样的
2.如果有并行的多个只读事务,那么每个事务的trx变量的指针地址是不同的,这样,不同并发只读事务,查出来的trx_id是不同的
为什么要加上2的48次方呢
为了就是保证只读事务和读写事务之间的区别,一眼能够认出来,但是一个MySQL执行的时候,如果足够长,那么会出现trx_id相同的情况,不过概率很低
只读事务部分配trx_id的话,可以减少事务中活跃的事务数组的大小,所以让MySQL只专注读写事务的trx_id
而且减少了trx_id的分配次数,避免了trx_id的申请的锁的冲突
但是 max_trx_id会持久化存储,于是一个MySQL跑的足够就,就可能出现max_trx_id到达2的48次方了,
如果到达了,会从0重新计数,就会出现一个脏读的问题
首先把max_trx_id修改为2的48次方-1
然后就会因为后来修改的事务的trx_id比之前的小,从一直出现了脏读的问题
因为事务一直持续增加,但是事务id从0开始增加,于是会持续出现脏读问题
这个问题,MySQL认为,每秒的TPS是50万,那么也会在17.8年后出现,所以暂时没必要担心
5.thread_id问题
线程id,是MySQL中最为常见的一种自增id,show processlist中的第一列,就是thread_id
这个逻辑好理解,就是系统粗暴拿了一个全局变量 thread_id_counter,每次新建一个线程,就会将thread_id_counter赋值给这个新连接的线程变量
这个变量的大小是4字节,如果达到了2的32次方-1,就会重置为0,然后继续增加
因为MySQL设置了一个唯一数组逻辑,这个线程分配thread_id的时候,逻辑代码是这样的
总结一下,
1.表的自增主键id到了上限后,不会在改变,但是继续插入数据会报主键冲突的错误
2.row_id达到了上限之后,归0会重新递增,出现row_id,会覆盖之前的数据
3.Xid只需要保证不在同一个binlog中出现了重复值
4.InnoDB trx_id,出现用完的可能性很低
5.thread_id,分组策略,每次使用完成加一个前缀,很好