데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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/

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1432
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1666
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2220
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1681
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2786
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2009
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1160
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 3904
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1344
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1445
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1268
» 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1456
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2564
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2333
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 1963
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2015
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1834
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3362
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2232





XE Login