《MySQL自增ID》告诉你不为人知的“秘密”…

1.概述

MySQL数据库是最常使用的数据之一,我们经常需要用到它的自增ID来标识记录。在MySQL中,可通过数据列的auto_increment属性来自动生成。也可以在建表时可用“auto_increment=n”选项来指定一个自增的初始值。可用“alter table table_name auto_increment=n”命令来重设自增的起始值,当然在设置的时候MySQL会取数据表中auto_increment列的最大值+1与n中的较大者作为新的auto_increment值。

      的auto_increment属性具有以下特性:

(1) 它是一个正数序列,如果把该数据列声明为UNSIGNED,编号个数可增加一倍。假如设置的是tinyint类型最大编号是127,如果加上UNSIGNED,那么最大编号变为255

(2) auto_increment数据列必须有唯一索引,以避免序号重复;必须具备NOT NULL属性。

那么,MySQL自增ID用过的都说好用,但是没用对,可能发生灾难…… 为什么这样说?因为我们最近在一个老项目上踩到了MySQL自增ID的大坑,事情是这样的…

神级爆款项目(暂且这样叫)在设计未来合服逻辑时,为了考虑不让ID发生冲突,聪明地在部署每个新服时强制带上服标识号作为自增ID的起始号,例如:

S1101服,部署脚本会强制指定 auto_increment=110100000000 作为该服的自增ID号。

S1102服,部署脚本会强制指定 auto_increment=110200000000 作为该服的自增ID号。

这样一来,在执行合服的过程中是绝对不会发生ID冲突的问题,每个服的玩家ID号是完全唯一的。相信很多项目也是这么设计。。。(我猜的)

但是问题来了,这么好的设计为什么说有坑呢?原因是我们使用了InnoDB作为存储引擎,Innodb引擎的表指定了一个auto_increment列,那么这张表会有一个auto_increment计数器,专门记录当前auto_increment的相关值,用来在insert时为auto_increment列赋值。非常重要的一点是,这个计数值是保存在内存中的,而非磁盘上。

当MySQL运行时,这个计数值随着insert增长。假设表中有100条记录,auto_increment计数值是101,当delete所有的记录后,再insert一条语句,这条语句的id便是101。但是如果把表清空了,再启动MySQL,auto_increment的值就会变为1,因为这个值是InnoDB存储在内存中,如果表中没有任何记录,auto_increment的值是会被重置的。

我们先回到刚才那个神级爆款项目(暂且这样叫),看看这个项目涉及自增ID的表:

      由于是一次半夜里机柜意外的断电,导致机器关机自动重启,MySQL和所服游戏服都被重启,重启之后游戏服并没有发生什么异常,也能正常玩。但过两周进行合服的时候,突然报ID冲突了。

      百思不得其解啊!明明设计之初就已经规避了的问题。因此对所有自增表进行了扫描,发现以下情况,真的无语了!

      机器被重启之前,这个服还没有对外开放,表里还是空的,正常来说部署的时候已经给ID赋值了106800000000,起始号应当是106800000000。当这个服开了之后,ID号就会106800000000+n。正因为机器被重启过,而且表也是空的,导致了ID自增号恢复为1,所以看到上面ID编号不对了,合服时也就发生了ID冲突。

2.揭晓真相

为了一究到底,我们做了一个实验来验证它。当然也就选用了MySQL两大优秀主流引擎 InnoDB和MyISAM。看看真实如此?

(1)首先测试的是InnoDB引擎,采用方法是建一个表,然后插入一些数据,再把所有数据清空,然后重启MySQL服务。

 

CREATE TABLE `table1` (
   `id` tinyint(3) NOT NULL auto_increment,
   `name` varchar(50),
   `time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建了一个table1的表,然后插入了9条数据,目前自增ID号为10。

然后把所有记录清空,再查看自增ID号仍为10。

然而,重启MySQL服务后,再查看自增ID号已变为1了。

(2)接下来看看MyISAM引擎,用样的方式先建一个表,然后插入一些数据,再把所有数据清空,然后重启MySQL服务。

 

CREATE TABLE `table1` (
   `id` tinyint(3) NOT NULL auto_increment,
   `name` varchar(50),
   `time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

建了一个table1的表,然后插入了9条数据,目前自增ID号为10。

然后把所有记录清空,再查看自增ID号仍为10。

重启MySQL服务后,再查看自增ID号依然是10 !!!

结论:

  1. InnoDB引擎的表,执行清空操作之后,表的auto_increment值不会受到影响;一旦重启MySQL,auto_increment值将变成1。
  2. MyISAM引擎的表,执行清空操作之后,表的auto_increment值不会受到影响;重启MySQL,auto_increment值也不会受到影响。
  3. 以上两个引擎在表中还有记录的情况下,若auto_increment指定了起始号,即使重启MySQL后,表的auto_increment值不会被重置。但InnoDB的auto_increment值会被设置为当前最后一条记录的ID+1。
  4. 证明InnoDB引擎不会在本地记录auto_increment值,它会在启动后以表中最后一条记录ID+1,作为auto_increment值。类似执行:

 

select max(id) maxId from table;
alter table auto_increment = maxId + 1;

3.延伸

关于InnoDB引擎对AUTO_INCREMENT计数器初始化的解析,可阅读https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html,这篇官方文档说明。

在官网上明确说明MySQL5.7以下版本的InnoDB引擎不会记录在磁盘上,而MySQL8.0版本以将会改变这个方式,让AUTO_INCREMENT计数值能够保持最后一次的ID值。

那么你的业务有涉及到类似的用法,你又不想有后续踩到坑,在业务允许的情况下,要么更新MySQL版本。要么就乖乖地做好修复方案SOP。

修复方案也很简单,例如要修复以下表:

AUTO_INCREMEN起始值原本是106800000000,现在需要把表的AUTO_INCREMEN自增ID为了10680000011,同时里面记录也需要106800000000+n。

alter table table1 auto_increment = 106800000000;

update table1 set id=106800000001+id where id < 106800000000;

每次重启MySQL后跑以上两条SQL。当然这不是最好的解决方法,只是万不得已才使用!

赞(0)
未经允许不得转载:运维军团 » 《MySQL自增ID》告诉你不为人知的“秘密”…

评论 抢沙发

*

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址