前言
前段時間我踩過一個坑：在mysql8的一張innodb引擎的表中泳猬，加了唯一索引，但最后發(fā)現(xiàn)數(shù)據(jù)竟然還是重復了。
到底怎么回事呢得封？
本文通過一次踩坑經(jīng)歷埋心，聊聊唯一索引，一些有意思的知識點忙上。
1.還原問題現(xiàn)場
前段時間拷呆，為了防止商品組產(chǎn)生重復的數(shù)據(jù)，我專門加了一張防重表疫粥。
問題就出在商品組的防重表上茬斧。
具體表結(jié)構(gòu)如下：
CREATE TABLE `product_group_unique` (
`id` bigint NOT NULL,
`category_id` bigint NOT NULL,
`unit_id` bigint NOT NULL,
`model_hash` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`in_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
為了保證數(shù)據(jù)的唯一性，我給那種商品組防重表梗逮，建了唯一索引：
alter table product_group_unique add unique index
ux_category_unit_model(category_id,unit_id,model_hash);
根據(jù)分類編號项秉、單位編號和商品組屬性的hash值，可以唯一確定一個商品組慷彤。
給商品組防重表創(chuàng)建了唯一索引之后伙狐，第二天查看數(shù)據(jù)，發(fā)現(xiàn)該表中竟然產(chǎn)生了重復的數(shù)據(jù)：
表中第二條數(shù)據(jù)和第三條數(shù)據(jù)重復了瞬欧。
這是為什么呢贷屎？
2.唯一索引字段包含null
如果你仔細查看表中的數(shù)據(jù)，會發(fā)現(xiàn)其中一個比較特殊地方：商品組屬性的hash值（model_hash字段）可能為null艘虎，即商品組允許不配置任何屬性唉侄。
在product_group_unique表中插入了一條model_hash字段等于100的重復數(shù)據(jù)：
執(zhí)行結(jié)果：
從上圖中看出，mysql的唯一性約束生效了野建，重復數(shù)據(jù)被攔截了属划。
接下來，我們再插入兩條model_hash為null的數(shù)據(jù)候生，其中第三條數(shù)據(jù)跟第二條數(shù)據(jù)中category_id同眯、unit_id和model_hash字段值都一樣。
從圖中看出唯鸭，竟然執(zhí)行成功了须蜗。
換句話說，如果唯一索引的字段中目溉，出現(xiàn)了null值明肮，則唯一性約束不會生效。
最終插入的數(shù)據(jù)情況是這樣的：
- 當model_hash字段不為空時缭付，不會產(chǎn)生重復的數(shù)據(jù)柿估。
- 當model_hash字段為空時，會生成重復的數(shù)據(jù)陷猫。
我們需要特別注意：創(chuàng)建唯一索引的字段秫舌，都不能允許為null的妖，否則mysql的唯一性約束可能會失效。
3.邏輯刪除表加唯一索引
我們都知道唯一索引非常簡單好用足陨，但有時候嫂粟，在表中它并不好加。
不信钠右，我們一起往下看。
通常情況下忘蟹，要刪除表的某條記錄的話飒房，如果用delete語句操作的話。
例如：
delete from product where id=123;
這種delete操作是物理刪除媚值，即該記錄被刪除之后狠毯，后續(xù)通過sql語句基本查不出來。（不過通過其他技術(shù)手段可以找回褥芒，那是后話了）
還有另外一種是邏輯刪除嚼松，主要是通過update語句操作的。
例如：
update product set delete_status=1,edit_time=now(3)
where id=123;
邏輯刪除需要在表中額外增加一個刪除狀態(tài)字段锰扶，用于記錄數(shù)據(jù)是否被刪除献酗。在所有的業(yè)務(wù)查詢的地方，都需要過濾掉已經(jīng)刪除的數(shù)據(jù)坷牛。
通過這種方式刪除數(shù)據(jù)之后罕偎，數(shù)據(jù)任然還在表中，只是從邏輯上過濾了刪除狀態(tài)的數(shù)據(jù)而已京闰。
其實對于這種邏輯刪除的表颜及，是沒法加唯一索引的。
為什么呢蹂楣？
假設(shè)之前給商品表中的name和model加了唯一索引俏站，如果用戶把某條記錄刪除了，delete_status設(shè)置成1了痊土。后來肄扎，該用戶發(fā)現(xiàn)不對，又重新添加了一模一樣的商品赁酝。
由于唯一索引的存在反浓，該用戶第二次添加商品會失敗，即使該商品已經(jīng)被刪除了赞哗，也沒法再添加了雷则。
這個問題顯然有點嚴重。
有人可能會說：把name肪笋、model和delete_status三個字段同時做成唯一索引不就行了月劈？
答：這樣做確實可以解決用戶邏輯刪除了某個商品度迂，后來又重新添加相同的商品時，添加不了的問題猜揪。但如果第二次添加的商品惭墓，又被刪除了。該用戶第三次添加相同的商品而姐，不也出現(xiàn)問題了腊凶？
由此可見，如果表中有邏輯刪除功能拴念，是不方便創(chuàng)建唯一索引的钧萍。
但如果真的想給包含邏輯刪除的表，增加唯一索引政鼠，該怎么辦呢风瘦？
3.1 刪除狀態(tài)+1
通過前面知道，如果表中有邏輯刪除功能公般，是不方便創(chuàng)建唯一索引的万搔。
其根本原因是，記錄被刪除之后官帘，delete_status會被設(shè)置成1瞬雹，默認是0。相同的記錄第二次刪除的時候刽虹，delete_status被設(shè)置成1挖炬，但由于創(chuàng)建了唯一索引（把name、model和delete_status三個字段同時做成唯一索引）状婶，數(shù)據(jù)庫中已存在delete_status為1的記錄意敛，所以這次會操作失敗。
我們?yōu)樯恫粨Q一種思考：不要糾結(jié)于delete_status為1膛虫，表示刪除草姻，當delete_status為1、2稍刀、3等等撩独，只要大于1都表示刪除。
這樣的話账月，每次刪除都獲取那條相同記錄的最大刪除狀態(tài)综膀，然后加1。
這樣數(shù)據(jù)操作過程變成：
- 添加記錄a局齿，delete_status=0剧劝。
- 刪除記錄a，delete_status=1抓歼。
- 添加記錄a讥此，delete_status=0拢锹。
- 刪除記錄a，delete_status=2萄喳。
- 添加記錄a卒稳，delete_status=0。
- 刪除記錄a他巨，delete_status=3充坑。
由于記錄a，每次刪除時染突，delete_status都不一樣捻爷，所以可以保證唯一性。
該方案的優(yōu)點是：不用調(diào)整字段觉痛，非常簡單和直接役衡。
缺點是：可能需要修改sql邏輯茵休，特別是有些查詢sql語句薪棒，有些使用delete_status=1判斷刪除狀態(tài)的，需要改成delete_status>=1榕莺。
3.2 增加時間戳字段
導致邏輯刪除表俐芯，不好加唯一索引最根本的地方在邏輯刪除那里。
我們?yōu)槭裁床患觽€字段钉鸯，專門處理邏輯刪除的功能呢吧史？
答：可以增加時間戳字段。
把name唠雕、model贸营、delete_status和timeStamp，四個字段同時做成唯一索引
在添加數(shù)據(jù)時岩睁，timeStamp字段寫入默認值1钞脂。
然后一旦有邏輯刪除操作，則自動往該字段寫入時間戳捕儒。
這樣即使是同一條記錄冰啃，邏輯刪除多次，每次生成的時間戳也不一樣刘莹，也能保證數(shù)據(jù)的唯一性阎毅。
時間戳一般精確到秒。
除非在那種極限并發(fā)的場景下点弯，對同一條記錄扇调，兩次不同的邏輯刪除操作，產(chǎn)生了相同的時間戳抢肛。
這時可以將時間戳精確到毫秒肃拜。
該方案的優(yōu)點是：可以在不改變已有代碼邏輯的基礎(chǔ)上痴腌，通過增加新字段實現(xiàn)了數(shù)據(jù)的唯一性。
缺點是：在極限的情況下燃领，可能還是會產(chǎn)生重復數(shù)據(jù)士聪。
3.3 增加id字段
其實，增加時間戳字段基本可以解決問題猛蔽。但在在極限的情況下剥悟，可能還是會產(chǎn)生重復數(shù)據(jù)。
有沒有辦法解決這個問題呢曼库？
答：增加主鍵字段：delete_id区岗。
該方案的思路跟增加時間戳字段一致，即在添加數(shù)據(jù)時給delete_id設(shè)置默認值1毁枯，然后在邏輯刪除時慈缔，給delete_id賦值成當前記錄的主鍵id。
把name种玛、model藐鹤、delete_status和delete_id，四個字段同時做成唯一索引赂韵。
這可能是最優(yōu)方案娱节，無需修改已有刪除邏輯，也能保證數(shù)據(jù)的唯一性祭示。
4. 重復歷史數(shù)據(jù)如何加唯一索引肄满？
前面聊過如果表中有邏輯刪除功能，不太好加唯一索引质涛，但通過文中介紹的三種方案稠歉，可以順利的加上唯一索引。
但來自靈魂的一問：如果某張表中汇陆，已存在歷史重復數(shù)據(jù)怒炸，該如何加索引呢？
最簡單的做法是瞬测，增加一張防重表横媚，然后把數(shù)據(jù)初始化進去。
可以寫一條類似這樣的sql：
insert into product_unqiue(id,name,category_id,unit_id,model)
select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;
這樣做可以是可以月趟，但今天的主題是直接在原表中加唯一索引灯蝴，不用防重表。
那么孝宗，這個唯一索引該怎么加呢穷躁？
其實可以借鑒上一節(jié)中，增加id字段的思路。
增加一個delete_id字段问潭。
不過在給product表創(chuàng)建唯一索引之前猿诸，先要做數(shù)據(jù)處理。
獲取相同記錄的最大id：
select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;
然后將delete_id字段設(shè)置成1狡忙。
然后將其他的相同記錄的delete_id字段梳虽，設(shè)置成當前的主鍵。
這樣就能區(qū)分歷史的重復數(shù)據(jù)了灾茁。
當所有的delete_id字段都設(shè)置了值之后窜觉，就能給name、model北专、delete_status和delete_id禀挫，四個字段加唯一索引了。
完美拓颓。
5.給大字段加唯一索引
接下來语婴，我們聊一個有趣的話題：如何給大字段增加唯一索引。
有時候驶睦，我們需要給幾個字段同時加一個唯一索引砰左，比如給name、model啥繁、delete_status和delete_id等菜职。
但如果model字段很大青抛，這樣就會導致該唯一索引旗闽，可能會占用較多存儲空間。
我們都知道唯一索引蜜另，也會走索引适室。
如果在索引的各個節(jié)點中存大數(shù)據(jù)，檢索效率會非常低举瑰。
由此捣辆，有必要對唯一索引長度做限制。
目前mysql innodb存儲引擎中索引允許的最大長度是3072 bytes此迅，其中unqiue key最大長度是1000 bytes汽畴。
如果字段太大了，超過了1000 bytes耸序，顯然是沒法加唯一索引的忍些。
此時，有沒有解決辦法呢坎怪？
5.1 增加hash字段
我們可以增加一個hash字段罢坝，取大字段的hash值，生成一個較短的新值搅窿。該值可以通過一些hash算法生成嘁酿，固定長度16位或者32位等隙券。
我們只需要給name、hash闹司、delete_status和delete_id字段娱仔，增加唯一索引。
這樣就能避免唯一索引太長的問題游桩。
但它也會帶來一個新問題：
一般hash算法會產(chǎn)生hash沖突拟枚，即兩個不同的值，通過hash算法生成值相同众弓。
當然如果還有其他字段可以區(qū)分恩溅，比如：name，并且業(yè)務(wù)上允許這種重復的數(shù)據(jù)谓娃，不寫入數(shù)據(jù)庫脚乡，該方案也是可行的。
5.2 不加唯一索引
如果實在不好加唯一索引滨达，就不加唯一索引奶稠，通過其他技術(shù)手段保證唯一性。
如果新增數(shù)據(jù)的入口比較少捡遍，比如只有job锌订，或者數(shù)據(jù)導入，可以單線程順序執(zhí)行画株，這樣就能保證表中的數(shù)據(jù)不重復辆飘。
如果新增數(shù)據(jù)的入口比較多，最終都發(fā)mq消息谓传，在mq消費者中單線程處理蜈项。
5.3 redis分布式鎖
由于字段太大了，在mysql中不好加唯一索引续挟，為什么不用redis分布式鎖呢紧卒？
但如果直接加給name、model诗祸、delete_status和delete_id字段跑芳，加redis分布式鎖，顯然沒啥意義直颅，效率也不會高博个。
我們可以結(jié)合5.1章節(jié)，用name际乘、model坡倔、delete_status和delete_id字段，生成一個hash值，然后給這個新值加鎖罪塔。
即使遇到hash沖突也沒關(guān)系投蝉，在并發(fā)的情況下，畢竟是小概率事件征堪。
6.批量插入數(shù)據(jù)
有些小伙們瘩缆，可能認為，既然有redis分布式鎖了佃蚜，就可以不用唯一索引了庸娱。
那是你沒遇到，批量插入數(shù)據(jù)的場景谐算。
假如通過查詢操作之后熟尉，發(fā)現(xiàn)有一個集合：list的數(shù)據(jù)，需要批量插入數(shù)據(jù)庫洲脂。
如果使用redis分布式鎖斤儿，需要這樣操作：
for(Product product: list) {
try {
String hash = hash(product);
rLock.lock(hash);
//查詢數(shù)據(jù)
//插入數(shù)據(jù)
} catch (InterruptedException e) {
log.error(e);
} finally {
rLock.unlock();
}
}
需要在一個循環(huán)中，給每條數(shù)據(jù)都加鎖恐锦。
這樣性能肯定不會好往果。
當然有些小伙伴持反對意見，說使用redis的pipeline批量操作不就可以了一铅？
也就是一次性給500條陕贮，或者1000條數(shù)據(jù)上鎖，最后使用完一次性釋放這些鎖潘飘？
想想都有點不靠譜肮之，這個鎖得有多大呀。
極容易造成鎖超時福也，比如業(yè)務(wù)代碼都沒有執(zhí)行完局骤，鎖的過期時間就已經(jīng)到了攀圈。
針對這種批量操作暴凑，如果此時使用mysql的唯一索引，直接批量insert即可赘来，一條sql語句就能搞定现喳。
數(shù)據(jù)庫會自動判斷，如果存在重復的數(shù)據(jù)犬辰，會報錯嗦篱。如果不存在重復數(shù)據(jù)，才允許插入數(shù)據(jù)幌缝。
