SQL Server中如何选择聚集索引?
通常在 SQL Server Management Studio 中通过设置主键创建聚集索引，但是我最近关于 PK <-> 聚集索引的问题 (主键对Microsoft SQL Server 2008 的意义) 已经表明没有必要将PK 和聚集索引设置为相等.
Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (Meaning of Primary Key to Microsoft SQL Server 2008) has shown that it is not necessary to set PK and clustered index to be equal.
那我们应该如何选择聚集索引呢?举个例子:
So how should we choose clustered indexes then? Let's have the following example:
create table Customers (ID int, ...)
create table Orders (ID int, CustomerID int)
我们通常会在两个 ID 列上创建 PK/CI，但我考虑为 CustomerID 中的订单创建它.这是最好的选择吗?
We would usually create the PK/CI on both ID columns but I thought about creating it for Orders in CustomerID. Is that the best choice?
推荐答案
根据索引女王 - Kimberly Tripp - 她在聚集索引中寻找的主要是:
According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily:
- 独一无二
- 窄
- 静态
如果你也能保证:
- 不断增加的模式
那么您就非常接近拥有理想的聚类密钥了！
then you're pretty close to having your ideal clustering key!
查看她的整个此处的博客文章，还有另一篇关于聚类关键影响对表操作的非常有趣的文章:聚集索引辩论继续.
Check out her entire blog post here, and another really interesting one about clustering key impacts on table operations here: The Clustered Index Debate Continues.
任何像 INT(特别是 INT IDENTITY)或可能是 INT 和 DATETIME 的东西都是理想的候选者.由于其他原因，GUID 根本不是很好的候选对象 - 因此您可能有一个 GUID 作为您的 PK，但不要将您的表聚集在其上 - 它会被分割得无法识别并且性能会受到影响.
Anything like an INT (esp. an INT IDENTITY) or possibly an INT and a DATETIME are ideal candiates. For other reasons, GUID's aren't good candidates at all - so you might have a GUID as your PK, but don't cluster your table on it - it'll be fragmented beyond recognition and performance will suffer.
相关文章