SQL Server - 何时使用Clustered与非Clustered Index？ [英] SQL Server - When to use Clustered vs non-Clustered Index?
问题描述
我知道群集和非群集索引之间的主要区别，并了解它们的实际工作方式。我理解聚簇索引和非聚簇索引如何提高读取性能。但有一件事我不确定是什么原因让我选择一个而不是另一个。
例如：如果一个表没有聚集索引，那么应该创建一个非聚集索引并获得做什么的好处
我只想提出警告：请非常谨慎选择您的聚集索引！每个常规数据表都应该有一个聚簇索引，因为拥有聚簇索引确实加快了很多操作 - 是的，加速，甚至插入和删除！但是，只有选择良好聚簇索引。
这是SQL Server数据库中复制最多的数据结构。集群密钥也将成为您桌面上每个非聚集索引的一部分。
选择集群密钥时应特别小心 - 它应该是：
-
狭窄（4字节理想）
-
唯一（毕竟它是行指针。如果你没有使它成为唯一的SQL Server会在后台为你做这个，花费你几个字节每个条目乘以行数和非聚集索引的数量 - 这可能非常昂贵！）
-
static （永远不会改变 - 如果可能的话）
-
理想情况下不断增加所以你最终不会得到可怕的索引碎片（GUID是<= p>
-
它应该是不可为空的，理想情况下也是固定的宽度 -
varchar（250）制作一个非常差的聚类键
其他任何事情都应该是这些要点背后的第二和第三级别的重要性......
参见一些金伯利·特里普（索引女王）关于这个主题的博客帖子 - 她在博客上写的任何东西都是绝对无价的 - 读它，消化它 - 靠它生活！
I know primary differences between clustered and non clustered indexes and have an understanding of how they actually work. I understand how clustered and non-clustered indexes improve read performance. But one thing I am not sure is that what would be the reasons where I would choose one over the other.
For example: If a table does not have a clustered index, should one create a non-clustered index and whats the benefit of doing
I just want to put in a word of warning: please very carefully pick your clustered index! Every "regular" data table ought to have a clustered index, since having a clustered index does indeed speed up a lot of operations - yes, speed up, even inserts and deletes! But only if you pick a good clustered index.
It's the most replicated data structure in your SQL Server database. The clustering key will be part of each and every non-clustered index on your table, too.
You should use extreme care when picking a clustering key - it should be:
narrow (4 bytes ideal)
unique (it's the "row pointer" after all. If you don't make it unique SQL Server will do it for you in the background, costing you a couple of bytes for each entry times the number of rows and the number of nonclustered indices you have - this can be very costly!)
static (never change - if possible)
ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)
it should be non-nullable and ideally also fixed width - a
varchar(250)makes a very poor clustering key
Anything else should really be second and third level of importance behind these points ....
See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!
- GUIDs as PRIMARY KEYs and/or the clustering key
- The Clustered Index Debate Continues...
- Ever-increasing clustering key - the Clustered Index Debate..........again!
- Disk space is cheap - that's not the point!
这篇关于SQL Server - 何时使用Clustered与非Clustered Index？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
