上一篇文章講了表設(shè)計的18條軍規(guī)，其中講到了唯一索引的坑，今天就來細(xì)說一下。

閉眼建表所需的18條軍規(guī)

在之前的工作中，遇到過一次唯一索引的 Bug，今天就分享分享，省的有同兄弟踩坑里。

為眾人抱薪者，不可使其凍斃于風(fēng)雪。兄弟們一鍵三連?。。。「兄x！

一、現(xiàn)場還原

先看表結(jié)構(gòu)，其中 name、age、city 三個字段創(chuàng)建一個聯(lián)合唯一索引。

CREATE TABLE `test` (
  `id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`age`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

如果在這三個字段上創(chuàng)建一個聯(lián)合唯一索引，那么就不會存在兩行數(shù)據(jù)在這三個字段上的值完全相同。

下面來看一組數(shù)據(jù)。

通過上圖可以看到，北京的張三有多個，畢竟年齡可以不一樣嗎，其中有兩個 age 為空的，同樣上海的李四也有多個。

有的同學(xué)可能會說了，是不是唯一索引沒生效啊，那我們現(xiàn)在試一下唯一索引的生效情況。

可以看到已經(jīng)報錯，對于這個唯一索引已經(jīng)出現(xiàn)了重復(fù)的數(shù)據(jù)，那么究竟是什么造成了唯一索引的失效呢？

二、原因分析

就是因為我們插入的數(shù)據(jù)中 age 為 Null 導(dǎo)致的。在 MySQL 官方文檔中也有說明，Null 與任何值都不相等，包括與另一個 Null 比較。

MySQL 關(guān)于Null 描述地址：https://dev.mysql.com/doc/refman/8.4/en/problems-with-null.html

所以結(jié)論就是：當(dāng)多個字段一起創(chuàng)建唯一索引時，需要設(shè)置每一項字段非空，如果其中一項出現(xiàn) Null 值，MySQL 的唯一索引會失效。

三、邏輯刪除

說到唯一索引，就要想到有邏輯刪除這個東西，對于現(xiàn)在的系統(tǒng)來說，10個系統(tǒng)里面8個是用邏輯刪除。

對于系統(tǒng)中記錄的刪除，一般是兩種，一種是物理刪除，也就是使用delete語句進(jìn)行刪除。另一種就是使用邏輯刪除，在表中增加一個刪除標(biāo)記字段，比如deleted，默認(rèn)0，當(dāng)需要刪除時改為1。

通過物理刪除的數(shù)據(jù)，在表中是查詢不出來的，不過可以通過 binlog 進(jìn)行恢復(fù)，如果你感興趣，歡迎評論區(qū)留言，下一篇咱就講一下如何還原數(shù)據(jù)。

通過邏輯刪除的數(shù)據(jù)，在表中還是存在的，只是刪除標(biāo)記deleted變成了1。

我們還拿上面那張表舉例，假如我們刪除了張三、18、北京這條記錄，如果后面再次插入張三、18、北京這條數(shù)據(jù)是無法插入的，因為我們創(chuàng)建的唯一索引是name、`age、city三個字段。

那么這種情況我們可以怎么解決呢？

有的同學(xué)可能就會說了，在上面name、`age、city唯一索引的基礎(chǔ)上，增加deleted，創(chuàng)建4個字段的唯一索引不就行了。

那么真的可以嗎？

還是那句話，用上面的表舉個栗子：

下面跟我一起來看下如何解決？

四、刪除狀態(tài)加1

第一種方式就是刪除狀態(tài)加1，現(xiàn)在我們表中deleted默認(rèn)0，如果刪除了之后就是1，那么我們更改為，deleted默認(rèn)0，刪除之后獲取當(dāng)前相同記錄的最大刪除狀態(tài)，然后加1。

舉個栗子：

通過上面的例子可以看出來，每次刪除記錄，當(dāng)前記錄的刪除標(biāo)記deleted都會獲取當(dāng)前相同記錄的最大刪除狀態(tài)，然后加1進(jìn)行刪除。

這樣deleted 每次刪除的時候都是不一樣的，所以可以保證唯一索引的生效。

使用這種方案的缺點(diǎn)就是需要修改代碼中的sql邏輯，比如查詢deleted為1的刪除數(shù)據(jù)時需要改為deleted>1。

五、增加時間戳

除了上面這種對刪除狀態(tài)進(jìn)行加1的方式外，還可以增加一個時間戳字段，創(chuàng)建name、`age、city、timestamp四個字段的聯(lián)合唯一索引。

時間戳一般精確到秒，如果并發(fā)高，還是可能生成重復(fù)數(shù)據(jù)，那么時間戳的話可以精確到毫秒。

然后設(shè)置時間戳字段默認(rèn)值為1，當(dāng)進(jìn)行邏輯刪除刪除時，直接插入當(dāng)前時間的時間戳。

這種方案的優(yōu)點(diǎn)就是不用修改原來代碼邏輯，缺點(diǎn)就是極限情況下還是可能會產(chǎn)生重復(fù)數(shù)據(jù)。

六、增加刪除ID

我們還可以使用增加刪除ID的方法來進(jìn)行去重。

創(chuàng)建唯一索引name、`age、city、deleted_id。

插入數(shù)據(jù)時deleted_id默認(rèn)1，當(dāng)進(jìn)行邏輯刪除時修改為當(dāng)前記錄的主鍵ID。

這種方法與增加時間戳字段類似，優(yōu)點(diǎn)就是可以解決時間戳字段的重復(fù)數(shù)據(jù)問題，并且無需修改現(xiàn)有系統(tǒng)的刪除邏輯，也可以保證數(shù)據(jù)的唯一，所以如果再有邏輯刪除的表中，推薦使用這種方式。

七、歷史數(shù)據(jù)加唯一索引

在上面的幾個方案中，都是對新表添加的唯一索引，現(xiàn)在有一張歷史數(shù)據(jù)表，其中還有重復(fù)數(shù)據(jù)，那么我們該如何添加索引呢？

我們使用deleted_id的方案，首先獲取出相同記錄的最大ID，然后將這些記錄的deleted_id設(shè)置為1，然后其他的記錄deleted_id就是當(dāng)前記錄的主鍵，這樣我們就可以區(qū)分表中的重復(fù)數(shù)據(jù)了。

當(dāng)表中的deleted_id都有值之后，創(chuàng)建唯一索引name、`age、city、deleted_id。

八、大字段添加唯一索引

創(chuàng)建索引的要求大家應(yīng)該都知道，字段不可以太大，因為索引本身大了之后檢索的效率也是很低的。

關(guān)于MySQL 中 InnoDB 引擎的限制可以查看這個鏈接：https://dev.mysql.com/doc/refman/8.4/en/innodb-limits.html

對于大的字段添加唯一索引，可以使用hash算法，創(chuàng)建一個hash字段，將大字段進(jìn)行Hash運(yùn)算之后的結(jié)果保存到 hash 中，然后創(chuàng)建唯一索引name、age、city、hash。

用到Hash算法，肯定就會有Hash沖突，所以這種方案會帶來一個問題就是不同的值Hash卻相同。

所以創(chuàng)建多列的聯(lián)合唯一索引時需要在增加一個其他的字段進(jìn)行區(qū)分。

還有一種方案就是不使用唯一索引，使用唯一索引的目的就是去重，直接代碼層面MQ、單線程處理等。

總結(jié)

本文通過還原唯一索引失效的場景，得出當(dāng)多列唯一索引中的某一列有為Null的值時，唯一索引會失效。

總結(jié)出在有邏輯刪除的業(yè)務(wù)表中，可以通過刪除狀態(tài)值加1、增加時間戳字段、增加刪除ID字段三種方式進(jìn)行添加多列唯一索引。

最后還給出了如何對歷史重復(fù)數(shù)據(jù)添加唯一索引以及使用hash給大字段添加唯一索引的方式。