MSSQL 복합인덱스의 컬럼 순서가 성능에 미치는 영향
단순한 테이블이 아닌 매우 복잡한 테이블을 만들고, 수많은 데이터를 넣고 조인하고 검색하기를 하려면 복합 인덱스를 만드는 것은 불가피합니다. 이것저것 많은 인덱스를 잡아야하며, 여러 칼럼을 하나의 인덱스로 만들 때, 무조건 다 때려 넣는 것은 성능에 안 좋은 영향을 미칩니다.
복합 인덱스는 단일 칼럼으로 구성된 인덱스와 달리 다음과 같이 저장됩니다. 예를 들어서 카드결제 관련 테이블을 만들고 그 테이블에 로그를 쌓는다고 생각해 보겠습니다. 그리고 그곳에 인덱스는 "카드결제일","결제항목" (그냥 막 잡았으니 이해하시길 ^^...)으로 구성되어 있다고 생각해 보겠습니다.
실제로 데이터를 확인하면서 직접 해보도록 하겠습니다.
Chapter 1. 어떻게 쌓이는지 확인
|
-- 테이블 생성
CREATE TABLE TBL_CARD_1
(
Reg_Date DATETIME
,Payment VARCHAR(20)
,Amount INT
) GO
-- 인덱스 생성
CREATE CLUSTERED INDEX TBL_CARD_1 ON TBL_CARD_1(Reg_Date,Payment)
GO
-- 데이터 입력
INSERT INTO TBL_CARD_1 VALUES ('2016-06-10 08:01','김밥',1000)
INSERT INTO TBL_CARD_1 VALUES ('2016-06-10 12:32','영화관',20000)
INSERT INTO TBL_CARD_1 VALUES ('2016-06-10 08:01','피자',10000)
INSERT INTO TBL_CARD_1 VALUES ('2016-06-11 10:01','병원',20000)
INSERT INTO TBL_CARD_1 VALUES ('2016-06-11 14:01','축구화',70000)
INSERT INTO TBL_CARD_1 VALUES ('2016-06-12 17:01','영화관',20000)
INSERT INTO TBL_CARD_1 VALUES ('2016-06-12 19:11','데이트',40000)
GO
-- 데이터 확인
SELECT * FROM TBL_CARD_1 WITH(NOLOCK)
GO
|
cs |
테이블을 생성하고, 조회를 해보면 다음과 같이 데이터가 입력되어 있습니다.
아래의 데이터를 보면 우선 Reg_Date로 정렬이 되어 있으며, 그 다음으로 Payment로 정렬됩니다.
이것을 그림으로 표현하면 다음과 같이 표현됩니다. 블럭으로 보면 편할 거 같습니다.
그렇다면, 내가 06월 10일에 카센터를 갔다고 치고, 데이터를 입력하면 어떻게 될까요? 다음 그림과 같이 우선 날짜와 시간을 보고 날짜 데이터 쪽을 쭉 봅니다. 날짜가 2016-06-10일 중에 가장 마지막이라고 하겠습니다. 카센터는 2016-06-10의 마지막으로 들어 갑니다.
실제로 데이터를 넣어 보겠습니다.
|
INSERT INTO TBL_CARD_1 VALUES ('2016-06-10 15:32','카센터',20000)
|
cs |
다음과 같이 데이터가 실제로 그림과 같은 위치로 들어갔습니다. 결론적으로 정렬순서를 잡을 때 먼저 Reg_Date를 잡고, 그 뒤에 중복이면 Payment의 이름 순으로 정렬을 합니다.
Chapter 2. 언제 성능에 영향을 미칠까?
복합 인덱스를 생성할 때 순서가 중요한 이유는 인덱스를 타고 안타 고의 문제가 생기기 때문입니다.
다음의 예시를 보면 확실히 이해가 될 듯합니다.
좀 더 확실하게 보기 위해서 데이터가 좀 더 많은 테이블을 복사하겠습니다.
이 데이터는 AdventureWorks2012의 Person.Person 테이블을 복사한 테이블입니다. 쿼리는 다음과 같습니다.
|
USE AdventureWorks2012
GO
-- 테이블 복사
SELECT * INTO [Person].[Person_Temp] FROM [Person].[Person]
GO
SELECT * INTO [Person].[Person_Copy] FROM [Person].[Person]
-- 인덱스 생성
CREATE CLUSTERED INDEX NC_1 ON [Person].[Person_Temp](ModifiedDate,FirstName)
CREATE CLUSTERED INDEX NC_1 ON [Person].[Person_Copy](FirstName)
-- 데이터 조회
SELECT * FROM [Person].[Person_Temp] WHERE FirstName ='Rob'
SELECT * FROM [Person].[Person_Copy] WHERE FirstName ='Rob'
GO
|
cs |
테이블에서 Preson_Temp는 ModifiedDate , FirstName으로 이루어진 복합 인덱스를 구성하였으며, Person_Copy는 FirstName을 인덱스로 잡고 있습니다. 이럴 때 FirstName으로 조회를 한다고 치면 어떤 결과가 나올까요?
논리적 읽기 수와 실행계획에서의 비용 차이가 엄청난 차이를 보입니다. 분명히 인덱스에 둘 다 FirstName이 들어가 있는데, 왜 다르게 나타날까요? 이걸 그림으로 나타내서 어떻게 인덱스를 타는지 보면 이해가 잘 되실 겁니다.
Person_Temp의 데이터는 다음과 같은 형태로 인덱스가 만들어져서 정렬되어 있을 것입니다. 여기서 중요한 것은 칼럼의 순서입니다.
컬럼의 순서가 ModifiedDate가 먼저 나오는데, 인덱스를 살펴보았으나 FirstName은 그다음입니다. 그러므로, FirstName이 정렬되어 있지 않기 때문에 옵티마이저는 쭉 아래까지 뒤져야 합니다 왜냐면 "Rob"이 아래에도 없다고 장담할 수 없기 때문입니다. 그러므로 스캔이 일어날 수밖에 없는 것입니다.
Chapter 3. 해결법
어찌 보면 이것은 아주 간단히 해결 가능합니다. "그냥 인덱스를 다시 만들어"라고 하면 끝입니다. 인덱스를 만들 때 FirstName으로 만들면 그만 아니야? 할 수 있지만, 이 서버가 라이브 서버이며, 건수가 매우 많아서 인덱스를 재생성하기 어렵다면 어떻게 해야 할까요?
이럴 경우 WHERE 조건을 약간 손봄으로써 성능을 향상할 수 있습니다. 물론 이 방법은 계속해서 사용하는 것은 어느 정도 무리가 있습니다.
|
-- 데이터 조회
SELECT *
FROM [Person].[Person_Temp] WHERE FirstName ='Rob'
AND ModifiedDate >= '2001-01-01'
AND ModifiedDate < '2002-01-01'
|
cs |
위의 결과는 처음 우리가 조회한 결과와 차이가 조금 있습니다. ModifiedDate에 범위에 따라서 결과가 다릅니다.
Seek로 실행계획이 변경되었으며, 논리적 읽기 수도 현저하게 떨어졌습니다.
하지만 위에서 언급 하였 듯이 결괏값이 상의합니다.
이것은 말 그대로 일시적입니다. 하지만 이것을 계속 유지할 수 있는 방법도 있습니다.
이것을 조회하는 유저에게 월별로 데이터를 조회하도록 유도하고, 오랜 기간 (예를 들면 5~10년 치)을 조회할 경우 성능이 느릴 수 있다고 말할 수 있습니다. 실제 많은 쿼리들이 다음과 같은 방법으로 만들어지는 경우가 많습니다. 하여, 우리는 최대한 그 row를 줄이기 위해서 날짜 데이터를 작은 기간을 검색하도록 권고하는 경우가 많습니다.
경우에 따라서 다르겠지만, 대부분의 사람들이 10년 치 데이터를 한 번에 보고자 하지는 않습니다. (물론 제 경험...)
만약에 정말 많은 사람들이 날짜에 구애받지 않고 몇십 몇백만 건의 데이터를 오직 FirstName으로 조회하고자 한다면, 임시방편 방법을 사용한 뒤에 점검 시간에 인덱스를 재성 성해야 합니다. 하지만 그것이 아니라면, 이 방법을 계속해서 사용하여도 무리는 전혀 없습니다.
이것은 DBA가 비즈니스 로직을 기획자, 사용자와 협의하여 결정해야 할 문제입니다. 하지만 제일 베스트 한 방법은 처음부터 기획의도를 충분히 협의하여 적절하게 인덱스를 생성해줘야 할 것입니다.
감사합니다.
참조
서적 : SQL Server 51인 MVP Chapter 44 인덱스의 칼럼 순서가 문제가 되는가?