Текущая версия сущности в MySQL

Question

Предположим, у меня есть следующая таблица

CREATE TABLE `entities` (
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `timestamp` TIMESTAMP NOT NULL
      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `data` VARCHAR(255),
   PRIMARY KEY (`id`,`timestamp`)
);

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

Каков наиболее эффективный (с точки зрения производительности и места хранения) метод выбора самой последней редакции? Есть ли принятая практика для этой проблемы?

На мой взгляд, есть два метода: (1) Создать представления вокруг GROUP BY

CREATE VIEW groupedEntities AS
   SELECT id, max(timestamp) AS maxt FROM entities GROUP BY id;
CREATE VIEW currentEntities AS
   SELECT a.id, data, timestamp FROM groupedEntities AS a
      INNER JOIN entities AS b ON b.id=a.id AND b.timestamp=a.maxt
      WHERE timestamp <= CURRENT_TIMESTAMP;
SELECT * FROM currentEntities WHERE id=?;

Обратите внимание <=CURRENT_TIMESTAMP позволяет "удалить" сущность, установив метку времени на далекое будущее. И (2) создать отдельную таблицу для хранения текущих версий

CREATE TABLE currentEntities (
   `id` INT(10) UNSIGNED PRIMARY KEY,
   `timestamp` TIMESTAMP,
   CONSTRAINT FOREIGN KEY (`id`, `timestamp`)
      REFERENCES `entities` (`id`,`timestamp`)
);
SELECT * FROM currentEntites INNER JOIN groupedEntities WHERE id=?;

Или какой-то другой вариант (3)?

Answer 1

Представления съедят ваш обед с точки зрения производительности, благодаря тому, как MySQL обрабатывает представления. В частности, MySQL материализует промежуточную таблицу MyISAM для представления и не "проталкивает" предикаты из внешнего запроса в представление (сохраненное или встроенное).

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

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

Запрос с встроенным представлением с предикатом INSIDE определения представления даст наилучшую производительность:

SELECT e.id
     , e.timestamp
     , e.data
  FROM `entities` e
  JOIN ( SELECT m.id 
              , MAX(m.timestamp) AS `timestamp`
           FROM `entities` m
          WHERE m.id = ?
          GROUP BY m.id
       ) c
     ON c.id = e.id 
    AND c.timestamp = e.timestamp

Вывод EXPLAIN должен показатьUsing where; Using index"на шаге к материализации встроенного представления (производная таблица). Предикат объединения по внешнему запросу выполняется по первичному ключу, который является оптимальным для получения data колонка.