关于mysql:MySQL学习笔记13自增id

1次阅读

共计 1990 个字符，预计需要花费 5 分钟才能阅读完成。

自增 id：定义初始值，而后不停往上加步长。其下限取决于字节长度，如无符号整型 (unsigned int) 是 4 个字节，id 下限就是 2^32-1。

1、表定义的自增值达到下限后的逻辑是：再申请下一个 id 时，失去的值放弃不变。
2、例子：

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 没有扭转（还是 4294967295），就导致了第二个 insert 语句又拿到雷同的自增 id 值，再试图执行插入语句，报主键抵触谬误。

3、应该创立成 8 个字节的 bigint unsigned。4 字节无符号 42 亿，有符号 21 亿，除非删除极其频繁，不然不可能用完，因为单表存储最多也就千万级，超过时就要分库分表了，而存储记录达千万（下限 1 亿吧），自增 id 达 21 亿时，阐明删除了 20 亿（其它起因导致 id 减少的可能比拟小），新增 1 条就会删除 20 条，这种场景极其常见。

1、InnoDB 表不指定主键时会创立一个不可见的长度为 6 个字节的 row_id。
2、特色：
row_id 写入表中的值范畴，是从 0 到 2^48-1；
当 dict_sys.row_id=2^48 时，如果再有插入数据的行为要来申请 row_id，拿到当前再取最初 6 个字节的话就是 0，而后持续循环。如果表中曾经存在 row_id=N 的行，新写入的行就会笼罩原有的行。
3、应该在 InnoDB 表中被动创立自增主键。

1、MySQL 外部保护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，而后给这个变量加 1。如果以后语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。
2、global_query_id 定义的长度是 8 个字节，这个自增值的下限是 2^64-1，根本等于说不可能达到下限。

1、InnoDB 外部保护了一个 max_trx_id 全局变量，每次须要申请一个新的 trx_id 时，就取得 max_trx_id 的以后值，而后并将 max_trx_id 加 1。
2、InnoDB 数据可见性的核心思想是：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的办法，就是通过事务的一致性视图与这行数据的 trx_id 做比照。
3、算法：把以后事务的 trx 变量的指针地址转成整数，再加上 2^48。
能够保障以下两点：因为同一个只读事务在执行期间，它的指针地址是不会变的，所以不论是在 innodb_trx 还是在 innodb_locks 表里，同一个只读事务查出来的 trx_id 就会是一样的。如果有并行的多个只读事务，每个事务的 trx 变量的指针地址必定不同。这样，不同的并发只读事务，查出来的 trx_id 就是不同的。

1、零碎保留了一个全局变量 thread_id_counter，每新建一个连贯，就将 thread_id_counter 赋值给这个新连贯的线程变量。
2、thread_id_counter 定义的大小是 4 个字节，因而达到 232-1 后，它就会重置为 0，而后持续减少。
3、为什么不会呈现反复的 thread_id

do {new_id= thread_id_counter++;} while (!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 逻辑了。

正文完