詳解數據冗餘會產生的問題「如何建立冗餘數據」

今天提前下班了，突然有個開發同學急沖沖找過來說有個業務需求需要緊急支持下。

大體的背景是有一張表中的數據目前存在一些冗餘的記錄，從業務層面來看這些看起來冗餘的數據是某些虛擬福利會被重複領取，所以需要馬上做下限制，根據用戶的基本屬性（比如userid,usercode）進行唯一性標識。

因為這個服務的使用率不是很高，重複領取的情況確實存在，但是頻率相對不高，如果活動大量推送後期會有很大的業務損失，所以修復這個潛在問題迫在眉睫。

所以直接的操作就是alter table xxxx add unique key(xxx)

但是顯然會失敗，因為表中存在冗餘數據，需要先完成數據清理的工作才可行。

和開發同學溝通後，發現實際的數據清理需求比想象的要略複雜一些，一方面要按照業務特點刪除一些已有的數據，然後才按照冗餘數據的寫入情況清理，數據表為rc_user_info,數據量不是很大，大概是30萬左右，轉換為數據操作大體如下：

1）按照業務屬性刪除部分數據，刪除ustatus=2的數據

2）按照字段uuser，ucode組合清理冗餘數據，只保留最新的數據記錄(字段use_info_id是流水號）即可。

這個操作時間緊，數據質量要求高，而且需要保證整個過程可以追溯和回退，於是我快速設計了如下的操作方式。

在test數據庫中複製數據，模擬整個數據清理和創建唯一性索引的完整過程，待驗證確認後，在線上環境進行數據清理和變更。

比如樣例數據如下:

經過一通清理之後，需要保留的數據僅為1條，即use_info_id=61543的數據記錄，其中紅色框住的數據是ustatus=2的數據，需要清理，然後按照use_info_id進行排序，取最新的值即可。

我和開發同學討論後，整理的大體的步驟如下：

第一步：備份 rc_use_info

create table test.rc_use_info like userdb.rc_use_info;

insert into test.rc_use_info select * from userdb.rc_use_info;

第二步：刪除 ustatus=2 的記錄

delete from test.rc_use_info where ustatus=2;

第三步：刪除 ustatus=1 的 (uuser ucode) 存在多條記錄的數據，保留一條

1）在此創建了3張臨時表，tmp_dup_user是冗餘的數據，直接提取max(use_info_id)

create table tmp_dup_user(use_info_id bigint,uuser varchar(100),ucode varchar(100));

insert into test.tmp_dup_user select max(use_info_id),uuser,ucode from test.rc_use_info group by uuser,ucode having count(*)>=2;

3978 rows in set (0.34 sec)

2）tmp_dup_user_uniq1是當前數據中沒有重複記錄的用戶信息

create table tmp_dup_user_uniq1(use_info_id bigint,uuser varchar(100),ucode varchar(100));

insert into test.tmp_dup_user_uniq1 select max(use_info_id),uuser,ucode from test.rc_use_info group by uuser,ucode having count(*)=1;

3）delete_id僅存儲需要刪除的冗餘數據id

create table delete_id (use_info_id bigint primary key);

insert into delete_id select use_info_id from test.rc_use_info where use_info_id not in (select use_info_id from tmp_dup_user_uniq1)

and use_info_id not in (select use_info_id from tmp_dup_user)

4）按照id清理冗餘數據

delete from test.rc_use_info where use_info_id in (select use_info_id from delete_id);

5）快速驗證，按照預期，輸出結果應該是0條

select use_info_id from test.rc_use_info where use_info_id not in (select use_info_id from tmp_dup_user_uniq1)

and use_info_id not in (select use_info_id from tmp_dup_user)

第四步：建立唯一索引 (uuser ucode)

alter table test.rc_use_info add unique key idx_uuser_ucode(uuser,ucode);

第五步：清理線上數據，建立唯一索引 (uuser ucode)

1）正式備份線上數據

create table test.rc_use_info_bak like userdb.rc_use_info;

insert into test.rc_use_info_bak select * from userdb.rc_use_info;

2）清理線上的業務數據

delete from userdb.rc_use_info where ustatus=2;

3）按照delete_id清理冗餘數據

delete from userdb.rc_use_info where use_info_id in (select use_info_id from test.delete_id);

4）提前唯一性索引

alter table userdb.rc_use_info add unique key idx_uuser_ucode(uuser,ucode);

整個過程有很多改進之處，相對來說，每一步都可以衡量，而且可以根據整個推演的過程計算出應該變更的數據量，整個過程就像是做一道計算題。

一旦某個業務的數據按照現有邏輯清理存在問題，也可以及時進行追溯和調整。