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