将聚簇索引添加到现有表是否会提高性能？ [英] Will adding a clustered index to an existing table improve performance?

问题描述

我有一个我继承的SQL 2005数据库，其中一个表在大约15年的时间里已经增长到大约1700万条记录，现在非常慢。

I have a SQL 2005 database I've inherited, with a table that has grown to about 17 million records over the course of about 15 years, and is now horribly slow.

表格布局如下所示：

id_column = nvarchar(20),indexed, not unique
column2 = nvarchar(20), indexed, not unique
column3 = nvarchar(10), indexed, not unique
column4 = nvarchar(10), indexed, not unique
column5 = numeric(8,0), indexed, not unique
column6 = numeric(8,0), indexed, not unique
column7 = nvarchar(20), indexed, not unique
column8 = nvarchar(10), indexed, not unique

（还有大约5个列看起来几乎相同，没有索引）

(and about 5 more columns that look pretty much the same, not indexed)

'id'字段是最终用户在前端应用程序中输入的值。

The 'id' field is a value entered in a front-end application by the end-user.

没有定义的主键，也没有可以组合成唯一行的列（除非组合所有列）。该表实际上是另一个表的详细信息表，但没有约束确保参照完整性。

There are no defined primary keys, and no columns that can be combined to make a unique row (unless all columns are combined). The table actually is a 'details' table to another table, but there are no constraints ensuring referential integrity.

每个列都大量用于查询中的where子句，这就是为什么我假设每个人都有一个索引，或者是一个绝望的尝试来加快另一个DBA的速度。

Every column is heavily used in 'where' clauses in queries, which is why I assume there's an index on every one, or perhaps a desperate attempt to speed things up by another DBA.

说了这么多，我的问题是：可以添加聚集索引在这一点上对我有用吗？

Having said all that, my question is: could adding a clustered index do me any good at this point?

如果我确实添加了一个聚簇索引，我认为它必须是一个新列，即一个标识列？基本上，这值得吗？

If I did add a clustered index, I assume it would have to be a new column, ie., an identity column? Basically, is it worth the trouble?

感谢任何建议。

推荐答案

我建议添加一个聚簇索引，即使它是一个标识列有三个原因：

I would recommend adding a clustered index, even if it's an identity column for 3 reasons:

1. 假设你现有的查询每次都必须经过整个表格，聚集索引扫描仍然比表扫描更快。

该表是其他表的子项。通过一些额外的工作，您可以使用新的 child_id 来加入父表。这样就可以实现聚簇索引搜索，这在某些情况下比扫描快得多。

The table is a child to some other table. With some extra works, you can use the new child_id to join against the parent table. This enables clustered index seek, which is a lot faster than scan in some cases.

根据它们的设置方式，现有指数可能没什么好处。我遇到了一些可怕的索引，每个索引包含1列，或者索引不包含相应的列，导致代价高昂的Key Lookup操作。 检查您的索引统计信息，看看它们是否属于正在使用。

Depend on how they are setup, the existing indices may not do much good. I've come across some terrible indices that cover 1 column each, or indices that don't include the appropriate columns, causing costly Key Lookups operations. Check your index stats to see if they are being used at all.

这篇关于将聚簇索引添加到现有表是否会提高性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！