表自定义自增id

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

  1. create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
  2. insert into t values(null);
  3. //成功插入一行 4294967295
  4. show create table t;
  5. /* CREATE TABLE `t` (
  6.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
  9. */
  11. insert into t values(null);
  12. //Duplicate entry '4294967295' for key 'PRIMARY'

int类型占4个字节，也就是32位，因此最大到2-1=4294967295。
当插入一行id=4294967295之后，再次执行插入就会报主键冲突。

2-1不是一个特别大的数，对于一个频繁插入数据的表来说，是可能会用完的。因此在建表的时候要考虑到这个表是否会使用到上限，如果有可能，就应该创建成8个字节的bigint unsigned。

InnoDB系统自增row_id

如果你创建的表没有指定主键，那么InnoDB会创建一个不可见的，长度为6个字节的row_id。InnoDB维护了一个全局的dict_sys.row_id，所有无主键的InnoDB表，每插入一行数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，并把dict_sys.row_id加1。

在MySQL代码实现中，row_id是一个8个字节的无符号长整型(bigint unsigned)，但是InnoDB在设计的时候只给row_id留了6个字节，所以row_id能写到数据表中的值有两个特征

  • row_id写入表中值的范围是，从0到2-1
  • 当dict_sys.row_id=2``48``-1的时候，如果再有插入数据的行为，再申请row_id，就会从0重新开始

写入表的row_id是从0到2-1。达到上限之后，下一个值就是0，再重新循环。如果出现这种情况，那么新的row_id=N这行记录会覆盖原来那条记录。

  • 从这个角度看，还是使用自定义的自增id比较好，毕竟当自增id达到上限之后，再次插入数据只会报主键冲突，影响的是可用性；
  • 而InnoDB系统自增的row_id会出现数据覆盖丢失情况，影响的是数据可靠性；
  • 一般可靠性是大于可用性的

    Xid

    redo log和binlog配合来做崩溃恢复的时候，是通过两个日志共有的Xid来联系到一起的。

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

global_query_id是一个内存型变量，每次重启之后就清零了，但是MySQL每次重启之后都会重新生成binlog，也就保证了，同一个binlog文件里，Xid一定是唯一的。

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

global_query_id长度是8个字节，因此自增值的上限是2-1，因此出现上面的情况基本不会。

InnoDB trx_id

Xid是由Server层维护的。InnoDB内部使用Xid，就是为了能够在InnoDB事务和server之间做关联。

InnoDB自己的trx_id是另外维护的。

trx_id是保证事务可见性的。

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

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

  • 对于一个事务，如果只有读事务，那么trx_id就是每次查询的时候由系统临时计算出来的。算法是：把当前事务的trx变量的指针地址转换成整数，再加上2。
  • 如果不涉及写，InnoDB引擎并不会分配trx_id
  • update或者delete语句除了事务本身，还涉及到标记删除旧数据，也就是把数据放到purge队列里等待后续物理删除，这个操作也会把max_trx_id+1，因此在一个事务中，至少+2

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

    只读事务不分配trx_id的好处？

  • 减小事务视图中，活跃事务数组的大小，因为当前运行的只读事务是不影响数据可见性判断的。因此在创建一致性视图的时候，InnoDB就只拷贝读写事务的trx_id

  • 减少trx_id的申请次数。在InnoDB中即使你只是执行一个普通的select语句，在执行过程中，也就是要对应一个只读事务的。只读事务优化后，查询语句不需要去申请trx_id，就大大减小了并发事务申请trx_id的锁冲突
  • max_trx_id的增长速度变慢了

max_trx_id会持久化存储，重启也不会重置为0，因为trx_id在每行记录上都由存储，因此不能置零。

如果max_trx_id到最大值2-1的时候，就会从0重新开始，因此会出现幻读的情况。
自增id用完怎么办？ - 图1
在 T2 时刻，session B 执行第一条 update 语句的事务 id 就是 248-1，而第二条 update 语句的事务 id 就是 0 了，这条 update 语句执行后生成的数据版本上的 trx_id 就是 0。

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

这就是脏读，当事务id从0重新开始的时候，就会导致从这个时刻开始，所有的查询都会出现脏读。

但是这种情况基本上不会出现。

thread_id

线程id，show processlist中的第一列就是。

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

threade_id_counter大小是4个字节，因此达到2-1之后，它就会重置为0.
但是在show processlist中不会看到一样的thread_id，因为，在分配thread_id的时候，判断了如果插入到线程数组中之后的数组中该thread_id的个数是否为2，如果为2就继续循环，直到不重复为止。

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