Редкие данные: эффективное хранение и поиск в СУБД
У меня есть таблица, представляющая значения метрик исходного файла для разных версий проекта, например:
Revision FileA FileB FileC FileD FileE ...
1 45 3 12 123 124
2 45 3 12 123 124
3 45 3 12 123 124
4 48 3 12 123 124
5 48 3 12 123 124
6 48 3 12 123 124
7 48 15 12 123 124
(Реляционное представление приведенных выше данных отличается. Каждая строка содержит следующие столбцы: Revision, FileId, Value. Файлы и их версии, из которых рассчитываются данные, хранятся в репозиториях Subversion, поэтому мы пытаемся представить структура в реляционной схеме.)
В 10000 ревизий может быть до 23750 файлов (как в программе рисования ImageMagick). Как видите, большинство значений одинаковы между последовательными ревизиями, поэтому полезные данные в таблице довольно скудны. Я ищу способ хранить данные, которые
- избегает репликации и эффективно использует пространство (в настоящее время для нерезкого представления требуется 260 ГБ (данные + индекс) для менее 10% данных, которые я хочу сохранить)
- позволяет мне эффективно извлекать значения для конкретной ревизии с помощью SQL-запроса (без явного перебора ревизий или файлов)
- позволяет мне эффективно получить версию для определенного значения метрики.
В идеале решение не должно зависеть от конкретного СУБД и должно быть совместимо с Спящий режим. Если это невозможно, я могу жить с использованием специфических для Hibernate, MySQL или PostgreSQL функций.
Извините, это была опечатка, я забыл изменить ревизию после копирования строки. Исправил сейчас данные.
Ответы 1
Вот как я могу это смоделировать. Я пропустил таблицу редакций и таблицу файлов, поскольку они не требуют пояснений.
CREATE TABLE Revision_Files
(
start_revision_number INT NOT NULL,
end_revision_number INT NOT NULL,
file_number INT NOT NULL,
value INT NOT NULL,
CONSTRAINT PK_Revision_Files PRIMARY KEY CLUSTERED (start_revision_number, file_number),
CONSTRAINT CHK_Revision_Files_start_before_end CHECK (start_revision_number <= end_revision_number)
)
GO
Чтобы получить все значения для файлов определенной ревизии, вы можете использовать следующий запрос. Присоединение к таблице файлов с помощью внешнего соединения позволит вам получить те, которые не имеют определенного значения для этой ревизии.
SELECT
REV.revision_number,
RF.file_number,
RF.value
FROM
Revisions REV
INNER JOIN Revision_Files RF ON
RF.start_revision_number <= REV.revision_number AND
RF.end_revision_number >= REV.revision_number
GO
Предполагая, что я правильно понимаю, что вы хотите в третьем пункте, это позволит вам получить все ревизии, для которых конкретный файл имеет определенное значение:
SELECT
REV.revision_number
FROM
Revision_Files RF
INNER JOIN Revisions REV ON
REV.revision_number BETWEEN RF.start_revision_number AND RF.end_revision_number
WHERE
RF.file_number = @file_number AND
RF.value = @value
GO
Отлично - спасибо! Кажется, что диапазоны - это лучший вариант. Я также восхищаюсь вашим умелым использованием SQL для определения таблиц.
Я не уверен, что полностью понимаю, что вы отслеживаете. В версии 4 есть два разных значения для FileB? Если вам нужна ревизия 4, как узнать, какое значение использовать?