資料庫，邏輯刪還是物理刪？

三分惡發表於2021-04-02
資料庫

相信很多人都聽過刪庫跑路這個詞，用搜尋引擎檢索刪庫跑路，可以看到很多程式設計師刪庫跑路的討論和新聞。

刪庫跑路

但是，嚴格來講，大部分程式設計師想刪庫跑路也做不到。為什麼呢？因為沒有資料庫的刪除許可權。——真正能刪庫跑路的是運維，再準確點DBA。

刪庫跑路

那麼平時業務中的刪除是怎麼做的呢？答案是邏輯刪除。

邏輯刪除：又名軟刪除，與物理刪除、硬刪除相對應，含義是並沒有實際的刪除資料，只是將資料標記已刪除（例如增加is_deleted欄位）。

為什麼要用邏輯刪除？

物理刪除很好理解，就是真的把資料給刪了。

以MySQL為例，假如資料刪了，應該怎麼恢復呢？主要方式有兩種。

利用 binlog 日誌

binlog是記錄所有資料庫表結構變更（例如CREATE、ALTER TABLE…）以及表資料修改（INSERT、UPDATE、DELETE…）的二進位制日誌。

使用binlog恢復資料，本質上就是通過binlog找到所有DML操作，去掉錯誤的SQL語句，然後執行其它的SQL語句，就可以將資料恢復。

binlog恢復資料示意圖如下：

binlog恢復資料

可以看到，恢復時候不僅需要停掉資料庫，假如資料量大的話，去篩選恢復資料sql就?

資料庫延時同步節點

  • 給資料庫配置一個同步資料的資料庫
  • 同步時間，要延時：比如 1 小時同步一次
  • 當出現問題的時候，只要在這個延時時間內，都還可以恢復出一定的資料

所以，綜上，可以認識到，資料刪除之後的恢復是要付出很大代價的，而且還存在不可恢復的風險。所以出於安全考慮，生產環境資料庫應當儘可能禁止物理刪除。

邏輯刪除會帶來什麼問題？

資料冗餘

這個不用說，資料沒有實際刪除，自然會產生大量的對業務無用的冗餘資料。

增加開發複雜度

寫sql進行資料處理時需要排除那些已經邏輯刪除的資料，這就會導致sql複雜，容易出錯，特別是涉及多表查詢時。

例如：

  select t1.name,t2.category from product t1
     left join category t2 on t1.category_id=t2.id
   where
    t1.is_deleted=1
    and t1.is_deleted=1

影響惟一性約束

如果資料表的某個欄位要求唯一，並強制約束，比如使用者表中的登入使用者名稱欄位，設計為邏輯刪除的話，一旦有新的同使用者名稱記錄就無法插入。但如果不將該欄位設定為唯一性約束的，那麼在每次插入資料的時候，都需先進行一次查詢，看看有無未（邏輯）刪除的同名記錄存在。

惟一性約束

如何設計邏輯刪除？

首先需要在表裡設計一個刪除的標誌欄位is_deleted。

插入資料資料時，這個值預設為0。刪除資料時將這個值設定為1。查詢和更新資料時都將‘deleted=0’這個條件帶上，只查詢和更新沒有刪除的資料。

是不是很簡單？但別忘了我們上面提到的影響惟一性約束的問題。這個該怎麼解決呢？

可以將唯一約束欄位和刪除相關的欄位建立成組合唯一索引：

  • 將刪除標記設定預設值(例如0)，將唯一欄位與刪除標記新增唯一鍵約束。當某一記錄需要刪除時，將刪除標記置為NULL。

    由於NULL不會和其他欄位有組合唯一鍵的效果，所以當記錄被刪除時(刪除標記被置為NULL時)，解除了唯一鍵的約束。此外該方法能很好地解決批量刪除的問題(只要置為NULL就完事了)，消耗的空間也並不多(1位 + 聯合索引)。

  • NULL在某些情況下是存在一些問題的，刪除時可以將刪除標記更新為主鍵，這樣同樣保證了唯一約束欄位和刪除標記組合索引的唯一性。

  • 還可以用另外一種方案，新增一個刪除時間delete_time的欄位，設定一個不為NULLl的預設值，和惟一欄位組成聯合唯一索引，當進行邏輯刪除的時候同時要更新delete_time,這樣同樣可以保證惟一性。

如果是Java語言開發，推薦MyBatis Plus框架，提供了比較好的邏輯刪除支援。

應該用邏輯刪除嗎？

到現在我們認識到，邏輯刪除有利好之處，但是也要付出一定的代價。那麼應該用邏輯刪除嗎？

  • 從專案的規模來講

    • 一般的建議是小型系統可以採用物理刪除，因為資料恢復的成本，或者說資料的價值，相比較使用邏輯刪除的開發、運維付出要少。但是物理刪除!=隨意刪除,要認識到刪除操作的風險，重要資料應該設計歷史表，刪除之前將刪除的資料複製到歷史表。

    • 中大型專案應該採用邏輯刪除,有一句話“資料是無價的”。必須承認，在很多時候，資料的價值是遠遠高於人工的成本的。

  • 從資料價值來講

    • 價值比較高的資料，如電商系統的訂單之類的，毫無疑問，一般都是隻允許邏輯刪除的，及時必須要做物理刪除,也要通過備份表、備份日誌等方式保證資料可以快速恢復。

    • 價值比較低的資料，比如使用者操作日誌之類，這種資料價值不高，而且資料量很大，在資源有限的情況下，可以考慮物理刪除,但是這種危險操作儘量也不要由系統功能去做，而應該由專業的DBA去完成——至於怎麼降低DBA操作的風險，大概就得靠制度了。


參考：

【1】：邏輯刪除實現方案

【2】：邏輯刪除還是物理刪除

【3】：MySQL 通過 binlog 恢復資料

【4】：使用binlog日誌恢復MySQL資料庫刪除資料的方法

【5】：實現資料邏輯刪除的一種方案

【6】：邏輯刪除實現方案

相關文章