背景资料

前段时间我们有一位DB顾问进来查看我们的数据库架构.当我们软删除记录时,我们将更新相应表上的IsDeleted标志.有人建议,不要使用标志,而是将已删除的记录存储在单独的表中,并使用连接,因为这样会更好.我已经把这个建议付诸实践了,但至少在表面上,额外的表和连接看起来比使用标志更昂贵.

初步测试

我已经设置了这个测试.

两个表,Example和DeletedExample.我在IsDeleted列上添加了一个非聚簇索引.

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

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

结果 – 50/50

结果 – 10/90

结果 – 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,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,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO