2012/12/20某业务合服后在唯一索引键上出现了重复键现象:
mysql> select count(RoleGID) from RoleData_0;
+----------------+
| count(RoleGID) |
+----------------+
| 41255 |
+----------------+
1 row in set (0.00 sec)
mysql> select count(distinct RoleGID) from RoleData_0;
+-------------------------+
| count(distinct RoleGID) |
+-------------------------+
| 41123 |
+-------------------------+
1 row in set (0.00 sec)
其中RoleGID是表RoleData_0的唯一索引,但是部分数据却出现重复。
【问题回顾】
运维同事反馈,问题是这样产生的:
1.执行合服脚本,脚本会向db_roledata_102504库导入RoleData_*表的数据
2.由于脚本没有后台运行,并且过程中跳板机挂死,因此必须重新执行脚本,但此时已有部分数据入库。
3.合服脚本中是使用mysql –f参数导入数据,会忽略导入过程中产生的错误(如重复键)。
4.最后合服脚本校验数据,发现数据出现重复记录。
【原因分析】
理论上,mysql会保证唯一索引上记录不会重复。
通过mysql官方文档和源码跟踪定位原因如下:
mysqldump默认产生的sql文件会指定unique_checks为0,目的是减少唯一键检查,增加导入速度。
原因是这个参数设为0后,如果目标唯一索引的页面不在buffer中,会使用insert buffer(5.5后增强为change buffer)来实现延时写,从而不进行唯一性检查,导致重复键。
详见函数btr_cur_search_to_nth_level:
【问题方案】
这个问题的关键是mysqldump默认启动了unique_checks=0,并且该行为是不可预知的,有可能允许重复键的出现。
因此,这个问题的规避方法如下:
1. 不重复导入数据,或重复前先清理数据。另外,脚本应后台运行。
2. 尽量不要使用mysql –f导入数据
3. 更根本的手段是禁用mysqldump默认unique_checks=0的行为。而官方的mysqldump只有通过指定--tab参数才不启动该特性。
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
但是,这个会导致schema和数据文件分开,可能并不适用所有情况。
另外一种是修改mysqldump的实现,通过参数控制该行为,并默认关闭,目前互娱DBA团队便是采用这种策略。