SQL (23) - 복합 인덱스의 컬럼 순서

이번 포스트에서는 하나의 인덱스에 여러 열을 종합하여 사용하는 복합 인덱스에 대해 알아보겠습니다.

그리고 그중에서도 복합 인덱스에 사용된 열들간의 컬럼 순서가 

복합 인덱스의 동작에 어떤 영향을 끼치는지 알아보겠습니다.

 

이를 위해 새로운 테이블을 생성해보겠습니다.

-- 새로운 테이블 생성
SELECT *
INTO TestOrders
FROM Orders;

DECLARE @i INT = 1;
DECLARE @emp INT;
SELECT	@emp = MAX(EmployeeID) FROM ORders;

-- 더미 데이터의 양을 늘린다 (830 * 1000)
WHILE(@i < 1000)
BEGIN
	INSERT INTO TestOrders(CustomerID, EmployeeID, OrderDate)
	SELECT CustomerID, @emp + @i, OrderDate
	FROM Orders;
	SET @i = @i + 1;
END

SELECT COUNT(*)
FROM TestOrders;

 

위와 같이 실험용으로 쓸 테이블을 하나 생성하였습니다.

생성된 테이블의 데이터 양을 기하급수적으로 늘려보았습니다.

최종적으로 테이블안의 데이터의 양은 다음과 같습니다.

 

다음으로 생성된 테이블에 2개의 Non-Clustered 인덱스를 생성해보겠습니다.

-- Non-Clustered 인덱스 생성(열의 순서를 바꾼 2개의 복합 인덱스 생성)
CREATE NONCLUSTERED INDEX idx_emp_ord
ON TestOrders(EmployeeID, OrderDate);

CREATE NONCLUSTERED INDEX idx_ord_emp
ON TestOrders(OrderDate, EmployeeID);

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

 

이제 2개의 복합 인덱스의 연산 결과를 통해

무슨 차이점이 있는지 알아보겠습니다.

 

1) 복합 인덱스에서의 열 순서로 인한 차이

-- 두 복합 인덱스를 비교
-- 첫 번째 Non-Clustered 인덱스
SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate = CONVERT(DATETIME, '19970101');

-- 두 번째 Non-Clustered 인덱스
SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate = CONVERT(DATETIME, '19970101');

 

두 쿼리를 실행하면 다음의 결과를 얻을 수 있습니다.

 

 

두 쿼리 모두 동일하게 5번의 논리적 읽기가 수행되었고,

Index Seek + RID Look UP의 방식으로 쿼리를 수행했다는 것을 알 수 있습니다.

 

실행 결과가 위와 같이 나온 이유는 다음과 같습니다.

-- EmployeeID로 정렬하되, 같다면 OrderDate로 정렬
SELECT *
FROM TestOrders
ORDER BY EmployeeID, OrderDate;

-- OrderDate로 정렬하되, 같다면 EmployeeID로 정렬
SELECT *
FROM TestOrders
ORDER BY OrderDate, EmployeeID;

 

 

 

위와 같이 구성되었기 때문입니다.

그렇다면 범위를 잡아서 데이터를 검색하는 경우에는 어떻게 동작하는지 알아보겠습니다.

 

2) 범위를 집어서 데이터를 검색

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';

SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';

 

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

 

방식은 두 쿼리 모두 Index Seek와 RID LookUp으로 동작하였지만,

 

위의 쿼리의 경우 논리적 읽기가 16번, 아래의 쿼리의 경우 논리적 읽기가 5번 수행되었습니다.

위와 같은 차이가 발생한 이유는 BETWEEN 연산자를 사용한것에 찾을 수 있습니다.

 

INDEX(a, b, c)로 구성된 복합 인덱스에서

BETWEEN 연산자에 a를 넣어주면 b와 c는 인덱스의 기능을 상실합니다.

따라서 예제에서 위의 쿼리에서의 EmployeeID는 인덱스의 기능을 상실하여 일일이 탐색하게 됩니다.

 

3) 범위를 잡아서 데이터를 검색 - BETWEEN 개선

BETWEEN 연산자 대신에 IN 연산자를 사용하면 조금이라도 연산의 양을 개선할 수 있습니다.

IN 연산자를 사용하면 매번 비교 연산자를 사용하여 일일이 순회하지 않게 됩니다.

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate IN ('19970101', '19970102', '19970103');

 

위의 예제에서의 쿼리를 위와 같이 개선하였습니다.

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

 

논리적 읽기의 양이 11로 감소한 것을 확인할 수 있습니다.

 

3) 복합 인덱스 사용시의 주의사항

복합 인덱스를 사용하는 경우에 인자의 순서가 쿼리의 연산에 영향을 줄 수 있다는 것을 명심해야 합니다.

특히 이러한 영향은 BETWEEN이나 부등호와 같은 비교 연산에 많은 영향을 줍니다.

 

비교 연산에 선행 열을 사용하면 후행 열은 인덱스로서의 기능을 상실하게 됩니다.

따라서 선행 열에 BETWEEN을 사용할때, 범위가 적으면 IN-LIST로 대체하여 사용하는 것이 좋습니다.
그러나 후행 열에 BETWEEN을 사용하는 것은 괜찮으므로 IN-LIST를 사용하지 않아도 문제없습니다.