Azure SQL数据库中的主键 [英] Primary key in an Azure SQL database

问题描述

我正在使用CQRS和DDD原理的分布式系统上工作.基于此，我决定实体的主键应该是Guid，它是由我的域(而不是数据库)生成的.

I'm working on a distributed system that uses CQRS and DDD principles. Based on that I decided that the primary keys of my entities should be guids, which are generated by my domain (and not by the database).

我一直在阅读有关Guid作为主键的信息.但是，如果将某些最佳实践应用于Azure SQL数据库，则似乎不再有效.

I have been reading about guids as primary keys. However, it seems that some of the best practices are not valid anymore if applied to Azure SQL database.

1. 如果您使用本地SQL Server计算机，则顺序引导很不错-生成的顺序引导将始终是唯一的.但是，在Azure上，情况不再如此.如此内容中所述线程，甚至不再受支持；生成它们也是一个坏主意，因为它变成了单点故障，并且不再保证它在服务器之间是唯一的.我想顺序向导在Azure上没有意义，所以我应该坚持常规向导.这是正确的吗?

1. Sequential guids are nice if you use an on premise SQL server machine - the sequential guids that are generated will always be unique. However, on Azure, this is not the case anymore. As discussed in this thread, it's not even supported anymore; and generating them is also a bad idea as it becomes a single point of failure and it will not be guaranteed unique anymore across servers. I guess sequential guids don't make sense on Azure, so I should stick to regular guids. Is this correct?

类型为Guid的列不适合用于群集.但是

Columns of type Guid are bad candidates for clustering. But this article states that this is not the case on Azure, and this one suggests the opposite! Which one should I believe? Should I just make my primary key a guid and leave it clustered (as it is the default for primary keys); or should I not make it clustered and choose another column for clustering?

感谢您的见解！

推荐答案

考虑以下内容

1. Sql Azure需要群集索引来执行复制.注意，索引不必是唯一的. http://blogs.msdn.com/b/sqlazure/archive/2010/05/12/10011257.aspx

聚簇索引的优势在于，索引的范围查询是通过最少的寻道来最佳执行的.

The advantage of a clustered index is that range queries on the index are performed optimally with minimum seeks.

聚集索引的缺点是，如果以不按顺序的顺序添加数据，则可能会发生页面拆分，并且插入速度可能会相对较慢.

The disadvantages of a clustered index is that, if data is added in out of sequence order, page split may occur and inserts may be relatively slower.

参考以上内容，我建议以下内容

Referencing the above, I suggest the following

1. 如果您拥有真实的密钥范围，则需要查询，例如日期，序号等

1. If you have a real key range you need to query upon, for example date, sequential number etc

1. 为该键创建一个(唯一/非唯一)聚集索引.
2. 使用域生成的GUID创建附加的唯一索引.

如果不存在真正的键范围，则只需使用域生成的GUID创建群集的唯一索引.(添加伪造的不需要的聚簇索引的开销比提供帮助更多的是障碍.)

这篇关于Azure SQL数据库中的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！