sql-server - 没有主键的表

Question

我有几个表，其唯一的唯一数据是 uniqueidentifier(Guid)列。因为 guid 是非顺序的(并且它们是客户端生成的，所以我不能使用 newsequentialid())，所以我在此 ID 字段上创建了非主、非聚集索引，而不是为表提供聚集主索引关键。

我想知道这种方法对性能有何影响。我看到有些人建议表应该有一个自动递增(“身份”)int作为聚集主键，即使它没有任何意义，因为这意味着数据库引擎本身可以使用该值来快速查找一行而不必使用书签。

我的数据库是在一堆服务器上进行合并复制的，所以我避开了 Identity int 列，因为它们在复制中有点麻烦。

你有什么想法？表应该有主键吗？或者，如果没有合理的列来建立索引，那么不使用任何聚集索引是否可以？

Answer 1

处理索引时，您必须确定表的用途。如果您主要每秒插入 1000 行并且不执行任何查询，那么聚集索引会对性能造成影响。如果每秒执行 1000 个查询，那么没有索引将导致性能非常差。尝试调整查询/索引时最好的办法是使用 SQL Server 中的查询计划分析器和 SQL 事件探查器。这将向您显示在哪里遇到代价高昂的表扫描或其他性能障碍。

至于 GUID 与 ID 的争论，您可以在网上找到对两者都认可的人。我一直被教导要使用 GUID，除非我有充分的理由不这样做。 Jeff 有一篇很好的文章，讨论了使用 GUID 的原因:https://blog.codinghorror.com/primary-keys-ids-versus-guids/ .

与大多数与开发相关的事情一样，如果您希望提高性能，那么没有唯一正确的答案。这实际上取决于您想要实现的目标以及您如何实现解决方案。唯一正确的答案是根据性能指标进行测试、测试、再测试，以确保您实现目标。

[编辑]@Matt，在对 GUID/ID 争论做了更多研究之后，我发现了这篇文章。就像我之前提到的，没有真正的正确或错误的答案。这取决于您的具体实现需求。但以下是使用 GUID 作为主键的一些非常有效的理由:

For example, there is an issue known as a "hotspot", where certain pages of data in a table are under relatively high currency contention. Basically, what happens is most of the traffic on a table (and hence page-level locks) occurs on a small area of the table, towards the end. New records will always go to this hotspot, because IDENTITY is a sequential number generator. These inserts are troublesome because they require Exlusive page lock on the page they are added to (the hotspot). This effectively serializes all inserts to a table thanks to the page locking mechanism. NewID() on the other hand does not suffer from hotspots. Values generated using the NewID() function are only sequential for short bursts of inserts (where the function is being called very quickly, such as during a multi-row insert), which causes the inserted rows to spread randomly throughout the table's data pages instead of all at the end - thus eliminating a hotspot from inserts.

Also, because the inserts are randomly distributed, the chance of page splits is greatly reduced. While a page split here and there isnt too bad, the effects do add up quickly. With IDENTITY, page Fill Factor is pretty useless as a tuning mechanism and might as well be set to 100% - rows will never be inserted in any page but the last one. With NewID(), you can actually make use of Fill Factor as a performance-enabling tool. You can set Fill Factor to a level that approximates estimated volume growth between index rebuilds, and then schedule the rebuilds during off-peak hours using dbcc reindex. This effectively delays the performance hits of page splits until off-peak times.

If you even think you might need to enable replication for the table in question - then you might as well make the PK a uniqueidentifier and flag the guid field as ROWGUIDCOL. Replication will require a uniquely valued guid field with this attribute, and it will add one if none exists. If a suitable field exists, then it will just use the one thats there.

Yet another huge benefit for using GUIDs for PKs is the fact that the value is indeed guaranteed unique - not just among all values generated by this server, but all values generated by all computers - whether it be your db server, web server, app server, or client machine. Pretty much every modern language has the capability of generating a valid guid now - in .NET you can use System.Guid.NewGuid. This is VERY handy when dealing with cached master-detail datasets in particular. You dont have to employ crazy temporary keying schemes just to relate your records together before they are committed. You just fetch a perfectly valid new Guid from the operating system for each new record's permanent key value at the time the record is created.

http://forums.asp.net/t/264350.aspx