MySQL自增字段暴增

十二月 9th, 2012 Categories: 后端, 技术, 知识原野

MySQL5数据库服务器,大数据中心模式。今年出现过两次大的数据中心宕机/迁移/修复事件。

所涉及数据库已经运行三年,某数据表自增字段ID int(10) ,原有数据插入正常自增至 1800多条,下一条数据突增至1211000,再下面的数据突增至1399000。直接查看这两条新数据,发现是当时被有关权力部门要求隐藏的数据,手工更改的ID(当时没直接删除这两条,而是在后面加了4个0),接下来的数据库就从最大的那条数据开始自增。为什么会这样?

找了点资料:

从网上看到一篇文章,mysql在检测到表中有损坏的记录时,会自动修复,为了保证数据的完整性,mysql会以空格(0x20)写进磁盘来完成修复。根据字段的类型,自增字段的长度不同,所允许的最大值也不同。见下:
int(10) unsigned类型最大值十进制为4294967295,十六进制ffffffff;
mediumint(8) unsigned类型最大值十进制为16777215,十六进制ffffff;
smallint(6) unsigned类型最大值十进制为65535,十六进制ffff;
tinyint(3) unsigned类型最大值十进制为255,十六进制ff
mail中的自增字段是int(10),也就是0x20202020,转换成10进制就是538976288,超过了4294967295这个值,此时,系统会以最大值显示。问了下具体的操作人员,确实做了数据库修复的操作。
摘自后附资料
结论一(关于MYSQL确定和更新某个自增字段最大值的方式):
第 一次对某个表创建并填充好结构文件.frm,索引文件.MYI和数据文件.MYD的数据后,首先读取索引文件.MYI中关于这个自增字段的最大值,并且放 入内存进行计数,以后所有的自增字段最大值都来源于这个内存计数器;隔一段时间重新读取数据文件中对应字段的确切最大值,并和内存的值做比较,然后用两者 中的最大值更新内存的数值和索引文件的数值。
结论二(关于不同的异常修复时对自增字段最大值的影响):
在MYISAM的存储结构中,数据 库数据的异常不一定会导致立刻启动自我修复;并且这种修复不是全面的修复,大多数情况下,它仅仅对数据文件.MYD进行修复,而这种修复并不对索引产生即 时的影响(换句话说,自增字段也不会受到即时的影响);只有当用户要求进行修复的时候,才会全面更新索引(比如输入SQL语句:REPAIR TABLE __TABLE_NAME__)。而一旦更新了索引(无论是手动还是自动更新),对自增字段的影响有可能是爆发性的呈现。

结论一的解释:
在 试验时,我发现假如终止MYSQL进程后再重新启动,tid这个自增键值的最大值是直接读取索引文件cdb_threads.MYI确定,而不管是否和数 据文件cdb_threads.MYD中的tid最大值是否一致。并且发现,1,只要索引文件损坏并且无法自我修复,这个表就有可能无法读取甚至导致 MYSQL进程当掉。2,索引文件并不经常更新,而是有规律的隔段时间写入。
结论二的解释:
我的测试方法很简单,在MYSQL运行时直接 修改数据文件的尾部。用PHPMYADMIN打开表的前几页数据,正常;我直接跳到最后一页,这时候MYSQL提示出错,要修复这个表,刷新了两下,自我 修复完毕,此时出现一条tid:2105376的无用记录,然而插入新记录时,tid赋予的下一个值仍然是3201;只有当我显式的命令修复过后,才赋予 tid下一个值为2105377。
所以,朋友的那个论坛数据库,07年已经出现了异常,然而直到08年12月,才执行了自我修复,然而这种修复在当时没有影响到索引,而是根据前面的结论,继续潜伏一段时间后,才显出tid暴增的问题。

看来,手工修改数据ID,增加4个0之后,数据主键的索引会更新,但不会影响自增(AUTO_INCREMENT)基数。

而数据库出现故障之后,修复恢复数据库时,自增基数会根据索引值的最大ID进行递增。

updated:直接验证修改当前一条数据ID,末尾增加两个零。插入一条新记录,数据是在这末尾加两0的数据ID加1,看来在数据库正常运行情况下,MyISAM格式的数据表ID自增是基于最大值。这应该是MySQL5采用来避免数据主键冲突的最简单方式。

Tags: ,
尚无评论.

留言回复