with SQL Server

오늘 얘기할 부분은 복합 인덱스 설정 시 발생할 수 있는 성능 차이에 대해서 말해 보겠습니다.

가끔 Primary Key 또는 인덱스 설정 시에 하나의 컬럼이 아닌 다수 컬럼을 묶어 인덱스를 설정하는 경우가 있습니다.

이때 컬럼 순서에 따라 의도하지 않게 성능 저하가 일어날 수 있습니다.

예를 들어 설명해 보죠.

col1, col2, col3 이라는 컬럼이 존재하고 3개의 컬럼이 PK로 잡혀야 하는 비즈니스 입니다.

처음으로 할 테스트는 컬럼 순서대로 인덱스를 설정한 경우입니다.

--테이블생성

CREATE TABLE dbo.PKDesign_Test (

        col1 INT  not null,

        col2 INT IDENTITY(1, 10) not null,

        col3 CHAR(3) not null )

--컬럼순서대로PK 설정 

ALTER TABLE dbo.PKDesign_Test ADD  CONSTRAINT [PK_PKDesign_Test] PRIMARY KEY CLUSTERED

(

        col1, col2, col3 ASC

)

--테스트데이터넣기

DECLARE @i INT = 1

WHILE @i < 1000       

        BEGIN

                   INSERT INTO dbo.PKDesign_Test(col1, col3)

                   SELECT @i, REPLICATE(LEFT(NEWID(), 1), 3)

                   SET @i = @i + 1

        END

그런데 아래와 같이 해당 테이블을 조회할 때 사용되는 필터링 컬럼은 col2, col3라고 하는군요.

SELECT col1, col2, col3

FROM dbo.PKDesign_Test

WHERE col2 > 5000 and col3 = 'EEE'

위 실행계획에서 보시는 것과 같이 복합 인덱스 첫 번째 컬럼이 필터조건에 존재하지 않으면,

Index Scan 즉 Full Scan이 일어나 인덱스를 사용할 수 없습니다.

결국 인덱스를 설정했지만 사용하지도 못하는 인덱스가 돼버렸네요.

그렇기 때문에 인덱스 설정 시 필터 대상 컬럼은 반드시 첫 번째 위치에 존재해야 합니다.

아마 이건 다 아시는 내용일 수도 있겠네요.

사실 제가 말하고자 한 내용은 이제부터 입니다.

Col2, col3를 필터조건으로 걸 때에도 두 컬럼의 순서에 따라 성능 차이가 있을 수 있습니다.

그럼 col2 컬럼이 첫 번째인 인덱스와 col3 컬럼이 첫 번째인 인덱스를 갖는 테이블을 각각 생성해 보겠습니다.

CREATE TABLE dbo.PKDesign_Test1 (

        col1 INT  not null,

        col2 INT IDENTITY(1, 10) not null,

        col3 CHAR(3) not null );

--col2 컬럼이첫번째     

ALTER TABLE dbo.PKDesign_Test1 ADD  CONSTRAINT [PK_PKDesign_Test1] PRIMARY KEY NONCLUSTERED

(

        col2, col3, col1 ASC

);

CREATE TABLE dbo.PKDesign_Test2 (

        col1 INT  not null,

        col2 INT IDENTITY(1, 10) not null,

        col3 CHAR(3) not null );

--col3 컬럼이첫번째     

ALTER TABLE dbo.PKDesign_Test2 ADD  CONSTRAINT [PK_PKDesign_Test2] PRIMARY KEY NONCLUSTERED

(

        col3, col2, col1 ASC

);

--dbo.PKDesign_Test 테이블에서데이터복사

INSERT INTO dbo.PKDesign_Test1(col1, col3)

SELECT col1, col3

FROM dbo.PKDesign_Test;

INSERT INTO dbo.PKDesign_Test2(col1, col3)

SELECT col1, col3

FROM dbo.PKDesign_Test;

테이블 생성과 동일한 데이터를 두 테이블에 입력하였습니다.

그럼, 아래와 같이 각각의 테이블에 동일한 쿼리를 만들어 실행계획을 살펴 볼까요?

SELECT col1, col2, col3

FROM dbo.PKDesign_Test1

WHERE col2 > 5000 and col3 = 'EEE'

SELECT col1, col2, col3

FROM dbo.PKDesign_Test2

WHERE col2 > 5000 and col3 = 'EEE'

위 실행계획을 보면 필터 조건에 복합 인덱스 첫 번째 행이 포함되어 있기 때문에

두 쿼리 모두 Index Seek(인덱스에서 행 검색)을 확인하실 수 있습니다.

다만, 쿼리 비용은 58%와 42%로 차이가 발생합니다.

실행계획을 자세히 보도록 하겠습니다.

            [PKDesign_Test1] 테이블                           [PKDesign_Test2] 테이블

보시면 아시겠지만 Test1 테이블(col2 첫번째 컬럼)은 인덱스 검색을 col2 > 5000 보단 큰 값을

쭉~ 검색하면서 col3 = ‘EEE’ 인 Row을 찾아 냅니다.

이 와는 다르게 Test2 테이블(col3 첫번째 컬럼)은 인덱스 검색을 col3 = ‘EEE’ 인 값을 우선 찾고

그 중에 col2 > 5000 인 Row를 찾아 냅니다.

어떤 게 더 유리할까요?

Col2 > 5000 인 rows는 499개 이며, col3 = ‘EEE’ 인 rows는 63개 입니다.(실제 테이블이 더 크다면 차이도 더 클 겁니다.)

당연히 비교적 낮은 row 수를 갖는 col3 컬럼을 먼저 찾아 내는 게 유리합니다.

보통 범위연산(부등호) 보다 ‘=’ 연산이 좁은 범위의 rows을 갖기 때문에

복합 인덱스 설정 시에는 컬럼 순서 조정을 고려해서 디자인하는 것이

성능에 매우 유리합니다.

끝~