复合主键是否应该在SQL Server中进行群集？

考虑这个示例表(假设SQL Server 2005):

create table product_bill_of_materials
(
    parent_product_id int not null,
    child_product_id int not null,
    quantity int not null
)

我正在考虑一个包含两个product_id列的复合主键(我肯定想要一个唯一的约束)而不是一个单独的唯一ID列.问题是,从性能的角度来看,主键是否应该聚类？

我是否还应该在每个ID列上创建索引,以便更快地查找外键？我相信这个表在读取上会比写入更多.

正如其他几个人已经说过的那样,这取决于你将如何访问该表.但请记住,只要该列首先出现,那里的任何RDBMS都应该能够使用聚簇索引进行单列搜索.例如,如果您的聚集索引处于打开状态(parent_id,child_id),则不需要在(parent_id)上使用另一个单独的索引.

你最好的选择可能是(parent_id,child_id)上的聚集索引,它也恰好是主键,在(child_id)上有一个单独的非聚集索引.

最后,在了解了如何访问数据库之后,应该解决索引问题.如果可以的话,想出一些标准的性能压力测试,然后使用分析工具(SQL Profiler for SQL Server)和性能调整来分析行为.如果您没有提前做到这一点的专业知识或知识,那么请尝试(希望有限)发布应用程序,收集性能指标,并查看提高性能所需的位置并找出哪些索引有助于.

如果你做得对,你应该能够捕获数据库访问方式的"典型"配置文件,然后在尝试各种索引方法时,可以在测试服务器上反复重新运行.

在你的情况下,我可能只是在(parent_id,child_id)上放置一个聚类PK,然后只有当我看到一个可以帮助它的性能问题时才添加非聚集索引.

"您经常查询的内容"不一定是选择群集索引的最佳理由.最重要的是您查询获取多行的内容.聚类是一种适合于在最少数量的磁盘读取中获得多行的策略.

最好的例子是客户的销售历史.

假设您在Sales表上有两个索引,一个在Customer上(可能是日期,但该点适用于任何一种方式).如果您最常在CustomerID上查询表,那么您将希望所有客户的销售记录一起为所有记录提供一次或两次磁盘读取.

主键OTOH可能是代理键或SalesId,但在任何情况下都是唯一值.如果这是聚类的,那么与普通的唯一索引相比,它没有任何好处.

编辑:让我们把这个特定的表格进行讨论 - 它将揭示更多细微之处.

"自然"主键很可能是parentid + childid.但按什么顺序？Parentid + childid并不比childid + parentid更独特.出于聚类目的,哪种排序更合适？人们会认为它必须是parentid + childid,因为我们想问:"对于给定的项目,它的成分是什么"？但这不是不太可能想要走另一条路,并问"对于给定的constant,它是什么项目的组件？".

添加"覆盖索引"的考虑,其在索引中包含满足查询所需的所有信息.如果这是真的,那么你永远不需要阅读记录的其余部分; 所以聚类没有任何好处; 只是阅读索引就足够了.(顺便说一句,这意味着同一对字段上的两个索引,顺序相反;在这种情况下,这可能是正确的事情.或者至少在一个上有一个复合索引,在另一个上有一个单字段索引. )

但是,这仍然没有决定哪些应该聚集; 实际上,最终可能由哪些查询确定需要获取数量字段的记录.

即使是这样一个明显的例子,原则上最好将decidintg留给其他索引,直到你可以用真实的数据测试它们(显然在生产之前); 但在这里要求猜测是毫无意义的.测试总能为您提供正确的答案.

忘记放慢插入直到遇到问题(在大多数情况下永远不会发生),并且可以测试以确保放弃有用的索引以获得可衡量的好处.

但事情仍然不确定,因为像这样的联结表也经常涉及许多其他类型的查询.因此,我只需选择一个并根据需要进行测试,因为应用程序凝胶,测试的数据量变得可用.

顺便说一句,我希望它最终会成为parentid + childid的PK; 关于childid的非唯一索引; 和第一个聚集的.如果你更喜欢代理PK,那么你仍然需要一个关于parentid + childid的独特索引.聚类代理键不太可能是最佳的.