集群和非聚簇索引之间的差异 [英] Difference between clustered and nonclustered index

问题描述

好吧，我正在自己开发一个项目（第一次为我量身定做），它开始了，所以我的桌子很可悲。我需要添加适当的索引，并需要一些帮助。

我很困惑，需要澄清几点：

• 我应该对非int列使用索引吗？为什么/为什么不


• 我已经阅读了很多关于聚簇和非聚簇索引，但是我仍然无法决定何时使用其他索引。一个很好的例子可以帮助我和许多其他的开发者。

我知道我不应该为经常更新的列或表使用索引。我还应该注意什么，我怎么知道在进行测试阶段之前都很好？

谢谢，

解决方案

你真的需要分开两个问题：

1）主键是一个逻辑结构 - 唯一可靠地识别表中每行的候选键之一。这可以是任何事情，真的 - 一个INT，一个GUID，一个字符串 - 选择对你的场景最有意义。

2）聚类键（在表中定义聚集索引的列或列） - 这是一个与物理存储相关的东西，这里，一个小型，稳定的，不断增长的数据类型是最佳选择 - INT或BIGINT作为您的默认选项。

默认情况下，SQL Server表上的主键也用作集群键 - 但不需要这样！

我应用的一条经验法则是：任何常规表（用于存储数据，即查找表等）应该具有一个聚类密钥。真的没有点没有聚类键。实际上，与普通人相反，拥有聚类密钥实际上可以加速所有常见的操作 - 甚至插入和删除（因为表组织是不同的，通常比使用堆更好 - 没有聚类的表键）。

索引的女王Kimberly Tripp 有一个关于为什么要拥有聚类键的主题的很多很好的文章，以及最好使用什么样的列你的聚类键由于您每个表只能获得一个，所以选择右侧聚类键至关重要 - 而不仅仅是任何聚类键。

• GUID作为PRIMARY KEY和/或群集密钥


• 聚集索引辩论继续


• 不断增加的聚类密钥 - 聚集索引辩论..........再次！ / a>


• 磁盘空间便宜 - 这不是点！

Marc

Ok, I'm in the middle of developing a project on my own (first time for me for this scale) and it started in a rush so my tables are kind of miserable. I need to add proper index' and need some help.

I'm confused and need to clarify a few points:

• Should I use index for non-int columns? Why/why not
• I've read a lot about clustered and nonclustered index yet I still can't decide when to use one over the other. A good example would help me and a lot of other developers.

I know that I shouldn't use indexes for columns or tables that are often updated. What else should I be careful about and how can I know that it is all good before going to test phase?

Thank you,

解决方案

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way!

One rule of thumb I would apply is this: any "regular" table (one that you use to store data in, that is a lookup table etc.) should have a clustering key. There's really no point not to have a clustering key. Actually, contrary to common believe, having a clustering key actually speeds up all the common operations - even inserts and deletes (since the table organization is different and usually better than with a heap - a table without a clustering key).

Kimberly Tripp, the Queen of Indexing has a great many excellent articles on the topic of why to have a clustering key, and what kind of columns to best use as your clustering key. Since you only get one per table, it's of utmost importance to pick the right clustering key - and not just any clustering key.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!

Marc

这篇关于集群和非聚簇索引之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！