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

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

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)?


mysql query-performance
person Allan Rempel    schedule 15.11.2013    source источник

Ответы (1)


arrow_upward
0
arrow_downward

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

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

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

Запрос со встроенным представлением с предикатом ВНУТРИ определения представления даст наилучшую производительность:

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.

person spencer7593    schedule 15.11.2013
comment
Причина, по которой я хотел использовать представления, в первую очередь заключалась в том, что у меня есть три разные таблицы, каждая с таким типом управления версиями, и мне нужно объединить все текущие записи. Без представлений оператор select выглядит ужасно, но я вижу ценность помещения предиката во внутренний select. - person Allan Rempel; 15.11.2013
comment
Связанный с этим вопрос, в случае с одной таблицей, как я могу применить ограничения внешнего ключа из другой таблицы? Первичный ключ не только id, но также включает timestamp, но я хочу сослаться на id как на ключ, только нет таблицы, где id является уникальным столбцом. - person Allan Rempel; 16.11.2013