聚集索引和非聚集索引的区别
我需要在我的表格中添加适当的 index 并且需要一些帮助.
I need to add proper index to my tables and need some help.
我很困惑，需要澄清几点:
I'm confused and need to clarify a few points:
我应该对
non-int列使用索引吗?为什么/为什么不
Should I use index for
non-intcolumns? Why/why not
我已经阅读了很多关于 clustered 和 non-clustered 索引的内容，但我仍然无法决定何时使用一个而不是另一个.一个很好的例子将帮助我和许多其他开发人员.
I've read a lot about clustered and non-clustered 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?
推荐答案
你真的需要把两个问题分开:
You really need to keep two issues apart:
1) 主键 是一种逻辑结构 - 唯一且可靠地标识表中每一行的候选键之一.这实际上可以是任何东西 - 一个 INT、一个 GUID、一个字符串 - 选择最适合您的场景的内容.
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) 聚簇键(在表上定义聚簇索引"的一列或多列)——这是一个物理存储相关的东西，这里, 小型、稳定、不断增加的数据类型是您的最佳选择 - INT 或 BIGINT 作为您的默认选项.
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.
默认情况下，SQL Server 表上的主键也用作集群键 - 但不必如此！
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).
金伯利特里普，The Queen of Indexing 有很多优秀的文章，主题是为什么要使用集群键，以及哪种列最适合用作您的集群键.由于每个表只能获得一个，因此选择正确的聚类键至关重要 - 而不仅仅是任何聚类键.
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.
- GUID 作为PRIMARY KEY 和/或聚集键
- 聚集索引的争论还在继续
- 曾经- 增加聚集键 - 聚集索引辩论.......... 再次！
- 磁盘空间很便宜 - 不是 重点！
马克
相关文章