SQL (19) - 복합 INDEX

이번 포스트에서는 INDEX를 여러 개 결합하여 사용하는 복합 INDEX에 대해 알아보겠습니다.

우선, 작업을 수행하기 전 사용할 임시 복합 인덱스를 만들겠습니다.

-- 임시 테스트 테이블을 만들고 해당 데이터 테이븡에 데이터를 복사하여 사용
SELECT *
INTO TestOrderDetails
FROM [Order Details];

-- 두 개의 열 OrderID와 ProductID를 합쳐 복합 인덱스를 만듬
CREATE INDEX Index_TestOrderDetails
ON TestOrderDetails(OrderID, ProductID);	

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

 

위 쿼리를 실행한 결과는 다음과 같습니다.

 

1) 복합 INDEX를 사용하여 페이지 가져오기

INDEX가 데이터 테이블에서 데이터를 가져오는 방식은 2가지가 존재합니다.

• INDEX SCAN : 인덱스가 관찰대상이 아니므로 인덱스를 전부 순회하여 인덱스를 탐색
• INDEX SEEK : 인덱스가 관찰대상이므로 인덱스를 바로 가져올 수 있음

위의 2가지 방법으로 구분할 수 있습니다.

이때, INDEX SCAN의 경우 인덱스를 전부 순회하므로 높은 검색비용을 소모하고

INDEX SEEK의 경우 인덱스가 관찰되고 있기에 데이터를 바로 가져와 낮은 검색비용을 소모합니다.

 

위와 같은 방식으로 인덱스가 작동하는 것을 알게 되었으므로 복합 인덱스를 사용해보도록 하겠습니다.

현재 복합 인덱스는 (OrderID, ProductID)로 구성되어 있으며,

차례대로 두 개의 인덱스 모두 사용 / OrderID만 사용 / ProductID만 사용하는 방식으로 진행합니다.

 

1 - 1) 두 개의 인덱스 모두 사용

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

 

위 쿼리의 수행비용은 다음과 같습니다.

 

SEEK로 동작하여 적은 검색 비용을 사용하는 것을 알 수 있습니다.

 

1 - 2) OrderID 인덱스만 사용

SELECT *
FROM TestOrderDetails
WHERE OrderID = 10248;

 

위 쿼리의 수행비용은 다음과 같습니다.

 

마찬가지로 SEEK로 동작하는 것을 확인할 수 있습니다.

 

1 - 3) ProductID 인덱스만 사용

SELECT *
FROM TestOrderDetails
WHERE ProductID = 11;

 

위 쿼리의 수행비용은 다음과 같습니다.

 

위의 2개의 쿼리와는 다르게 SCAN으로 동작하는 것을 확인할 수 있습니다.

 

1 - 4) 위와 같은 문제가 발생하는 이유

우선 문제가 발생하는 복합 인덱스를 살펴보겠습니다.

-- INDEX 데이터 검색
DBCC IND('Northwind', 'TestOrderDetails', 2);

 

실행 결과는 다음과 같습니다.

 

복합 인덱스의 트리를 구성하면 다음과 같이 구성할 수 있습니다.

 

위와 같은 상황에서 페이지를 하나 자세히 살펴보겠습니다.

DBCC PAGE('Northwind', 1, 1016, 3);

 

실행결과는 다음과 같습니다.

 

실행결과를 살펴보면,

데이터들이 OrderID를 기준으로 정렬되어 있고,

만약 OrderID가 동일한 경우에는 ProductID로 정렬되어 있는 것을 확인할 수 있습니다.

따라서 위와 같은 복합 인덱스에서 두 번째 인덱스 인자로 데이터를 검색하면

정렬되어 있지 않은 데이터를 순회하게 되어 많은 검색 비용을 소모합니다.

따라서 복합 인덱스를 사용한다면,  복합 인덱스를 구성하는 인덱스의 순서에 유의하여 사용해야 합니다.

 

2) 인덱스의 페이지 분할

인덱스의 경우 데이터가 추가 / 갱신 / 삭제되어도 유지되어야 합니다.

다시 말해서, 위에서 확인할 수 있는 인덱스 트리가 깨지는 일 없이 유지되어야 한다는 뜻입니다.

따라서 기존의 복합 인덱스에 데이터 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개 추가 후 다시한번 인덱스 정보 확인
DBCC IND('Northwind', 'TestOrderDetails', 2);

 

위 쿼리의 실행 결과는 다음과 같습니다.

 

위 인덱스 트리를 그림으로 표현하면 다음과 같습니다.

 

하나의 페이지에서 저장가능한 범위를 넘어서는 양의 데이터가 추가되는 경우,

페이지를 분할하여 저장하는 것을 확인할 수 있습니다.

 

3) 인덱스 가공

우선, 실습을 위한 임시 테이블과 해당 임시 테이블에서 사용할 인덱스를 추가하였습니다.

-- 가공을 위한 임시 테이블 추가
SELECT LastName
INTO TestEmployees
FROM Employees;

-- 임시 테이블에 인덱스 추가
CREATE INDEX Index_TestEmployees
ON TestEmployees(LastName);

 

다음으로 인덱스를 가공하여 특정 데이터를 검색하는 로직을 만들겠습니다.

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

 

위 쿼리의 실행비용은 다음과 같습니다. Scan되어 실행비용이 많이 소모되는 것을 확인할 수 있습니다.

 

위 쿼리에서 소모하는 실행비용을 줄이고 싶다면,

다음과 같이 인덱스를 가공하지 않는 로직으로 바꿔서 수행하면 됩니다.

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

 

4) 인덱스를 사용하는 경우 주의사항

이번 포스트의 핵심을 정리하면 다음의 3가지로 정리할 수 있습니다.

• 복합 인덱스를 사용할 때 결합된 인덱스의 순서애 주의하여 사용할 것
• 인덱스에서는 페이지를 자동분할하므로 페이지의 용량에 걱정하지 말것
• 되도록 인덱스의 키를 가공하지 않는 방향으로 로직을 구성할 것