복합 인덱스

1. 복합 인덱스 생성

복합 인덱스란? 인덱스를 걸 때 여러 컬럼을 동시에 걸어버리는 것이 복합인덱스이다.

 

먼저 테스트용 테이블을 만든다.

USE Northwind;

SELECT *
INTO TestOrderDetails
FROM [Order Details];

SELECT *
FROM TestOrderDetails;

 

-- 복합 인덱스 추가
CREATE INDEX Index_TestOrderDetails
ON TestOrderDetails(OrderID, ProductID);

-- 인덱스 정보 살펴보기
EXEC sp_helpindex 'TestOrderDetails';

인덱스가 OrderID와 ProductID가 순서대로 잘 저장되어 있음을 잘 확인할 수 있다.

 

2. 복합 인덱스 사용순서

그렇다면 궁금한점은 복합인덱스를 걸면 서칭(Searching)이 어떻게 일어나는가이다.

두 개의 인덱스가 있으므로 둘다 사용(OrderID, ProductI0D)하는 것과 OrderID만 사용, ProductID만 사용하는 총 3가지의 경우를 예로 들어보자.

 

실험하기 앞서 구문을 실행하고 Ctrl+L 을 누르면 성능을 분석할 수 있는데

INDEX SEEK가 나오면 성능이 좋다는 뜻이다.

INDEX SCAN이 나오면 성능이 안좋다는 뜻이다.

 

CASE1) 둘 다 사용(OrderID, ProductID)

SELECT *
FROM TestOrderDetails
WHERE OrderID = 10248 AND ProductID = 11;

Ctrl + L 결과창

 

CASE2) OrderID만 사용

SELECT *
FROM TestOrderDetails
WHERE OrderID = 10248;

Ctrl + L 결과창

 

CASE3) ProductID만 사용

SELECT *
FROM TestOrderDetails
WHERE ProductID = 11;

Ctrl + L 결과창

CASE1, 2의 경우 INDEX SEEK가 뜨는데 CASE3의 경우에는 INDEX SCAN이 뜨는 것을 볼 수 있다.

이유는 인자의 전달 순서가 원인이다.

 

 

-- 복합 인덱스 추가
CREATE INDEX Index_TestOrderDetails
ON TestOrderDetails(OrderID, ProductID);

위에서 복합 인덱스를 추가했을 때 OrderID의 인자를 먼저 전달하였고 그 다음으로 ProductID의 인자가 전달되었다.

그런데 데이터가 정렬될 때는 OrderID가 정렬되어야지만 ProductID를 정렬할 수 있다.

CASE3의 경우에는 ProductID만을 쓰기 때문에 데이터가 정렬이 안되는 것이다. 정렬이 안되었기 때문에 테이블의 모든 데이터를 처음부터 끝까지 살펴보는 INDEX SCAN이 뜨는 것이다.

 

결론은 복합인덱스를 사용할 때 왼쪽의 인자부터 차례대로 사용을 해줘야한다는 것이다.

 

3. 페이지 분할

-- INDEX 정보
DBCC IND ('Northwind', 'TestOrderDetails', 2);

앞서 '인덱스'파트에서 페이지번호를 살펴보는 법을 익혔다. 이것을 트리구조로 살펴보면

                888
 832  856 857 858 859 860

위의 구조로 저장되어 있다.

 

 

궁금한 점은 인덱스는 데이터가 추가/갱신/삭제 되어도 유지되어야 한다. 그래서 데이터 50개를 강제로 넣으면 발생하는 일을 봐보자.

DECLARE @i INT = 0;
WHILE( @i < 50 )
BEGIN
	INSERT INTO TestOrderDetails
	VALUES (10248, 100 + @i, 10, 1, 0);
	SET @i = @i + 1;
END

반복문을 이용해 50개의 데이터를 넣어주었다.

 

 

-- INDEX 정보
DBCC IND ('Northwind', 'TestOrderDetails', 2);

889의 페이지가 새롭게 추가된 것을 볼 수 있다.

                888
 832 889(New!) 856 857 858 859 860

50개의 데이터를 추가 했을 때 832페이지안에는 인덱스의 허용범위를 넘어서게 되는데 이 때 832페이지 안의 일부 데이터가 새롭게 생성된 889에 추가가된다.

이러한 과정을 페이지 분할(Page Split)이라고 한다.

 

4. 인덱스 가공

인덱스 가공이란 인덱스 데이터를 그대로 이용하지 않고, 예를들어 인덱스 이름의 앞 2글자만 추출해서 사용하는 경우를 인덱스 가공이라고 한다.

EX) 앞의 2글자만 추출해서 사용(인덱스 가공)

SELECT *
FROM TestEmployees
WHERE SUBSTRING(LastName, 1, 2) = 'Bu';

이처럼 SUBSTRING()과 같이 함수 내부를 알 수 없는 경우에는 인덱스가 가공될 수 있기 때문에 조심해서 사용해야 한다.

 

EX) 가공을 거치지 앞의 2글자만 추출

SELECT *
FROM TestEmployees
WHERE LastName LIKE 'Bu%';

LIKE를 이용해서 인덱스 앞의 두 글자를 추출하면 가공 과정을 거치지 않기 때문에 INDEX SEEK가 뜬다.

위의 경우를 교훈삼아서 INDEX SCAN이 뜨지 않도록 인덱스의 실행계획을 살펴보는 습관을 가져야한다.