출처 : http://sisseo.tistory.com/8
오늘 얘기할 부분은 복합 인덱스 설정 시 발생할 수 있는 성능 차이에 대해서 말해 보겠습니다.
가끔 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을 갖기 때문에
복합 인덱스 설정 시에는 컬럼 순서 조정을 고려해서 디자인하는 것이
성능에 매우 유리합니다.
끝~
