每个用户表都应该有一个聚集索引吗? [英] 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 更简洁地说明这一点 布拉德·麦吉希:

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.

当索引值唯一时，聚集索引对于查找特定行是有效的，例如基于主键选择行的常见模式.

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.

聚集索引经常 允许对经常搜索值范围(between、> 等)的列进行高效查询.

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 列，它大于身份并且也是有效的随机值(不太可能被排序)，尽管 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.

使用堆(没有聚集索引)是如果数据总是通过非聚集索引访问并且已知 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 中的表上创建主键时，默认情况下它会创建一个唯一的聚集索引(如果它还没有).这意味着，如果您发现一个表没有聚集索引，但有一个主键(所有表都应该有)，那么开发人员之前已经决定以这种方式创建它.您可能希望有一个令人信服的理由来改变它(正如我们所见，其中有很多).添加、更改或删除聚簇索引需要重写整个表和任何非聚簇索引，因此在大表上这可能需要一些时间.

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屋！