복합 인덱스 컬럼 순서

1. 준비

USE Northwind; 

SELECT *
INTO TestOrders
From Orders;

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

-- 더미 데이터를 늘리는 작업 ( 830 * 1000 )
WHILE(@i < 1000)
BEGIN
	INSERT INTO TestOrders(CustomerID, EmployeeID, OrderDate)
	SELECT CustomerID, @emp + @i, OrderDate
	FROM Orders;
	SET @i = @i + 1;
END

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개 생성하였다.

그리고 충분한 비교가 되게 데이터의 크기를 1000배로 늘려주었다.

 

2. 순서에 따른 비교

CASE1) 단순하게 순서만 바꾸기

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

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

idx_emp_ord : 논리적 읽기 5

idx_ord_emp : 논리적 읽기 5

결론, 단순값만 비교할 때는 순서에 구애받지 않는다.

 

CASE2) WHERE조건을 범위로 설정하기

SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';
-- OrderDate >= '19970101' AND OrderDate <= '19970103'같은 방법으로도 사용할 수 있음

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

idx_emp_ord : 논리적 읽기 5

idx_ord_emp : 논리적 읽기 16

차이가 발생하는 이유는 Index(a, b, c)로 구성되어 있을 때 선행에 between을 사용하면 후행은 인덱스 기능을 상실한다.
예를들어, a에 between이 걸리면 b와 c의 인덱스기능이 상실되고, b에 between이 걸리면 c의 인덱스기능이 상실한다.

 

CASE3) IN-LIST 활용

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

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

idx_emp_ord : 논리적 읽기 11

idx_ord_emp : 논리적 읽기 11

 

경우에 따라선 between보다 좋을 수도 안좋을 수도 있다.

between을 올바르게 사용하면 논리적 읽기가 5인 반면에 올바르게 사용하지 못하면 논리적 읽기가 16이 나오는데 IN-LIST를 활용할 때는 11로 동일하게 나온다.

결론적으로, 선행에 BETWEEN이 불가피하게 오는 경우에만 IN-LIST를 활용하면 좋다.