根据性能要求和查询该表的频率,优缺点会有所不同。
首先,我们来看下面的例子:
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)