数据库运维管理系列之逻辑删除和物理删除的传统套路

数据库运维管理系列之逻辑删除和物理删除的传统套路

前几日，一位做产品经理的老友问我逻辑删除和物理删除的区别，想起很久以前就想写一写关于逻辑删除和物理删除的事情，加上近日家中亲人较多，为逃避热闹到公司加班，难得清闲，正好写一写。

首先，简单介绍一下逻辑删除和物理删除。根据百度百科释义：

逻辑删除是指文件没有被真正的删除，只不过是文件名的第一个字节被改成操作系统无法识别的字符。通常这种删除操作是可逆的，就是说用适当的工具或软件可以把删除的文件恢复出来。

物理删除指文件存储所用到的磁存储区域被真正的擦除或清零，这样删除的文件是不可以恢复的。

定义说的很明白，简言之，逻辑删除尚可恢复，物理删除不可恢复，一个是再见，一个是永别。逻辑删除和物理删除的设计思路体现在设计和生活的很多方面，例如计算机的垃圾箱功能，生活中的垃圾桶等等。就数据库方面而言，不太严谨的理解可以是这样：物理删除是指用delete删除某一些数据；逻辑删除是指用update将某一些数据的is_delete字段更新是已删除。之所以说不太严谨，是因为delete的数据是可以通过日志文件恢复的。做DBA的几年的工作经历发现关于逻辑删除有这几种情况：

（1）有的公司毫无逻辑删除的意识，直接delete，出了问题则通过日志尝试恢复数据，日志不完备，则只好放弃；

（2）有的公司不允许开发直接delete，只能update，但是权限未做控制，导致开发在实际工作中并未严格执行，仍有delete的情况，情况多了，DBA很被动；

（3）还有的公司做到了逻辑删除update，但是只是止步于update，未做进一步的设计，导致原表数据越来越大，数据库性能越来越差，最后不得不花力气进行优化……等等情况很多，那么怎么设计逻辑删除呢？

设计逻辑删除并不难，不考虑新的技术方案（没有深入研究），比较传统的套路大致如下：

1、线上生产库的读写权限回收delete、truncate等权限。对于新设计的系统建议采用直接回收权限，切断delete途径，每一个表新增is_delete和update_time字段，对表的删除进行更新is_delete和update_time（有的时候会考虑设计delete_time，但大多数情况下update_time足够，如果is_delete为已删除，则该条语句不应该再修改，update_time即为该条数据的删除时间）；对于已经存在的系统，可能存在delete的情况，比较常用的做法是分析sql执行记录日志，如oracle的awr报告、mysql的general_log，通过sql执行日志记录分析目前存在delete的表和语句，整理并通知开发，同时开发在代码层面进行全局关键字搜索，将涉及delete的地方整理，进行修改，实现无delete的情况，这是一个比较痛苦的过程，需要研发、测试和DBA 共同努力。

2、由DBA与研发、产品等人员第一次确认is_delete的数据的保留时间。例如保留7天即可，则通过ETL的方式定时的将表中is_delete为已删除，且update_time/delete_time在7天以前的数据迁移至归档库。归档库的表结构与生产库表结构一致但有新增字段。因为可能涉及到历史数据分析，归档库的设计可以作为数据仓库的一部分，也可以与数据仓库分开存储。Is_delete为已删除的数据由生产库迁移至归档库之后，将生产库的该数据进行delete删除。对于归档库有的人会在生产库设计归档表，将逻辑删除的数据放到归档表，这个也是可以的，但是建议使用归档库，尽量实现生产库的瘦库瘦表。

3、由DBA与产品、数据分析等人员第二次确认历史数据的保留时间。例如保留最近1年、3年等。通过ETL定时将归档库的过期数据进行delete删除，实现数据的最终物理删除。

整个过程中，数据以行为单位，其生命周期如下：

在落地实施过程中有以下几点需要特别注意：

1、 在回收delete权限的时候，要将开发库、测试库等一并回收，保持生产库权限与开发库、测试库等一致，避免因环境不一致导致返工。

2、 Is_delete字段在设计的时候，整个公司的所有数据库要尽量做到字典值一致，如：0-未删除，1-已删除。增加is_delete的同时要增加update_time/delete_time等时间戳字段和updator操作人字段，同时业务上要尽量避免对于is_delete为已删除的数据进行再次修改。

3、 两次删除数据的保留时间要确认清楚。不宜太久，也不宜过短，这个需要根据实际业务情况和所处行业的某些法规要求等。

4、 在处理已经存在的系统进行改造的时候，要尽量缓和一些，增加测试覆盖面，避免生产出现问题。

5、 归档库的表除了要跟生产库的相应表保持一致的是，还要增加新的时间戳等字段。

6、 在做ETL迁移逻辑删除数据到归档库的时候要记得将生产库的逻辑删除数据进行delete物理删除。同时定时任务要确保能够按时准确的执行。