什么是數(shù)據(jù)庫的物理和邏輯刪除，該用哪種？90%的人只能答一半

回答重點(diǎn)

邏輯刪除是一種將數(shù)據(jù)標(biāo)記為已刪除但實(shí)際不會從數(shù)據(jù)庫中移除的刪除方式。一般是在表中添加一個表示刪除狀態(tài)的字段，如 is_deleted ，默認(rèn)是 0 表示未刪除，1 表示已刪除。

物理刪除則是直接從數(shù)據(jù)庫中刪除記錄。

一般業(yè)務(wù)上都是使用邏輯刪除，便于后續(xù)的數(shù)據(jù)分析、追溯等。

擴(kuò)展知識

物理刪除的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

• 節(jié)省存儲空間：物理刪除釋放了數(shù)據(jù)占用的存儲空間。
• 查詢性能提高：由于數(shù)據(jù)被徹底刪除，查詢時不需要過濾已刪除的數(shù)據(jù)。

缺點(diǎn)：

• 不可恢復(fù)：一旦數(shù)據(jù)被刪除，無法恢復(fù)，除非有備份。
• 缺乏審計(jì)和追溯：無法追溯數(shù)據(jù)的歷史狀態(tài)和變更記錄。

邏輯刪除的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

• 數(shù)據(jù)可恢復(fù)：可以通過更改標(biāo)記字段的值來恢復(fù)數(shù)據(jù)。
• 支持審計(jì)和追溯：保留了數(shù)據(jù)的歷史狀態(tài)，有助于數(shù)據(jù)審計(jì)和分析。

缺點(diǎn)：

• 占用存儲空間：已刪除的數(shù)據(jù)仍然存在，占用存儲空間。
• 查詢性能影響：查詢時需要額外的條件來過濾已刪除的數(shù)據(jù)，可能影響性能。
• 復(fù)雜性增加：需要在查詢、更新等操作中考慮邏輯刪除字段的處理。

邏輯刪除與唯一性問題

很多時候表需要設(shè)置唯一索引來保證數(shù)據(jù)的唯一性。

例如用戶參加店鋪活動，我們有個活動記錄表以 userId+shopId 作為唯一索引，防止用戶重復(fù)參加一個店鋪活動也用于后續(xù)活動記錄。

后續(xù)如果用戶反悔了，他說不想?yún)⒓恿耍藭r一個方式就是直接物理刪除這條記錄，但是為了審計(jì)和后續(xù)的追溯，我們用的都是邏輯刪除，因此使用 is_deleted 字段，標(biāo)記為 1 表明已刪除。

但是用戶又反悔了，他又報(bào)名參加了，這時候由于我們將 userId+shopId 作為唯一索引，且這條字段僅僅是邏輯刪除，表中還存在這條記錄，所以此時就產(chǎn)生了唯一索引沖突，業(yè)務(wù)無法正常執(zhí)行下去。

這就是 邏輯刪除與唯一性問題。

怎么處理？把 userId+shopId+is_deleted 一起作為唯一索引？

也不行，is_deleted 只有 0 和 1，如果用戶多次反復(fù)橫跳，這個設(shè)計(jì)無法滿足。

常見有三種簡單的解決方式：

1）is_deleted 改為 deleted_at ，deleted_at 存儲的是時間戳，默認(rèn)為空。

把 userId+shopId+deleted_at 作為唯一索引，通過 deleted_at 是否為空來判斷是否被邏輯刪除，然后每次刪除把當(dāng)前時間設(shè)置到 deleted_at 字段上，這樣唯一索引就能繼續(xù)生效了。

由于時間戳的隨機(jī)性，不會產(chǎn)生唯一索引沖突

2）is_deleted 改為 bigint，存儲的是主鍵，默認(rèn)為 0 。

每次刪除，把當(dāng)前記錄的 id 設(shè)置到 is_deleted 字段，例如 id 是 2222 ，那么 is_deleted 就變?yōu)?2222。

這時候?qū)?userId+shopId+is_deleted 作為唯一索引，就可以解決用戶反復(fù)退出再加入的問題。

判斷已經(jīng)被刪除的邏輯從 is_deleted = 1 變成 is_deleted > 0。

3）復(fù)用一條記錄+日志表（流水表）

所謂的日志或者流水指的是機(jī)械地記錄用戶的操作記錄，不會做修改和刪除，只有新增。

用戶某時某刻參加了活動，則記錄。

用戶退出了活動，則記錄。

審計(jì)和追溯可以使用流水表。這時候活動表僅需設(shè)置 userId+shopId 為唯一索引即可，用戶參加活動 is_deleted 為 0 ，退出活動改為 1，再次參加繼續(xù)改為 0 ，僅需在一條記錄上修改記錄。