明明加了唯一索引,為什麼還是產生了重複資料?
前言
前段時間我踩過一個坑:在mysql8
的一張innodb
引擎的表
中,加了唯一索引
,但最後發現資料
竟然還是重複
了。
到底怎麼回事呢?
本文通過一次踩坑經歷,聊聊唯一索引,一些有意思的知識點。
1.還原問題現場
前段時間,為了防止商品組產生重複的資料,我專門加了一張防重表
。
如果大家對防重表,比較感興趣,可以看看我的另一篇文章 《高併發下如何防重?》,裡面有詳細的介紹。
問題就出在商品組的防重表上。
具體表結構如下:
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;
為了保證資料的唯一性
,我給那種商品組防重表,建了唯一索引:
alter table product_group_unique add unique index
ux_category_unit_model(category_id,unit_id,model_hash);
根據分類編號、單位編號和商品組屬性的hash值,可以唯一確定一個商品組。
給商品組防重表建立了唯一索引
之後,第二天檢視資料,發現該表中竟然產生了重複的資料: 表中第二條資料和第三條資料重複了。
這是為什麼呢?
2.唯一索引欄位包含null
如果你仔細查看錶中的資料,會發現其中一個比較特殊地方:商品組屬性的hash值(model_hash欄位)可能為null
,即商品組允許不配置任何屬性。
在product_group_unique表中插入了一條model_hash欄位等於100的重複資料: 執行結果:
從上圖中看出,mysql的唯一性約束生效了,重複資料被攔截了。
接下來,我們再插入兩條model_hash為null的資料,其中第三條資料跟第二條資料中category_id、unit_id和model_hash欄位值都一樣。 從圖中看出,竟然執行成功了。
換句話說,如果唯一索引的欄位中,出現了null值,則唯一性約束不會生效。
最終插入的資料情況是這樣的:
- 當model_hash欄位不為空時,不會產生重複的資料。
- 當model_hash欄位為空時,會生成重複的資料。
我們需要特別注意:建立唯一索引的欄位,都不能允許為null,否則mysql的唯一性約束可能會失效。
3.邏輯刪除表加唯一索引
我們都知道唯一索引非常簡單好用,但有時候,在表中它並不好加。
不信,我們一起往下看。
通常情況下,要刪除表的某條記錄的話,如果用delete
語句操作的話。
例如:
delete from product where id=123;
這種delete操作是物理刪除
,即該記錄被刪除之後,後續通過sql語句基本查不出來。(不過通過其他技術手段可以找回,那是後話了)
還有另外一種是邏輯刪除
,主要是通過update
語句操作的。
例如:
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。
這樣資料操作過程變成:
- 新增記錄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都不一樣,所以可以保證唯一性。
該方案的優點是:不用調整欄位,非常簡單和直接。
缺點是:可能需要修改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:
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:
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衝突也沒關係,在併發的情況下,畢竟是小概率事件。
6.批量插入資料
有些小夥們,可能認為,既然有redis分散式鎖了,就可以不用唯一索引了。
那是你沒遇到,批量插入資料的場景。
假如通過查詢操作之後,發現有一個集合:list的資料,需要批量插入資料庫。
如果使用redis分散式鎖,需要這樣操作:
for(Product product: list) {
try {
String hash = hash(product);
rLock.lock(hash);
//查詢資料
//插入資料
} catch (InterruptedException e) {
log.error(e);
} finally {
rLock.unlock();
}
}
需要在一個迴圈中,給每條資料都加鎖。
這樣效能肯定不會好。
當然有些小夥伴持反對意見,說使用redis的pipeline
批量操作不就可以了?
也就是一次性給500條,或者1000條資料上鎖,最後使用完一次性釋放這些鎖?
想想都有點不靠譜,這個鎖得有多大呀。
極容易造成鎖超時,比如業務程式碼都沒有執行完,鎖的過期時間就已經到了。
針對這種批量操作,如果此時使用mysql的唯一索引,直接批量insert即可,一條sql語句就能搞定。
資料庫會自動判斷,如果存在重複的資料,會報錯。如果不存在重複資料,才允許插入資料。
最後說一句(求關注,別白嫖我)
如果這篇文章對您有所幫助,或者有所啟發的話,幫忙掃描下發二維碼關注一下,您的支援是我堅持寫作最大的動力。 求一鍵三連:點贊、轉發、在看。 關注公眾號:【蘇三說技術】,在公眾號中回覆:面試、程式碼神器、開發手冊、時間管理有超讚的粉絲福利,另外回覆:加群,可以跟很多BAT大廠的前輩交流和學習。
- 快看!這隻貓兩次登上 Github Trending !!!
- 百萬級資料excel匯出功能如何實現?
- SQL為什麼動不動就N百行以K計?
- 一次SpringBoot版本升級,引發的血案
- MYSQL中的14個神仙功能,驚豔到我了!!!
- 瞧瞧別人家的API介面,那叫一個優雅
- 同事吐槽我的介面效能差,原來它是真凶!
- 如何讓JSON跑得更快?
- 糟了,線上服務出現OOM了
- 7000字 24張圖帶你徹底弄懂執行緒池
- 分頁 模糊查詢 有坑!
- 這12款idea外掛,能讓你程式碼飛起來
- 聊聊資料庫建表的15個小技巧
- 明明加了唯一索引,為什麼還是產生了重複資料?
- 明明加了唯一索引,為什麼還是產生了重複資料?
- Java程式碼優化的30個小技巧
- 聊聊Java中程式碼優化的30個小技巧
- 盤點一下,99%的Java程式設計師會踩的坑
- 聊聊spring事務失效的12種場景,太坑了
- 從20s優化到500ms,我用了這三招