转载

MySQL去除“关联表”重复数据，以建立联合唯一索引

前言

昨天遇到一个问题，需要对一张关系表进行重构和优化。然而这张关系表由于已有代码没有注重并发导致了很多的脏数据，即重复数据。

表名thread_recommend，帖子推荐表，为两个实体 user_id 和 thread_id 的(推荐)关系表，表结构很简单如下：

/*用户推荐帖子记录表*/
CREATE TABLE `thread_recommend` (
`id` int(11) NOT NULL AUTO_INCREMENT, 
`thread_id` int(11) DEFAULT NULL COMMENT '被用户推荐的帖子编号', 
`user_id` int(11) DEFAULT NULL COMMENT '推荐该帖子的用户编号', 
`status` int(11) DEFAULT '1' COMMENT '状态0 取消推荐，1推荐', 
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '推荐时间', 
PRIMARY KEY (`id`), 
KEY `userid` (`user_id`) USING BTREE
) ENGINE=InnoDB;

问题在于，由于代码不规范，在高并发时（或数据库压力大时造成的延时积压时）会出现多个（相同thread_id和user_id的）组合，如下：

MySQL去除“关联表”重复数据，以建立联合唯一索引

之后你们懂的，各种和原想不一致的神奇bug喷涌而出，比如：

我刚刚取消了推荐，怎么还显示我推荐着！！
显示的总推荐数怎么和实际推荐用户加起来不一样！！

解决方案一：使用 `insert where not exists` 语句

声明：此方案并不是最佳方案，不推荐使用。

先上代码：（这里拿另一个关系表的真实query举例，原理一样）

INSERT INTO `user_topic` (`user_id`, `topic_id`)
SELECT :userId, :topicid FROM `user_topic`
WHERE NOT EXISTS (SELECT * FROM `user_topic`
  WHERE `user_topic`.`user_id` = :userId
  AND `user_topic`.`topic_id` = :topicid)
LIMIT 1;

（相同方法见 http://stackoverflow.com/a/31... ）

通过这种“插入时判断不存在才插入并返回行数为1，存在的话返回行数为0”的方法，可以做到：

只有在返回行数为1的情况下才执行之后逻辑（如缓存内的统计数+1，缓存内帖子推荐人增加此userId等等）
如果返回行数为0，则接口返回error

解决方案二：清理脏数据并建立联合唯一索引

这个方案是本文的核心了，也是我们目前认为的最佳实践。

第一步：查找user_id, thread_id的联合duplication

SELECT a.* FROM `thread_recommend` a
INNER JOIN (SELECT * FROM `thread_recommend` GROUP BY `thread_id`, `user_id` HAVING COUNT(id) > 1) b ON a.`thread_id` = b.`thread_id` AND a.`user_id` = b.`user_id`
ORDER BY a.`user_id` ASC, a.`thread_id` ASC, a.`id` DESC

或简单的版本

SELECT * FROM `thread_recommend`
WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

得到

MySQL去除“关联表”重复数据，以建立联合唯一索引

哇！所有的重复项都在这里了，好想马上把它们干掉！

现在需要将重复的条目中ID更大的所有条目都删除，只留ID最小的那一个。

删之前先获得需要删除项，比对一下，

SELECT * FROM `thread_recommend`
WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1)
AND `id` NOT IN (SELECT MIN(`id`) FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

下一步， SELECT * FROM 改成 DELETE FROM ，删除！

DELETE FROM `thread_recommend`
WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1)
AND `id` NOT IN (SELECT MIN(`id`) FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

Oops!报错! You can't specify target table 'thread_recommend' for update in FROM clause
这是Mysql的一个小问题，我们参见解决方案 http://stackoverflow.com/a/14... 后修改一下SQL就好：

DELETE FROM `thread_recommend`
WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM (SELECT * FROM `thread_recommend`) a GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1)
AND `id` NOT IN (SELECT MIN(`id`) FROM (SELECT * FROM `thread_recommend`) b GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

最后，加联合唯一索引！

ALTER TABLE `thread_recommend`
ADD UNIQUE KEY `thread_id_user_id_unique`(`thread_id`,`user_id`) USING BTREE;

Of course，如果上述清理工作没有完成将会报错！

完！

原文 https://segmentfault.com/a/1190000006169960

正文到此结束

所属分类：编程技术

本文标签： update cat src mysql 压力 key 数据库 Select tar 时间代码 https 数据删除 db UI http tab sql 统计
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

MySQL去除“关联表”重复数据，以建立联合唯一索引

前言

解决方案一：使用 `insert where not exists` 语句

解决方案二：清理脏数据并建立联合唯一索引

第一步：查找user_id, thread_id的联合duplication

最后，加联合唯一索引！

完！

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流

MySQL去除“关联表”重复数据，以建立联合唯一索引

前言

解决方案一：使用 insert where not exists 语句

解决方案二：清理脏数据并建立联合唯一索引

第一步：查找user_id, thread_id的联合duplication

最后，加联合唯一索引！

完！

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流

解决方案一：使用 `insert where not exists` 语句