软删除 - 使用IsDeleted标志还是单独的连接表？ [英] Soft Delete - Use IsDeleted flag or separate joiner table?

问题描述

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

Should we use a flag for soft deletes, or a separate joiner table? Which is more efficient? Database is SQL Server.

背景信息

前段时间我们有一个DB顾问进来看看我们的数据库架构。当我们软删除记录时，我们将更新相应表上的IsDeleted标志。有人建议，不要使用标志，而是将已删除的记录存储在单独的表中，并使用连接，因为这样会更好。我已经把这个建议放到了测试中，但至少表面上看，额外的表格和联接看起来比使用旗帜更贵。

A while back we had a DB consultant come in and look at our database schema. When we soft delete a record, we would update an IsDeleted flag on the appropriate table(s). It was suggested that instead of using a flag, store the deleted records in a seperate table and use a join as that would be better. I've put that suggestion to the test, but at least on the surface, the extra table and join looks to be more expensive then using a flag.

初步测试

我已经设置了此测试。

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

Two tables, Example and DeletedExample. I added a nonclustered index on the IsDeleted column.

我做了三次测试，加载了一百万条记录，其中包含以下已删除/未删除的比率：

I did three tests, loading a million records with the following deleted/non deleted ratios:

• 已删除/未删除


• 50/50


• 10/90


• 1/99

• Deleted/NonDeleted
• 50/50
• 10/90
• 1/99

结果 - 50/50

Results - 50/50

结果 - 10/90

Results - 10/90

结果 - 1/99

Results - 1/99

数据库脚本，供参考，示例，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

推荐答案

您所拥有的数字似乎表明我的初步印象是正确的：如果针对此数据库的最常见查询是过滤 IsDeleted = 0 ，那么性能会更好带有一个简单的位标志，特别是如果你明智地使用索引。

The numbers you have seem to indicate that my initial impression was correct: if your most common query against this database is to filter on IsDeleted = 0, then performance will be better with a simple bit flag, especially if you make wise use of indexes.

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

If you often query for deleted and undeleted data separately, then you could see a performance gain by having a table for deleted items and another for undeleted items, with identical fields. But denormalizing your data like this is rarely a good idea, as it will most often cost you far more in code maintenance costs than it will gain you in performance increases.

这篇关于软删除 - 使用IsDeleted标志还是单独的连接表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！