日期列作为主键的优缺点

Question

我目前正在处理一个数据库，需要记录一堆不同数据的变化情况，例如价格变化、项目状态变化等。为了实现这个目标，我创建了不同的“日志”表来存储需要保留的数据。

举个例子，为了追踪需要订购的零件价格变化，我创建了一个名为“Part_Price_Log”的表。主键是由修改零件价格的日期和指向“Parts”表中零件唯一ID的外键组成的复合主键。

我的逻辑是，如果你需要查找某个零件的当前价格，只需要找到该零件ID的最新条目即可。然而，我被告知不要以这种方式实现，因为在主键中使用日期是导致数据错误的简单方法。

所以我的问题是：

使用日期列作为复合主键的一部分有什么优缺点？有什么更好的替代方案吗？

- Skitzafreak

---

日期时间以其二进制表示形式存储，与整数类似。因此，我不太明白将其用作主键的错误之处。根据其用途（获取当前价格），由于这些价格一天内没有多次变化（我猜），对于您拥有的任何产品，每年最多只会有几十个记录。如果我的假设是正确的，我将为产品ID创建一个非唯一索引，并获取该产品的“MAX（Registration_Date）”记录。这只是简化目的的想法... - FDavidov

@GordonLinoff：假设它是“零件价格”数据库，因为这可能没有什么区别。 - onedaywhen

Answer 1

一般来说，我认为最好的主键是合成自动递增的主键。这些具有以下优点：

• 键值记录插入顺序。
• 键是固定长度（通常为4个字节）。
• 单个键对于外键引用要简单得多。
• 在按照主键聚集数据的数据库中（例如默认情况下的SQL Server），插入将“在末尾”进行。
• 它们相对容易输入和比较（我的眼睛无法很好地比较UUID）。

第四个优点在有大量插入的数据库中非常重要，正如您的数据所示。

复合主键本质上并没有什么问题。它们有时很有用。但这不是我会采取的方向。

- Gordon Linoff

---

那么您的意思是，不使用复合键，而是创建一个新的自增ID列，并将其用作主键？ - Skitzafreak

@Skitzafreak……是的。我创建的大多数表都有自增列作为主键。 - Gordon Linoff

那么在价格+自然键上放置一个候选键呢？ - onedaywhen

Answer 2

根据性能要求和查询该表的频率，优缺点会有所不同。

首先，我们来看下面的例子：

CREATE TABLE Part_Price_Log (
    ModifiedDate DATE,
    PartID INT,
    PRIMARY KEY (ModifiedDate, PartID))

如果ModifiedDate是第一列，并且这是一个仅插入行的日志表，则每个新行都将放置在末尾，这很好（减少了碎片化）。当您想要直接按ModifiedDate或ModifiedDate+PartID进行过滤时，此方法也很好，因为ModifiedDate是主键中的第一列。缺点是如果按PartID搜索，则无法直接查找主键的聚集索引。

第二个示例与第一个相同，但主键排序相反：

CREATE TABLE Part_Price_Log (
    ModifiedDate DATE,
    PartID INT,
    PRIMARY KEY (PartID, ModifiedDate))

这对于按PartID查询非常好，但直接按ModifiedDate查询效果不佳。而且将PartID放在前面会使插入操作移动数据页，因为插入的PartID小于最大的PartID（这会增加碎片化）。

最后一个例子是使用类似于IDENTITY的替代主键。

CREATE TABLE Part_Price_Log (
    LogID BIGINT IDENTITY PRIMARY KEY,
    ModifiedDate DATE,
    PartID INT)

这将使所有插入操作都在最后执行，减少碎片化，但您需要一个额外的索引来查询数据，例如：

CREATE NONCLUSTERED INDEX NCI_Part_Price_Log_Date_PartID ON Part_Price_Log (ModifiedDate, PartID)
CREATE NONCLUSTERED INDEX NCI_Part_Price_Log_PartID_Date ON Part_Price_Log (PartID, ModifiedDate)

这最后一个方案的缺点是插入操作会变慢（因为索引也要被更新），而且表的大小会因为索引而增加。

还要注意，如果您的数据允许同一天内对同一部件进行多次更新，则使用复合主键会导致第二次更新失败。在这里，您的选择是使用代理键、使用DATETIME代替DATE（这将给您更多的更新余地），或使用没有PRIMARY KEY或UNIQUE约束的CLUSTERED INDEX。

---

我建议采取以下措施。您只保留一个索引（实际上是表，因为它是聚集的），顺序始终是插入，您不需要担心重复的ModifiedDate与相同的PartID，并且您按日期查询的速度会很快。

CREATE TABLE Part_Price_Log (
    LogID INT IDENTITY PRIMARY KEY NONCLUSTERED,
    ModifiedDate DATE,
    PartID INT)

CREATE CLUSTERED INDEX NCI_Part_Price_Log_Date_PartID ON Part_Price_Log (ModifiedDate, PartID)

- EzLo

Answer 3

我认为在这种情况下，最好将标识列/唯一标识符作为主键。另外，如果您将partid和date作为复合主键，则在两个并发用户尝试同时更新part价格时，它将失败。在这种情况下，主键将失败。因此，更好的方法是将标识列作为主键，并继续将更改转储到日志表中。如果您以后遇到性能障碍，可以按年份对表进行分区，并克服该性能挑战。

- ErGaurav

---

如果您将partid和date作为复合主键，当两个并发用户尝试同时更新零件价格时，它将会失败。我认为这是一个薄弱的论点，您提出的解决方案似乎是一种竞争条件或现实世界中的重复（重叠时间段的不同价格）！ - onedaywhen

我提出该参数，因为我们的系统应该足够强大，以处理每种情况。在同一时间插入相同值是完全可能的，我已经在我的一个项目中遇到了这个问题。 - ErGaurav

Answer 4

不知道你的领域是什么，很难提供建议。在现实世界中，如何识别零件？假设您使用EAN，这是您的“自然键”。那么，每次价格变动时，零件是否会获得新的EAN？可能不会，在这种情况下，零件价格的真实世界标识符是其EAN和该价格有效期的组合。

我认为关于“数据中容易出现错误的简单方法”的评论是指时间数据库不仅本质上更加复杂（它们具有额外的维度-时间），而且大多数SQL DBMS中缺乏对时间功能的支持。

例如，您选择的SQL产品是否具有间隔数据类型，或者是否需要使用一对“start_date”和“end_date”列来自己创建？您选择的SQL产品是否具有内部表约束的功能，例如防止相同零件的重叠或非并发间隔？您选择的SQL产品是否具有查询时间数据的时间函数的能力？

- onedaywhen