逻辑删除和唯一索引

逻辑删除与物理删除

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删除，实现数据的最终物理删除。

解决逻辑删除与唯一索引冲突的问题

使用物理删除

如果在业务角度可以接受这种情况，比如不需要回溯历史、简单的关联表，推荐优先使用物理删除。

使用历史表 （不推荐）

主表进行物理删除，同时将删除的记录保存到历史表中。

缺点：所有的表都要有个历史表，大材小用。

使用redis保证唯一约束，不使用数据唯一索引 （不推荐）

取消表的唯一约束，在项目中引入redis，通过redis来判重，新增时往redis set记录，删除时，删除redis记录。

缺点：额外增加复杂度，同时还得保证redis和数据库的一致性。

变更删除标记为时间戳

将删除状态不以0,1表示，而是以时间戳为值，然后将删除状态为与之前的唯一约束A重新组成唯一联合约束index（A、del_flag），删除时变更del_flag的时间戳。

优点：不仅可以记录删除状态，还会记录删除时间。

保留删除标记，同时新建一个字段del_unique_key （适用于已有的业务改造）

保留删除状态位，再新增一个字段del_unique_key,该字段默认值为0，字段类型和大小与主键id保持一致，同时与原先的唯一约束重新组成联合唯一约束index(A,del_unique_key),业务进行逻辑删除，变更del_unique_key的值为该删除行的主键id。

变更逻辑删除未删除值为0 删除为null

添加唯一索引时，除了业务相关字段，把逻辑删除字段也包含在唯一索引中，null不会与任何值形成有效的索引。

mybatis-plus:  
  global-config:  
    db-config:  
      logic-delete-field: deleted  
      logic-not-delete-value: 0  
      logic-delete-value: "null"

当Mysql中建立的联合索引， 只要索引中的某一列的值为空时(NULL)，即便其他的字段完全相同，也不会引起唯一索引冲突。