Как лучше всего реализовать мягкое удаление?
В данный момент мы работаем над проектом, и нам необходимо реализовать мягкое удаление для большинства пользователей (ролей пользователей). Мы решили добавить поле is_deleted='0' в каждую таблицу в базе данных и установить для него значение '1', если определенные роли пользователей нажимают кнопку удаления в определенной записи.
Для будущего обслуживания сейчас каждый запрос SELECT должен будет гарантировать, что они не включают записи where is_deleted='1'.
Есть ли лучшее решение для реализации мягкого удаления?
Обновление: я также должен отметить, что у нас есть база данных аудита, которая отслеживает изменения (поле, старое значение, новое значение, время, пользователь, IP-адрес) для всех таблиц / полей в базе данных приложения.
Ответы 14
Вы можете выполнять все свои запросы к представлению, содержащему предложение WHERE IS_DELETED='0'.
не забудьте проиндексировать этот столбец;)
Также рассмотрите возможность секционирования по этому ключу, это должно иметь большее положительное влияние на производительность, чем простой индекс для столбца с низкой избирательностью.
если таблица большая и производительность является проблемой, вы всегда можете переместить «удаленные» записи в другую таблицу, в которой есть дополнительная информация, такая как время удаления, кто удалил запись и т. д.
таким образом вам не нужно добавлять еще один столбец в основную таблицу
Это почти получило принятый ответ. Некоторые из таблиц будут большими (иш), и я не думал о проблемах с производительностью. Если бы не было базы данных аудита, я бы определенно применил этот подход.
сладко ... это на самом деле не о том, чтобы заработать мне очки ... если это полезно, это действительно важно. рад, что вы нашли ответ полезным :)
Мне действительно очень нравится эта идея, потому что тогда моя основная таблица не загромождена тысячами мягко удаленных записей. Но как бы вы поступили с дочерними и зависимыми записями? Вы бы в основном воссоздали те же зависимости таблиц (например, для заказа и элементов заказа)?
если производительность является проблемой, вы можете разделить таблицу с помощью is_deleted или deleted_at или любого другого, чтобы ускорить запросы.
Я бы склонился к столбцу deleted_at, который содержит дата и время, когда произошло удаление. Затем вы получите немного бесплатных метаданных об удалении. Для вашего SELECT просто получите строки WHERE deleted_at IS NULL
вот как мы это делаем - удаление всегда поле даты
+1, потому что он содержит точно такую же информацию, что и решение, представленное в «лучшем ответе», и, кроме того, дает вам информацию о дате удаления.
Выгодно ли в этом случае индексировать deleted_at?
Я думаю, что использование поля даты было бы лучше, чем использование флага, только потому, что вы можете разделить поле даты различными способами.
Согласны !!!! ИМО, использование datetime поможет нам узнать больше о времени удаления. Более того, это делает наш код читабельным и имеет некоторые соглашения об именах, например created_at, updated_at, deleted_at, created_by, updated_by, deleted_by.
@JeffAtwood, а что происходит, когда строка "восстанавливается"? Разве (или не ...) вы где-то храните всю эту информацию?
Это зависит от того, какая информация вам нужна и какие рабочие процессы вы хотите поддерживать.
Вы хотите:
- знаете, какая информация там была (до удаления)?
- знаете когда его удалили?
- знаете кто его удалил?
- знаете, в каком качестве они действовали, когда удалили его?
- иметь возможность отменить удаление записи?
- уметь узнать, когда он был не удален?
- и Т. Д.
Если запись была удалена и не удалена четыре раза, достаточно ли вам знать, что она в настоящее время находится в не удаленном состоянии, или вы хотите знать, что произошло за это время (включая любые изменения между последовательными удаления!)?
У вас определенно будет лучшая производительность, если вы переместите удаленные данные в другую таблицу, как сказал Джим, а также у вас будет запись о том, когда они были удалены, почему и кем.
Добавление where ко всем вашим запросам значительно замедлит их и затруднит использование любого из индексов, которые могут быть в таблице. По возможности избегайте наличия «флагов» в ваших таблицах.deleted=0
Наличие колонки is_deleted - достаточно хороший подход.
Если это в Oracle, для дальнейшего повышения производительности я бы рекомендовал разбить таблицу на разделы, создав раздел списка в столбце is_deleted.
Тогда удаленные и не удаленные строки будут физически находиться в разных разделах, хотя для вас это будет прозрачно.
В результате, если вы наберете такой запрос, как
SELECT * FROM table_name WHERE is_deleted = 1
тогда Oracle выполнит «сокращение раздела» и изучит только соответствующий раздел. Внутренне раздел - это другая таблица, но она прозрачна для вас как пользователя: вы сможете выбирать по всей таблице, независимо от того, разбита она на разделы или нет. Но Oracle сможет запросить ТОЛЬКО нужный раздел. Например, предположим, что у вас есть 1000 строк с is_deleted = 0 и 100000 строк с is_deleted = 1, и вы разбиваете таблицу на is_deleted. Теперь, если вы включите условие
WHERE ... AND IS_DELETED=0
тогда Oracle будет сканировать ТОЛЬКО раздел с 1000 строками. Если бы таблица не была разбита на разделы, ей пришлось бы сканировать 101000 строк (оба раздела).
Хотел бы я проголосовать за это больше одного раза, красиво, легко, и это просто решило мою проблему
-1 Почему раздел - это не таблица, а не представление? Было действительно необходимо ввести еще одну концепцию; Разве у Oracle уже нет томов документации, конкурирующих с Библиотекой Конгресса?
Я предпочитаю сохранять столбец статуса, поэтому я могу использовать его для нескольких разных конфигураций, то есть опубликованных, частных, удаленных, needsAproval ...
Используйте представление, функцию или процедуру, которая проверяет is_deleted = 0; т.е. не выбирайте непосредственно в таблице, если таблица должна измениться позже по другим причинам.
И проиндексируйте столбец is_deleted для больших таблиц.
Поскольку у вас уже есть контрольный журнал, отслеживание даты удаления излишне.
Что-то, что я использую в проектах, - это столбец statusInd tinyint not null по умолчанию 0 Использование statusInd в качестве битовой маски позволяет мне управлять данными (удалять, архивировать, реплицировать, восстанавливать и т. д.). Используя это в представлениях, я могу выполнять распределение, публикацию и т. д. Данных для приложений-потребителей. Если производительность является проблемой для представлений, используйте небольшие таблицы фактов для поддержки этой информации, отбрасывая факт, удаляя связь и позволяя масштабное удаление.
Хорошо масштабируется и ориентирован на данные, сохраняя довольно небольшой объем данных - ключ для 350 ГБ + dbs с проблемами в реальном времени. Использование альтернатив, таблиц, триггеров имеет некоторые накладные расходы, которые в зависимости от необходимости могут или не могут сработать для вас.
Аудитам, связанным с SOX, может потребоваться нечто большее, чем просто поле, чтобы помочь в вашем случае, но это может помочь. Наслаждаться
К сожалению, лучший ответ зависит от того, чего вы пытаетесь достичь с помощью мягких удалений, и от базы данных, в которой вы это реализуете.
В SQL Server лучшим решением было бы использовать столбец deleted_on / deleted_at с типом SMALLDATETIME или DATETIME (в зависимости от необходимой степени детализации) и сделать этот столбец допускающим значение NULL. В SQL Server данные заголовка строки содержат битовую маску NULL для каждого столбца в таблице, поэтому выполнение IS NULL или IS NOT NULL незначительно быстрее, чем проверка значения, хранящегося в столбце.
Если у вас большой объем данных, вам нужно будет рассмотреть возможность разделения данных либо через саму базу данных, либо через две отдельные таблицы (например, Products и ProductHistory) или через индексированное представление.
Я обычно избегаю полей флагов, таких как is_deleted, is_archive и т. д., Потому что они несут только одно значение. Обнуляемые поля deleted_at, archived_at предоставляют дополнительный уровень смысла для вас и для всех, кто наследует ваше приложение. И я избегаю полей битовых масок, таких как чума, поскольку они требуют понимания того, как была построена битовая маска, чтобы уловить какой-либо смысл.
Я согласен, «deleted_at» намного предпочтительнее простого «is_deleted», поскольку вы получаете дополнительную информацию бесплатно.
вы не упоминаете, какой продукт, но SQL Server 2008 и postgresql (и другие, я уверен) позволяют вам создавать отфильтрованные индексы, поэтому вы можете создать покрывающий индекс, где is_deleted = 0, смягчая некоторые из негативов этого конкретного подхода .
Будьте осторожны с мягко удаленными записями, вызывающими нарушения ограничений уникальности. Если в вашей БД есть столбцы с уникальными ограничениями, будьте осторожны, чтобы предыдущие мягко удаленные записи не помешали вам воссоздать запись.
Подумайте о цикле:
- создать пользователя (логин = JOE)
- мягкое удаление (установить для удаленного столбца ненулевое значение).
- (повторно) создать пользователя (логин = JOE). ОШИБКА. LOGIN = JOE уже занят
Второе создание приводит к нарушению ограничения, поскольку login = JOE уже находится в строке с обратимым удалением.
Некоторые техники: 1. Переместите удаленную запись в новую таблицу. 2. Задайте ограничение уникальности в столбцах "login" и "deleted_at timestamp".
Мое собственное мнение - +1 за переход на новый стол. Это займет много дисциплина для поддержания * AND delete_at = NULL * во всех ваших запросы (для всех ваших разработчиков)
Привет, Энди, я встретил именно твои обстоятельства. Но я не думаю, что переход к другому столу хорош для длительного обслуживания. Вариант 2 вроде бы лучше .. Почему не порекомендуете? Мне просто любопытно.
Есть другой подход, при котором для удаления строки вы копируете уникальный ключ в столбец с мягким удалением (того же типа) и делаете исходный нулевым.
@ HoàngLong Я не думаю, что вариант 2 - это решение, поскольку невозможно удалить второй JOE.
@NicholasPipitone, почему бы и нет? Обратите внимание, что мы установили ограничение на (логин, delete_at_timestamp), НЕ (логин, is_deleted)
@ HoàngLong А, понятно, в таком случае nvm. Было бы возможно.
@ HoàngLong Я только что протестировал его, и он не работает, но по интересной причине. Очевидно, MySQL не применяет уникальные ограничения для столбцов NULL, а только для столбцов, отличных от NULL. Таким образом, вариант 2 не работает.
@mrmashal, так что вам нужен столбец мягкого удаления для каждого столбца? звучит неэффективно!
@Menasheh Я думаю, что в большинстве случаев существует не более одного альтернативного ключевого столбца с уникальным индексом, и мы можем объявить его индекс уникальным только для ненулевых значений. Таким образом, мы можем реализовать мягкое удаление с одним дополнительным столбцом, который содержит копию альтернативного ключа. Вместо этого нас может заинтересовать строковый столбец, допускающий значение NULL, содержащий JSON и т. д., Содержащий любую резервную копию или дополнительную информацию, которую нам нужно где-то хранить, в качестве столбца мягкого удаления.
@NicholasPipitone, а не только MySQL: «Любая попытка вставить повторяющуюся запись приведет к ошибке. Для целей уникальных индексов все значения NULL считаются отличными от всех других значений NULL и поэтому уникальны. Это одно из двух возможных интерпретаций стандарта SQL-92 (язык в стандарте неоднозначен) и интерпретации, которой придерживаются PostgreSQL, MySQL, Firebird и Oracle. Informix и Microsoft SQL Server придерживаются другой интерпретации стандарта ». От sqlite.org/lang_createindex.html
@NicholasPipitone вы можете использовать временную метку UNIX для столбца is_deleted, что 0 означает, что не удалено, таким образом не будет нулевых строк. ссылка: medium.com/@BBreyten/…
Создайте другую схему и предоставьте все это в своей схеме данных. Внедрите VPD в свою новую схему, чтобы в каждом запросе был предикат, позволяющий выбирать только не удаленную строку, добавленную к нему. http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/cmntopc.htm#CNCPT62345
@AdditionalCriteria("this.status <> 'deleted'")
поместите это поверх вашего @entity
Во-вторых, что. Звучит идеально для представления.