重新索引大桌子 - 我有多么伤心？ [英] Re-indexing large table - how screwed am I?

问题描述

我有一个1 TB，600m的行，它有一个错误的索引列选择，特别是主键列上的聚集索引，从未在select查询中使用。

我想从此行中删除聚簇索引，并在多个其他行上创建。

表目前是这样的：

• colA（PK， nvarchar（3））[聚集索引pt b]




• colB（PK，bigint）[聚集索引pt a]




• colC（DateTime）[非聚集索引]




• colD（Money）[非聚集索引]




• colE（bit）[no index]




• colF（bit）[no index] / p>




• colG（int）[no index]




• 更多非索引列

我想将其更改为：

• colA（PK，nvarchar（3））[聚集索引pt a]




• colB（PK，bigint） [非聚集索引]




• colC（DateTime）[非聚集索引]



• p> colD（Money）[聚集索引pt d]




• colE（bit）[clustered index pt b]




• colF（bit）[clustered index pt c]




• colG（int）[clustered index pt e]




• 更多非索引列

两个问题：
1）你会猜测这个更改需要多长时间（服务器规格在消息结束）。不幸的是，它是一个实时数据库，我不能有停机时间，没有一个想法多长时间会下来。

2）这是一个可怕的想法，添加许多列到聚簇索引？更新几乎从未执行。有许多插件和许多选择始终使用所有建议的索引行作为选择参数。

服务器规格：RAID 5中的5 x 15kRPM驱动器，MS-SQL Sever 2005年和一些比特让他们运行。

解决方案

一方面，我会避免使聚簇索引比绝对有更广泛的成为。将其分为五个部分似乎是有害的。这个化合物中的所有列聚集索引是否稳定，例如从不改变？？

如果没有，我会不惜一切代价避免他们。聚集索引应为：

• 唯一


• 稳定


• 尽可能狭窄

您可以更改非聚集索引 - 没有问题。但避免使聚集索引凌乱！这肯定会降低你的表现！

查看Kimberly Tripp关于索引的优秀博客文章：

• 此处的主要链接


• 聚集索引的最佳做法， a>

Marc

I have a 1 TB, 600m row, table which has a misguided choice of indexed columns, specifically a clustered index on the primary key column which is never used in a select query.

I want to remove the clustered index from this row and create it on a number of other rows.

Table is currently like this:

• colA (PK, nvarchar(3)) [clustered index pt b]

• colB (PK, bigint) [clustered index pt a]

• colC (DateTime) [non-clustered index]

• colD (Money) [non-clustered index]

• colE (bit) [no index]

• colF (bit) [no index]

• colG (int) [no index]

• more non-indexed columns

I would like to change it to look like this:

• colA (PK, nvarchar(3)) [clustered index pt a]

• colB (PK, bigint) [non-clustered index]

• colC (DateTime) [non-clustered index]

• colD (Money) [clustered index pt d]

• colE (bit) [clustered index pt b]

• colF (bit) [clustered index pt c]

• colG (int) [clustered index pt e]

• more non-indexed columns

Two questions: 1) How long would you guesstimate that this change will take (server spec at end of message). Unfortunately it is a live DB and I can't have downtime without some idea of how long it will be down for.

2) Is it a terrible idea to add so many columns to a clustered index? Updates are nearly never performed. There are many inserts and many selects which always use all of the proposed indexed rows as select parameters.

Server spec: 5 x 15kRPM drives in RAID 5, MS-SQL Sever 2005 and some bits to keep them running.

解决方案

For one thing, I would AVOID making the clustered index wider than it absolutely has to be. Making it into five parts seems about contra-productive. Are ALL the columns in this compound clustered index stable, e.g. never change??

If not, I would avoid them at all costs. A clustered index should be:

• unique
• stable
• as narrow as possible

You can change your non-clustered indices - no problem. But avoid making the clustered index messy! That'll definitely bring down your performance!

Check out Kimberly Tripp's excellent blog articles on indexing:

• main link here
• best practices for clustering index here

Marc

这篇关于重新索引大桌子 - 我有多么伤心？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！