인덱스 구성과 상황에 따른 인덱스 성능

인덱스 구성과 상황에 따른 인덱스 성능

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 에서 클러스터 인덱스의 효율적인 설계는 SQL Server 테이블에 많은 성능 향상을 가져올 수 있다. 그러나 일부 쿼리에서는 비클러스터 인덱스가 클러스터인덱스보다 더 나은 성능을 나타낸다.

 

클러스터 인덱스에 비클러스터 인덱스를 생성하여 개선 할 수 있는 몇 가지 상황에 대해 알아보자.

 

클러스터 인덱스와 비클러스터 인덱스가 같은 열에 있을 때 업데이트, 삽입, 삭제는 디스크의 추가 공간 할당으로 인하여 성능이 저하된다. 이러한 단점으로 인덱스를 추가할 때 넌클러스터 인덱스를 추가하기 전에 고려해야 한다.

 

클러스터된 인덱스는 논리적으로 인덱스 키를 기준으로 전체 테이블을 구성 한다. 클러스터형 인덱스가 없는 경우 테이블이 저장되는 방식은 데이터가 정렬되지 않은 힙에 저장 된다.

 

넌클러스터 인덱스는 테이블에서 개별적으로 존재 한다. 이는 완전한 데이터가 포함된 행에 대한 포인터를 저장한다. 더 작은 인덱스 공간으로 완전한 정보를 얻을 수 있기에 상황에 따라 더 빠른 검색을 할 수 있다.

 

다음 스크립트는 인덱스 유형에 따른 성능 차이를 비교하기 위한 테스트 환경이다. (데이터 사이즈가 크고 생성 시간이 오래 걸림을 미리 알려둔다.) 동일한 4개의 정크 테이블을 만들고 각기 다른 인덱스를 설정한다.

• No indexes
• Clustered index created along with the primary key
• Only a non-clustered index
• Both clustered and non clustered indexes

 

/* This script will generate the tables used for testing clustered and nonclustered indexes and populate them. This can take quite a while to run. Almost 20 minutes on my test machine. */   -- First, create a table with no indexes as a control.   if OBJECT_ID('noIndex', 'U') is not NULL drop table dbo.noIndex   CREATE TABLE dbo.noIndex( n int NOT NULL, -- part of index singleChar char(1) NOT NULL, -- part of index stringData char(2000) NOT NULL, bigIntData bigint NOT NULL, decimalData decimal(18, 0)NOT NULL )   GO   --Populate the table with data insert into dbo.noIndex(n, singleChar, stringData, bigIntData, decimalData) select top 1000000 --1 Million, large enough to take some time on searches row_number() over (order by s1.name) as n, CHAR((row_number() over (order by s1.name) % 89) + 33) as singleChar, REPLICATE(CHAR((row_number() over (order by s1.name) % 89) + 33), 2000) as stringData, row_number() over (order by s1.name) * 1000000000 as bigIntData, row_number() over (order by s1.name) *1.1 as decimalData from master.dbo.syscolumns s1, master.dbo.syscolumns s2   ------ --Now create one with a primary key, which will automatically create the clustered index   GO   if OBJECT_ID('pkIndex', 'U') is not NULL drop table dbo.pkIndex   CREATE TABLE dbo.pkIndex( n int NOT NULL, singleChar char(1) NOT NULL, stringData char(2000) NOT NULL, bigIntData bigint NOT NULL, decimalData decimal(18, 0) NOT NULL, constraint PK_pkIndextable primary key clustered (n, singleChar) )   GO   --Populate the table with data insert into dbo.pkIndex(n, singleChar, stringData, bigIntData, decimalData) select n, singleChar, stringData, bigIntData, decimalData from dbo.noIndex     -------------- --Just the non_clustered index GO   if OBJECT_ID('nonclusteredIdx', 'U') is not NULL drop table dbo.nonclusteredIdx   CREATE TABLE dbo.nonclusteredIdx( n int NOT NULL, singleChar char(1) NOT NULL, stringData char(2000) NOT NULL, bigIntData bigint NOT NULL, decimalData decimal(18, 0) NOT NULL )   GO   --Populate the table with data insert into dbo.nonclusteredIdx(n, singleChar, stringData, bigIntData, decimalData) select n, singleChar, stringData, bigIntData, decimalData from dbo.pkIndex     create unique nonclustered index nonclusteredIdx_n on dbo.nonclusteredIdx (n, singleChar)   -------------- --Just table with both indexes GO   if OBJECT_ID('bothIdx', 'U') is not NULL drop table dbo.bothIdx   GO   CREATE TABLE dbo.bothIdx( n int NOT NULL, singleChar char(1) NOT NULL, stringData char(2000) NOT NULL, bigIntData bigint NOT NULL, decimalData decimal(18, 0) NOT NULL, constraint PK_bothIdx primary key clustered (n, singleChar) )   --Populate the table with data insert into dbo.bothIdx(n, singleChar, stringData, bigIntData, decimalData) select n, singleChar, stringData, bigIntData, decimalData from dbo.pkIndex   create unique nonclustered index both_nonclusteredIdx_n on dbo.bothIdx (n, singleChar)   -------------------------------------------- --Playing around with some queries   select n, singleChar from dbo.bothIdx where n % 10 = 0   select n, singleChar from dbo.bothIdx where singlechar = 'a'   select n, singleChar from dbo.noIndex where n % 10 = 0   select n, singleChar, bigIntData from dbo.nonclusteredIdx where n % 10 = 0   select n, singleChar, bigIntData from dbo.pkIndex where n % 10 = 0 order by n desc

 

 

 

생성된 데이터에서 간단한 카운트를 실행 하는 경우 클러스터 인덱스가 비클러스터 인덱스보다 비싼 비용을 나타내었다.

select count(*) from dbo.nonclusteredIdx select count(*) from dbo.pkIndex

 

 

 

다음 그림은 원문의 저작자가 파이선을 이용하여 테스트한 결과를 나타내었다. (테스트 파이선 코드는 원문을 참고 한다.) 테스트한 결과 비클러스터형 인덱스가 가장 빠른 결과를 나타내었다.

 

 

필자도 쿼리에서 set statistics time on을 설정하여 실제 쿼리 실행 시간을 비교해 보았을 때 비클러스터형 인덱스가 가장 빠른 속도가 나왔다.

set statistics time on select count(*) from dbo.nonclusteredIdx select count(*) from dbo.pkIndex select count(*) from dbo.bothIdx select count(*) from dbo.noIndex

 

 

 

Select 구문이 인덱스에 의해 커버될 경우에도 비클러스터형 인덱스가 더욱 빠른 성능을 나타내었다.

 

 

물론 클러스터 특정 키 값에 대한 검색에는 클러스터 인덱스의 경우 매우 빠른 성능을 나타내었다.

select * from dbo.bothIdx where n = 5 select * from dbo.nonclusteredIdx where n = 5

 

 

 

이처럼 클러스터 인덱스와 비클러스터 인덱스는 서로 다른 상황에서 최고의 성능을 나타낸다. 따라서 클러스터형 인덱스에 비클러스터형 인덱스를 만드는 것은 특정 작업의 유형에 도움이 될 수 있지만 인덱스 특성에 따른 추가 오버헤드에 대한 균형을 잘 고려해야 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3041/when-sql-server-nonclustered-indexes-are-faster-than-clustered-indexes/

 

 

 

2013-10-04 / 강성욱 / http://sqlmvp.kr