SQL Server -什么时候使用集群索引和非集群索引?

时间：2021-09-21 02:47:37

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

例如:如果一个表没有聚集索引，应该创建一个非聚集索引，这样做的好处是什么

1 个解决方案

#1

109  

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.

它是SQL Server数据库中复制最多的数据结构。集群键也是表中每个非集群索引的一部分。

You should use extreme care when picking a clustering key - it should be:

在选择聚类键时，您应该非常小心——它应该是:

  • narrow (4 bytes ideal)

    狭窄的理想(4字节)

  • 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!)

    唯一(它毕竟是“行指针”。如果你不让它成为唯一的SQL Server，它就会在后台为你做，每个条目花费你几个字节，乘以你的行数和非集群索引的数量——这可能是非常昂贵的!

  • 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)

    理想的情况是不断增加，这样您就不会得到可怕的索引碎片(由于特定的原因，GUID是一个很好的聚类键的完全相反)

  • it should be non-nullable and ideally also fixed width - a varchar(250) makes a very poor clustering key

    它应该是非空的，而且理想的是固定宽度——varchar(250)是一个非常糟糕的聚类键。

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!

看看金伯利·特里普(索引女王)关于这个话题的一些博客文章——她在博客中所写的任何东西都是无价的——读它，消化它——靠它生活!

#1

109  

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.

它是SQL Server数据库中复制最多的数据结构。集群键也是表中每个非集群索引的一部分。

You should use extreme care when picking a clustering key - it should be:

在选择聚类键时，您应该非常小心——它应该是:

  • narrow (4 bytes ideal)

    狭窄的理想(4字节)

  • 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!)

    唯一(它毕竟是“行指针”。如果你不让它成为唯一的SQL Server，它就会在后台为你做，每个条目花费你几个字节，乘以你的行数和非集群索引的数量——这可能是非常昂贵的!

  • 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)

    理想的情况是不断增加，这样您就不会得到可怕的索引碎片(由于特定的原因，GUID是一个很好的聚类键的完全相反)

  • it should be non-nullable and ideally also fixed width - a varchar(250) makes a very poor clustering key

    它应该是非空的，而且理想的是固定宽度——varchar(250)是一个非常糟糕的聚类键。

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!

看看金伯利·特里普(索引女王)关于这个话题的一些博客文章——她在博客中所写的任何东西都是无价的——读它，消化它——靠它生活!

标签：

相关文章