Как обычно записывается запись каждого изменения строки в базе данных?

В проекте, над которым я работаю, каждое изменение строк в некоторых таблицах базы данных должно отслеживаться для дальнейшего аудита или отката. Должно быть легко найти, кто изменил строку, с которой IP-адрес и когда, и сможет восстановить предыдущую версию.

Аналогичная вещь используется, например, Stack Exchange. Когда я изменяю чужой вопрос, можно найти, что я его изменил, и отменить изменения.

Каков общий метод, используемый для хранения каждого изменения объекта в базе данных , учитывая, что моя текущая схема имеет в основном те же свойства (ниже), что среднее бизнес-приложение?

  • Объекты имеют относительно небольшой размер: например, может быть несколько nvarchar(1000), но не огромные капли двоичных данных, этот сохраняются непосредственно на диске и доступны напрямую, а не через Microsoft SQL filestream,
  • Загрузка базы данных довольно низкая, и вся база данных обрабатывается одной виртуальной машиной на сервере,
  • Доступ к предыдущим версиям не должен быть таким быстрым, как доступ к последней версии, но все же должен быть актуальным¹ и не слишком медленным.

& л; ТЛ-др >

Я подумал о следующих случаях, но у меня нет реального опыта подобных сценариев, поэтому я слышал мнения других:

  1. Храните все в одной таблице, различая строки по идентификатору и версии. ИМО, это серьезно глупо, и рано или поздно будет вредить уровню производительности. При таком подходе также невозможно установить другой уровень безопасности для последних элементов и трассировки версий. Наконец, каждый запрос будет сложнее писать. На самом деле, чтобы получить доступ к актуальным данным, мне пришлось бы группировать все по идентификатору и получать в каждой группе последнюю версию.

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

  3. Сохраните последнюю версию в одной таблице и список измененных свойств с их предыдущими значениями в другой таблице. Это, по-видимому, имеет два недостатка: наиболее важным является то, что единственный способ сортировки гетерогенных типов предыдущих значений в одном столбце - это binary(max). Второй заключается в том, что было бы, я считаю, сложнее использовать такую ​​структуру при отображении предыдущих версий пользователю.

  4. Сделайте то же самое, что и в двух предыдущих моментах, но сохраните версии в отдельной базе данных. По производительности, это может быть интересно, чтобы не замедлять доступ к последним версиям, имея предыдущие версии в одной и той же базе данных; Тем не менее, я считаю, что это преждевременная оптимизация, и это необходимо сделать, только если есть доказательство того, что наличие более старых и последних версий в одной и той же базе данных является узким местом.

& л; /TL-DR >


¹ Например, было бы неприемлемо хранить изменения в файле журнала, как это делается для журналов HTTP, и сбросить данные из журнала в базу данных ночью, когда загрузка сервера минимальна. Информация о разных версиях должна быть доступна сразу или почти сразу; допустима несколько секунд.

² Информация не доступна очень часто и только определенной группой пользователей, но все же было бы неприемлемо заставить их ждать 30 секунд для отображения списка версий. Опять же, допустимо несколько секунд задержки.

10 голосов | спросил Arseni Mourzenko 16 32011vEurope/Moscow11bEurope/MoscowWed, 16 Nov 2011 00:28:03 +0400 2011, 00:28:03

3 ответа


8

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

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

Дату изменения можно сделать, если столбец datetime не имеет значения null со значением по умолчанию getdate (); столбец пользователя аудита будет захватывать пользователя с нулевым столбцом, по умолчанию для которого является Suser_Sname (). Предполагая, что фактический пользователь олицетворен в сеансе, это приведет к тому, что пользователь внесет изменения.

В базе данных нет способа узнать, как IP-адрес подключается к веб-серверу. Приложение должно будет явно захватить и зарегистрировать IP-адрес с транзакцией.

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

Это решение, безусловно, лучше всего по нескольким причинам:

  • Он фиксирует любые изменения в таблице, а не только те, которые были сделаны приложением.

  • Таблицы аудита могут быть помещены на другой набор дисков, чтобы уменьшить нагрузку ввода-вывода на ваши первичные таблицы.

  • Вы можете использовать представление, основанное на объединении таблицы таблицы и журнала аудита, на которое будет отображаться вся история, включая текущую версию.

  • Вы можете индексировать таблицы журнала аудита по мере необходимости, чтобы пользователи аудита могли быстро их запрашивать. Как обычно, выбор индекса - это компромисс между производительностью запросов и служебными данными обновления.

ответил ConcernedOfTunbridgeWells 16 32011vEurope/Moscow11bEurope/MoscowWed, 16 Nov 2011 01:00:40 +0400 2011, 01:00:40
0

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

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

ответил Dharmendar Kumar 'DK' 16 32011vEurope/Moscow11bEurope/MoscowWed, 16 Nov 2011 02:43:09 +0400 2011, 02:43:09
0

О версии управления версиями CMS; для drupal он создает специальную таблицу для каждого поля объекта, которое хранит старое значение; такая концепция позволяет вам тонко манипулировать вашими данными, но я думаю, что это дорого, мое собственное решение - преобразовать мой объект в формат xml и сохранить его как строку с другими полями (changetime, id ...)

ответил Bourkadi 24 MarpmTue, 24 Mar 2015 20:13:40 +03002015-03-24T20:13:40+03:0008 2015, 20:13:40

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132