精华内容
下载资源
问答
  • 2021-12-20 20:35:30

    1、遇到的问题

    Mysql 的自增主键达到最大值,会发生什么你知道吗?就在今天,我们线上就发生了这种问题!!!

    他会发生异常,报错如下:

    Duplicate entry '2147483647' for key 'PRIMARY'

    org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

    insert 数据就会报错。

    2、分析

    总结来说这算是架构上的设计问题吧。先说一下我们目前暴露出来的俩问题:

    ① Mysql 的自增主键默认是 int 类型,4 个字节,一个字节 8 位,即如果是有符号的位的话,最大值就是(2^31)-1,也就是2147483647,基本也就能存储 20 亿数据。当数据达到千万上亿级别时,查询数据就会特别特别慢了,所以我们就采取了数据归档。这里是另外一个坑下面在介绍。应该根据业务数据量,分析数据的增长情况,提前规划进行分库分表,至少要保证每个表不超过千万级。这样才能保证查询效率。

    ② 这里说下上边提到的另外一个坑:我们的因为查询比较慢,对表做过几次数据归档,就是将数据写入一张表中,当数据量很大时,就定时将某个时间之前的数据写入另一个表中,这样做也是有一些坑的。

    Mysql的 delete 并不会真正的删除磁盘空间,而只是标记相应的区域,在合适的时候还可以再利用。如果要真正腾出磁盘空间还必须使用 optimize table xxx 进行磁盘碎片处理,但是这个命令会在相应的库下产生一个很大的 #sql-xxx 文件,此文件的赠长速度特别快,要清除的表越大它的增长速度就越快,所以不能等磁盘已经快满了才想起来清理。

    而且 optimize 命令会锁表,一般根据表的数据增长速度和删除等情况综合考虑决定 optimize 命令的执行频率。比如在访问量最小的时候一个月或者两个月执行一次。

    3、解决办法

    ① 修改 id 字段类型,int 改为 bigint(太占空间了,一个bigint的存储大小为8字节) bigint 的大小是8个字节,一个字节 8 位,有符号的最大值就是 2 的 63 次方 -1。即 bigint 带符号的范围是 -9223372036854775808 到 9223372036854775807。无符号的范围是 0 到 18446744073709551615。

    ② 有能力还是分表,有效避免这个问题

    ③ 将 int 类型设置为无符号的可以扩大一倍

    基于以上三种最好的处理方法还是分库分表。

    4、临时解决方案

    目前我们基于线上的临时解决方案是新建了一个同结构的表,业务数据走新表,查询兼容旧表

    更多相关内容
  • mysql自增主键达到最大后如何处理

    千次阅读 2021-05-12 15:16:55
    然后如果这个自增主键达到最大值,是会报错的 Duplicate entry '2147483647' for key 'PRIMARY' 错误翻译过来的意思大概是主键2147483647重复了。 解决方法: ①、修改id字段类型,int改为bigint(太占空间了,...

    在mysql中int类型占四个字节,有符号书的话,最大值就是(2^31)-1,也就是2147483647,二十多亿。
    然后如果这个自增主键达到最大值,是会报错的

    Duplicate entry '2147483647' for key 'PRIMARY'

    错误翻译过来的意思大概是主键2147483647重复了。

    解决方法:

    ①、修改id字段类型,int改为bigint(太占空间了,一个bigint的存储大小为8字节) bigint的大小是8个字节,一个字节8位,有符号的最大值就是2的63次方-1

    ②、有能力还是分表,有效避免这个问题

    ③、将int类型设置为无符号的可以扩大一倍

    有符号int最大可以支持到约22亿,远远大于我们的需求和MySQL单表所能支持的性能上限。

    对于OLTP应用来说,单表的规模一般要保持在千万级别,不会达到22亿上限。如果要加大预留量,可以把主键改为改为无符号int,上限为42亿,这个预留量已经是非常的充足了。
    使用bigint,会占用更大的磁盘和内存空间,内存空间毕竟有限,无效的占用会导致更多的数据换入换出,额外增加了IO的压力,对性能是不利的。

    因此推荐自增主键使用int unsigned类型,但不建议使用bigint

    有能力选择分表,更好解决这个问题。

     

    展开全文
  • 面试官:”那自增主键达到最大值了,用完了怎么办?” 你:”what,没复习啊!!” (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得挺有意(KENG)思(B)! 于是,今天我们就来谈一谈,这个自增主键...
  • mysql作为最常用的关系型数据库,无论是在应用还是在面试中都是必须掌握的技能。

    mysql作为最常用的关系型数据库,无论是在应用还是在面试中都是必须掌握的技能。

    mysql自增主键会用完吗

    我们在使用mysql设置的自增主键的时候,一般都是定义初始值和定义步长,我们知道自然数是没有上限的,但是mysql的自增主键是会设置字节长度的,但凡有字节长度那么就会有上限。

    mysql自增主键用完会怎样

    不管我们设置字节长度为多大,如果假设mysql运行时间足够长,那么就一定会用完,对于mysql的情况会分为两种

    1. 程序员自己设置的自增主键。

    毫无疑问,当数值达到最大时候,再去获取自增主键得到的依然是最大值,插入的时候就会报主键冲突。这个是在server层实现的。

    1. 程序员没有设置自增主键,mysql自动创建row_id。

    这里需要注意,mysql中的row_id是在引擎层实现的,InnoDB代码中会创建一个不可见的长度为8的自增字段row_id,步长为1,但是InnoDB在实现的时候却只给此字段分配6个字节的空间长度,因此在保存数据的时候只能取row_id字段的最后6字节进行保存,我们知道6字节数值最大为2的248次方,如果已经达到这个值后,再次插入数据时候,row_id就是2的248次方加1,从这个数值中取最后6字节正好是0,而在InnoDB的实现逻辑中如果row_id重复,不会报主键冲突,而是会覆盖原数据。

    现在你应该清楚mysql的自增主键是有上限的,达到上限后就会出现上面说的现象。

    mysql中还有哪些自增id,达到最大又会如何呢

    max_trx_id

    我们知道mysql中,没创建一个事物就会去申请一个事物id(trx_id),申请的方式就是从获取全局变量max_trx_id当前值,然后将max_trx_id+1,max_trx_id是InnoDB内部维护的,并且是持久化保存的,也就是说即便mysql重启也不会重置这个值。

    一般的select语句是不会申请事物id的,除非语句后面加上for update

    max_trx_id也是8个字节的长度,虽然数字足够大,但是假设mysql运行时间足够长,早晚也会达到最大值的,max_trx_id达到最大值后会重置为0,重新开始。

    这种情况下就会有个问题,看下图:

    在这里插入图片描述

    我们知道在可重复读隔离级别下数据的可见性是通过事物的一致性视图来判断的。这种情况下就会出现脏读的bug

    解释:

    假设在上面sql执行前系统的max_trx_id已经是最大值999(假设这是最大值),所以在session A启动的事务的低水位就是999。

    在T2时刻,session B执行第一条update语句的事务id就是999,而第二条update语句的事务id就是0了,这条update语句执行后生成的数据版本上的trx_id就是0。

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

    但实际sessionA不应该看到c=3这条数据,因此出现这个是脏读。

    这是mysql必现的一个bug。

    thread_id

    thread_id是mysql中常见的一种自增id,长度为4个字节,当达到最大值时就会重置为0,重新开始,但是我们在日常的维护中用show processlist查看的时候从来都不会看到重复的id,这是因为mysql在实现的时候做了一些操作,代码如下

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

    因此mysql中的thread_id不会出现重复。

    mysql中还有一些其他的自增id,比如mysql中还有redo log和binlog相关的xid,binlog文件序号,还有table_id等。但是我们最应该知道就是上面这几个,其他的感兴趣可以随时来找我探讨。

    如果需要带注释的spring源码或者了解更多行业技能请关注微信公众号 码农本农 如果需要《mysql实战45讲》请关注微信公众号直接发消息索要 码农本农

    展开全文
  • 然后如果这个自增主键达到最大值,是会报错的 Duplicate entry '2147483647' for key 'PRIMARY' 错误翻译过来的意思大概是主键2147483647重复了。 解决方法:修改id字段类型,int改为bigint,有能力还是分表,...

    在mysql中int类型占四个字节,有符号书的话,最大值就是(2^31)-1,也就是2147483647,二十多亿。
    然后如果这个自增主键达到最大值,是会报错的

    Duplicate entry '2147483647' for key 'PRIMARY'


    错误翻译过来的意思大概是主键2147483647重复了。

    解决方法:

    ①、修改id字段类型,int改为bigint(太占空间了,一个bigint的存储大小为8字节) bigint的大小是8个字节,一个字节8位,有符号的最大值就是2的63次方-1

    ②、有能力还是分表,有效避免这个问题

    ③、将int类型设置为无符号的可以扩大一倍

    有符号int最大可以支持到约22亿,远远大于我们的需求和MySQL单表所能支持的性能上限。

    对于OLTP应用来说,单表的规模一般要保持在千万级别,不会达到22亿上限。如果要加大预留量,可以把主键改为改为无符号int,上限为42亿,这个预留量已经是非常的充足了。
    使用bigint,会占用更大的磁盘和内存空间,内存空间毕竟有限,无效的占用会导致更多的数据换入换出,额外增加了IO的压力,对性能是不利的。

    因此推荐自增主键使用int unsigned类型,但不建议使用bigint

    有能力选择分表,更好解决这个问题。

     

    在这里插入图片描述

    展开全文
  • 如果mysql自增主键达到最大值会发生什么?比如主键类型设为int,其最大值为:2147483647,如果此时我们再往其中写入数据,就会发生异常: Duplicate entry '2147483647' for key 'PRIMARY' 也即写不进去了。...
  • MySQL 自增主键

    千次阅读 2022-02-10 17:46:23
    ​ 在创建主键的时候可以使用AUTO_INCREMENT关键字进行自增主键设置,设置之后,每插入一条数据之后主键都会自动累加1 ​ 使用AUTO_INCREMENT=100可以在表级别设定偏移量,比如这里设定为100,即从100开始累加 2、...
  • MySQL自增主键详解

    千次阅读 2021-01-18 19:35:11
    一、自增值保存在哪儿?不同的引擎对于自增值的保存...每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id),然后将max(id)+步长作为这个表当前的自增值select max(ai_col) from table_name for upda...
  • Mysql 自增主键

    2021-05-30 18:51:55
    整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击: 用 BIGINT 做主键,而不是 INT; 自增值并不持久化,可能会...
  • mysql自增主键详解

    2021-04-08 23:22:30
    文章目录什么是自增主键自增主键的优点好处?自增值保存在哪儿?自增值修改机制自增主键 id不能保证连续的原因?原因一:唯一键冲突原因二:事务回滚原因三:自增锁的优化思考题 什么是自增主键? 当设置了主键...
  • MySQL [xxx_mall]> alter table shop_base_info AUTO_INCREMENT=11000;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0MySQL [xxx_mall]> SELECT-> auto_increment-> FRO...
  • int 最大值 在上表中我们看到,首位的 bit 用来表示符号,因此在 sigend 是少一位 bit 来存储内容。int 是 4 byte,结合上述,可以得出 int 在 signed 最大值为 231−1=2147483647 在 unsigned 中,无需首位 ...
  • MySQL 自增主键机制

    2021-01-19 04:37:28
    自增主键:特指在自增列上定义的主键。 自增主键的优点是让主键索引保持递增顺序的插入,避免页分裂,索引更加紧凑。...每次重启后第一次打开表,都会去查找自增值的最大值max(id), 并设置表当前自增值为ma...
  • MySQL自增主键

    2021-08-12 11:27:34
    MySQL8.0之后会将自增主键进行持久化(写入redo log),所以数据库重启后,可以接着表中当前数据行后继续插入自增主键自增主键的计算方法 当用户没有指定插入数据行的主键id时,系统默认用AUTO_INCREMENT的。 ...
  • mysql自增主键怎么用

    2021-02-02 16:09:22
    导航自增主键怎么设置MySQL是怎么保存自增主键自增主键的修改机制造成自增主键不连续的几种情形自增主键和UUID及随机ID比较自增主键的好处自增主键的坏处UUID及随机ID参考文章 自增主键怎么设置 通常,自增主键的...
  • InnoDB引擎在5.7及之前将当前自增值存储在内存中,MySQL重启时从表中查询自增最大值+步长作为当前自增值。InnoDB引擎在8.0及之后版本中将自增值变动记录存储在redo log中,重启MySQL后根据redo log恢复之前的自...
  • 文章目录背景测试过程创建表,自增ID场景一:测试自增ID已经达到最大值场景二:测试自增ID即将达到最大值总结解决方案扩展扩展1:修改字段的数据类型,建议使用modify 用法扩展2:Alter table的底层原理 背景 之前的...
  • 问题描述 ...在排查问题的遇到了一个诡异的问题,mysql 自增主键被删除后,又重复出现的问题 mysql> select * from test; +----+----------------+ | id | name | +----+----------------+ | 1 |
  • MySQL自增主键auto_increment原理; MySQL的innodb_autoinc_lock_mode参数说明; MySQL的AUTO-INC锁原理; 自增主键出现间隙不连续现象的定位;
  • 核对当前report_cert_day表的主键report_id 的auto_increment为128,并且与report_id的最大值不冲突。 SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name="表名"; mysql> show create ...
  • MySQL自增主键会用完吗?我们先来看下在MySQL中,int和bigint两种类型的值域是多少, 类型 最小值 最大值 所占字节 int(有符号) -2^31 2^31 4 int(无符号) 0 2^32 4 bigint(有符号) -2^63 2^63 8 ...
  • mysql出现主键自增长到最大
  • 联合主键自增问题今天上午闲来无事翻看了下数据库分类表的设计,看到这样一幕:当时我好奇的是怎么cateId自增会存在重复的问题,然后翻看了下主键是由siteId和cateId组成。所以进行了查阅资料:当多列组成联合...
  • 最后有个问题: 假如,innodb_autoinc_lock_mode=1 模式下在被导入的表设置一个较大的increment 自增值,大于我们要导入的数据的最大值,这样可以避免在导数据的时候出现锁等待的问题,可以吗? 答案是不可以的,...
  • 每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。 举例来说,如果一个表当前数据行里最大的 id 是 10,AUTO_INCREMENT=11。这时候,我们删除 id=10 的...
  • MySql自增主键ID重置这个坑货

    千次阅读 2020-08-03 09:37:48
    这两天在面试的时候被问到一个问题:在mysql中用自增列作为主键时,先往表里插入5条数据,此时表里数据id为1、2、3、4、5,如果此时删除id=4、5的数据后,再重启数据库,重启成功后向表里insert数据的时候,INNODB、...
  • 微信搜索“coder-home”或扫一扫下面的二维码,关注公众号,第一时间了解更多干货分享,还有各类视频教程资源。扫描它,带走我 ...那么如果我的主键值是1,3,5,7...,2n+1这样的算是连续自增主键.

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 30,523
精华内容 12,209
关键字:

mysql自增主键的最大值

mysql 订阅