이 2007 백서 는 클러스터 된 인덱스로 구성된 테이블에 대한 개별 선택 / 삽입 / 삭제 / 업데이트 및 범위 선택 문의 성능과 CI와 동일한 키 열에 클러스터되지 않은 인덱스가있는 힙으로 구성된 테이블의 성능을 비교합니다. 표.
일반적으로 클러스터 된 인덱스 옵션은 유지 관리 할 구조가 하나 뿐이고 책갈피 조회가 필요하지 않으므로 테스트에서 더 나은 성능을 보였습니다.
이 백서에서 다루지 않은 잠재적으로 흥미로운 사례는 힙의 클러스터되지 않은 인덱스와 클러스터 된 인덱스의 비 클러스터 된 인덱스를 비교 한 것입니다. 이 경우 NCI 리프 수준에서 SQL Server에 클러스터형 인덱스를 순회하지 않고 직접 따라야 할 RID가 있으면 힙 성능이 한 번 더 향상 될 것으로 예상했습니다.
누구든지이 영역에서 수행 된 유사한 공식 테스트를 알고 있습니까? 그렇다면 그 결과는 무엇입니까?
답변
귀하의 요청을 확인하기 위해이 구성표에 따라 2 개의 테이블을 만들었습니다.
- 잔액 정보를 나타내는 790 만 개의 레코드.
- 1-7 백만에 이르는 신원 필드
- 약 500k 그룹으로 레코드를 그룹화하는 숫자 필드.
첫 번째 테이블 heap은 필드에서 클러스터되지 않은 인덱스를 얻었습니다 group. 호출 된 두 번째 테이블은 호출 clust된 순차 필드에서 클러스터 된 인덱스를 얻었고 필드 key에서 비 클러스터형 인덱스를 얻었습니다.group
테스트는 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) 2011 년 3 월 9 일 업데이트 : Sql Server Profiler에서 다음 .net 코드를 실행하고 Duration, CPU, Reads, Writes 및 RowCounts를 로깅하여 두 번째로 광범위한 벤치 마크를 수행했습니다. 사용 된 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 일 업데이트 종료 .
성능 선택
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이 벤치 마크의 결과는 다음과 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 0ms2011 년 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.001802011 년 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 0ms2011 년 3 월 9 일 업데이트 :
cmd.CommandText = "select * from clust where group between @id and @id+1000";
- 721 개의 행에 0 개 이상의 CPU가 있으며 0 개 이상의 행에 영향을 미칩니다.
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.002142011 년 3 월 9 일 업데이트 종료 .
참여 성과로 선택
cmd.CommandText = "select * from heap/clust h join keys k on h.group = k.group where h.group between @id and @id+1000";
이 벤치 마크의 결과는 다음과 heap같습니다.
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이 벤치 마크 결과는 다음과 heap같습니다.
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 0ms2011 년 3 월 9 일 업데이트 :
cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000";
- 811 개의 행에 0 개 이상의 CPU가 있고 0 개 이상의 행에 영향을 미칩니다.
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.003442011 년 3 월 9 일 업데이트 종료 .
이 벤치 마크 결과는 다음과 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 0ms2011 년 3 월 9 일 업데이트 :
cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000";
- 853 개의 행에> 0 개의 CPU가 있으며 0 개 이상의 행에 영향을줍니다.
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.001552011 년 3 월 9 일 업데이트 종료 .
벤치 마크 삭제
내가 실행 한 세 번째 쿼리는 삭제 문입니다.
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이 벤치 마크의 결과는 다음과 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 9ms2011 년 3 월 9 일 업데이트 :
cmd.CommandText = "delete heap where group between @id and @id+1000";
- 724 개의 행에> 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.003492011 년 3 월 9 일 업데이트 종료 .
이 벤치 마크 결과는 다음과 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 0ms2011 년 3 월 9 일 업데이트 :
cmd.CommandText = "delete clust where group between @id and @id+1000";
- 751 개의 행에> 0 개의 CPU가 있으며 0 개 이상의 행에 영향을줍니다.
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.005552011 년 3 월 9 일 업데이트 종료 .
INSERT 벤치 마크
벤치 마크의 마지막 부분은 insert 문의 실행입니다.
힙 / 클러스터 (…) 값에 삽입 (…), (…), (…), (…), (…), (…)
이 벤치 마크의 결과는 다음과 heap같습니다.
rows reads CPU Elapsed
----- ----- ----- --------
6 38 0ms 31ms2011 년 3 월 9 일 업데이트 :
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;
- 912 문에> 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.004402011 년 3 월 9 일 업데이트 종료 .
이 벤치 마크의 결과는 다음과 clust같습니다.
rows reads CPU Elapsed
----- ----- ----- --------
6 50 0ms 18ms2011 년 3 월 9 일 업데이트 :
string str = @"insert into clust (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;
- 946 개의 명령문은> 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.006142011 년 3 월 9 일 업데이트 종료 .
결론
클러스터 된 인덱스와 비 클러스터형 인덱스 (비 클러스터형 인덱스를 사용하는 동안)로 테이블에 액세스 할 때 더 많은 논리적 읽기가 진행되지만 성능 결과는 다음과 같습니다.
- SELECT 문은 비교 가능
- 클러스터 된 인덱스가 있으면 UPDATE 문이 더 빠릅니다.
- 클러스터 된 인덱스가 있으면 DELETE 문이 더 빠릅니다.
- 클러스터형 인덱스가 있으면 INSERT 문이 더 빠릅니다.
물론 내 벤치 마크는 특정 종류의 테이블과 매우 제한된 쿼리 집합으로 매우 제한되었지만이 정보를 기반으로 테이블에서 클러스터형 인덱스를 만드는 것이 실제로 항상 더 낫다는 것을 이미 시작할 수 있다고 생각합니다.
2011 년 3 월 9 일 업데이트 :
추가 된 결과에서 볼 수 있듯이 제한된 테스트에 대한 결론은 모든 경우에 정확하지 않았습니다.
결과는 이제 클러스터형 인덱스의 이점이있는 유일한 명령문이 업데이트 명령문임을 나타냅니다. 다른 인덱스는 클러스터 된 인덱스가있는 테이블에서 약 30 % 느립니다.
힙 대 클러스터에 대한 쿼리 당 가중치 지속 시간을 표시 한 일부 추가 차트.
보시다시피 insert 문의 성능 프로필은 매우 흥미 롭습니다. 급증은 완료하는 데 시간이 오래 걸리는 몇 가지 데이터 요소로 인해 발생합니다.
2011 년 3 월 9 일 업데이트 종료 .
답변
인덱싱의 여왕 – – 킴벌리 트립으로 아주 능숙하게 그녀를 포스트 블로그에 설명 클러스터 된 인덱스 토론을 … 계속 거의 속도까지 데이터베이스 테이블의 클러스터링 키를 가진, 모든 작업 – 단지 SELECT.
좋은 클러스터링 키 (예 🙂 를 선택하기 만하면 SELECT는 일반적으로 클러스터 된 테이블에 비해 힙에서 느려집니다 INT IDENTITY. GUID 또는 가변 길이 구성 요소가 많은 복합 키와 같이 실제로 나쁜 클러스터링 키를 사용하면 힙이 더 빠를 수 있습니다. 그러나이 경우 데이터베이스 디자인을 처음부터 정리해야합니다.
따라서 일반적으로 힙에 어떤 점이 없다고 생각합니다. 유용하고 유용한 클러스터링 키를 선택하면 모든면에서 이점이 있습니다.
답변
그냥 건너 우연히 이 글 주소이 질문에 그 조 장에서합니다. 아래에 그의 결론을 붙여 넣었다.
인덱스에 깊이가 4 인 테이블을 고려하여 루트 레벨, 2 개의 중간 레벨 및 리프 레벨이 있습니다. 인덱스는 단일 인덱스 키 (키 검색 없음)를 검색하여 4 개의 논리 IO (LIO)를 생성합니다. 이제 키 조회가 필요한지 고려하십시오. 테이블에 깊이 4의 클러스터 된 인덱스가있는 경우 각 키 조회는 4 개의 LIO를 생성합니다. 테이블이 힙인 경우 각 키 조회는 1 개의 LIO를 생성합니다. 실제로, 힙에 대한 키 조회는 4 : 1 LIO 비율에 가까운 곳이 아니라 클러스터 된 인덱스에 대한 키 조회보다 약 20-30 % 저렴합니다.