Мягкое удаление - использовать флаг IsDeleted или отдельную таблицу объединения?

Следует ли использовать флаг для мягкого удаления или отдельную таблицу объединения? Что более эффективно? База данных - это SQL Server.

Справочная информация

Некоторое время назад к нам зашел консультант по БД и посмотрел на схему нашей базы данных. Когда мы мягко удаляем запись, мы обновляем флаг IsDeleted в соответствующей таблице (таблицах). Было предложено вместо использования флага хранить удаленные записи в отдельной таблице и использовать соединение, так как это было бы лучше. Я проверил это предложение, но, по крайней мере, на первый взгляд, дополнительная таблица и соединение выглядят дороже, чем использование флага.

Первоначальное тестирование

Я настроил этот тест.

Две таблицы, Пример и DeletedExample. Я добавил некластеризованный индекс в столбец IsDeleted.

Я провел три теста, загрузив миллион записей со следующими соотношениями удаленных / не удаленных:

  • Удалено / Не удалено
  • 50/50
  • 10/90
  • 1/99

Результаты - 50/50 50 50

Результаты - 10/90 10 90

Результаты - 1/99 введите описание изображения здесь

Сценарии базы данных, для справки, пример, DeletedExample и индекс для примера.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

soft-delete indexing sql-server-2008-r2
person Jon Raynor    schedule 13.01.2012    source источник

Ответы (2)


arrow_upward
10
arrow_downward

Цифры, которые вы видите, указывают на то, что мое первоначальное впечатление было правильным: если ваш самый распространенный запрос к этой базе данных состоит в том, чтобы отфильтровать IsDeleted = 0, тогда производительность будет лучше с простым битовым флагом, особенно если вы разумно используете индексы.

Если вы часто запрашиваете удаленные и восстановленные данные по отдельности, вы можете увидеть прирост производительности, имея таблицу для удаленных элементов и другую для восстановленных элементов с идентичными полями. Но подобная денормализация данных редко бывает хорошей идеей, поскольку чаще всего это будет стоить вам гораздо больше затрат на обслуживание кода, чем даст вам повышение производительности.

person StriplingWarrior    schedule 13.01.2012
comment
Хороший ответ. Также рассмотрите возможность использования отфильтрованных индексов. Если вы запрашиваете таблицу Example только по столбцу Column1, когда записи не удаляются, индексируйте Column1 WHERE IsDelete = 0. - person Rob Garrison; 16.01.2012

arrow_upward
1
arrow_downward

Я не специалист по SQL, но, на мой взгляд, все зависит от частоты использования базы данных. Если к базе данных обращается большое количество пользователей и она должна быть эффективной, тогда будет хорошо использовать отдельную таблицу isDeleted. Лучшим вариантом было бы использование флага во время производства, и в рамках ежедневного / еженедельного / ежемесячного обслуживания вы можете переместить все мягко удаленные записи в таблицу isDeleted и очистить производственную таблицу от мягко удаленных записей. Смесь обоих вариантов будет хорошей хорошей.

person blackwind    schedule 13.01.2012