mysql - SQL表设计问题

Question

我正在考虑表格设计的两种选择，我不确定每种选择的优缺点。

以下是对我的情况的抽象描述:

我正在跟踪一些数据点 (category_id, point_id, value)。大多数时候，我只对数据点的当前值感兴趣。但是只要有变化，我就需要记录所有历史值。

偶尔我可能想查看特定点的历史值，但如果这些查询有点慢也没关系。最重要的是我可以尽快得到所有点的当前值，或者特定类别中所有点的当前值。

我正在考虑的两种(也可能是三种)方法:

1. 使用两个单独的表，一个current_vales 和一个history 表，并使用一个触发器，每当current_values< 中有内容时，就会在历史表中插入一行 更改。
2. 只使用一个表，每行都有一个 bool 标志 isCurrent。每当值更改时，将该行标记为不再是当前行，并插入具有更新值的新当前行。
3. (在每一行上只使用一个带有时间戳的表——然后特定 id 的当前值是具有最新时间戳的行。但这似乎很难表达为查询，特别是如果我想获取所有特定类别的当前值，甚至不确定在没有子查询或性能的情况下我将如何表达它)

一次只有大约 3,000-5,000 个当前点，但这些值的变化足够频繁，每天最多可以更改其中一半，因此最终将有数十万行历史记录。

上述每种方法的优缺点是什么(或者有没有我没有提到的另一种更好的方法)？鉴于我的目标是尽快获取当前点集，并且可以接受较慢的历史查询，哪个最好？

Answer 1

选项 1 和 2 将具有相似的性能 - 您在选项 1 中对数据的手动“分区”也可以使用聚集索引进行管理，其中 IsCurrent 作为选项 2 中的第一列。您始终可以拥有一个只提供current 并且在某些方面，这在性能上非常相似，因为更改 IsCurrent 将物理移动旧行(由于集群)并添加新行，就像您的触发器将在两个表中删除和插入一样。

您还可以使用 MySQL 的分区功能。

拥有单独的表或单个表的分区的一大好处是以更细粒度的方式控制数据的备份(并可能清除)。

选项 1 的真正好处是您确实保存了那个小列，当您达到数十亿行时，这可能会很有用。

选项 2 的维护好处是架构始终相同(不必使更改保持同步)，因为只有一个表。

选项 3 不会执行得很好，因为当前值的前沿更难找到 - 即变化(尽管使用标识符和时间戳 DESC 上的索引并非不可能提高性能)