前言
昨天遇到一个问题,需要对一张关系表进行重构和优化。然而这张关系表由于已有代码没有注重并发导致了很多的脏数据,即重复数据。
表名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`) USING BTREE
) 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` = :userId
AND`user_topic`.`topic_id` = :topicid)
LIMIT 1;
通过这种“插入时判断不存在才插入并返回行数为1,存在的话返回行数为0”的方法,可以做到:
只有在返回行数为1的情况下才执行之后逻辑(如缓存内的统计数+1,缓存内帖子推荐人增加此userId等等)
如果返回行数为0,则接口返回error
解决方案二:清理脏数据并建立联合唯一索引
这个方案是本文的核心了,也是我们目前认为的***实践。
***步:查找user_id, thread_id的联合duplication
SELECTa.*FROM`thread_recommend` a
INNERJOIN(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`) USING BTREE;
Of course,如果上述清理工作没有完成将会报错!
完!
【编辑推荐】
【责任编辑:枯木 TEL:(010)68476606】
点赞 0