通常，聚集索引选项在测试中表现更好，因为只需要维护一种结构，并且因为不需要书签查找。
一种可能有趣的情况本文没有涉及的是堆上非聚集索引与聚集索引上非聚集索引之间的比较。在那种情况下，我希望堆甚至可以在NCI叶级别上表现更好，因为SQL Server具有直接遵循的RID，而不需要遍历聚集索引。
有人知道在这一领域进行过类似的正式测试吗？如果是，结果是什么？
#1 楼
为了检查您的请求，我按照此方案创建了2个表：790万条代表余额信息的记录。从1到790万的身份字段
将记录分为约50万的数字字段。
第一个称为
heap的表在字段group上获得了非聚集索引。第二个表clust在顺序字段key上有一个聚集索引，而在字段group上有一个非聚集索引。测试是在具有2个超线程内核，4Gb内存和64-位窗口7.
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2011年3月9日更新：我通过运行以下.net代码并记录了工期，CPU，做了另一个更广泛的基准测试，读取，写入和RowCounts在Sql Server Profiler中。 （结果中将提到使用的CommandText。）注意：CPU和持续时间以毫秒为单位
1000个查询
零个CPU查询从结果中删除
从结果中消除了受影响的0行
int[] idList = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here.
using (var conn = new SqlConnection(@"Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;"))
{
conn.Open();
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from heap where common_key between @id and @id+1000";
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Prepare();
foreach (int id in idList)
{
cmd.Parameters[0].Value = id;
using (var reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
count++;
}
Console.WriteLine(String.Format("key: {0} => {1} rows", id, count));
}
}
}
}
更新结束2011年3月9日。
选择性能
要检查性能编号，我在堆表上一次在集群表上执行了以下查询：
select * from heap/clust where group between 5678910 and 5679410
select * from heap/clust where group between 6234567 and 6234967
select * from heap/clust where group between 6455429 and 6455729
select * from heap/clust where group between 6655429 and 6655729
select * from heap/clust where group between 6955429 and 6955729
select * from heap/clust where group between 7195542 and 7155729
此基准测试的结果适用于
heap：rows reads CPU Elapsed
----- ----- ----- --------
1503 1510 31ms 309ms
401 405 15ms 283ms
2700 2709 0ms 472ms
0 3 0ms 30ms
2953 2962 32ms 257ms
0 0 0ms 0ms
更新于2011年3月9日：
cmd.CommandText = "select * from heap where group between @id and @id+1000"; 721行具有> 0 CPU，影响多于0行
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1001 69788 6368 -
Cpu 15 374 37 0.00754
Reads 1069 91459 7682 1.20155
Writes 0 0 0 0.00000
Duration 0.3716 282.4850 10.3672 0.00180
更新结束于2011年3月9日。
表
clust的结果是：rows reads CPU Elapsed
----- ----- ----- --------
1503 4827 31ms 327ms
401 1241 0ms 242ms
2700 8372 0ms 410ms
0 3 0ms 0ms
2953 9060 47ms 213ms
0 0 0ms 0ms
2011年3月9日更新：
q431207 9q
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1001 69788 6056 -
Cpu 15 468 38 0.00782
Reads 3194 227018 20457 3.37618
Writes 0 0 0 0.0
Duration 0.3949 159.6223 11.5699 0.00214
更新于2011年3月9日结束。此基准测试的结果适用于
cmd.CommandText = "select * from clust where group between @id and @id+1000";：Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1009 4170 1683 -
Cpu 15 47 18 0.01175
Reads 2145 5518 2867 1.79246
Writes 0 0 0 0.00000
Duration 0.8215 131.9583 1.9095 0.00123
此基准测试的结果适用于
cmd.CommandText = "select * from heap/clust h join keys k on h.group = k.group where h.group between @id and @id+1000";：865行具有> 0 CPU，并且影响多于0行br />
第二批查询是更新语句：
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1000 4143 1685 -
Cpu 15 47 18 0.01193
Reads 5320 18690 8237 4.97813
Writes 0 0 0 0.00000
Duration 0.9699 20.3217 1.7934 0.00109
heap的本基准测试结果：update heap/clust set amount = amount + 0 where group between 5678910 and 5679410
update heap/clust set amount = amount + 0 where group between 6234567 and 6234967
update heap/clust set amount = amount + 0 where group between 6455429 and 6455729
update heap/clust set amount = amount + 0 where group between 6655429 and 6655729
update heap/clust set amount = amount + 0 where group between 6955429 and 6955729
update heap/clust set amount = amount + 0 where group between 7195542 and 7155729
2011年3月9日更新：
clust 811行具有> 0 CPU，并且影响大于0行
rows reads CPU Elapsed
----- ----- ----- --------
1503 3013 31ms 175ms
401 806 0ms 22ms
2700 5409 47ms 100ms
0 3 0ms 0ms
2953 5915 31ms 88ms
0 0 0ms 0ms
更新于2011年3月9日结束。
>
heap的基准测试结果：Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1001 69788 5598 811
Cpu 15 873 56 0.01199
Reads 2080 167593 11809 2.11217
Writes 0 1687 121 0.02170
Duration 0.6705 514.5347 17.2041 0.00344
2011年3月9日更新：
cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000"; 853行具有> 0 CPU，并影响超过0行
rows reads CPU Elapsed
----- ----- ----- --------
1503 9126 16ms 35ms
401 2444 0ms 4ms
2700 16385 31ms 54ms
0 3 0ms 0ms
2953 17919 31ms 35ms
0 0 0ms 0ms
更新于2011年3月9日结束。
删除基准测试
我运行的第三批查询是删除语句
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1001 69788 5420 -
Cpu 15 594 50 0.01073
Reads 6226 432237 33597 6.20450
Writes 0 1730 110 0.01971
Duration 0.9134 193.7685 8.2919 0.00155
clust的基准测试结果：delete heap/clust where group between 5678910 and 5679410
delete heap/clust where group between 6234567 and 6234967
delete heap/clust where group between 6455429 and 6455729
delete heap/clust where group between 6655429 and 6655729
delete heap/clust where group between 6955429 and 6955729
delete heap/clust where group between 7195542 and 7155729
9月更新2011年3月：
cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000"; 更新于2011年3月9日结束。
heap的基准测试结果：rows reads CPU Elapsed
----- ----- ----- --------
1503 10630 62ms 179ms
401 2838 0ms 26ms
2700 19077 47ms 87ms
0 4 0ms 0ms
2953 20865 62ms 196ms
0 4 0ms 9ms
2011年3月9日更新：
cmd.CommandText = "delete heap where group between @id and @id+1000"; 751行具有> 0 CPU并影响超过0行
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 192 69788 4781 -
Cpu 15 499 45 0.01247
Reads 841 307958 20987 4.37880
Writes 2 1819 127 0.02648
Duration 0.3775 1534.3383 17.2412 0.00349
2011年3月9日更新结束。
INSERT基准测试
基准测试的最后一部分是insert语句的执行。
插入到堆/集群（...）
值（...），
（... ），
（...），
（...），
（...），
（...）
clust的基准测试结果：rows reads CPU Elapsed
----- ----- ----- --------
1503 9228 16ms 55ms
401 3681 0ms 50ms
2700 24644 46ms 79ms
0 3 0ms 0ms
2953 26955 47ms 92ms
0 3 0ms 0ms
2011年3月9日更新：
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 144 69788 4648 -
Cpu 15 764 56 0.01538
Reads 989 458467 30207 6.48490
Writes 2 1830 127 0.02694
Duration 0.2938 2512.1968 24.3714 0.00555
rows reads CPU Elapsed
----- ----- ----- --------
6 38 0ms 31ms
更新于2011年3月9日结束。
cmd.CommandText = "delete clust where group between @id and @id+1000";的本基准测试结果：string str = @"insert into heap (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
values";
for (int x = 0; x < 999; x++)
{
str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'), ", x);
}
str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);
cmd.CommandText = str;
2011年3月9日更新：
Counter Minimum Maximum Average Weighted
--------- ------- ---------- ------- ---------
RowCounts 1000 1000 1000 -
Cpu 15 2138 25 0.02500
Reads 5212 7069 6328 6.32837
Writes 16 34 22 0.02222
Duration 1.6336 293.2132 4.4009 0.00440
946语句具有> 0 CPU
rows reads CPU Elapsed
----- ----- ----- --------
6 50 0ms 18ms
2011年3月9日更新结束。
结论
使用聚集索引和非聚集索引访问表时，尽管有更多的逻辑读取（w （使用非聚集索引时）的性能结果是：
SELECT语句具有可比性。有了聚集索引就可以了。
INSERT语句有了聚集索引就可以更快了。
当然，我的基准测试对特定类型的表和查询集非常有限，但是我认为基于这些信息，我们已经可以开始说在表上创建聚簇索引实际上总是更好。
2011年3月9日更新：
从添加的结果中可以看出，有限测试的结论在每种情况下都不正确。
现在的结果表明从聚集索引中受益的唯一语句是更新语句。在具有聚集索引的表上，其他语句的速度要慢大约30％。
我还绘制了一些其他图表，显示了每个查询的堆与集群的加权持续时间。
/>
如您所见，insert语句的性能配置文件非常有趣。峰值是由一些需要较长时间才能完成的数据点引起的。
2011年3月9日更新结束。
#2 楼
正如金伯利·特里普（Kimberly Tripp）（索引女王）在她的博客文章《群集索引辩论》中继续很好地解释...，在数据库表上具有群集键几乎可以加快所有操作-不仅仅是SELECT。与选择聚簇表相比，只要在堆中选择好聚类键（例如
INT IDENTITY），SELECT通常会比聚簇表慢。如果您使用非常糟糕的群集密钥（例如GUID或具有很多可变长度组件的复合密钥），那么（只有这样）堆可能会更快。但是在那种情况下，您真的首先需要清理数据库设计... 所以总的来说，我认为堆中没有任何意义-选择一个好的，有用的群集密钥，您应该在各个方面都受益。
评论
这是无答案的。 Martin在SQL Server上非常扎实。这个问题的目的是从性能测试中获得经过真实世界测试的验证结果，而不是更多的理论。
–理查德又名网络猕猴桃
2011年3月9日19:11
金伯利·特里普（Kimberly Tripp）的文章有效地假设所有非聚簇索引都被覆盖。如果真是这样，那么就不会有查找，并且堆在查找中的优势也会被抵消。但是，这不是我们大多数人生活的世界。在我们的案例中，尝试设计全部或大部分非聚集索引来覆盖会产生自身的问题。
–user31683
2013年12月13日18:08
@ dbaguy52：为什么您认为Kim Tripp假定所有NC索引都覆盖了？我在她的博客文章中看不到任何想法.....请更详细地说明是什么使您相信情况如此（或者这是她的假设）
– marc_s
2013年12月13日19:43在
#3 楼
碰巧碰到Joe Chang的这篇文章，它解决了这个问题。在下面粘贴他的结论。考虑一个表，索引的深度为4，因此有一个根级别，2个中间级别和叶级别。单个索引键（即，没有键查找）的索引seek
将生成4
逻辑IO（LIO）。现在考虑是否需要密钥查找。如果
表具有深度也为4的聚簇索引，则每个键查找都会生成
4 LIO。如果表是堆，则每个键查找都会生成1个LIO。实际上，对堆的键查询要比对聚簇索引的键查询便宜约20-30％，而不是接近4：1 LIO比率。
br />
评论
值得注意的是，Joe Chang的报价根据他的假设确定了20-30％的堆效率优势，这与3月9日对本文进行更新时确定的优势几乎相同。
–user31683
2013年12月13日18:08
评论
@Martin下周找到一些时间时，我将尝试在具有几张具有5亿条记录的表的服务器上运行此命令。
–Filip De Vos
2011-3-4在22:59
我怀疑这项测试的准确性。有些部分需要特别注意，例如INSERT的性能声称聚集索引更快-CLUST版本中的读取次数更多，但是经过的时间更少。我个人会忽略所经过的时间（在10毫秒以内）（时序可变性）-这意味着少于读取计数。
–理查德又名网络猕猴桃
11年8月8日在21:18
查看Kimberly Tripp的“聚簇索引辩论继续”，她解释了为什么对聚簇表进行的大多数（如果不是全部）操作都比对堆进行操作更快-有些与您的结果相反...
– marc_s
2011-3-9在5:22
@ Martin，@ Richard，@ marc_s。我现在正在制定更严格的基准测试。我希望能够在今天晚些时候添加结果。
–Filip De Vos
2011年3月9日在20:05
@Filip-哇！您肯定会得到所有为此付出的辛勤工作的悬赏。尽管正如您完全正确地指出的那样，这是针对特定类型表的一个基准，并且查询量非常有限，而且里程数无疑会有所不同。
–马丁·史密斯
2011年10月10日在12:52