sql-server – 堆上的非聚簇索引与聚簇索引的性能
发布时间：2021-01-02 16:34:59 所属栏目：MsSql教程 来源：网络整理
导读：This 2007 White Paper比较了组织为聚簇索引的表上的单个select / insert / delete / update和range select语句的性能与组织为具有与CI表相同的键列上的非聚簇索引的堆的表的性能. 通常,聚簇索引选项在测试中表现更好,因为只需要维护一个结构,因为不需要书签
|
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
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
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
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
删除基准 我运行的第三批查询是删除语句 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
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
这个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
|