前言

前段時(shí)間我踩過(guò)一個(gè)坑：在mysql8?的一張innodb?引擎的表?中，加了唯一索引?，但最后發(fā)現(xiàn)數(shù)據(jù)?竟然還是重復(fù)了。

到底怎么回事呢？

本文通過(guò)一次踩坑經(jīng)歷，聊聊唯一索引，一些有意思的知識(shí)點(diǎn)。

一、還原問(wèn)題現(xiàn)場(chǎng)

前段時(shí)間，為了防止商品組產(chǎn)生重復(fù)的數(shù)據(jù)，我專(zhuān)門(mén)加了一張防重表。

如果大家對(duì)防重表，比較感興趣，可以看看我的另一篇文章 《??高并發(fā)下如何防重？??》，里面有詳細(xì)的介紹。

問(wèn)題就出在商品組的防重表上。

具體表結(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ù)分類(lèi)編號(hào)、單位編號(hào)和商品組屬性的hash值，可以唯一確定一個(gè)商品組。

給商品組防重表創(chuàng)建了唯一索引?之后，第二天查看數(shù)據(jù)，發(fā)現(xiàn)該表中竟然產(chǎn)生了重復(fù)的數(shù)據(jù)：

表中第二條數(shù)據(jù)和第三條數(shù)據(jù)重復(fù)了。

這是為什么呢？

二、索引字段包含null

如果你仔細(xì)查看表中的數(shù)據(jù)，會(huì)發(fā)現(xiàn)其中一個(gè)比較特殊地方：商品組屬性的hash值（model_hash字段）可能為??null??，即商品組允許不配置任何屬性。

在product_group_unique表中插入了一條model_hash字段等于100的重復(fù)數(shù)據(jù)：執(zhí)行結(jié)果：從上圖中看出，mysql的唯一性約束生效了，重復(fù)數(shù)據(jù)被攔截了。

接下來(lái)，我們?cè)俨迦雰蓷lmodel_hash為null的數(shù)據(jù)，其中第三條數(shù)據(jù)跟第二條數(shù)據(jù)中category_id、unit_id和model_hash字段值都一樣。從圖中看出，竟然執(zhí)行成功了。

換句話(huà)說(shuō)，如果唯一索引的字段中，出現(xiàn)了null值，則唯一性約束不會(huì)生效。

最終插入的數(shù)據(jù)情況是這樣的：

1. 當(dāng)model_hash字段不為空時(shí)，不會(huì)產(chǎn)生重復(fù)的數(shù)據(jù)。
2. 當(dāng)model_hash字段為空時(shí)，會(huì)生成重復(fù)的數(shù)據(jù)。

我們需要特別注意：創(chuàng)建唯一索引的字段，都不能允許為null，否則mysql的唯一性約束可能會(huì)失效。

三、邏輯刪除表加唯一索引

我們都知道唯一索引非常簡(jiǎn)單好用，但有時(shí)候，在表中它并不好加。

不信，我們一起往下看。

通常情況下，要?jiǎng)h除表的某條記錄的話(huà)，如果用??delete??語(yǔ)句操作的話(huà)。

例如：

delete from product where id=123;

這種delete操作是物理刪除，即該記錄被刪除之后，后續(xù)通過(guò)sql語(yǔ)句基本查不出來(lái)。（不過(guò)通過(guò)其他技術(shù)手段可以找回，那是后話(huà)了）

還有另外一種是邏輯刪除?，主要是通過(guò)update語(yǔ)句操作的。

例如：

update product set delete_status=1,edit_time=now(3) 
where id=123;

邏輯刪除需要在表中額外增加一個(gè)刪除狀態(tài)字段，用于記錄數(shù)據(jù)是否被刪除。在所有的業(yè)務(wù)查詢(xún)的地方，都需要過(guò)濾掉已經(jīng)刪除的數(shù)據(jù)。

通過(guò)這種方式刪除數(shù)據(jù)之后，數(shù)據(jù)任然還在表中，只是從邏輯上過(guò)濾了刪除狀態(tài)的數(shù)據(jù)而已。

其實(shí)對(duì)于這種邏輯刪除的表，是沒(méi)法加唯一索引的。

為什么呢？

假設(shè)之前給商品表中的name和model加了唯一索引，如果用戶(hù)把某條記錄刪除了，delete_status設(shè)置成1了。后來(lái)，該用戶(hù)發(fā)現(xiàn)不對(duì)，又重新添加了一模一樣的商品。

由于唯一索引的存在，該用戶(hù)第二次添加商品會(huì)失敗，即使該商品已經(jīng)被刪除了，也沒(méi)法再添加了。

這個(gè)問(wèn)題顯然有點(diǎn)嚴(yán)重。

有人可能會(huì)說(shuō)：把name、model和delete_status?三個(gè)字段同時(shí)做成唯一索引不就行了？

答：這樣做確實(shí)可以解決用戶(hù)邏輯刪除了某個(gè)商品，后來(lái)又重新添加相同的商品時(shí)，添加不了的問(wèn)題。但如果第二次添加的商品，又被刪除了。該用戶(hù)第三次添加相同的商品，不也出現(xiàn)問(wèn)題了？

由此可見(jiàn)，如果表中有邏輯刪除功能，是不方便創(chuàng)建唯一索引的。

但如果真的想給包含邏輯刪除的表，增加唯一索引，該怎么辦呢？

1、刪除狀態(tài)+1

通過(guò)前面知道，如果表中有邏輯刪除功能，是不方便創(chuàng)建唯一索引的。

其根本原因是，記錄被刪除之后，delete_status會(huì)被設(shè)置成1，默認(rèn)是0。相同的記錄第二次刪除的時(shí)候，delete_status被設(shè)置成1，但由于創(chuàng)建了唯一索引（把name、model和delete_status三個(gè)字段同時(shí)做成唯一索引），數(shù)據(jù)庫(kù)中已存在delete_status為1的記錄，所以這次會(huì)操作失敗。

我們?yōu)樯恫粨Q一種思考：不要糾結(jié)于delete_status為1，表示刪除，當(dāng)delete_status為1、2、3等等，只要大于1都表示刪除。

這樣的話(huà)，每次刪除都獲取那條相同記錄的最大刪除狀態(tài)，然后加1。

這樣數(shù)據(jù)操作過(guò)程變成：

1. 添加記錄a，delete_status=0。
2. 刪除記錄a，delete_status=1。
3. 添加記錄a，delete_status=0。
4. 刪除記錄a，delete_status=2。
5. 添加記錄a，delete_status=0。
6. 刪除記錄a，delete_status=3。

由于記錄a，每次刪除時(shí)，delete_status都不一樣，所以可以保證唯一性。

該方案的優(yōu)點(diǎn)是：不用調(diào)整字段，非常簡(jiǎn)單和直接。

缺點(diǎn)是：可能需要修改sql邏輯，特別是有些查詢(xún)sql語(yǔ)句，有些使用delete_status=1判斷刪除狀態(tài)的，需要改成delete_status>=1。

2、增加時(shí)間戳字段

導(dǎo)致邏輯刪除表，不好加唯一索引最根本的地方在邏輯刪除那里。

我們?yōu)槭裁床患觽€(gè)字段，專(zhuān)門(mén)處理邏輯刪除的功能呢？

答：可以增加時(shí)間戳字段。

把name、model、delete_status和timeStamp，四個(gè)字段同時(shí)做成唯一索引

在添加數(shù)據(jù)時(shí)，timeStamp字段寫(xiě)入默認(rèn)值1。

然后一旦有邏輯刪除操作，則自動(dòng)往該字段寫(xiě)入時(shí)間戳。

這樣即使是同一條記錄，邏輯刪除多次，每次生成的時(shí)間戳也不一樣，也能保證數(shù)據(jù)的唯一性。

時(shí)間戳一般精確到秒。

除非在那種極限并發(fā)的場(chǎng)景下，對(duì)同一條記錄，兩次不同的邏輯刪除操作，產(chǎn)生了相同的時(shí)間戳。

這時(shí)可以將時(shí)間戳精確到毫秒。

該方案的優(yōu)點(diǎn)是：可以在不改變已有代碼邏輯的基礎(chǔ)上，通過(guò)增加新字段實(shí)現(xiàn)了數(shù)據(jù)的唯一性。

缺點(diǎn)是：在極限的情況下，可能還是會(huì)產(chǎn)生重復(fù)數(shù)據(jù)。

3、增加id字段

其實(shí)，增加時(shí)間戳字段基本可以解決問(wèn)題。但在在極限的情況下，可能還是會(huì)產(chǎn)生重復(fù)數(shù)據(jù)。

有沒(méi)有辦法解決這個(gè)問(wèn)題呢？

答：增加主鍵字段：delete_id。

該方案的思路跟增加時(shí)間戳字段一致，即在添加數(shù)據(jù)時(shí)給delete_id設(shè)置默認(rèn)值1，然后在邏輯刪除時(shí)，給delete_id賦值成當(dāng)前記錄的主鍵id。

把name、model、delete_status和delete_id，四個(gè)字段同時(shí)做成唯一索引。

這可能是最優(yōu)方案，無(wú)需修改已有刪除邏輯，也能保證數(shù)據(jù)的唯一性。

四、重復(fù)歷史數(shù)據(jù)如何加唯一索引？

前面聊過(guò)如果表中有邏輯刪除功能，不太好加唯一索引，但通過(guò)文中介紹的三種方案，可以順利的加上唯一索引。

但來(lái)自靈魂的一問(wèn)：如果某張表中，已存在歷史重復(fù)數(shù)據(jù)，該如何加索引呢？

最簡(jiǎn)單的做法是，增加一張防重表，然后把數(shù)據(jù)初始化進(jìn)去。

可以寫(xiě)一條類(lèi)似這樣的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;

這樣做可以是可以，但今天的主題是直接在原表中加唯一索引，不用防重表。

那么，這個(gè)唯一索引該怎么加呢？

其實(shí)可以借鑒上一節(jié)中，增加id字段的思路。

增加一個(gè)delete_id字段。

不過(guò)在給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è)置成當(dāng)前的主鍵。

這樣就能區(qū)分歷史的重復(fù)數(shù)據(jù)了。

當(dāng)所有的delete_id字段都設(shè)置了值之后，就能給name、model、delete_status和delete_id，四個(gè)字段加唯一索引了。

完美。

五、給大字段加唯一索引

接下來(lái)，我們聊一個(gè)有趣的話(huà)題：如何給大字段增加唯一索引。

有時(shí)候，我們需要給幾個(gè)字段同時(shí)加一個(gè)唯一索引，比如給name、model、delete_status和delete_id等。

但如果model字段很大，這樣就會(huì)導(dǎo)致該唯一索引，可能會(huì)占用較多存儲(chǔ)空間。

我們都知道唯一索引，也會(huì)走索引。

如果在索引的各個(gè)節(jié)點(diǎn)中存大數(shù)據(jù)，檢索效率會(huì)非常低。

由此，有必要對(duì)唯一索引長(zhǎng)度做限制。

目前mysql innodb存儲(chǔ)引擎中索引允許的最大長(zhǎng)度是3072 bytes，其中unqiue key最大長(zhǎng)度是1000 bytes。

如果字段太大了，超過(guò)了1000 bytes，顯然是沒(méi)法加唯一索引的。

此時(shí)，有沒(méi)有解決辦法呢？

1、增加hash字段

我們可以增加一個(gè)hash字段，取大字段的hash值，生成一個(gè)較短的新值。該值可以通過(guò)一些hash算法生成，固定長(zhǎng)度16位或者32位等。

我們只需要給name、hash、delete_status和delete_id字段，增加唯一索引。

這樣就能避免唯一索引太長(zhǎng)的問(wèn)題。

但它也會(huì)帶來(lái)一個(gè)新問(wèn)題：

一般hash算法會(huì)產(chǎn)生hash沖突，即兩個(gè)不同的值，通過(guò)hash算法生成值相同。

當(dāng)然如果還有其他字段可以區(qū)分，比如：name，并且業(yè)務(wù)上允許這種重復(fù)的數(shù)據(jù)，不寫(xiě)入數(shù)據(jù)庫(kù)，該方案也是可行的。

2、不加唯一索引

如果實(shí)在不好加唯一索引，就不加唯一索引，通過(guò)其他技術(shù)手段保證唯一性。

如果新增數(shù)據(jù)的入口比較少，比如只有job，或者數(shù)據(jù)導(dǎo)入，可以單線(xiàn)程順序執(zhí)行，這樣就能保證表中的數(shù)據(jù)不重復(fù)。

如果新增數(shù)據(jù)的入口比較多，最終都發(fā)mq消息，在mq消費(fèi)者中單線(xiàn)程處理。

3、redis分布式鎖

由于字段太大了，在mysql中不好加唯一索引，為什么不用redis分布式鎖呢？

但如果直接加給name、model、delete_status和delete_id字段，加redis分布式鎖，顯然沒(méi)啥意義，效率也不會(huì)高。

我們可以結(jié)合5.1章節(jié)，用name、model、delete_status和delete_id字段，生成一個(gè)hash值，然后給這個(gè)新值加鎖。

即使遇到hash沖突也沒(méi)關(guān)系，在并發(fā)的情況下，畢竟是小概率事件。

六、批量插入數(shù)據(jù)

有些小伙們，可能認(rèn)為，既然有redis分布式鎖了，就可以不用唯一索引了。

那是你沒(méi)遇到，批量插入數(shù)據(jù)的場(chǎng)景。

假如通過(guò)查詢(xún)操作之后，發(fā)現(xiàn)有一個(gè)集合：list的數(shù)據(jù)，需要批量插入數(shù)據(jù)庫(kù)。

如果使用redis分布式鎖，需要這樣操作：

for(Product product: list) {
   try {
        String hash = hash(product);
        rLock.lock(hash);
        //查詢(xún)數(shù)據(jù)
        //插入數(shù)據(jù)
    } catch (InterruptedException e) {
       log.error(e);
    } finally {
        rLock.unlock();
    }
}

需要在一個(gè)循環(huán)中，給每條數(shù)據(jù)都加鎖。

這樣性能肯定不會(huì)好。

當(dāng)然有些小伙伴持反對(duì)意見(jiàn)，說(shuō)使用redis的pipeline批量操作不就可以了？

也就是一次性給500條，或者1000條數(shù)據(jù)上鎖，最后使用完一次性釋放這些鎖？

想想都有點(diǎn)不靠譜，這個(gè)鎖得有多大呀。

極容易造成鎖超時(shí)，比如業(yè)務(wù)代碼都沒(méi)有執(zhí)行完，鎖的過(guò)期時(shí)間就已經(jīng)到了。

針對(duì)這種批量操作，如果此時(shí)使用mysql的唯一索引，直接批量insert即可，一條sql語(yǔ)句就能搞定。

數(shù)據(jù)庫(kù)會(huì)自動(dòng)判斷，如果存在重復(fù)的數(shù)據(jù)，會(huì)報(bào)錯(cuò)。如果不存在重復(fù)數(shù)據(jù)，才允許插入數(shù)據(jù)。