复合主键应该在SQL Server中集群吗？ [英] Should a Composite Primary Key be clustered in SQL Server?

问题描述

考虑这个例子表（假设SQL Server 2005）：

Consider this example table (assuming 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
)

b $ b

我正在考虑一个复合主键包含两个product_id列（我一定要一个唯一约束），而不是一个单独的唯一ID列。问题是，从性能的角度来看，主键是否应该被聚集？

I'm considering a composite primary key containing the two product_id columns (I'll definitely want a unique constraint) as opposed to a separate unique ID column. Question is, from a performance point of view, should that primary key be clustered?

我应该在每个ID列上创建一个索引，以便查找外键更快？

Should I also create an index on each ID column so that lookups for the foreign keys are faster? I believe this table is going to get hit much more on reads than writes.

推荐答案

正如其他几个人所说的，它取决于你将如何访问表。请记住，任何RDBMS应该能够使用聚集索引来搜索单个列，只要该列首先出现。例如，如果你的聚集索引是（parent_id，child_id），你不需要另一个单独的索引（parent_id）。

As has already been said by several others, it depends on how you will access the table. Keep in mind though, that any RDBMS out there should be able to use the clustered index for searching by a single column as long as that column appears first. For example, if your clustered index is on (parent_id, child_id) you don't need another separate index on (parent_id).

你最好的赌注可能是聚集索引（parent_id，child_id），这也恰好是主键，在（child_id）上有一个单独的非聚集索引。

Your best bet may be a clustered index on (parent_id, child_id), which also happens to be the primary key, with a separate non-clustered index on (child_id).

最后，索引应该在你有一个想法如何访问数据库。如果您可以使用性能分析工具（SQL Profiler for SQL Server）分析行为，并从中进行性能调整，请进行一些标准性能压力测试。如果您没有提前做到这一点的专业知识或知识，请尝试一个（希望有限的）应用程序版本，收集性能指标，并查看您需要提高性能，找出哪些指标将帮助。

Ultimately, indexing should be addressed after you've got an idea of how the database will be accessed. Come up with some standard performance stress tests if you can and then analyze the behavior using a profiling tool (SQL Profiler for SQL Server) and performance tune from there. If you don't have the expertise or knowledge to do that ahead of time, then try for a (hopefully limited) release of the application, collect the performance metrics, and see where you need to improve performance and figure out what indexes will help.

如果你做的正确，你应该能够捕获如何访问数据库的典型配置文件，然后你可以重复一遍

If you do things right, you should be able to capture the "typical" profile of how the database is accessed and you can then rerun that over and over again on a test server as you try various indexing approaches.

在你的情况下，我可能只是把一个聚集PK（parent_id，child_id）开始，然后添加非 - 如果我看到一个性能问题，它会帮助它。

In your case I would probably just put a clustered PK on (parent_id, child_id) to start with and then add the non-clustered index only if I saw a performance problem that would be helped by it.

这篇关于复合主键应该在SQL Server中集群吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！