mysql - 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 创建 View

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允许通过设置遥远 future 的时间戳来“删除”实体。 (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 处理 View 的方式， View 会在性能方面吃掉你的午餐。具体来说，MySQL 为 View 具体化了一个中间 MyISAM 表，并且不会将谓词从外部查询“推送”到 View (存储的或内联的)中。

使用一个单独的表来保存常用的“当前”修订版的选项将是您提​​供的两个选项中更好的选择。这确实增加了复杂性，使所有内容保持同步，使用不同的查询来获取当前与历史，以及额外插入的开销等。

仅给出原始表(将所有历史修订版本存储在与当前修订版本相同的表中(仅针对最新修订版本没有单独的表)...

具有内联 View 且在 View 定义内带有谓词的查询将提供最佳性能:

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 输出应在具体化内联 View (派生表)的步骤中显示“Using where;Using index”。外部查询上的连接谓词是通过主键进行的，这对于data 列的检索是最佳的。