MySQL自增ID耗尽实例讲解

这篇文章主要给大家介绍了关于MySQL自增ID耗尽的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧

显示定义ID

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

— (2^32-1) = 4,294,967,295

— 建议使用 BIGINT UNSIGNED

CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;

INSERT INTO t VALUES (null);

— AUTO_INCREMENT没有改变

mysql> SHOW CREATE TABLE t;

+——-+——————————————————+

| Table | Create Table |

+——-+——————————————————+

| t | CREATE TABLE `t` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 |

+——-+——————————————————+

mysql> INSERT INTO t VALUES (null);

ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

InnoDB row_id

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

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

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

3、代码实现上,row_id是一个8 Bytes的BIGINT UNSIGNED

  • 但InnoDB设计时,给row_id只保留了6 Bytes的空间,写到数据表时只会存放最后的6 Bytes
  • 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是8 Bytes,上限为2^64-1

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

InnoDB trx_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

操作序列

时刻

session A

session B

T1BEGIN;
SELECT * FROM t LIMIT 1;
T2USE information_schema;
SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
T3INSERT INTO t VALUES (null);
T4SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

— T2时刻

mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;

+—————–+———————+

| trx_id | trx_mysql_thread_id |

+—————–+———————+

| 281479812572992 | 30 |

+—————–+———————+

— T4时刻

mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;

+—————–+———————+

| trx_id | trx_mysql_thread_id |

+—————–+———————+

| 7417540 | 30 |

+—————–+———————+

mysql> SHOW PROCESSLIST;

+—-+—————–+———–+——————–+———+——–+————————+——————+

| Id | User | Host | db | Command | Time | State | Info |

+—-+—————–+———–+——————–+———+——–+————————+——————+

| 4 | event_scheduler | localhost | NULL | Daemon | 344051 | Waiting on empty queue | NULL |

| 30 | root | localhost | test | Sleep | 274 | | NULL |

| 31 | root | localhost | information_schema | Query | 0 | starting | SHOW PROCESSLIST |

+—-+—————–+———–+——————–+———+——–+————————+——————+

1、trx_mysql_thread_id=30就是线程ID,即session A所在的线程

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

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

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

4、session A在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的逻辑类似,定义长度为8 Bytes

  • 在理论上,可能会出现一个读写事务与一个只读事务显示的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、SHOW PROCESSLIST的第一列就是thread_id

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

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

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

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

?

1

2

3

do {

new_id= thread_id_counter++;

} while (!thread_ids.insert_unique(new_id).second);

参考资料

《MySQL实战45讲》

总结

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

原文链接:http://zhongmingmao.me/2019/03/19/mysql-self-increase-id-run-out/

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/17058/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年8月30日 下午11:05
下一篇 2022年8月30日 下午11:05
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!