# 明明加了唯一索引，為什麼還是產生了重複資料？

## 前言

前段時間我踩過一個坑：在 `mysql8` 的一張 `innodb` 引擎的`表`中，加了`唯一索引`，但最後發現`資料`竟然還是`重複`了。

到底怎麼回事呢？

本文透過一次踩坑經歷，聊聊唯一索引，一些有意思的知識點。

![img](https://f.pz.al/pzal/2022/09/13/de14cef322a21.png)

## 1. 還原問題現場

前段時間，為了防止商品組產生重複的資料，我專門加了一張`防重表`。

如果大家對防重表，比較感興趣，可以看看我的另一篇文章 《[高併發下如何防重？](https://www.oschina.net/action/GoToLink?url=https%3A%2F%2Fmp.weixin.qq.com%2Fs%3F__biz%3DMzkwNjMwMTgzMQ%3D%3D%26mid%3D2247495570%26idx%3D1%26sn%3Deed3102c7dffc4ddbc59844dd9b865a5%26chksm%3Dc0e8377af79fbe6c29aefa3ae3aab48c6459b673005e2f97ae402172f6e5cdf8573aea5e7663%26token%3D758132007%26lang%3Dzh_CN%23rd)》，裡面有詳細的介紹。

問題就出在商品組的防重表上。

具體表結構如下：

```sql
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;
```

為了保證資料的`唯一性`，我給那種商品組防重表，建了唯一索引：

```sql
alter table product_group_unique add unique index 
ux_category_unit_model(category_id,unit_id,model_hash);
```

根據分類編號、單位編號和商品組屬性的 hash 值，可以唯一確定一個商品組。

給商品組防重表建立了`唯一索引`之後，第二天檢視資料，發現該表中竟然產生了重複的資料： ![img](https://f.pz.al/pzal/2022/09/13/a3f78e748a1a9.png) 表中第二條資料和第三條資料重複了。

這是為什麼呢？

## 2. 唯一索引欄位包含 null

如果你仔細檢視錶中的資料，會發現其中一個比較特殊地方：商品組屬性的 hash 值（model_hash 欄位）可能為 `null`，即商品組允許不配置任何屬性。

在 product_group_unique 表中插入了一條 model_hash 欄位等於 100 的重複資料： ![img](https://f.pz.al/pzal/2022/09/13/ea2c485b61aaf.png) 執行結果： ![img](https://f.pz.al/pzal/2022/09/13/ea655e7549a16.png) 從上圖中看出，mysql 的唯一性約束生效了，重複資料被攔截了。

接下來，我們再插入兩條 model_hash 為 null 的資料，其中第三條資料跟第二條資料中 category_id、unit_id 和 model_hash 欄位值都一樣。 ![img](https://f.pz.al/pzal/2022/09/13/a3f78e748a1a9.png) 從圖中看出，竟然執行成功了。

換句話說，如果唯一索引的欄位中，出現了 null 值，則唯一性約束不會生效。

最終插入的資料情況是這樣的：

1. 當 model_hash 欄位不為空時，不會產生重複的資料。
2. 當 model_hash 欄位為空時，會生成重複的資料。

> 我們需要特別注意：建立唯一索引的欄位，都不能允許為 null，否則 mysql 的唯一性約束可能會失效。

## 3. 邏輯刪除表加唯一索引

我們都知道唯一索引非常簡單好用，但有時候，在表中它並不好加。

不信，我們一起往下看。

通常情況下，要刪除表的某條記錄的話，如果用 `delete` 語句操作的話。

例如：

```sql
delete from product where id=123;
```

這種 delete 操作是`物理刪除`，即該記錄被刪除之後，後續透過 sql 語句基本查不出來。（不過透過其他技術手段可以找回，那是後話了）

還有另外一種是`邏輯刪除`，主要是透過 `update` 語句操作的。

例如：

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

邏輯刪除需要在表中額外增加一個刪除狀態欄位，用於記錄資料是否被刪除。在所有的業務查詢的地方，都需要過濾掉已經刪除的資料。

透過這種方式刪除資料之後，資料任然還在表中，只是從邏輯上過濾了刪除狀態的資料而已。

其實對於這種邏輯刪除的表，是沒法加唯一索引的。

為什麼呢？

假設之前給商品表中的 `name` 和 `model` 加了唯一索引，如果使用者把某條記錄刪除了，delete_status 設定成 1 了。後來，該使用者發現不對，又重新新增了一模一樣的商品。

由於唯一索引的存在，該使用者第二次新增商品會失敗，即使該商品已經被刪除了，也沒法再新增了。

這個問題顯然有點嚴重。

有人可能會說：把 `name`、`model` 和 `delete_status` 三個欄位同時做成`唯一索引`不就行了？

答：這樣做確實可以解決使用者邏輯刪除了某個商品，後來又重新新增相同的商品時，新增不了的問題。但如果第二次新增的商品，又被刪除了。該使用者第三次新增相同的商品，不也出現問題了？

由此可見，如果表中有邏輯刪除功能，是不方便建立唯一索引的。

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

### 3.1 刪除狀態 + 1

透過前面知道，如果表中有邏輯刪除功能，是不方便建立唯一索引的。

其根本原因是，記錄被刪除之後，delete_status 會被設定成 1，預設是 0。相同的記錄第二次刪除的時候，delete_status 被設定成 1，但由於建立了唯一索引（把 name、model 和 delete_status 三個欄位同時做成唯一索引），資料庫中已存在 delete_status 為 1 的記錄，所以這次會操作失敗。

我們為啥不換一種思考：不要糾結於 delete_status 為 1，表示刪除，當 delete_status 為 1、2、3 等等，只要大於 1 都表示刪除。

這樣的話，每次刪除都獲取那條相同記錄的最大刪除狀態，然後加 1。

這樣資料操作過程變成：

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，每次刪除時，delete_status 都不一樣，所以可以保證唯一性。

該方案的優點是：不用調整欄位，非常簡單和直接。

缺點是：可能需要修改 sql 邏輯，特別是有些查詢 sql 語句，有些使用 delete_status=1 判斷刪除狀態的，需要改成 delete_status>=1。

### 3.2 增加時間戳欄位

導致邏輯刪除表，不好加唯一索引最根本的地方在邏輯刪除那裡。

我們為什麼不加個欄位，專門處理邏輯刪除的功能呢？

答：可以增加`時間戳`欄位。

把 name、model、delete_status 和 timeStamp，四個欄位同時做成唯一索引

在新增資料時，timeStamp 欄位寫入預設值 `1`。

然後一旦有邏輯刪除操作，則自動往該欄位寫入時間戳。

這樣即使是同一條記錄，邏輯刪除多次，每次生成的時間戳也不一樣，也能保證資料的唯一性。

時間戳一般精確到`秒`。

除非在那種極限併發的場景下，對同一條記錄，兩次不同的邏輯刪除操作，產生了相同的時間戳。

這時可以將時間戳精確到`毫秒`。

該方案的優點是：可以在不改變已有程式碼邏輯的基礎上，透過增加新欄位實現了資料的唯一性。

缺點是：在極限的情況下，可能還是會產生重複資料。

### 3.3 增加 id 欄位

其實，增加時間戳欄位基本可以解決問題。但在在極限的情況下，可能還是會產生重複資料。

有沒有辦法解決這個問題呢？

答：增加`主鍵`欄位：delete_id。

該方案的思路跟增加時間戳欄位一致，即在新增資料時給 delete_id 設定預設值 1，然後在邏輯刪除時，給 delete_id 賦值成當前記錄的主鍵 id。

把 name、model、delete_status 和 delete_id，四個欄位同時做成唯一索引。

這可能是最優方案，無需修改已有刪除邏輯，也能保證資料的唯一性。

## 4. 重複歷史資料如何加唯一索引？

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

但來自靈魂的一問：如果某張表中，已存在`歷史重複資料`，該如何加索引呢？

最簡單的做法是，增加一張`防重表`，然後把資料初始化進去。

可以寫一條類似這樣的 sql：

```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;
```

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

那麼，這個唯一索引該怎麼加呢？

其實可以借鑑上一節中，增加 `id` 欄位的思路。

增加一個 delete_id 欄位。

不過在給 product 表建立唯一索引之前，先要做資料處理。

獲取相同記錄的最大 id：

```java
select max(id), select name,category_id,unit_id,model from product
group by name,category_id,unit_id,model;
```

然後將 delete_id 欄位設定成 1。

然後將其他的相同記錄的 delete_id 欄位，設定成當前的主鍵。

這樣就能區分歷史的重複資料了。

當所有的 delete_id 欄位都設定了值之後，就能給 name、model、delete_status 和 delete_id，四個欄位加唯一索引了。

完美。

## 5. 給大欄位加唯一索引

接下來，我們聊一個有趣的話題：如何給大欄位增加唯一索引。

有時候，我們需要給幾個欄位同時加一個唯一索引，比如給 name、model、delete_status 和 delete_id 等。

但如果 model 欄位很大，這樣就會導致該唯一索引，可能會佔用較多儲存空間。

我們都知道唯一索引，也會走索引。

如果在索引的各個節點中存大資料，檢索效率會非常低。

由此，有必要對唯一索引長度做限制。

目前 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 演算法會產生 hash 衝突，即兩個不同的值，透過 hash 演算法生成值相同。

當然如果還有其他欄位可以區分，比如：name，並且業務上允許這種重複的資料，不寫入資料庫，該方案也是可行的。

### 5.2 不加唯一索引

如果實在不好加唯一索引，就不加唯一索引，透過其他技術手段保證唯一性。

如果新增資料的入口比較少，比如只有 job，或者資料匯入，可以單執行緒順序執行，這樣就能保證表中的資料不重複。

如果新增資料的入口比較多，最終都發 mq 訊息，在 mq 消費者中單執行緒處理。

### 5.3 redis 分散式鎖

由於欄位太大了，在 mysql 中不好加唯一索引，為什麼不用 `redis分散式鎖`呢？

但如果直接加給 name、model、delete_status 和 delete_id 欄位，加 `redis分散式鎖`，顯然沒啥意義，效率也不會高。

我們可以結合 5.1 章節，用 name、model、delete_status 和 delete_id 欄位，生成一個 hash 值，然後給這個新值加鎖。

即使遇到 hash 衝突也沒關係，在併發的情況下，畢竟是小機率事件。

![img](https://f.pz.al/pzal/2022/09/13/8d5ddcaaeef5a.png)

## 6. 批次插入資料

有些小夥們，可能認為，既然有 redis 分散式鎖了，就可以不用唯一索引了。

那是你沒遇到，批次插入資料的場景。

假如透過查詢操作之後，發現有一個集合：list 的資料，需要批次插入資料庫。

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

```java
for(Product product: list) {
   try {
        String hash = hash(product);
        rLock.lock(hash);
        //查詢資料
        //插入資料
    } catch (InterruptedException e) {
       log.error(e);
    } finally {
        rLock.unlock();
    }
}
```

需要在一個迴圈中，給每條資料都加鎖。

這樣效能肯定不會好。

以上的環境博主都是部署在3A伺服器上，感興趣的夥伴可以自己部署一套環境進行測試。

當然有些小夥伴持反對意見，說使用 redis 的 `pipeline` 批次操作不就可以了？

也就是一次性給 500 條，或者 1000 條資料上鎖，最後使用完一次性釋放這些鎖？

想想都有點不靠譜，這個鎖得有多大呀。

極容易造成鎖超時，比如業務程式碼都沒有執行完，鎖的過期時間就已經到了。

針對這種批次操作，如果此時使用 mysql 的唯一索引，直接批次 insert 即可，一條 sql 語句就能搞定。

資料庫會自動判斷，如果存在重複的資料，會報錯。如果不存在重複資料，才允許插入資料。

明明加了唯一索引，為什麼還是產生了重複資料？

相關文章