sql-server – 堆上的非聚簇索引与聚簇索引的性能
副标题[/!--empirenews.page--]
This 2007 White Paper比较了组织为聚簇索引的表上的单个select / insert / delete / update和range select语句的性能与组织为具有与CI表相同的键列上的非聚簇索引的堆的表的性能. 通常,聚簇索引选项在测试中表现更好,因为只需要维护一个结构,因为不需要书签查找. 本文未涉及的一个可能有趣的案例是堆上的非聚簇索引与聚簇索引上的非聚簇索引之间的比较.在那个例子中,我原本期望堆甚至可以在NCI叶级别上执行得更好SQL Server有一个RID可以直接跟随而不需要遍历聚簇索引. 是否有人知道在这个领域进行了类似的正式测试,如果是的话,结果是什么? 解决方法为了检查你的请求,我按照这个方案创建了两个表:> 790万条代表余额信息的记录. 第一个名为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)
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)); } } } }
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
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
对于表格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
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
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”; 这个基准测试的结果是针对堆的: (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |