组合主键与单个整数主键大表
我们有一个数据库,它以下面的方式存储大量的测量数据。组合主键与单个整数主键大表
- 有一个名为“Instrument”的表,每个仪器都有三列主键[CustomerCode | LocationCode | InstrumentCode。那些类型为VARCHAR(4)的colums,所以完整的主键看起来像ABC | L001 | S001。这些代码对我们来说是有意义的,所以我们不能只改变整数。其他关系也定义在这些列上,但它们超出了这个问题的范围。该表格包含大约200,000行代表不同的测量数据点。
- 有一个名为“InstrumentLoggings”的表格,其中每个InstrumentLogging具有四列主键[CustomerCode | LocationCode | InstrumentCode |时间戳。 Timestamp列的类型是DateTime。外键关系在“仪器”表的前三列定义。然后是VARCHAR(25)类型的第五个字段,其中包含该时间戳的值。这个表格包含大约50亿条记录(这是否太离谱了?或者它根本不坏?)。
这里的现状很短的示意图:
我们的问题是,InstumentLoggings台增长到了200GB,性能开始下降。备份和恢复也耗费太多时间。我们正在寻找将InstrumentLoggings表中的所有主键字段清除到单个字段的方法。
我可以简单地在Instrument表上添加一个额外的列InstrumentId,并创建一个只有三个Colums的InstrumentLogging表[InstrumentId |时间戳|值]主键由InstrumentId和Timestamp列组成或者,对于性能来说,将额外的列InstrumentLoggingId添加到以前的想法会更好吗?
在下图中,您可以看到现在的日志表,以及两种备选方案。我对你的想法很好奇,如果有任何替代方案,我不看现在...
看一看Why use multiple columns as primary keys (composite primary key)。它看起来像是我们用于新开发的一致意见:单列主键,然后在必要时对所包含的复合键进行唯一约束。
这是InstrumentLoggingId的选项2。如果需要,您可以在InstrumentId/Timestamp上添加一个唯一的约束条件或附加的索引。
编辑
理由为这种选择(根据经验 - 我不是一个训练有素的DBA :-)):
- ORM简单性和适应未来发展。如果将新列添加到业务键中,则任何引用表都不必更改,代码更改也更简单。
- 唯一性和平局。假设你使用InstrumentId/Timestamp作为你的PK,你在夏令时做什么... UTC避免重复?或者由于PK冲突而导致设备出现错误并丢失数据?如果其中一个设备出现错误或其时钟同步错误,会发生什么情况......它可能会开始喷出重复的时间。拥有一个单独的唯一密钥允许您通过按时间排序并调整特定记录以调整可能存在重复的特定记录,从而找出稍后进行的操作。
- 我有第三个原因,但不记得它现在是什么,除非它与数字2混合。稍后编辑,如果我能记得:-)
- 插入性能。 AFAIK使您独特的(可能是身份)PK集群将记录插入到最后,而不是插入和洗牌基于业务密钥的记录的物理顺序(例如,假设您使用了InstrumentId/Timestamp的集群PK,仪器1将物理插入仪器2)的记录之前。我不是完全的技术方式,但是我知道有比开始插入更多的开销。
谢谢你的答案。你能否提供更多关于为什么这是一个好的解决方案的技术论证? – Jeroen1984
在EDIT中添加参数。 – SMM
谢谢你的辩论;-)论点4对我来说是最重要的,所以我会试着进一步调查这一点...... – Jeroen1984
如果没有其他表与instrumentLoggings相关,我认为没有增加另一列来充当它的主键的好处。 InstrumentId与Timestamp结合可以保留该表的主键。至于存储空间,您将保存添加InstrumentId列 - 它将替换表中的每个记录的4个字节的18个字节。 –
由于这看起来是新的开发,我建议不要使用名称Timestamp。不仅是sql server中的数据类型与日期或时间无关,而且它是令人难以置信的模糊。像DateAdded这样的一个更好,更清晰的名字。我也会尝试避免像Value这样的列名。对于手头的问题,我可能会使用身份作为组合键的一部分而不是日期时间值。 –
减少InstrumentLogging表中表示的信息可能会导致需要额外的连接来重建查询中的信息。性能将取决于您的查询以及是否存在合适的索引来支持它们。您应该基于实际测试进行优化,并解释您的疑问。 – reaanb