什么是数据库的物理和逻辑删除，该用哪种？90%的人只能答一半

回答重点

逻辑删除是一种将数据标记为已删除但实际不会从数据库中移除的删除方式。一般是在表中添加一个表示删除状态的字段，如 is_deleted ，默认是 0 表示未删除，1 表示已删除。

物理删除则是直接从数据库中删除记录。

一般业务上都是使用逻辑删除，便于后续的数据分析、追溯等。

扩展知识

物理删除的优缺点

优点：

• 节省存储空间：物理删除释放了数据占用的存储空间。
• 查询性能提高：由于数据被彻底删除，查询时不需要过滤已删除的数据。

缺点：

• 不可恢复：一旦数据被删除，无法恢复，除非有备份。
• 缺乏审计和追溯：无法追溯数据的历史状态和变更记录。

逻辑删除的优缺点

优点：

• 数据可恢复：可以通过更改标记字段的值来恢复数据。
• 支持审计和追溯：保留了数据的历史状态，有助于数据审计和分析。

缺点：

• 占用存储空间：已删除的数据仍然存在，占用存储空间。
• 查询性能影响：查询时需要额外的条件来过滤已删除的数据，可能影响性能。
• 复杂性增加：需要在查询、更新等操作中考虑逻辑删除字段的处理。

逻辑删除与唯一性问题

很多时候表需要设置唯一索引来保证数据的唯一性。

例如用户参加店铺活动，我们有个活动记录表以 userId+shopId 作为唯一索引，防止用户重复参加一个店铺活动也用于后续活动记录。

后续如果用户反悔了，他说不想参加了，此时一个方式就是直接物理删除这条记录，但是为了审计和后续的追溯，我们用的都是逻辑删除，因此使用 is_deleted 字段，标记为 1 表明已删除。

但是用户又反悔了，他又报名参加了，这时候由于我们将 userId+shopId 作为唯一索引，且这条字段仅仅是逻辑删除，表中还存在这条记录，所以此时就产生了唯一索引冲突，业务无法正常执行下去。

这就是 逻辑删除与唯一性问题。

怎么处理？把 userId+shopId+is_deleted 一起作为唯一索引？

也不行，is_deleted 只有 0 和 1，如果用户多次反复横跳，这个设计无法满足。

常见有三种简单的解决方式：

1）is_deleted 改为 deleted_at ，deleted_at 存储的是时间戳，默认为空。

把 userId+shopId+deleted_at 作为唯一索引，通过 deleted_at 是否为空来判断是否被逻辑删除，然后每次删除把当前时间设置到 deleted_at 字段上，这样唯一索引就能继续生效了。

由于时间戳的随机性，不会产生唯一索引冲突

2）is_deleted 改为 bigint，存储的是主键，默认为 0 。

每次删除，把当前记录的 id 设置到 is_deleted 字段，例如 id 是 2222 ，那么 is_deleted 就变为 2222。

这时候将 userId+shopId+is_deleted 作为唯一索引，就可以解决用户反复退出再加入的问题。

判断已经被删除的逻辑从 is_deleted = 1 变成 is_deleted > 0。

3）复用一条记录+日志表（流水表）

所谓的日志或者流水指的是机械地记录用户的操作记录，不会做修改和删除，只有新增。

用户某时某刻参加了活动，则记录。

用户退出了活动，则记录。

审计和追溯可以使用流水表。这时候活动表仅需设置 userId+shopId 为唯一索引即可，用户参加活动 is_deleted 为 0 ，退出活动改为 1，再次参加继续改为 0 ，仅需在一条记录上修改记录。