Mysql 的自增主键达到最大值，怎么办

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、临时解决方案

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