RDS ERROR 1062 && online DDL

一、RDS ERROR 1062 
1.背景：
  数据库：RDS-MySQL5.6；业务表trade，该业务表的数据量是百万级别的，表字段已有70个左右，索引已有十几个，有唯一性索引unique_idx(uniq1,uniq2)
 
2.现象：
  给trade表加字段（因为mysql5.6的版本已支持online-DDL，所以白天执行加字段的sql，之前测试下来对业务影响不大）sql如下： 
1 ALTER TABLE `trade`
2 ADD COLUMN `col1` BIGINT(20) DEFAULT NULL,
3 ADD COLUMN `col2` BIGINT(20) DEFAULT NULL,
4 ADD COLUMN `col3` VARCHAR(20) DEFAULT NULL;

  但是执行过程中，多个库都出现报错，报错大致如下：

错误代码： 1062
Duplicate entry '69950493617-27155840530' for key 'unique_idx'
 
3.分析：
  1>报错显示是唯一性索引出现重复数据，于是首先去trade表中查询是否出现重复数据，sql如下：
SELECT DISTINCT(uniq1),COUNT(uniq2) cnt FROM trade GROUP BY uniq1 HAVING cnt >1 ORDER BY cnt DESC;
  没有查询到重复数据（显然是不会有重复数据的，因为建表之初就是存在唯一性索引的，不可能有重复数据呀）
 
  以上网址中解释如下：这是RDS实例的bug，是由于该表的DML很多导致磁盘碎片化严重，同时该表是业务表，实时数据操作也很多，所以导致线上执行alter table异常报错。同时网址中也提到了rds官方提供的技术支持，可以使用Percona Toolkit三方工具。
 
  3>限制性：由于我们的rds是基于聚石塔，没有操作系统的权限，另外考虑到在ecs上使用第三方工具操作rds，可能会有网络影响。
 
  4>考虑到mysql5.6新增了 online DDL特性（以前新增字段的话，MySQL5.5会锁表，后升级到MySQL5.6），于是分析一下online DDL的原理
 
 
 
二、online DDL
 
  1.MySQL5.1之前的版本实现DDL操作简单原理：
    1>对表table1执行DDL时，会先给表table1建一张临时表table1_tmp,该临时表的表结构是（DDL操作后）新定义的表结构
    2>对table1进行锁表操作，然后将table1中的数据copy到table1_tmp
    3>对table1 进行rename为table2，同时对table1_tmp进行rename为table1，然后删除掉table2
    老版本的操作带来的最大问题是，表的数据量越大，copy表数据的时间越长，导致锁表时间越长，对客户的影响就越大。（互联网行业一般是维持7*24小时的不间断服务，但是执行这些会锁表的操作会选择业务低峰期，但这样人为花费的精力就蛮大的）
 
  2.MySQL5.1（with innodb plugin）和MySQL5.5及以上的版本
        这些版本中出现了一个新特性 Fast Index Creation （快速创建索引），该特性能加快alter table table1 add/drop key idx的消耗时间，这个主要是节省在copy table这个步骤上，创建二级索引时对table1加上一个s锁，创建过程不需要重建table1_tmp，删除innodb二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义。这个特性也是需要锁表操作的，但是加快了修改索引的速度
（注意：主键索引的修改仍然需要copy table，是因为innodb IOT（索引组织表）特性）
 
  3.MySQL5.6 online DDL三个阶段
 
 
 
三、解决RDS加字段可能会带来的1062 Duplicate error
采用如下sql：
1 ALTER TABLE `trade`
2 ADD COLUMN `col1` BIGINT(20) DEFAULT NULL,
3 ADD COLUMN `col2` BIGINT(20) DEFAULT NULL,
4 ADD COLUMN `col3` VARCHAR(20) DEFAULT NULL,
5 lock=shared;

#lock=shared的话，会对原表进行锁表操作，所以需要在业务低峰期执行以上sql。另外也可以使用copy算法来设置，达到同样的效果。

 

1 ALTER TABLE `trade`
2 ADD COLUMN `col1` BIGINT(20) DEFAULT NULL,
3 ADD COLUMN `col2` BIGINT(20) DEFAULT NULL,
4 ADD COLUMN `col3` VARCHAR(20) DEFAULT NULL,
5 ALGORITHM=COPY;

 

#因为online DDL默认算法INPLACE是不需要重建表的；所以更改算法为COPY的话，需要拷贝原表，拷贝的过程中不允许DML操作的,但是COPY算法默认的lock=none，在DDL期间，原表还是能允许DML操作的（个人比较推荐这种，对业务影响会更小一点）
 
 
文末： 非常感谢以上网址中两位博主的分享，目前已解决rds error1062的问题。

posted on 2017-12-25 11:51  Kid_Zora  阅读(517)  评论(0)  编辑  收藏  举报

导航