SQL Server中如何选择聚集索引? [英] How to choose the clustered index in 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.

这篇关于SQL Server中如何选择聚集索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！