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

前言

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

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

/*用户推荐帖子记录表*/CREATETABLE`thread_recommend`(`id`int(11)NOTNULLAUTO_INCREMENT,`thread_id`int(11)DEFAULTNULLCOMMENT'被用户推荐的帖子编号',`user_id`int(11)DEFAULTNULLCOMMENT'推荐该帖子的用户编号',`status`int(11)DEFAULT'1'COMMENT'状态0取消推荐，1推荐',`created`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'推荐时间',PRIMARYKEY(`id`),KEY`userid`(`user_id`)USINGBTREE)ENGINE=InnoDB;

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

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

我刚刚取消了推荐，怎么还显示我推荐着!!

显示的总推荐数怎么和实际推荐用户加起来不一样!!

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

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

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

INSERTINTO`user_topic`(`user_id`,`topic_id`)SELECT:userId,:topicidFROM`user_topic`WHERENOTEXISTS(SELECT*FROM`user_topic`WHERE`user_topic`.`user_id`=:userIdAND`user_topic`.`topic_id`=:topicid)LIMIT1;

(相同方法见http://stackoverflow.com/a/31…)

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

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

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

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

***步：查找user_id, thread_id的联合duplication

SELECTa.*FROM`thread_recommend`aINNERJOIN(SELECT*FROM`thread_recommend`GROUPBY`thread_id`,`user_id`HAVINGCOUNT(id)>1)bONa.`thread_id`=b.`thread_id`ANDa.`user_id`=b.`user_id`ORDERBYa.`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`GROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1);

得到

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

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

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

SELECT*FROM`thread_recommend`WHERE(`user_id`,`thread_id`)IN(SELECT`user_id`,`thread_id`FROM`thread_recommend`GROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1)AND`id`NOTIN(SELECTMIN(`id`)FROM`thread_recommend`GROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1);

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

DELETEFROM`thread_recommend`WHERE(`user_id`,`thread_id`)IN(SELECT`user_id`,`thread_id`FROM`thread_recommend`GROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1)AND`id`NOTIN(SELECTMIN(`id`)FROM`thread_recommend`GROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1);

Oops!报错! You can’t specify target table ‘thread_recommend’ for update in FROM clause

这是Mysql的一个小问题，我们参见解决方案 http://stackoverflow.com/a/14… 后修改一下SQL就好：

DELETEFROM`thread_recommend`WHERE(`user_id`,`thread_id`)IN(SELECT`user_id`,`thread_id`FROM(SELECT*FROM`thread_recommend`)aGROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1)AND`id`NOTIN(SELECTMIN(`id`)FROM(SELECT*FROM`thread_recommend`)bGROUPBY`user_id`,`thread_id`HAVINGCOUNT(1)>1);

***，加联合唯一索引!

ALTERTABLE`thread_recommend`ADDUNIQUEKEY`thread_id_user_id_unique`(`thread_id`,`user_id`)USINGBTREE;

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

完!