Notes

Exploring note

0%

自增id

发表于 更新于 分类于
本文字数： 5.3k 阅读时长 ≈ 9 分钟
自增id相关问题

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

表定义自增值id

表结构定义中的自增字段。
MySQL-自增主键

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

1
2
3
4
5
6
7
8
9
10
11
12
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没有改变，就导致第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误。

在建表的时候需要考虑表是否有可能达到232-1这个上限，如果有可能，就应该创建成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到248-1；
  2. 当dict_sys.row_id=248时，如果再有插入数据的行为要来申请row_id，拿到以后再取最后6个字节的话就是0。

写入表的row_id是从0开始到248-1。达到上限后，下一个值就是0，然后继续循环。在InnoDB逻辑里，申请到row_id=N后，就将这行数据写入表中；如果表中已经存在row_id=N的行，新写入的行就会覆盖原有的行。

通过gdb修改系统自增row_id。 

1
2
3
4
5
6
7
mysql>create table table t(a int) engine=innodb;  
gdb -p <pid of mysqld> -ex 'p dict_sys.row_id=1' --batch  
mysql>insert into t values(1);  
gdb -p <pid.mysqld> -ex 'p dict_sys.row_id=281474976710656' --batch  
mysql>insert into t values(2);  
mysql>insert into t values(3);  
mysql>select * from t;

用gdb将dict_sys.row_id设置为248之后，再插入的a=2的行会出现在表t的第一行，因为这个值的row_id=0。之后在插入的a=3的行，由于row_id=1，就覆盖了之前a=1的行，因为a=1这一行的row_id也是1。

在InnoDB表中主动创建自增主键，表自增id到达上限后，再插入数据时报主键冲突错误，更能被接受。覆盖数据，则意味着数据丢失，影响的是数据可靠性；报主键冲突，是插入失败，影响的是可用性。一般情况下，可靠性优先于可用性。

Xid

redo log和binlog相配合的时候，它们有一个共同的字段叫做Xid，它在MySQL中是用来对应事务的。
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个字节，这个自增值的上限是264-1。重复的可能性质存在于理论上。

  1. 执行一个事务，假设Xid是A；
  2. 接下来执行264次查询语句，让global_query_id回到A；
  3. 再启动一个事务，这个事务的Xid也是A。

Innodb trx_id

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

Xid是由server层维护的。InnoDB内部使用Xid，是为了能够在InnoDB事务和server之间做关联。InnoDB自己的trx_id是另外维护的。事务可见性用到的事务id(transaction id)。
MySQL-隔离事务

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

InnoDB数据可见性的核心思想是：每一行数据都记录了更新它的trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的trx_id做对比。

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

sessionA sessionB
T1 begin;
select * from t limit 1;
T2 use information_schema;
select trx_id, trx_mysql_thread_id from innodb_trx;
trx_id=421578461423440
trx_mysql_thread_id=5
T3 insert into t values(null);
T4 select trx_id, trx_mysql_thread_id from innodb_trx;
trx_id=1289
trx_mysql_thread_id=5

sessionB中，从innodb_trx表里查出两个字段，trx_mysql_thread_id表示线程id，说明这两次查询看到的事务对应的线程id都是5，就是sessionA所在的线程。

在T1时刻，sessionA还没有涉及到更新，是一个只读事务(select…for update不是只读事务)。对于只读事务，InnoDB并不会分配trx_id。

  1. 在T1时刻，trx_id的值其实就是0。T2中这个很大的数，只是显示用的。
  2. 直到sessionA在T3时刻执行insert语句的时候，InnoDB才真正分配了trx_id。T4时刻，sessionB查到的这个trx_id的值就是1289。

显示的事务id不止加1

  1. update和delete语句除了事务本省，还涉及到标记删除旧数据，也就是要把数据放到purge队列里等待后续物理伤处，这个操作也会把max_trx_id+1，因此在一个事务中至少加2；
  2. InnoDB的后台操作，如表的索引信息统计这类操作，也会启动内部事务。综合导致看到的trx_id值并不是按照加1递增的。

只读事务超大事务id

这个数字是每次查询的时候有系统临时计算出来的。算法是：把当前事务的trx变量的指针地址转成整数，再加上248。

  1. 因为同一个只读事务在执行期间，它的指针地址是不会变的，所以不论是在innodb_trx还是在innodb_locks表里，同一个只读事务查出来的trx_id就会是一样的。
  2. 如果有并行的多个只读事务，每个事务的trx变量的指针地址肯定不同。这样，不同的并发只读事务，查出来的trx_id就是不同的。

在显示值里面加上248，目的是要保证只读事务显示的trx_id值比较大，正常情况下就会区别于读写事务的id。

trx_id跟row_id的逻辑类似，定义长度也是8个字节，在理论上还是可能出现一个读写事务与一个只读事务显示的trx_id相同的情况。概率低，危害小，可忽略。

只读事务不分配trx_id好处

  1. 这样做可以减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务，是不影响数据的可见性判断的。所以在创建事务的一致性视图时，InnoDB就只需要拷贝读写事务的trx_id。
  2. 可以减少trx_id的申请次数。在InnoDB里，即使只是执行一个普通的select语句，在执行过程中，也是要对应一个只读事务的。只读事务优化后，普通的查询语句不需要申请trx_id，大大减少了并发事务申请trx_id的锁冲突。

由于只读事务不分配trx_id，trx_id的增加速度变慢了。

脏读bug

max_trx_id会持久化存储，重启也不会重置为0，从理论上讲，只要一个MySQL服务跑的足够久，就可能出现max_trx_id达到248-1的上限，然后从0开始的情况。 

1
2
3
4
mysql>create table t(id int primary key, c int)engine=innodb;  
mysql>insert into t values(1,1);  
gdb -p <pid.mysqld> -ex 'p trx_sys->max_trx_id=281474976710655' --batch  
// 将max_trx_id的值修改为2<sup>48</sup>-1
sessionA sessionB
T1 begin;
select * from t;//TA
id=1;c=1;
T2 update t set c=1 where id=1;
begin;
update t set c=3 where id=1;
T3 select * from t;
id=1;c=3;//脏读

在T1时刻，由于已经把系统的max_trx_id设置成了248-1，所以在sessionA启动的事务TA的低水位就是248-1。

在T2时刻，sessionB执行第一条update语句的事务id就是248-1，而第二条update语句的事务id就是0了，这条update语句执行后生成的数据版本上的trx_id就是0。

在T3时刻，sessionA执行select语句的时候，判断可见性发现，c=3这个数据版本的trx_id，小于事务TA的低水位，因此认为这个数据可见。但这个是脏读。

由于低水位值会持续增加，而事务id从0开始计数，就导致了系统在这个时刻之后，所有的查询斗湖出现脏读。并且MySQL重启时max_trx_id也不会清0，重启MySQL，这个bug仍然存在。这个bug只要MySQL实例服务时间够长，就会必然出现。

thread_id

线程id是MySQL中最常见的一种自增id。平时在查各种现场的时候，show processlist里面的第一列，就是thread_id。

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

thread_id_counter定义的大小是4个字节，达到232-1后，就会重置为0，然后继续增加。但是在show processlist中不会看到两个相同的thread_id。

MySQL设计了一个唯一数据的逻辑，给新线程分配thread_id的时候，逻辑代码如下： 

1
2
3
do {
    new_id = thread_id_counter++;  
} where (!thread_ids.insert_unique(new_id).second);

总结

  1. 表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
  2. row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。
  3. Xid只需要不在同一个binlog文件中出现重复值即可。理论上会出现重复值的概率极小，可忽略不计。
  4. InnoDB的，max_trx_id递增值每次MySQL重启都会被保存起来，脏读的是一个必先的bug。
  5. thread_id是使用中最常见的，也是处理的最好的一个自增id逻辑。