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的）组合，如下：

 

之后你们懂的，各种和原想不一致的神奇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
  
   

  目录

  显示的总推荐数怎么和实际推荐用户加起来不一样！！解决方案一：使用insert where not exists语句

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

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

  第一步：查找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);

  
  
  现在需要将重复的条目中ID更大的所有条目都删除，只留ID最小的那一个。
  删之前先获得需要删除项，比对一下，
  
   

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

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