软删除 - 使用 IsDeleted 标志还是单独的连接表？

我们应该使用软删除标志还是单独的连接表？哪个更有效率？数据库是SQL Server。

背景资料

不久前，我们有一位数据库顾问进来查看我们的数据库架构。当我们软删除一条记录时，我们将更新相应表上的 IsDeleted 标志。有人建议不要使用标志，而是将已删除的记录存储在单独的表中并使用联接，因为这样会更好。我已经对该建议进行了测试，但至少从表面上看，额外的表和连接看起来比使用标志更昂贵。

初步测试

我已经设置了这个测试。

两个表，Example 和DeletedExample。我在 IsDeleted 列上添加了一个非聚集索引。

我做了三个测试，加载一百万条记录，删除/未删除比率如下：

• 已删除/未删除
• 50/50
• 10/90
• 1/99

Results - 50/50

Results - 10/90

Results - 1/99

数据库脚本，供参考、Example、DeletedExample 和 Example.IsDeleted 的索引

CREATE TABLE [dbo].[Example](
    [ID] [int] NOT NULL,
    [Column1] [nvarchar](50) NULL,
    [IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Example] ADD  CONSTRAINT [DF_Example_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

CREATE TABLE [dbo].[DeletedExample](
    [ID] [int] NOT NULL,
 CONSTRAINT [PK_DeletedExample] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DeletedExample]  WITH CHECK ADD  CONSTRAINT [FK_DeletedExample_Example] FOREIGN KEY([ID])
REFERENCES [dbo].[Example] ([ID])
GO

ALTER TABLE [dbo].[DeletedExample] CHECK CONSTRAINT [FK_DeletedExample_Example]
GO

CREATE NONCLUSTERED INDEX [IX_IsDeleted] ON [dbo].[Example] 
(
    [IsDeleted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

你所掌握的数字似乎表明我的初步印象 https://stackoverflow.com/q/8841229/120955是正确的：如果您对此数据库最常见的查询是过滤IsDeleted = 0，那么使用简单的位标志性能会更好，特别是如果您明智地使用索引的话。

如果您经常分别查询已删除和未删除的数据，那么通过使用具有相同字段的已删除项目表和未删除项目表，您可以看到性能提升。但是像这样对数据进行非规范化很少是一个好主意，因为它通常会花费您更多的代码维护成本，而不是您获得的性能提升。