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

发布时间：2021-01-02 05:05:33 所属栏目：MsSql教程来源：网络整理

导读：This 2007 White Paper比较了组织为聚簇索引的表上的单个select / insert / delete / update和range select语句的性能与组织为具有与CI表相同的键列上的非聚簇索引的堆的表的性能. 通常,聚簇索引选项在测试中表现更好,因为只需要维护一个结构,因为不需要书签

副标题[/!--empirenews.page--]

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”;

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

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

1/5

尾页