问题提出

时常有这样的case: DB实例运行一段时间后,发现需要给1个table中的某(些)字段加unique 约束,

但创建unique constraints(或 index)时, 报出 DETAIL: Key (col)=(value) is duplicated !

此时就需要先按照一定逻辑将重复数据仅保留1条, 将冗余的delete掉

分析问题

delete数据, 重点自然在于定位所有待delete的row, 或需要保留的row.

解决问题

以假设业务要求要保留如下test表中每组info重复值中id最小的row为例

方法1 正向思维, 使用 array

使用高级数据类型array及其强大的function, 一次定位需要delete的row

方法2 正向思维, 使用 window function

思路同 方法1, 让我们体验一下 window function

方法3 逆向思维, 使用 not in

排除法, 逆向定位

方法4 逆向思维, 使用 not exists

思路同 方法3

方法5 正逆结合, 使用 in, not in

先定位存在重复值的组大集合,再排除小集合

方法6 正逆结合, 使用 exists, not exists

思路同 方法5

方法7 直接制作单条SQL

将所有存在重复值的组找到, 然后逐一定位需要保留每组中的最小id, 其余delete

方法8 复制数据到新表

如果应用可以接受短暂停止写入, 可以将所需唯一数据复制到新表

放在事务里是为了保证所做操作原子性, 避免出现瞬间无表可用的窗口期 

注: 为了便于与其他方式对比, 方法8会按照保留id的方式测试, 如果不保留id, group by 比 distinct 执行速度略快.

测试数据

由于query在 table 数据分布不同的情况下执行效率存在差异, 所以我们构造3组测试数据进行对比

生成数据

数据分布

各种方法对比

分析上表可知,

使用正向思维(方法1,2), 平均执行时间会随着冗余数据的增加而增加, 在冗余数据较少时, 推荐方法2;

使用逆向思维(方法3,4), 平均执行时间会随着冗余数据的增加而减少, 在冗余数据较多时, 推荐方法4;

正逆结合的思维(方法5,6)平均执行时间并不占优势, 原因是需要2次subquery来最终定位数据;

方法7 方法7 执行总时间最长(随着单条SQL的总条数的增加而增加),

但实际上对DB实例的冲击最小, 把1个长时间的对大量row 的lock, 离散化为仅对单个row或几个row的极短时间的lock,

在压力较大的生产环境中, 推荐此方法;

方法8 步骤稍繁, 在实际生产环境中由于table的字段可能较多,且整个table的(包括所有index)都会重建, 所以速度并不占优, 但却顺便把table彻底维护了一下 , 对于udpate, delete非常频繁的table, total size(包括所有index)会大为缩小(由于MVCC), 综合性能会明显提升.

总结

看一下DELETE 的语法

所以其实还有其他一些具体方法, 比如 使用 WITH Queries构造临时表, 使用 USING using_list 替代子查询, 使用存储过程将方法1封装起来(不推荐,因为这样整个delete过程为一个大事务)等等;

但整体思路无外乎上面的套路， 条条大路通罗马，结合table中数据分布情况(具体问题具体分析)，选择效率较高， 且是您最钟情的那个style就可以了.

DBA 日常操作选取原则

压力大的线上生产DB实例(尤其是交易系统), 首选对生产冲击最小的,

压力不大的生产DB实例或DB beta/dev 实例首选一条SQL且执行时间快的方法。

重复值delete之后，就可以创建唯一索引了，方法如下：

至此，该问题解决完毕，如有更高效， 更tricky的方法，欢迎交流学习。