MySQL自增ID耗尽实例讲解

2023-08-25 16:33:05 76

显示定义ID

表定义的自增值ID达到上限后，在申请下一个ID时，得到的值保持不变

--(2^32-1)=4,294,967,295
--建议使用BIGINTUNSIGNED
CREATETABLEt(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY)AUTO_INCREMENT=4294967295;
INSERTINTOtVALUES(null);

--AUTO_INCREMENT没有改变
mysql>SHOWCREATETABLEt;
+-------+------------------------------------------------------+
|Table|CreateTable|
+-------+------------------------------------------------------+
|t|CREATETABLE`t`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENT,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=4294967295DEFAULTCHARSET=utf8|
+-------+------------------------------------------------------+

mysql>INSERTINTOtVALUES(null);
ERROR1062(23000):Duplicateentry'4294967295'forkey'PRIMARY'

InnoDBrow_id

1、如果创建的InnoDB表没有指定主键，那么InnoDB会创建一个不可见的，长度为6Bytes的row_id

2、InnoDB维护一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行数据

都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值+1

3、代码实现上，row_id是一个8Bytes的BIGINTUNSIGNED

但InnoDB设计时，给row_id只保留了6Bytes的空间，写到数据表时只会存放最后的6Bytes
row_id的取值范围：0~2^48-1
达到上限后，下一个值就是0

4、在InnoDB里面，申请到row_id=N后，就将这行数据写入表中

如果表中已经有row_id=N的行，新写入的行就会覆盖原有的行

5、推荐显示创建自增主键

表自增ID达到上限后，再插入数据时会报主键冲突的错误，影响的是可用性
而覆盖数据，意味着数据丢失，影响的是可靠性
一般来说，可靠性优于可用性

XID

1、redolog和binlog相配合的时候，有一个共同的字段XID，对应一个事务

2、生成逻辑

MySQL内部维护一个全局变量global_query_id
每次执行语句的时候将global_query_id赋值给Query_id，然后global_query_id+1
如果当前语句是这个事务执行的第一条语句，把Query_id赋值给这个事务的XID

3、global_query_id是一个纯内存变量，重启之后清零

因此，在同一个数据库实例中，不同事务的XID也有可能是相同的
MySQL重启之后，会重新生成新的binlog
- 保证：同一个binlog文件里，XID是唯一的
global_query_id达到上限后，就会继续从0开始计数
- 因此理论上，同一个binlog还是会出现相同的XID，只是概率极低

4、global_query_id是8Bytes，上限为2^64-1

执行一个事务，假设XID是A
接下来执行2^64次查询语句，让global_query_id回到A
再启动一个事务，这个事务的XID也是A

InnoDBtrx_id

1、XID是由Server层维护的

2、InnoDB内部使用的是trx_id，为的是能够在InnoDB事务和Server层之间做关联

3、InnoDB内部维护一个max_trx_id的全局变量

每次需要申请一个新的trx_id，就获得max_trx_id的当前值，然后max_trx_id+1

4、InnoDB数据可见性的核心思想

每一行数据都记录了更新它的trx_id
当一个事务读到一行数据的时候，判断数据可见性的方法
- 事务的一致性视图和这行数据的trx_id做对比

5、对于正在执行的事务，可以通过information_schema.innodb_trx看到事务的trx_id

操作序列

时刻	sessionA	sessionB
T1	BEGIN; SELECT*FROMtLIMIT1;
T2		USEinformation_schema; SELECTtrx_id,trx_mysql_thread_idFROMinnodb_trx;
T3	INSERTINTOtVALUES(null);
T4		SELECTtrx_id,trx_mysql_thread_idFROMinnodb_trx;

--T2时刻
mysql>SELECTtrx_id,trx_mysql_thread_idFROMinnodb_trx;
+-----------------+---------------------+
|trx_id|trx_mysql_thread_id|
+-----------------+---------------------+
|281479812572992|30|
+-----------------+---------------------+

--T4时刻
mysql>SELECTtrx_id,trx_mysql_thread_idFROMinnodb_trx;
+-----------------+---------------------+
|trx_id|trx_mysql_thread_id|
+-----------------+---------------------+
|7417540|30|
+-----------------+---------------------+

mysql>SHOWPROCESSLIST;
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
|Id|User|Host|db|Command|Time|State|Info|
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
|4|event_scheduler|localhost|NULL|Daemon|344051|Waitingonemptyqueue|NULL|
|30|root|localhost|test|Sleep|274||NULL|
|31|root|localhost|information_schema|Query|0|starting|SHOWPROCESSLIST|
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+

1、trx_mysql_thread_id=30就是线程ID，即sessionA所在的线程

2、T1时刻，trx_id的值其实为0，而很大的值只是为了显示用的（区别于普通的读写事务）

3、T2时刻，trx_id是一个很大的数字，因为在T1时刻，sessionA并未涉及更新操作，是一个只读事务

对于只读事务，InnoDB不会分配trx_id

4、sessionA在T3时刻执行INSERT语句时，InnoDB才真正分配trx_id

只读事务

1、在上面的T2时刻，很大的trx_id是由系统临时计算出来的

把当前事务的trx变量的指针地址转成整数，再加上2^48

2、同一个只读事务在执行期间，它的指针地址是不会变的

不论是在innodb_trx还是innodb_locks表里，同一个只读事务查出来的trx_id都是一样的

3、如果有多个并行的只读事务，每个事务的trx变量的指针地址肯定是不同的

不同的并发只读事务，查出来的trx_id是不同的

4、加上2^48的目的：保证只读事务显示的trx_id值比较大，用于区别普通的读写事务

5、trx_id与row_id的逻辑类似，定义长度为8Bytes

在理论上，可能会出现一个读写事务与一个只读事务显示的trx_id相同的情况
但概率极低，并且没有什么实质危害

6、只读事务不分配trx_id的好处

可以减少事务视图里面活跃数组的大小
- 当前正在运行的只读事务，是不影响数据的可见性判断
- 因此，在创建事务的一致性视图时，只需要拷贝读写事务的trx_id
可以减少trx_id的申请次数
- 在InnoDB里，即使只执行一条普通的SELECT语句，在执行过程中，也要对应一个只读事务
- 如果普通查询语句不申请trx_id，就可以大大减少并发事务申请trx_id的锁冲突
- 由于只读事务不分配trx_id，trx_id的增加速度会变慢

7、max_trx_id会持久化存储，重启不会重置为0，只有到达2^48-1的上限后，才会重置为0

thread_id

1、SHOWPROCESSLIST的第一列就是thread_id

2、系统保存了一个环境变量thread_id_counter

每新建一个连接，就将thread_id_counter赋值给这个新连接的线程变量

3、thread_id_counter定义为4Bytes，因此达到2^32-1后就会重置为0

但不会在SHOWPROCESSLIST里面看到两个相同的thread_id
因为MySQL设计了一个唯一数组的逻辑，给新线程分配thread_id，逻辑代码如下

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

参考资料

《MySQL实战45讲》

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL自增ID耗尽实例讲解

热门推荐

随机推荐