数据库记录的物理删除与逻辑删除（硬删除与软删除）有何区别？

Question

相较于实际或物理删除记录，逻辑/软删除记录的优点是什么（即设置标志表示记录已删除）？

这种做法常见吗？

这样做安全吗？

- user21826

---

使用删除时间戳，而不是标记。 - Dave Jarvis

一面旗帜并不提供任何关于行被删除的时间信息。时间信息有很多用途，包括系统调试。 - Dave Jarvis

软删除不会添加有用的审计数据。如果您想创建审计历史记录，请创建一个专注于此目的的次要表。它甚至可以存储先前的版本，并且不会使开发和报告变得非常繁琐。 - Matthew Whited

在实施硬删除之前，请考虑是否删除了仍然需要访问的数据。例如，在博客网站上，从用户表中物理删除用户可能还会删除显示该用户博客条目所需的数据。禁用用户帐户是足够的，并且类似于软删除。 - Dave F

@DaveF，现在你必须非常小心地思考这个问题。由于GDPR和其他各个领域的立法，仅仅禁用用户账户是不够的，如果他们要求删除。你可以选择匿名化而不是删除，但即使如此也有一些限制。 - Luke

Answer 1

优点是您可以保留历史记录（有利于审核），而且不必担心通过引用要删除的行的其他各种数据库表级联删除。缺点是您必须编写任何报告/显示方法以考虑标记。

至于是否是常见做法，我会说是的，但是与任何东西一样，您是否使用它取决于您的业务需求。

编辑：想到了另一个缺点-如果在表上有唯一索引，则已删除记录仍将占用“一个”记录。所以您还需要针对这种可能性进行编码（例如，具有唯一索引的用户表用户名；已删除的记录仍会阻止已删除用户的用户名用于新记录。解决此问题，您可以为已删除的用户名列添加GUID，但这是一个非常hacky的解决方案，我不建议使用。在这种情况下，最好只是制定一个规则，即一旦使用了用户名，就不能替换它。）

- Chris

---

@ChrisShaffer，作为另一种选择，可以选择仅对未删除的行进行索引，而不是使用GUID。例如：CREATE UNIQUE INDEX ... WHERE DELETED_AT is null（在PostgreSQL中），这样任何具有删除日期的行都将不会被索引。（它们可以包含在非唯一索引中）。 - KajMagnus

@Chris Shaffer说：“你不需要担心将删除级联到其他各种表格。”这并不正确，你必须手动转发软删除，这非常麻烦并且会导致不一致。这实际上是一个缺点，因为不再有外键关系的强制执行。你很快就会得到数据垃圾。 - Stefan Steiger

@Chris Shaffer：引用“你保留历史记录（有利于审计）”。再次声明，不是这样的。如果我更新表中的值（或通过SSMS意外更新），您将无法记录旧值。如果我意外删除（使用真实删除）一行，则也不会留下记录。软删除作为审计替代品失败。 - Stefan Steiger

https://abstraction.blog/2015/06/28/soft-vs-hard-delete#comparison-factors - Abolfazl Aghili

对于担心删除印章会影响索引的问题，是否可以通过使用过滤索引来解决呢？https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/ - undefined

Answer 2

逻辑删除是常见的做法吗？是的，我在很多地方都看到过。它们是否安全？这取决于它们是否比删除前的数据更不安全。

当我担任技术主管时，我要求团队保留每一条数据，我知道我们将使用所有这些数据构建各种BI应用程序，尽管当时我们不知道需求会是什么样子。虽然从审计、故障排除和报告角度来说这是不错的（这是一个面向B2B交易的电子商务/工具网站，如果有人使用工具，即使他们的帐户以后被关闭了，我们也想记录下来），但它确实有几个缺点。

这些缺点包括（不包括其他已经提到的）：

1. 保留所有数据对性能的影响，我们需要开发各种归档策略。例如，应用程序的某个区域接近每周生成约1GB的数据。
2. 随着时间推移，保留数据的成本会增长，虽然磁盘空间很便宜，但保持和管理在线和离线多TB数据的基础设施量很大。确保备份顺畅进行需要大量的磁盘冗余以及人力投入。

在决定使用逻辑删除、物理删除或归档时，我会问自己以下问题：

1. 这是可能需要重新插入到表中的数据。例如，用户帐户符合此类别，因为您可能会激活或停用用户帐户。如果是这种情况，则逻辑删除是最合适的选择。
2. 存储数据是否具有固有价值？如果有，将生成多少数据。根据这个问题，我要么选择逻辑删除，要么实现一个归档策略。请记住，您始终可以对逻辑删除的记录进行归档。

- Josh

---

在您的用户账户示例中，将活动和非活动用户保留在不同的表中是否是一个好主意？例如，“已激活”表和“已停用”表模式 - Id，Name，etc.. 在“已激活”中的行 - 1001，Smith007，等等... 当他被停用时，我们可以清除除了Smith的ID列以外的所有内容，并将他添加到“已停用”。 - Erran Morad

如果你要保留Id和行，那么将所有数据移动有什么好处呢？也许对于大型记录可能有好处，但我认为这只是微观优化。 - JoshBerke

如果你需要在表之间移动数据，那么祝你好运处理级联外键约束。 - CAD bloke

Answer 3

可能有点晚了，但我建议大家查看Pinal Dave的博客文章，了解逻辑/软删除相关内容：

我根本不喜欢这种设计[软删除]。我坚信的架构是，只有必要的数据应该在单个表中，无用的数据应该移动到归档表中。建议使用两个不同的表：一个包含订单，另一个包含已删除的订单，而不是遵循isDeleted列。在这种情况下，您将不得不维护两个表，但实际上，它非常容易维护。当您编写UPDATE语句时，请编写INSERT INTO另一个表并从原始表中DELETE它。如果情况是回滚，则编写另一个INSERT INTO和相反顺序的DELETE。如果您担心事务失败，请在TRANSACTION中包装此代码。

在上述情况下，较小表格与较大表格的优点是什么？

- 较小的表格易于维护 - 索引重建操作速度更快 - 将归档数据移动到另一个文件组将减轻主文件组的负载（考虑到所有文件组都在不同的系统上）-这也会加快备份。 - 统计信息将由于较小的大小而频繁更新，这将减少资源消耗。 - 索引大小将变小 - 表的性能将随着表格大小的减小而提高。

- Tohid

---

你会如何使用这种方法来处理外键？可能有1个、10个或更多其他表引用正在被删除并移动到另一个表的记录！ - sam360

@sam360 - 这是一个巨大的挑战。老实说，我个人在我的项目中未能成功实现上述建议，因为需要处理主键和表之间的关系。不幸的是，那篇文章中没有真实世界的例子。我正在我的一个项目中努力寻找解决方案，如果它被证明是一个好的实现，我会与你分享代码... - Tohid

@eugene - 我不知道这种解决方案的具体术语。如果你能理解，它是一种真正的“删除”行并将已删除记录保留在“存档”表中的方法。 - Tohid

@Tohid：我使用Django自己实现了，你可以看一下我的发现。https://dev59.com/eZLea4cB1Zd3GeqPz0GF#34447879 - eugene

我相信，“将归档数据移动到另一个文件组”可以在Oracle中实现为分区，因此可以获得上述列出的好处... - Betlista

Answer 4

我是一名NoSQL开发者，在我的上一份工作中，我经常处理的数据对某些人来说非常重要。如果在创建后的同一天意外删除了数据，那么我将无法在昨天的备份中找到它。在这种情况下，软删除总能拯救全局。

我使用时间戳进行软删除，记录文档被删除的日期：

IsDeleted = 20150310  //yyyyMMdd

每个星期日，一个进程会在数据库上检查IsDeleted字段。如果当前日期和时间戳之间的差大于N天，则文档将被永久删除。考虑到文档可能仍然存在某些备份中，这样做是安全的。

编辑： 这个NoSQL使用案例是关于在数据库中创建的大型文档，每天有几十个或几百个，但不是千万级别的。通常情况下，它们都是具有工作流程状态、数据和附件的文档。这是为什么用户有可能会删除重要文档的原因。这个用户可能是具有管理员权限的人，或者可能是文档的所有者，只是举个例子。

简而言之，我的使用案例并不是大数据。在那种情况下，您需要采用不同的方法。

- Mario S

Answer 5

我使用的一种模式是创建一个镜像表并在主表上附加触发器，因此所有删除操作（如果需要还包括更新操作）都记录在镜像表中。

这使您可以“重建”已删除/更改的记录，您仍然可以在主表中进行硬删除并保持其“清洁”- 这还允许创建“撤消”功能，并且您还可以在镜像表中记录执行该操作的日期、时间和用户（在巫师狩猎情况下非常有价值）。

另一个优点是，除非您刻意费力地从镜像表中包括记录（您可能希望显示活动和已删除的记录），否则在从主表查询时不会意外包括已删除的记录。

另一个优点是，由于镜像表不应具有任何实际外键引用，因此可以独立清除它，相对于从使用软删除但仍具有参考连接到其他表的主表中清除，这使得此操作相对简单。

还有哪些优点？ - 如果您有一堆编码人员在项目上工作，使用混合技能和注意细节水平从数据库读取，你不必夜不能寐地担心他们中的一个忘记了不包括已删除的记录（哈哈，不包括已删除的记录= True），这会导致过度报告客户可用现金头寸，然后他们购买一些股票（即在交易系统中），当您使用交易系统时，您很快就会发现强大解决方案的价值，即使它们可能具有更多的初始“开销”。

例外情况：
- 作为指南，对于“参考”数据（例如用户、类别等），请使用软删除，对于“事实”类型数据（即交易历史记录），请将其硬删除到镜像表中。

- Code Warrior

Answer 6

我曾经使用软删除来保留旧记录，但我意识到用户并不像我想象的那样经常查看旧记录。如果用户想要查看旧记录，他们只需从存档或审计表中查看，对吗？那么软删除有什么好处呢？它只会导致更复杂的查询语句等问题。

在决定不再使用软删除之前，我已经实现了以下内容：

1. 实现审计以记录所有活动（添加、编辑、删除）。确保没有外键链接到审计，并确保此表是安全的，除管理员外没有人可以删除。

2. 确定哪些表被视为“事务性表”，很可能长时间保留，用户可能希望查看过去的记录或报告。例如购买交易。这个表不应该只保留主表的id（如dept-id），而且还应该保留其他参考信息，如名称（如dept-name）或任何其他必要的报告字段。

3. 实现主表的“活动/非活动”或“启用/禁用”或“隐藏/显示”记录。因此，用户可以将主记录禁用/非活动化，而不是删除记录。这样更安全。

以上仅是我的个人意见。

- David

Answer 7

我很喜欢逻辑删除，尤其是在业务应用或用户账户的上下文中。我的理由很简单：通常我不希望用户再能够使用系统（因此将账户标记为已删除），但如果我们删除了用户，我们会失去他们的所有工作和数据等。

另一个常见的场景是，用户可能在删除一段时间后重新创建。对于用户而言，拥有之前的所有数据比重新创建更好的体验。

我通常认为删除用户更像是无限期地“暂停”他们的账户。您永远不知道他们何时需要合法地回来。

- Jon Dewees

---

我们在这里不应该使用类似于账户激活/停用而不是逻辑删除吗？@jon-dewees - Eagle_Eye

一个常见的模式是创建一个两级用户数据结构。因此，用户将拥有一个登录帐户，您可以在其他系统中进行SSO，并且还有另一个与所有业务记录相关联的系统帐户。您可以删除第一级用户登录帐户，同时保留以后重新链接底层系统帐户的能力（如果需要）。 - KuN

Answer 8

我几乎总是使用软删除，原因如下：

• 如果客户要求恢复已删除的数据，则可以进行恢复。使用软删除可以让更多的客户满意。从备份中恢复特定数据比较复杂。
• 无论如何都需要检查userid，所以在任何地方检查isdeleted都不是问题。您可以通过编写代码或将这两个检查放置在单独的函数（或使用视图）中来强制执行检查。
• 优雅的删除。处理已删除内容的用户或进程会继续“看到”它，直到他们刷新。如果正在处理突然被删除的数据，则这是一个非常理想的特性。
• 同步：如果您需要设计数据库和移动应用程序之间的同步机制，则会发现软删除更容易实现。

- Gianluca Ghettini

---

@Jim 将数据持久化到数据库并不是非法的。如果客户告知你删除其数据后仍保留记录，则违法。软删除与GDPR完全兼容：在请求时，只需用空数据覆盖敏感数据即可。此外，如果用户删除了记录，可能希望将来可以撤销该操作或以某种方式恢复数据...这并不意味着他/她希望数据从数据库中完全消失。 - Gianluca Ghettini

Answer 9

我通常使用逻辑删除-我发现当你也间歇性地将“已删除”数据存档到一个存档表中（如果需要可以搜索），这种方法非常有效，因此不会影响应用程序的性能。

这个方法很好，因为如果你被审计了，你仍然拥有数据。如果你物理删除它，它就不存在了！

- Galwegian

Answer 10

关于"这个安全吗？"的问题 - 这要取决于你的意思。

如果你的意思是通过物理删除，防止任何人找到已删除的数据，那么是的，这或多或少是正确的；从数据库中永久删除敏感数据会更加安全。但是请注意，可能存在其他副本的相关数据，例如备份、事务日志或传输过程中的记录版本（例如数据包嗅探器）- 仅仅因为你从数据库中删除了数据，并不能保证它没有被保存在其他地方。

如果你的意思是通过逻辑删除，你的数据更加安全，因为你不会丢失任何数据，那也是正确的。这对审计场景很有用；我倾向于这样设计，因为它承认了一个基本事实，即一旦数据生成，它就永远不会真正消失（特别是如果它曾经有被互联网搜索引擎缓存的能力）。当然，真正的审计场景需要不仅仅是逻辑删除，还需要记录更新，以及更改时间和执行更改的人员。

如果你的意思是数据不会落入任何不应看到它的人手中，那完全取决于你的应用程序及其安全结构。在这方面，逻辑删除与数据库中的任何其他内容一样安全。

- Ian Varley