建站
咨詢
售后
建站咨詢
新聞中心
前言
為來鳳等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及來鳳網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為網(wǎng)站建設(shè)、做網(wǎng)站、來鳳網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！
昨天遇到一個(gè)問題，需要對(duì)一張關(guān)系表進(jìn)行重構(gòu)和優(yōu)化。然而這張關(guān)系表由于已有代碼沒有注重并發(fā)導(dǎo)致了很多的臟數(shù)據(jù)，即重復(fù)數(shù)據(jù)。
表名thread_recommend，帖子推薦表，為兩個(gè)實(shí)體user_id和thread_id的(推薦)關(guān)系表，表結(jié)構(gòu)很簡(jiǎn)單如下：
- /*用戶推薦帖子記錄表*/
- CREATE TABLE `thread_recommend` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `thread_id` int(11) DEFAULT NULL COMMENT '被用戶推薦的帖子編號(hào)',
- `user_id` int(11) DEFAULT NULL COMMENT '推薦該帖子的用戶編號(hào)',
- `status` int(11) DEFAULT '1' COMMENT '狀態(tài)0 取消推薦，1推薦',
- `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '推薦時(shí)間',
- PRIMARY KEY (`id`),
- KEY `userid` (`user_id`) USING BTREE
- ) ENGINE=InnoDB;
問題在于，由于代碼不規(guī)范，在高并發(fā)時(shí)(或數(shù)據(jù)庫壓力大時(shí)造成的延時(shí)積壓時(shí))會(huì)出現(xiàn)多個(gè)(相同thread_id和user_id的)組合，如下：
之后你們懂的，各種和原想不一致的神奇bug噴涌而出，比如：
我剛剛?cè)∠送扑]，怎么還顯示我推薦著!!
顯示的總推薦數(shù)怎么和實(shí)際推薦用戶加起來不一樣!!
解決方案一：使用insert where not exists語句
聲明：此方案并不是***方案，不推薦使用。
先上代碼：(這里拿另一個(gè)關(guān)系表的真實(shí)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...)
通過這種“插入時(shí)判斷不存在才插入并返回行數(shù)為1，存在的話返回行數(shù)為0”的方法，可以做到：
- 只有在返回行數(shù)為1的情況下才執(zhí)行之后邏輯(如緩存內(nèi)的統(tǒng)計(jì)數(shù)+1，緩存內(nèi)帖子推薦人增加此userId等等)
- 如果返回行數(shù)為0，則接口返回error
解決方案二：清理臟數(shù)據(jù)并建立聯(lián)合唯一索引
這個(gè)方案是本文的核心了，也是我們目前認(rèn)為的***實(shí)踐。
***步：查找user_id, thread_id的聯(lián)合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
或簡(jiǎn)單的版本
- 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);
得到
哇!所有的重復(fù)項(xiàng)都在這里了，好想馬上把它們干掉!
現(xiàn)在需要將重復(fù)的條目中ID更大的所有條目都刪除，只留ID最小的那一個(gè)。
刪之前先獲得需要?jiǎng)h除項(xiàng)，比對(duì)一下，
- 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!報(bào)錯(cuò)! You can't specify target table 'thread_recommend' for update in FROM clause
這是Mysql的一個(gè)小問題，我們參見解決方案 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);
***，加聯(lián)合唯一索引!
- ALTER TABLE `thread_recommend`
- ADD UNIQUE KEY `thread_id_user_id_unique`(`thread_id`,`user_id`) USING BTREE;
Of course，如果上述清理工作沒有完成將會(huì)報(bào)錯(cuò)!
完!
新聞名稱：MySQL去除“關(guān)聯(lián)表”重復(fù)數(shù)據(jù)，以建立聯(lián)合唯一索引
轉(zhuǎn)載注明：http://m.5511xx.com/article/djdhoih.html
