Дизайн первичного ключа СУРБД для управления версиями строк

Я хочу создать первичный ключ для своей таблицы с управлением версиями строк. Моя таблица содержит 2 основных поля: ID и Timestamp, а также множество других полей. Для уникального «ID» я хочу сохранить предыдущие версии записи. Следовательно, я создаю первичный ключ для таблицы, которая будет комбинацией полей идентификатора и метки времени. Следовательно, чтобы увидеть все версии конкретного идентификатора, я могу дать,

Select * from table_name where ID=<ID_value>

Чтобы вернуть самую последнюю версию идентификатора, я могу использовать

Select * from table_name where ID=<ID_value> ORDER BY timestamp desc

и получить первый элемент. Мой вопрос заключается в том, будет ли этот запрос эффективным и будет ли он выполняться в O (1) вместо сканирования всей таблицы, чтобы получить все записи, соответствующие одному и тому же идентификатору, учитывая, что поле идентификатора было частью полей первичного ключа? В идеале, чтобы получить результат за O(1), я должен был указать весь первичный ключ. Если ему нужно выполнить сканирование всей таблицы, то как еще я могу разработать свой первичный ключ, чтобы выполнить этот запрос за O (1)?

Спасибо, Шрирам


sql primary-key rdbms primary-key-design
person sriram    schedule 21.07.2015    source источник
comment
Я бы поместил кластеризованный индекс в другой уникальный/автоинкрементный ключ с некластеризованным индексом для идентификатора и временной метки. Если у вас есть какие-либо отношения за пределами этой таблицы, то этот PK будет болью в качестве FK (с идеей метки времени), однако, если вас интересует только необработанная скорость, кластеризованный индекс по идентификатору и метке времени даст самый быстрый результат. Прирост составного ключа не будет заметен, если вы не разделяете.   -  person Ross Bush    schedule 21.07.2015
comment
Во-первых, вы должны решить, какую базу данных вы используете: MySQL или SQL Server. Я удаляю оба тега и заменяю их SQL, но вы все равно должны указать правильный тег.   -  person Gordon Linoff    schedule 21.07.2015
comment
Извините, я имел в виду, что прирост кластеризованного PK-индекса идентификатора и временной метки будет заметен, если вы секционируете или имеете очень, очень большой набор данных.   -  person Ross Bush    schedule 21.07.2015
comment
Спасибо Гордон и LRB за ваше предложение.   -  person sriram    schedule 27.07.2015

Ответы (2)


arrow_upward
2
arrow_downward

Каноническим справочником по этому вопросу является Эффективная отметка времени в базах данных: https://www.cs.arizona.edu/~rts/pubs/VLDBJ99.pdf

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

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

person rmalayter    schedule 21.07.2015
comment
Спасибо rmalayter за ответ с бумажной ссылкой. Я все еще просматриваю статью, чтобы понять, почему нам нужна отдельная таблица для отслеживания только столбцов change_user, valid_from, valid_until. Я отвечу на вопросы после того, как пройду его. - person sriram; 28.07.2015
comment
Сложность необходима, если вы хотите отслеживать и включать все временные возможности. То есть, кто совершил транзакцию и когда именно, а также когда вы хотите, чтобы эти данные были видимыми или действительными, даже если это произойдет в будущем. Если вам не нужны все эти функции, ваш код обработки схемы и временных меток может быть значительно проще. - person rmalayter; 28.07.2015
comment
Требуется отдельная таблица только с ключом, поскольку ни одна популярная база данных SQL не поддерживает ссылочную целостность на основе одной части многостолбцового первичного ключа в форме (ID,valid_from,valid_until). Если вы не хотите полагаться на код приложения или триггеры для поддержания ссылочной целостности, для практической реализации концепций статьи потребуется только таблица ключей и множество соединений. - person rmalayter; 28.07.2015
comment
Большое спасибо за объяснение rmmalayter - person sriram; 29.07.2015

arrow_upward
1
arrow_downward

Гораздо проще и лучше использовать для этого паттерн History Table.

create table foo (
  foo_id int primary key,
  name text
);

create table foo_history (
  foo_id int,
  version int,
  name text,
  operation char(1) check ( operation in ('u','d') ),
  modified_at timestamp,
  modified_by text
  primary key (foo_id, version)
);

Создайте триггер для копирования строки foo в foo_history при обновлении или удалении.

https://wiki.postgresql.org/wiki/Audit_trigger_91plus для полного примера с postgres.

person Neil McGuigan    schedule 22.07.2015
comment
Спасибо Нил за ваше предложение. Определенно полезно. - person sriram; 27.07.2015
comment
Таблица истории — это общий шаблон, а таблицу истории и триггеры можно легко сгенерировать из базовых таблиц с помощью кода. Но этот шаблон не позволяет вставлять или обновлять будущие значения строк в таблице, чтобы они вступали в силу в определенную дату и время. Не так много приложений имеют это требование, но есть много систем, которые выполняют выставление счетов, подписки и т. д., где данные необходимо записывать сейчас и становиться видимыми позже. - person rmalayter; 31.07.2015