背景

最近运维同学反馈了一条慢查询的问题。研究完表结构后，发现是缺少索引。
看完了代码逻辑，发现这张表需要添加唯一索引。本想直接添加一个唯一索引解决问题。
但是，表中的数据已经存在重复数据。直接添加索引添加不成功。
常规思维，是去除掉重复数据，然后再添加唯一索引。

问题描述

如何去除表中重复数据？

解决办法

方法一：添加唯一索引

ALTER IGNORE TABLE `tbl_like_work` ADD UNIQUE INDEX `uk_tian`(`user_id`, `work_id`)

需要注意SQL语句中使用了 IGNORE 关键字，若不添加此关键字，则添加唯一索引会失败。

若添加索引成功，则自动会去除重复数据。

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

方法二：使用临时表

对于 MySQL 5.7.4 是不支持 INSERT IGNORE的。可以使用临时表完成数据去重。
通过使用临时表，备份数据。然后给原表添加唯一索引，再将临时表中的数据导入到原表，得到去重表。

整个流程如下：

1. 使用临时表备份数据。根据原表创建临时表
2. 清掉原表数据
3. 给原表添加唯一索引
4. 将临时表数据插入到原表，插入时忽略重复数据（使用 IGNORE 关键字）
5. 删除临时数据

CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;

方法三：删除重复数据

如果需要删除重复数据，必须找到重复数据。

如何查找重复数据

SELECT *, count(*) as total
FROM `tbl_like_work` 
GROUP BY user_id,work_id
HAVING total > 1

删除重复数据

DELETE tbl_like_work
WHERE `id` not in (
    SELECT min(id)  # 保留最旧的数据
    FROM  `tbl_like_work` 
    GROUP BY user_id,work_id
)

总结

需要注意的是，方法二会更改原表的主键ID，对于有主键依赖的业务。不能使用第二种方法。
建议使用方法一、方法三，只删除重复数据，且不会对原数据做出修改。

参考资料

• INSERT ... SELECT Syntax
• How to Find and Delete Duplicate Rows with SQL
• Removing duplicates with unique index