sql-server – 堆上的非聚簇索引与聚簇索引的性能

前端之家收集整理的这篇文章主要介绍了sql-server – 堆上的非聚簇索引与聚簇索引的性能前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
This 2007 White Paper比较了组织为聚簇索引的表上的单个select / insert / delete / update和range select语句的性能与组织为具有与CI表相同的键列上的非聚簇索引的堆的表的性能.

通常,聚簇索引选项在测试中表现更好,因为只需要维护一个结构,因为不需要书签查找.

本文未涉及的一个可能有趣的案例是堆上的非聚簇索引与聚簇索引上的非聚簇索引之间的比较.在那个例子中,我原本期望堆甚至可以在NCI叶级别上执行得更好sql Server有一个RID可以直接跟随而不需要遍历聚簇索引.

是否有人知道在这个领域进行了类似的正式测试,如果是的话,结果是什么?

解决方法

为了检查你的请求,我按照这个方案创建了两个表:

> 790万条代表余额信息的记录.
>一个身份字段,从1到790万
>一个数字字段,对大约500k组中的记录进行分组.

第一个名为heap的表在字段组上获得了非聚集索引.第二个名为clust的表在名为key的顺序字段上获得了聚簇索引,在字段组上获得了非聚集索引

测试在具有2个超线程内核,4Gb内存和64位窗口7的I5 M540处理器上运行.

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)

Update on 9 Mar 2011: I did a second more extensive benchmark by running the following .net code and logging Duration,cpu,Reads,Writes and RowCounts in sql Server Profiler. (The CommandText used will be mentioned in the results.)

NOTE: cpu and Duration are expressed in milliseconds

  • 1000 queries
  • zero cpu queries are eliminated from the results
  • 0 rows affected are eliminated from the results
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));
                        }
                    }
                }
            }

End of Update on 9 Mar 2011.

SELECT性能

为了检查performanc数字,我在堆表上执行了以下一次查询,在clust表上执行了一次:

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

这个基准测试的结果是针对堆的:

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

Update on 9 Mar 2011:
cmd.CommandText = "select * from heap where group between @id and @id+1000";

  • 721 Rows have > 0 cpu and affect more than 0 rows
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

End of Update on 9 Mar 2011.

对于表格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

Update on 9 Mar 2011:
cmd.CommandText = "select * from clust where group between @id and @id+1000";

  • 721 Rows have > 0 cpu and affect more than 0 rows
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

End of Update on 9 Mar 2011.

SELECT WITH JOIN性能

cmd.CommandText =“select * from heap / clust h join keys k on h.group = k.group where h.group between @id and @id 1000”;

这个基准测试的结果是针对堆的:

873行具有> 0 cpu并影响超过0行

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

这个基准测试的结果是针对clust的:

865行具有> 0 cpu并影响超过0行

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

更新表现

第二批查询是更新语句:

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

堆的这个基准测试的结果:

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

Update on 9 Mar 2011:
cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000";

  • 811 Rows have > 0 cpu and affect more than 0 rows
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

End of Update on 9 Mar 2011.

clust的基准测试结果如下:

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

Update on 9 Mar 2011:
cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000";

  • 853 Rows have > 0 cpu and affect more than 0 rows
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

End of Update on 9 Mar 2011.

删除基准

我运行的第三批查询删除语句

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

这个堆基准测试的结果:

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

Update on 9 Mar 2011:
cmd.CommandText = "delete heap where group between @id and @id+1000";

  • 724 Rows have > 0 cpu and affect more than 0 rows
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

End of Update on 9 Mar 2011.

这个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

Update on 9 Mar 2011:

cmd.CommandText = "delete clust where group between @id and @id+1000";

  • 751 Rows have > 0 cpu and affect more than 0 rows
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

End of Update on 9 Mar 2011.

INSERT基准测试

基准测试的最后一部分是插入语句的执行.

插入堆/ clust(…)
值(…),
(…),
(……)

这个堆基准测试的结果:

rows  reads cpu   Elapsed 
----- ----- ----- -------- 
6     38    0ms   31ms

Update on 9 Mar 2011:

string str = @"insert into heap (group,currency,year,period,domain_id,mtdAmount,ytdAmount,amount,ytd_restated,restated,auditDate,auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0},'EUR',2012,2,100,1000 + @id,1000,current_timestamp,'test'),",x);
                    }
                    str += string.Format(@"(@id,'CAD','test') ",1000);

                    cmd.CommandText = str;
  • 912 statements have > 0 cpu
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

End of Update on 9 Mar 2011.

这个clust基准的结果:

rows  reads cpu   Elapsed 
----- ----- ----- -------- 
6     50    0ms   18ms

Update on 9 Mar 2011:

string str = @"insert into clust (group,1000);

                    cmd.CommandText = str;
  • 946 statements have > 0 cpu
Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -      
cpu            15       2403      21   0.02157
Reads        6810       8997    8412   8.41223
Writes         16         25      19   0.01942
Duration   1.5375   268.2571  6.1463   0.00614

End of Update on 9 Mar 2011.

结论

虽然使用集群& amp;访问表时会有更多的逻辑读取.非聚集索引(使用非聚簇索引时)性能结果为:

> SELECT语句具有可比性
>使用聚簇索引时,UPDATE语句更快
>使用聚簇索引,DELETE语句更快
>使用聚簇索引时,INSERT语句更快

当然,我的基准测试对于特定类型的表以及非常有限的查询集非常有限,但我认为基于这些信息我们已经可以开始说,在表上创建聚簇索引几乎总是更好.

Update on 9 Mar 2011:

正如我们从增加的结果中看到的那样,有限测试的结论在每种情况下都不正确.

结果现在表明,受益于聚簇索引的唯一语句是update语句.使用聚簇索引的表上的其他语句约慢30%.

一些额外的图表,其中我绘制了堆与clust的每个查询的加权持续时间.

正如您所看到的,insert语句的性能配置文件非常有趣.尖峰是由一些数据点引起的,这些数据点需要更长的时间才能完成.

End of Update on 9 Mar 2011.

原文链接:https://www.f2er.com/mssql/80243.html

猜你在找的MsSQL相关文章