每个用户表都应该具有聚簇索引吗？ [英] Should every User Table have a Clustered Index?

问题描述

最近我在数据库中找到了几个没有定义聚簇索引的表。
但是定义了非聚集索引，因此它们处于HEAP状态。

Recently I found a couple of tables in a Database with no Clustered Indexes defined. But there are non-clustered indexes defined, so they are on HEAP.

在分析中，我发现select语句对非聚集索引中定义的列使用了过滤器。

On analysis I found that select statements were using filter on the columns defined in non-clustered indexes.

这些表上没有聚簇索引会影响性能吗？

Not having a clustered index on these tables affect performance?

推荐答案

很难比SQL Server MVP更简洁地说明这一点 Brad McGehee ：

It's hard to state this more succinctly than SQL Server MVP Brad McGehee:

根据经验，每个表都应该有一个聚簇索引。 通常，但并非总是如此，聚集索引应该位于单调增加的列上 - 例如标识列，或者值增加的其他列 - 并且是唯一的。在许多情况下，主键是聚簇索引的理想列。

As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. In many cases, the primary key is the ideal column for a clustered index.

BOL 回应了这种观点：

除少数例外情况外，每个表都应该有一个聚集索引。

With few exceptions, every table should have a clustered index.

这样做的原因很多，主要是基于这个事实聚集索引在物理上对存储中的数据进行排序。

The reasons for doing this are many and are primarily based upon the fact that a clustered index physically orders your data in storage.

• 如果您的聚集索引在单列单调增加，插入在您的存储设备上按顺序发生，并且页面拆分不会发生。

• If your clustered index is on a single column monotonically increases, inserts occur in order on your storage device and page splits will not happen.

聚簇索引在索引时查找特定行是有效的value是唯一的，例如基于主键选择行的常见模式。

Clustered indexes are efficient for finding a specific row when the indexed value is unique, such as the common pattern of selecting a row based upon the primary key.

聚簇索引经常允许对经常搜索值范围的列进行高效查询（介于，> 等。）。

A clustered index often allows for efficient queries on columns that are often searched for ranges of values (between, >, etc.).

群集可以加快数据通常排序的查询速度按一个或多个特定列。

Clustering can speed up queries where data is commonly sorted by a specific column or columns.

可以根据需要重建或重组聚簇索引，以控制表碎片。

A clustered index can be rebuilt or reorganized on demand to control table fragmentation.

这些好处甚至可以应用于观点。

您可能不希望拥有聚集索引：

You may not want to have a clustered index on:

• 频繁更改数据的列，因为SQL Server必须在物理上对存储中的数据进行重新排序。

• Columns that have frequent data changes, as SQL Server must then physically re-order the data in storage.

其他索引已经涵盖的列。

Columns that are already covered by other indexes.

宽键，因为聚簇索引也用于非聚集索引查找。

Wide keys, as the clustered index is also used in non-clustered index lookups.

GUID列，其中ar虽然，但身份大于身份，也有效的随机值（不太可能被排序） newsequentialid（） 可用于帮助缓解插入期间的物理重新排序。

GUID columns, which are larger than identities and also effectively random values (not likely to be sorted upon), though newsequentialid() could be used to help mitigate physical reordering during inserts.

A使用堆（没有聚集索引的表）的罕见理由是数据始终通过非聚簇索引访问，并且已知RID（SQL Server内部行标识符）小于聚簇索引键。

A rare reason to use a heap (table without a clustered index) is if the data is always accessed through nonclustered indexes and the RID (SQL Server internal row identifier) is known to be smaller than a clustered index key.

由于这些和其他注意事项，例如您的特定应用程序工作负载，您应该仔细选择聚簇索引以获得查询的最大好处。

Because of these and other considerations, such as your particular application workloads, you should carefully select your clustered indexes to get maximum benefit for your queries.

另请注意当您在SQL Server中的表上创建主键时，它将默认创建一个唯一的聚簇索引（如果它不是al准备好了）。这意味着如果您发现一个表没有聚集索引，但确实有一个主键（因为所有表都应该），开发人员之前已决定以这种方式创建它。您可能希望有一个令人信服的理由来改变它（其中有很多，正如我们所见）。 添加，更改或删除聚簇索引需要重写整个表和任何非聚簇索引，因此这可能需要一些大型表。

Also note that when you create a primary key on a table in SQL Server, it will by default create a unique clustered index (if it doesn't already have one). This means that if you find a table that doesn't have a clustered index, but does have a primary key (as all tables should), a developer had previously made the decision to create it that way. You may want to have a compelling reason to change that (of which there are many, as we've seen). Adding, changing or dropping the clustered index requires rewriting the entire table and any non-clustered indexes, so this can take some time on a large table.

这篇关于每个用户表都应该具有聚簇索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！